ActiveRecord SQL Server Adapter. For SQL Server 2012 And Higher.

- CI
- Appveyor
- Gem Version
- Community

Identity Inserts with Triggers

The adapter uses OUTPUT INSERTED so that we can select any data type key, for example UUID tables. However, this poses a problem with tables that use triggers. The solution requires that we use a more complex insert statement which uses a temporary table to select the inserted identity. To use this format you must declare your table exempt from the simple output inserted style with the table name into a concurrent hash. Optionally, you can set the data type of the table’s primary key to return.

adapter = ActiveRecord::ConnectionAdapters::SQLServerAdapter

# Will assume `bigint` as the id key temp table type.
adapter.exclude_output_inserted_table_names['my_table_name'] = true

# Explicitly set the data type for the temporary key table.
adapter.exclude_output_inserted_table_names['my_uuid_table_name'] = 'uniqueidentifier'


# Explicitly set data types when data type is different for composite primary keys.
adapter.exclude_output_inserted_table_names['my_composite_pk_table_name'] = { pk_col_one: "uniqueidentifier", pk_col_two: "int" }

Force Schema To Lowercase

Although it is not necessary, the Ruby convention is to use lowercase method names. If your database schema is in upper or mixed case, we can force all table and column names during the schema reflection process to be lowercase. Add this to your config/initializers file for the adapter.

ActiveRecord::ConnectionAdapters::SQLServerAdapter.lowercase_schema_reflection = true

Schemas & Users

Depending on your user and schema setup, it may be needed to use a table name prefix of dbo.. So something like this in your initializer file for ActiveRecord or the adapter.

ActiveRecord::Base.table_name_prefix = 'dbo.'

It’s also possible to create/change/drop a schema in the migration file as in the example below:

class CreateFooSchema < ActiveRecord::Migration[7.0]
  def up
    create_schema('foo')

    # Or you could move a table to a different schema

    change_table_schema('foo', 'dbo.admin')
  end

  def down
    drop_schema('foo')
  end
end

Configure Connection

The adapter conforms to the AbstractAdapter interface to configure connections. If you require additional connection configuration then implement the configure_connection method in an initializer like so. In the following example we are setting the TEXTSIZE to 64 megabytes.

module ActiveRecord
  module ConnectionAdapters
    class SQLServerAdapter < AbstractAdapter
      def configure_connection
        super
        @raw_connection.execute("SET TEXTSIZE #{64.megabytes}").do
      end
    end
  end
end

Executing Stored Procedures

Every class that sub classes ActiveRecord::Base will now have an execute_procedure class method to use. This method takes the name of the stored procedure which can be a string or symbol and any number of variables to pass to the procedure. Arguments will automatically be quoted per the connection’s standards as normal. For example:

Account.execute_procedure(:update_totals, 'admin', nil, true)
# Or with named parameters.
Account.execute_procedure(:update_totals, named: 'params')

New Rails Applications

When creating a new Rails application you can specify that you want to use the SQL Server adapter using the database option:

rails new my_app --database=sqlserver

To then connect the application to your SQL Server instance edit the config/database.yml file with the username, password and host of your SQL Server instance.

Setting Up Your Development Environment

To run the test suite you can use any of the following methods below. See RUNNING_UNIT_TESTS for more detailed information on running unit tests.

VirtualBox & Vagrant

The activerecord-sqlserver-adapter-dev-box is a Vagrant/VirtualBox virtual machine that has MS SQL Server installed. However, the activerecord-sqlserver-adapter-dev-box uses Vagrant and Virtual Box which will not work on Macs with Apple silicon.

Local Development

See the RUNNING_UNIT_TESTS file for the details of how to run the unit tests locally.

Community

There is a Gitter channel for the project where you are free to ask questions about the project.

Credits & Contributions

Many many people have contributed. If you do not see your name here and it should be let us know. Also, many thanks go out to those that have pledged financial contributions.

You can see an up-to-date list of contributors here: http://github.com/rails-sqlserver/activerecord-sqlserver-adapter/contributors

SQL Server