Templated SQL

I’ve recently been working on a data migration SQL script that performs the same operations against numerous tables. While I could use dynamic SQL – where SQL statements are built as a string and then executed – I don’t particularly like the downsides:

• It cannot be checked at compile time
• It is difficult to read, debug and reason about
• I’d have to use a cursor to iterate through each of the table names

Since this was a one-off migration task, I decided to render the SQL dynamically ahead of time and wrote a little utility in Node.js. So the template contained the SQL statements I wanted to execute, while the data contained a single column with the table names. For example:

data.csv

table
tblA
tblB
tblC

template.mst

ALTER TABLE {{table}} ADD col VARCHAR(50);

Output

ALTER TABLE tblA ADD col VARCHAR(50);
ALTER TABLE tblB ADD col VARCHAR(50);
ALTER TABLE tblC ADD col VARCHAR(50);

Wrap the output in a BEGIN/COMMIT TRANSACTION and we’re done.