POST requests to a PostgreSQL & PostGIS database to retrieve or update data.
For a simple query like
Previously, if I wanted to keep the SQL human readable in my JS code I would’ve done something like:
You have to be really careful when doing this and not to mention it’s pretty ugly, right? With ES6 template strings the above can simply become:
That’s much easier to write, read, and to avoid syntax errors in your SQL. There’s one catch though, if we were to log that template string as it is right now it would look like:
That’s not how we’d want to pass it off as part of the body to a
GET request to our database. Although CartoDB’s API is fairly good a removing extra white space, the newline (
\n) characters would screw up our query once the API attempts to run it on our database. However, there’s an easy way to deal with this problem: use a function that strips out those pesky
\n’s and indentation space so that the template string becomes a single line string.
Thanks to stumbling across a terrific blog post by the developer Stuart Colville, that function ends up looking like:
It could also work in the browser without using Babel by writing it like this:
To apply it to the above query we’d do this:
Which then outputs a single line of text, the format we’d want it in to pass off to CartoDB’s SQL API:
Tah dah! Another neat trick that I mentioned above when using the
IN operator with an array of values may be demonstrated as follows:
Using ES5 you would first have to convert your array into a string by doing something like
values.join(',');. However with template strings you can just pass the array in as is! The above template string becomes:
This method is especially useful when creating an
UPSERT query on the fly. In my case I needed to run a script on a scheduler that stores the most recent data from another API in a CartoDB table and was very happy to not have to use string concatenation.
So there you have it! If you’re not using ES6 with Babel yet you can still give it a try using Node.JS, on v4.4.2+. I encourage you to give it a try if you haven’t already.
Happy SQL + JS ‘ing!
If you found this website to be helpful please consider showing your gratitude by buying me a coffee. Thanks!