Rails 5.2.3 schema load failing for Microsoft SQL Server

Question

I have a Rails application that is normally deployed on PostgreSQL databases, but now we have a customer that wishes to use it with a Microsoft SQL Server database. The schema.rb file referenced below was generated while the application was set up on PostgreSQL.

When I try to create the tables using the rake db:schema:load command, it fails when trying to create the primary auto sequence id key field. It is trying to use an invalid data type of 'serial'.

It's failing on the first table, here is the table in my schema.rb file

  create_table "chart_configurations", id: :serial, force: :cascade do |t|
    t.string "kind", limit: 255
    t.text "display_attributes"
    t.string "primary_measurement_element", limit: 255
    t.string "primary_measurement_label", limit: 255
    t.string "function", limit: 255
  end

When I execute the rake command:

bundle exec rake db:schema:load
-- create_table("chart_configurations", {:id=>:serial, :force=>:cascade})
rake aborted!
ActiveRecord::StatementInvalid: TinyTds::Error: Column, parameter, or variable #1: Cannot find data type serial.: 
CREATE TABLE [chart_configurations] ([id] serial NOT NULL PRIMARY KEY,
[kind] nvarchar(255), [display_attributes] nvarchar(max),
[primary_measurement_element] nvarchar(255),
[primary_measurement_label] nvarchar(255), [function] nvarchar(255))

SQL Server version 2017
ruby 2.5.8p224 (2020-03-31 revision 67882) [x64-mingw32]
rails (5.2.3)
activerecord-sqlserver-adapter (5.2.1)
tiny_tds (2.1.2 x64-mingw32)

Any ideas about what I can do to get my schema loaded?

Answer 1

While adding broader database compatibility to a gem I maintain called The Brick, had discovered that serial and bigserial are not honoured by activerecord-sqlserver-adapter. There are a couple of ways to work around this -- either add the option is_identity: true when you create a column, or patch the sqlserver gem so that it understands serial and bigserial and does this for you.

In my case I chose to create a patch for The Brick gem that went in with this commit, adding overall Microsoft SQL Server support. If you drop that gem in then it will work -- or you can steal the patch from my code :)