Rails and PostgreSQL Column Limits

I learned something today that I thought I'd write about. I was looking into an error where a user put more than 255 characters in a certain form field, and it failed on writing the record to the database. The error message was a Postgres error: ERROR: value too long for type character varying(255) There were no Rails length validations on the model attribute. I figured I could reproduce the error locally, add the missing test and validation, and be done with it. But when I entered the same data locally, I did not get the error. Hmm.

After digging around a bit I found the ActiveRecord::Base.columns_hash method that returns a bunch of helpful information about a database column as represented in Postgres. I compared the output of my local environment with that of the production environment.

My local output:

pry(main)> Resume::Education.columns_hash['honors'].limit
=> nil
pry(main)> Resume::Education.columns_hash['honors'].sql_type
=> "character varying"

Production output:

pry(main)> Resume::Education.columns_hash['honors'].limit
=> 255
pry(main)> Resume::Education.columns_hash['honors'].sql_type
=> "character varying(255)"

Ok, so that's the issue. But how?

After more digging I learned that Rails changed how the :string column is represented in Postgres starting with Rails 4.2. Before Rails 4.2 a column type of :string was translated in Postgres as a character varying(255). Starting with Rails 4.2 the :string type translates in Postres as a character varying which essentially has no limit.

Here are the release notes describing the change: https://guides.rubyonrails.org/4_2_release_notes.html#active-record

The PostgreSQL and SQLite adapters no longer add a default limit of 255 characters on string columns.

In my case the staging and production databases have been around since the Rails 2 days, and this particular table/column has been around since Rails 4.1. When I created my local development database, the application was on Rails 5.2, and therefore the migration created my column with no PostgresSQL enforced size limit.

The fix here is two-fold. First, I needed to add in the appropriate measures to make sure user input is capped at 255 characters, both within the form, and as a Rails validation. Second, I needed to make sure that new environments, both development, and CI environments, have the same constraints as the production environment. That can be done by getting an up-to-date version of db/schema.rb from production, since production is the environment with the legacy constraints.

RAILS_ENV=production rake db:schema:dump

Voila! All of the t.string "column name" limit: 255 differences can now be seen.

As long as new environments are set up with rake db:schema:load instead of running through migrations, we will have consistent database environments and constraints.