Database Migrations Converting DbScriptomate to Ruby Running our SQL Scripts

August 23, 2014

So last time I posted on the DbScriptomate migration it was about a spike I done to get SQL Server and Ruby talking to each other. Tonight I got time to implement the code. We are now able to run scripts against our database!

Let’s see how this is done. Firstly I created a new class that will interact with TinyTDS called SqlServerRunner. The constructor is then passed the username, password, host and database. These parameters are used to create our TinyTDS connection that will be used to do the heavy work of interacting with SQL Server.

Here is the code to implement the SqlServerRunner class:

Next we are going to add a new method to our SqlServerRunner called run_sql. The run_sql method will take a string that will be our SQL to run. This can be any valid SQL, and it will be executed against the database that was pass through the command line. The run_sql method makes a single call to execute which is part of TinyTDS.

So far so good nothing to great about this code, all this code does is hide us from TinyTDS.

Next up we are going to edit our Command class. The Command class is the main orchestrator of DbScriptomate; it will do the directing of traffic so to speak. We can add a new method to the Command class called execute, execute will take an array of file paths, read the contents which is SQL and put that in an array for later. Next we are going to create an instance of the SqlServerRunner class and pass to it the necessary arguments to create an instance. We can now loop through the array that contains the SQL and call the run_sql method from SqlServerRunner. This will go ahead and execute the SQL scripts one by one against the DB.

The code for the execute method is pretty simple:

Here is the full listing of the command.rb file with the new code to run the SQL files.

You can again see how easy Ruby makes our lives. I wrote this code in about 5 minutes, and it is working perfectly for my needs. We are at a good place now and have an excellent foundation. There is still some work to do but in my next post I would like to write some tests and talk about refactoring this code.

Discussion, links, and tweets

My name is Deon Heyns and I am a developer learning things and documenting them in realtime. Python, Ruby, Scala, .NET, and Groovy are all languages I have written code in. I appeared in the New York Post once. I host my code up at GitHub and Bitbucket so have a look at my code, fork it and send those pull requests.

comments powered by Disqus