Rails and SQL Server Database Without Schema Modifications
Edu Depetris
- Jul 09, 2024
Github Actions
Multiple Databases
Sql Server
Ruby On Rails
One of our clients asked us to extend their legacy software to track order shipping from warehouses. Their current software processes orders but doesn’t track shipping status and requires additional functionality.
This off-the-shelf software has been in use for many years, and now they need to track orders between the warehouse and their system, allowing warehouse interaction.
We face some constraints: the software uses a SQL Server database that we can’t modify but access, we don’t have the source code, and the exposed code is in a language we don’t prefer.
merchant and warehouses
As Ruby enthusiasts, we decided to use Ruby on Rails and try out the multiple databases feature. We planned to have our own database to keep meta information and connect to the SQL Server to read and eventually write some columns.
Most of the configuration values under external are standard, except for database_tasks. This config value allows us to connect to the external database without any database management tasks such as schema management, migrations, seeds, etc. [Rails documentation, and pull request]. This is great because we’re not able to touch the schema or run operations on it.
Our external models look like this:
# app/models/external_record.rb
class ExternalRecord < ApplicationRecord
self.abstract_class = true
connects_to database: { writing: :external, reading: :external }
end
# app/models/external/order_reference.rb
module External
class OrderReference < ExternalRecord
self.table_name = "Extension.OrderReferences"
end
end
🎉 Great! We are able to use Active Record with our external database!
This solves half of the problem. Now, we want to have tests and a CI running with this configuration, but we don’t want to connect to an external host in these environments.
For our tests, we’re using RSpec and FactoryBot, and for CI, we use GitHub Actions.
Initially, our tests crashed because we didn’t have any database or table to match our models. Our solution was to provide a SQL Server database loaded with some tables to Rails so the app can connect and use it. I’m not using a Windows machine and have never installed SQL Server, so I decided to use Docker to get the SQL Server running.
Here’s my Docker Compose configuration:
# We are using the official Microsoft SQL Server Docker image.
version: '3.8'
services:
mssql:
image: mcr.microsoft.com/mssql/server:2019-latest
ports:
- 1433:1433
environment:
# The ACCEPT_EULA variable must be set to "Y" to accept the license agreement.
ACCEPT_EULA: "Y"
# The SA_PASSWORD variable sets the password for the SQL Server system administrator (SA) user.
SA_PASSWORD: "YourStrong!Passw0rd"
healthcheck:
# Health check to ensure the SQL Server service is ready before running the tests.
test: ["CMD-SHELL", "echo 'SELECT 1' | /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P YourStrong!Passw0rd"]
interval: 10s
timeout: 5s
retries: 10
Then:
docker-compose up -d
Sweet, we have our server running. Now let’s create the database and some tables:
I exported some tables from the external database using TablePlus and saved it into db/external_schemas.sql.
# Create the database
$ docker-compose exec mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'YourStrong!Passw0rd' -Q "CREATE DATABASE [test_db];"
# Copy the schema file to the instance
$ docker cp db/external_schemas.sql your_app-mssql-1:/external_schemas.sql
# Create the schema
$ docker-compose exec mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'YourStrong!Passw0rd' -d test_db -i /external_schemas.sql
✅ Now tests are working and we’re able to use our factories:
# spec/factories/external/order_references.rb
FactoryBot.define do
factory :external_order_reference, class: "External::OrderReference" do
OrderID { Faker::Number.non_zero_digit }
OrderEventID { SecureRandom.uuid }
RowGuid { SecureRandom.uuid }
# ...
end
end
# spec/models/external/order_reference_spec.rb
require "rails_helper"
RSpec.describe External::OrderReference, type: :model do
it "has the correct table name" do
expect(described_class.table_name).to eq("Extension.OrderReferences")
end
it "has a valid factory" do
expect(build(:external_order_reference)).to be_valid
end
end
Our final step is to configure the GitHub Action CI with a similar setup for SQL Server
Here’s a snippet showing that:
rspec:
runs-on: ubuntu-latest
services:
# Others
# omitted
# External
mssql:
image: mcr.microsoft.com/mssql/server:2019-latest
ports:
- 1433:1433
env:
ACCEPT_EULA: Y
SA_PASSWORD: "YourStrong!Passw0rd"
options: >-
--health-cmd "echo 'SELECT 1' | /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P YourStrong!Passw0rd"
--health-interval 10s
--health-timeout 5s
--health-retries 10
env:
RAILS_ENV: test
steps:
# Install some packages here
# omitted
# activerecord-sqlserver-adapter needs FreeTDS
- name: Install FreeTDS and dependencies
run: |
sudo apt-get install -y freetds-dev freetds-bin build-essential
- name: Set FreeTDS config for gem installation
run: |
echo "FREETDS_DIR=/usr" >> $GITHUB_ENV
- name: Checkout code
uses: actions/checkout@v4
- name: Install Ruby and gems
uses: ruby/setup-ruby@v1
with:
ruby-version: .ruby-version
bundler-cache: true
- name: Wait for SQL Server to be ready
run: |
echo "Waiting for SQL Server to start..."
until /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P YourStrong!Passw0rd -Q 'SELECT 1' &> /dev/null; do
echo -n '.'
sleep 1
done
- name: Create test database
run: |
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P YourStrong!Passw0rd -Q 'CREATE DATABASE [test_db];'
- name: Create schema from SQL script
run: |
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P YourStrong!Passw0rd -d test_db -i db/external_schemas.sql
- name: Prepare Primary database
run: bin/rails db:test:prepare
- name: Run tests
run: bundle exec rspec
The final ✅: Tests are running in our CI too.
Future Improvements:
There’s room for improving the developer experience. I would like to remove the database_tasks from the test database and rely on db:test:prepare. The future flow will be:
Get an external database dump via Rails $ bin/rails db:schema:dump:external
Remove the Docker commands to create the database and load the schema, and rely on $ bin/rails db:test:prepare.
Currently, my external database has multiple schemas, and the dump only works with dbo, but I saw an open PR that might address that here.
Also, my database has views, and they fail to load. There seems to be a difference between the dump generation and the load. This issue will probably be fixed. I think this GitHub pull request contains the approach, and I’ll keep an eye on the comments.