Rails and SQL Server Database Without Schema Modifications

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.

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.

To connect to the SQL Server (external) we used the Active Record SQL Server adapter, which worked smoothly:

development:
  primary:
    <<: *default
    database: storage/development.sqlite3
  external:
    adapter: sqlserver
	database_tasks: false
	azure: true
    database: "..."
    username: "..."
    password: "..."
    host: "..."
    
test:
  primary:
    <<: *default
    database: storage/test.sqlite3
  external:
    adapter: sqlserver
	database_tasks: false
    database: "test_db"
    username: "sa"
    password: "YourStrong!Passw0rd"
    host: "localhost"

# 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

# 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

docker-compose up -d 

# 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

# 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

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

1.  Get an external database dump via Rails $ bin/rails db:schema:dump:external
2. Remove the Docker commands to create the database and load the schema, and rely on $ bin/rails db:test:prepare.