Power Moves: SQL Server on Linux, Rails, and Docker

The Ultimate Teen Titans Power Move.

Microsoft is releasing new versions of SQL Server on Linux more and more frequently. Last month brought us SQL Server 2017 Community Technology Preview 2.0 and like the others before, it's a breeze to use with Docker.

Around the same time, Rails 5.1 was released. Writing modern web applications using every technology from the backend database to frontend JavaScript has never been more enjoyable. Rails 5.1 also raises intergration with SQL Server to an all-time record high.

Now... today... this very moment... it is time to get merge-obsessed and combine our developer super powers to form some sweet new Power Moves. GO!

Your Environment

Before moving on, we should pick a secure password for our local SQL Server administrator. The SA_PASSWORD variable is used both by Rails and SQL Server on Docker. I recommend setting this in your Bash, ZSH or whatever config your shell uses.

$ export SA_PASSWORD=mysecretpassword

Install Docker

Docker Memory Resource PreferencesIf you do not have it already, please download and install Docker. They offer a free Community Edition for most platforms. Once you have downloaded and verified your install, please update your Docker resource preferences to allow up to 4GB of memory. SQL Server will need a bit more than the 2GB default.

Install SQL Server

This command will download the latest SQL Server image from Microsoft's official hub on Docker, currently SQL Server 2017 CTP v2.0.

$ docker pull microsoft/mssql-server-linux

Now, start SQL Server using the previous environment settings. The first command will create a persistent data volume while the second starts SQL Server via Docker and attaches that volume to the container. All new databases will persist to the mssqldata volume.

$ docker create \
  -v /var/opt/mssql \
  --name mssqldata microsoft/mssql-server-linux

$ docker run -p 1433:1433 \
  -e "ACCEPT_EULA=Y" \
  -e "SA_PASSWORD=$SA_PASSWORD" \
  -d --volumes-from mssqldata \
  microsoft/mssql-server-linux

Create a Rails Application

This guide does not cover Ruby installation. Rails requires Ruby v2.2.2 or higher and if you are new to Ruby or do need to install the latest version, I recommend rbenv for POSIX systems or RubyInstaller if you are on Windows.

Let's install the latest version of Rails and Bundler using RubyGems.

$ gem install rails bundler
Successfully installed rails-5.1.1
Successfully installed bundler-1.15.1

To create your new Rails application with SQL Server as the database, we pass the -d flag with a value of sqlserver. This will install the SQL Server Adapter and TinyTDS dependencies – the Ruby bindings to DBLIB. Finally, we test that we can boot our new Rails application and load it in a browser. If all goes well, you should see the following image.

$ rails new my_app -d sqlserver
$ cd my_app
$ ./bin/rails server

=> Booting Puma
=> Rails 5.1.1 application starting in development on http://localhost:3000

Yay! You're on Rails! Welcome Screen

Ultimate Power Move

A static welcome page is fine and all, but let's see if we can make sure our SQL Server database works with ActiveRecord.

$ ./bin/rails db:setup

Created database 'my_app_development'
Created database 'my_app_test'

Success! The db:setup command has shown us that it has created two databases for us and that everything is bootstraped to SQL Server correctly. We can create a model/table now via a migration.

$ ./bin/rails generate model User name:string email:string
$ ./bin/rails db:migrate

== CreateUsers: migrating ======================================
-- create_table(:users)
   -> 0.0024s
   -> -1 rows
== CreateUsers: migrated (0.0025s) =============================

Success again! Here we crated a User model/table with a few attributes. Running the migration creates the table and columns as needed and we can even fire up a console to test it all out.

$ ./bin/rails console

> User.count
  SQL (0.9ms)  USE [my_app_development]
   (2.1ms)  SELECT COUNT(*) FROM [users]
=> 0

Thanks!

The MetaSkills Power MoveThere are a lot of good people working hard every day to make your experience with Ruby & SQL Server amazing. From engineers at Microsoft all the way to individual contributors on the Rails SQL Server repos.

In the coming weeks, we hope to do so much more. What might be next? Maybe better integration to JRuby within the adapter. Maybe native cross-platform tools to script large databases when the Rails DSL can not represent that schema. All of this is in the works.

I am very proud to be a part of this community and hope you find time to stop by and help, or drop a friendly note/issue about your experiences with Ruby & SQL Server. Lastly, thanks to CustomInk for supporting our passion projects and individual causes.

by Ken Collins
AWS Serverless Hero