1

I have a Node.js app that is handling database build / versioning by reading multiple .sql files and executing them as transactions.

The problem I am running into is that these database build scripts require a lot of GO statements, as you cannot execute multiple CREATEs, etc. in the same context.

However, GO is not T-SQL and errors when used outside of a Microsoft application context.

Take the following shorthand:

CREATE database foo /* ... */
use [foo]
CREATE TABLE bar /* ... */

This would error if GO statements were not injected between each line.

I would rather not break this into multiple .sql files for every separate transaction - I am building a database and that would turn into hundreds of files!

I could run String.split() functions on all go statements and have node execute each as a separate transaction, but that seems very hacky.

Is there any standard or best-practice solution to this type of problem?

Update

Looks like a semicolon will do the trick for everything except CREATE statements on functions, stored procedures, etc. Doesn't apply to tables or databases, though which is good.

Community
  • 1
  • 1
dthree
  • 19,847
  • 14
  • 77
  • 106
  • Just use the standard SQL statement termination character: `;` https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons –  May 18 '15 at 23:03
  • You think this'll work? This blog post is more about CTEs and so I don't know if its totally applicable, but I'll give it a shot. – dthree May 18 '15 at 23:15
  • All `GO` does is provide a way for the application to split the entire query into smaller queries. So using a string split is not a hack at all. – DavidG May 18 '15 at 23:31
  • @DavidG Okay - I guess I was just worried as I would have to get smart on how to do it. For example, `GO` might not have spaces to the left or right, but you don't want to split on the string 'pogostick' either. I'd have to read line by line, parse out comments from the line, do a trim, and then check if `GO` is the only remaining characters in that line. That seems like I'm reinventing someone's wheel, but I can do it. – dthree May 18 '15 at 23:36
  • Well if you have control over the SQL file, you don't need to use `GO`. Instead use your own custom string, for example `###===###` or something you know will not be used elsewhere. – DavidG May 18 '15 at 23:41
  • For those commands can be executed in same batch, you can put into a stored procedure (you can create and drop them dynamically). To create a database and go the database and do something, I think you are out of luck. – Tim3880 May 19 '15 at 00:35

1 Answers1

0

I ended up:

  1. Parsing the SQL file into an array of statements split on lines whose only content is GO statements & comments,
  2. Creating a SQL transaction,
  3. Executing all statements in the array in sequence,
  4. Conditionally rolling back the entire transaction on any single statement fail,
  5. Ending the transaction once all queries have completed synchronously.

A bit of work, but I think that was the best way to do it.

dthree
  • 19,847
  • 14
  • 77
  • 106