---
title: "Migrating Rails app from postgresql to sql server"
description: "Migrating rails app from postgresql to sql server"
canonical_url: "https://www.bigbinary.com/blog/migrating-from-postgresql-to-sqlserver"
markdown_url: "https://www.bigbinary.com/blog/migrating-from-postgresql-to-sqlserver.md"
---

# Migrating Rails app from postgresql to sql server

Migrating rails app from postgresql to sql server

- Author: Rohit Kumar
- Published: October 13, 2015
- Categories: Rails

We started development on a project with [PostgreSQL](http://www.postgresql.org)
as our database. However midway we had to switch to
[SQL Server](http://www.microsoft.com/en-us/server-cloud/products/sql-server/)
for a variety of reasons.

Here are some of the issues we noticed while migrating to SQL Server.

## Unique constraint on a column which has multiple NULL values

As per the ANSI SQL standard unique constraint should allow multiple NULL
values.

PostgreSQL documentation on unique constraint states following.

```ruby
In general, a unique constraint is violated when there is more than one row in
the table where the values of all of the columns included in the constraint are
equal.

However, two null values are not considered equal in this comparison.

That means even in the presence of a unique constraint it is possible to store
duplicate rows that contain a null value in at least one of the constrained
columns.

This behavior conforms to the SQL standard, but we have heard that
other SQL databases might not follow this rule. So be careful when developing
applications that are intended to be portable.
```

In SQL Server a unique constraint does **not** allow multiple NULL values.

[Devise](https://github.com/plataformatec/devise) by default adds unique index
on `reset_password_token` column.

```ruby
add_index :users, :reset_password_token, :unique => true
```

`Devise` is doing the right thing by enforcing a unique index on
`reset_password_token` so that when a user clicks on a link to reset password
the application would know who the user is.

However here is the problem. If we add a new user then by default the value of
`reset_password_token` is `NULL`. If we add another user then we have two
records with `NULL` value in `reset_password_token`. This works in PostgreSQL.

But SQL Server would not allow to have two records with `NULL` in
`reset_password_token` column.

So how do we solve this problem.

[Partial index](https://en.wikipedia.org/wiki/Partial_index) to rescue. It is
also known as `Filtered index`. Both
[PostgreSQL](http://www.postgresql.org/docs/8.0/static/indexes-partial.html) and
[SQL server](https://msdn.microsoft.com/en-us/library/cc280372.aspx) support it.
Rails also supports `partial index` by allowing us to pass `where` option as
shown below.

```ruby
add_index :users, :reset_password_token,
                  unique: true,
                  where: 'reset_password_token IS NOT NULL'
```

Please
[visit this issue](https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/153)
if you want to see detailed discussion on this topic.

This behavior of SQL Server comes in play in various forms. Let's say that we
are adding `api_auth_token` to an existing users table.

Typically a migration for that might look like as shown below.

```ruby
add_column :users, :api_auth_token, :string,
                                    :null => true,
                                    :unique => true
```

In this case we have plenty of records in the `users` table so the above
migration will fail in PostgreSQL. We will have to resort to usage of
`partial index` to fix this issue.

## Adding not null constraint on a column with an index

In PostgreSQL following case will work just fine.

```ruby
add_column :users, :email, :string, :unique => true
change_column :users, :email, :null => false
```

Above migration will fail with SQL Server.

In SQL Server a "not null constraint"
[cannot be added](https://msdn.microsoft.com/en-us/library/ms190273.aspx) on a
column which has a index on it. We need to first remove the unique index, then
add the "not null" constraint and then add the unique index back.

The other solution is to add `not NULL` constraint first in the migration and
then add any index.

## Serialize array into a string column

ActiveRecord
[supports Array Datatype](http://edgeguides.rubyonrails.org/active_record_postgresql.html#array)
for PostgreSQL. We were using this feature to store a list of IDs.

After switching to SQL server we converted the column into string type and
serialized the array.

```ruby
serialize :user_ids, Array
```

## Links

- [Human page](https://www.bigbinary.com/blog/migrating-from-postgresql-to-sqlserver)