There is one project (still tinkering- don’t judge too harshly!) that necessitates writing old ES5 style syntax, and it’s not much fun. We’re looking at bolting on a countdown timer feature to an older system (that is in the process of being massively upgraded, but that will take time). It’s pretty simple, but gets pretty ugly around the string templating aspect.
Anyway, that’s the project that got me thinking about strings. In the old days, if you needed to include a variable in your string, fragments went into quotes, then out of quotes with a ‘+’ operator to concatenate your variable, then back into quotes. If something you were adding onto the string had to remain a number type, you had to be very careful not to add two of them together back to back (or instead of 1 + 1 = 2, you’d get ‘1’ + ‘1’ = ’11’).
But there’s always a but. No matter how well designed or tested or coded your software is, someone will find a way to break it, misuse it, or complain about it. That even applies to a software language itself. In this case, trying to use the latest and greatest (ES6) with the old and busted (SQL queries over my database schema) caused a breakdown (of my code and my mental state).
The case: I’m currently working to update on old PHP/MySQL application to run on Node. I’m using the awesome mysql node library to make calls to the database. The database information can’t be changed- the application already has many users, and just dumping their data and starting over is not an option.
The mysql library lets me send those SQL queries to my database and gives me back the data I need (it even has a great ‘escape’ function to improve security on those requests). But it doesn’t fix the difficulties with strings in SQL, and it doesn’t fix a 15 year old naming conflict in our schema.
When the application was created, a column in some tables was dedicated to the order of items (menu items, or pages, etc). Unfortunately, this column was titled “order”. Also unfortunately, “order” is a reserved keyword in SQL (used to sort your results). In the old PHP code, the query string was just concatenated together over many lines. It’s really a forest of single quotes, periods (the PHP string concat operator), double quotes, and backticks. It’s hard to follow, so I decided to use ES6 string templating in my awesome new update. But every query that included “order” blew up in my face. And SQL errors are not exactly helpful. Think: “You have an error in your sql syntax around ‘some random place in my query string here'”.
Turns out, if you have a reserved keyword as a column title, you need to escape it in a query with backticks. You might be able to use single quotes, I’m not sure, but either way that won’t work inside an ES6 template string (at least, it didn’t work when I tested).
So, for requests to those specific tables, it’s back to queries like: “SELECT * FROM my_table WHERE id = ” + id + ” AND ‘order’ = ” + order; That’s a simple example- one query has to get the next available “order” value (the table wasn’t set up as auto increment), so first we have to get the max(order), then insert that variable into the “order” column. Again, ES6 is awesome, but I’ve somehow found a way to have to go back to the old days!