ruby

Pros and Cons of Using structure.sql in Your Ruby on Rails Application

Daniele Pestilli

Daniele Pestilli on

Last updated:

Pros and Cons of Using structure.sql in Your Ruby on Rails Application

This post was updated on 4 August 2023 to include a section on the advantages of structure.sql over schema.rb.

In today's post, we'll cover the significant differences and benefits of using structure.sql versus the default schema.rb schema formats in your Ruby on Rails application. In a data-driven world, knowing how to exploit all of your database's rich features can make the difference between a successful and unsuccessful enterprise.

After evincing the main differences between the two formats, we'll outline how to switch to structure.sql and demonstrate how it can help with ensuring data integrity as well as database functionality that you might otherwise not be able to preserve.

In the post, I'll give examples of a Rails app that makes use of structure.sql with a PostgreSQL database, but the underlying concepts can be transposed to other databases as well. No real-world web application is truly complete without a reliable database to support it.

Without further ado, let's dive right in!

The Difference Between schema.rb and structure.sql

One of the first things you need to do when starting a Ruby on Rails project is to run database migrations. If you generate a User model, for instance, Rails will inevitably ask you to run migrations, which will create a schema.rb file accordingly:

bash
rails g model User first_name:string last_name:string

Rails will generate the following migration:

ruby
class CreateUsers < ActiveRecord::Migration[6.0] def change create_table :users do |t| t.string :first_name t.string :last_name t.timestamps end end end

Once the migration is executed, you will find that Rails generated a schema.rb file for you:

ruby
ActiveRecord::Schema.define(version: 2019_12_14_074018) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" create_table "users", force: :cascade do |t| t.string "first_name" t.string "last_name" t.datetime "created_at", precision: 6, null: false t.datetime "updated_at", precision: 6, null: false end end

This schema.rb file is fantastic for relatively basic applications and use cases.

There are two main things to notice here:

  1. It is a Ruby representation of your database; schema.rb is created by inspecting the database and expressing its structure using Ruby.
  2. It is database-agnostic (i.e. whether you use SQLite, PostgreSQL, MySQL or any other database that Rails supports, the syntax and structure will remain largely the same)

However, there may come a time when this strategy becomes too limiting for your growing app.

Say, for instance, you have hundreds or thousands of migration files.

If you need to rapidly spin up a new production system, you might encounter a scenario where running them all in sequence takes too long. Or you might face a situation where some migrations contain code that was meant to be executed on an older version of your database, but that is no longer executable on the current version. You might have a situation where migrations were written with certain data assumptions that are no longer valid, which would cause the migrations to fail.

All these scenarios prevent efficiently setting up a new instance of your application⁠—be it in production or for a new team member⁠—with a simple rails db:create db:migrate command. If this were the case, how would you go about getting up to speed with a correct database schema?

Certainly, one way would be to go back and fix all the broken migrations. That's never a bad idea!

If going back and fixing a bunch of migrations is too costly, another way would be to run the rails db:setup task. This task will generate a database schema from your schema.rb file. However, what if your database contained complex logic that is not represented in the schema.rb representation of your database?

Luckily, Rails offers an alternative: structure.sql, which differs from schema.rb in a number of ways, and can be a better choice.

Advantages of structure.sql over schema.rb

  • Database-specific features - structure.sql allows you to use database-specific features which may not be supported in schema.rb. For example, let's say you want to make sure the jsonb data-type that is supported by PostgreSQL but not SQLite is represented properly in your database schema. You'll find that structure.sql will give you a better solution compared to the standard schema.rb.
  • Complex migrations - If your app uses complex migrations and SQL statements that need to be maintained as they are, you'll find that structure.sql will be exactly what you need.
  • Maintaining database state - structure.sql is a much better tool at capturing the exact state of a database compared to schema.rb. This becomes important in cases where you need to capture the entire structure of a database as is, for instance, when dealing with legacy databases or when moving databases between different deployment servers.

There's definitely more advantages to using structure.sql over schema.rb, but we'll leave it at that. It's suffice to say that once an application reaches a certain level of maturity, we have to use every trick in the book to boost efficiency, preserve data correctness, and ensure blazing-fast performance. Using structure.sql to manage a Rails database's behavior allows for this.

Switching From schema.rb to structure.sql

Making the change from schema.rb to structure.sql is a relatively straightforward process. All you need to do is set the following line in config/application.rb:

ruby
module YourApp class Application < Rails::Application config.load_defaults 6.0 # Add this line: config.active_record.schema_format = :sql end end

Then, run rails db:migrate and you should see the file in db/structure.sql. Voilà! Rails will dump the database structure using the tool specific to the database you are using (in PostgreSQL's case, that tool is pg_dump, for MySQL or MariaDB, it will contain the output of SHOW CREATE TABLE for each table, etc). It is advisable to ensure this file is under version control so that the rest of your team will have the same database structure.

A first glance at that file may be daunting: the schema.rb file was only 25 lines, whereas the structure.sql file is a whopping 109 lines! What benefits could such a large file add to the app development?

Adding Database-level Constraints

ActiveRecord is one of my favorite parts of using Rails. It allows you to query the database in a way that feels natural, almost like in a spoken language. For instance, if you want to find all a company's users named Dan, then ActiveRecord allows you to simply run a query like the following:

ruby
company = Company.find(name: 'Some Company') # Reads just like in a natural language! company.users.where(first_name: 'Dan')

There are some cases in which ActiveRecord falls short though. For instance, say you have the following validation on your User model:

ruby
class User < ApplicationRecord validate :name_cannot_start_with_d private def name_cannot_start_with_d if first_name.present? && first_name[0].downcase == 'd' errors.add(:first_name, "cannot start with the letter 'D'") end end end

If you try to create a user with the name 'Dan', you should see an error when the validation runs:

ruby
User.create!(first_name: 'Dan') Traceback (most recent call last): ActiveRecord::RecordInvalid (Validation failed: First name cannot start with the letter 'D')

This is fine, but suppose you or one of your team members changed the data by bypassing ActiveRecord's validation:

ruby
u = User.create(first_name: 'Pan') # The update_attribute method bypasses ActiveRecord validations u.update_attribute :first_name, 'Dan' u.first_name => "Dan"

As demonstrated, it is very easy to bypass the validation.

This can have disastrous consequences for our application. ActiveRecord can be a blessing as well as a curse⁠—while it has a very clean and natural DSL that makes it a pleasure to work with, it is often overly permissive when enforcing model-level validations. The solution, as you may already know, is to add database-level constraints.

bash
rails g migration AddFirstNameConstraintToUser

This will generate a file that you can edit with the logic to disallow first names that start with the letter 'D':

ruby
class AddFirstNameConstraintToUser < ActiveRecord::Migration[6.0] def up execute "ALTER TABLE users ADD CONSTRAINT name_cannot_start_with_d CHECK (first_name !~* '^d')" end def down execute "ALTER TABLE users DROP CONSTRAINT IF EXISTS name_cannot_start_with_d" end end

Note that it is very important to add code that successfully reverts the migration. In the above example, I have up and down directives. The up method gets executed when the migration runs, down gets executed when the migration is rolled back. Without properly reverting your database structure, you may have to do some manual house-cleaning later. I'd recommend always having a migration file that can be executed both up and down to avoid future headaches.

Now, run the migration and check whether you can bypass that constraint:

bash
rails db:migrate
ruby
user = User.create first_name: 'Pan' user.update_attribute :first_name, 'Dan' ActiveRecord::StatementInvalid (PG::CheckViolation: ERROR: new row for relation "users" violates check constraint "name_cannot_start_with_d") DETAIL: Failing row contains (2, Dan, null, 2019-12-14 09:40:11.809358, 2019-12-14 09:40:41.658974).

Perfect! Our constraint is working as intended. Even if, for whatever reason, we bypass ActiveRecord's validation, we can still rely on the database⁠—our ultimate goalkeeper⁠—to preserve our data integrity.

What does this have to do with structure.sql?

If you take a look at it, you'll see that the following was added:

sql
CREATE TABLE public.users ( id bigint NOT NULL, first_name character varying, last_name character varying, created_at timestamp(6) without time zone NOT NULL, updated_at timestamp(6) without time zone NOT NULL, CONSTRAINT name_cannot_start_with_d CHECK (((first_name)::text !~* '^d'::text)));

Your constraint is within the schema itself!

While schema.rb also supports database-level constraints, it is important to remember that it does not express everything your database may support such as triggers, sequences, stored procedures or check constraints. For example, this is what would happen to your schema file with the same exact migration (AddFirstNameConstraintToUser) if you were just to use schema.rb:

ruby
ActiveRecord::Schema.define(version: 2019_12_14_074018) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" create_table "users", force: :cascade do |t| t.string "first_name" t.string "last_name" t.datetime "created_at", precision: 6, null: false t.datetime "updated_at", precision: 6, null: false end end

The file has not changed! The constraint was not added.

If you were to onboard a new developer to work on your project, you could potentially be operating under different database regulations.

Committing structure.sql to version control would help ensure that your team is on the same page. If you were to run rails db:setup having a structure.sql file, your database's structure will contain the above constraint. With schema.rb there is no such guarantee.

The same can be said about a production system. If you needed to rapidly spin up a new instance of your application with a fresh database⁠—and running all migrations sequentially takes a long time⁠—setting up the database from the structure.sql file would be a lot quicker. We can rest assured that the structure.sql will create our database with the exact same structure as in other instances.

Growing Pains

Managing the concise schema.rb file across a team is a far easier task than managing the verbose structure.sql file.

One of the biggest growing pains when migrating to structure.sql is ensuring that only the required changes get committed to that file, which can sometimes be difficult to do.

Say, for instance, you pull someone's branch and run the migrations specific to that branch. Your structure.sql will now contain some changes. You then go back to working on your own branch and generate a new migration. Your structure.sql file will now contain both your branch's and the other branch's changes. This can be a bit of a hassle to deal with, and there is undoubtedly a bit of a learning curve when it comes to managing these conflicts.

By using this approach, we're making a tradeoff. We have to deal with a bit of code complexity upfront that allows us to preserve our database's advanced functionality. In turn, we also have to deal with a simpler schema representation as well as not having all the power of the database at our fingertips, e.g. if we want to set a backup from a db:setup task. I posit that it's best to put up with a bit of version-control hassle than to suffer through fixing corrupt/incorrect data in a production system, or to not be able to make use of all the advanced functionality that your database offers.

Generally speaking, there are two strategies I've used to ensure my structure.sql file only contains the necessary changes to a specific branch:

  • Once you are done working on a branch that contains migrations, make sure you run rails db:rollback STEP=n where n is the number of migrations in that branch. This will ensure your database structure reverts to its original state.
  • You might forget to rollback after working on a branch. In that case, when working on a new branch, make sure you pull a pristine structure.sql file from master before creating any new migrations.

As a rule of thumb, your structure.sql file should only contain the changes relevant to your branch before being merged into master.

Conclusion

Generally speaking, when Rails applications are small or don't need some of the more advanced features that a database offers then it's safe to use schema.rb, which is very readable, concise and easy to manage.

However, as an application grows in size and complexity, an accurate reflection of the database structure is of the essence. It will allow a team to maintain the right constraints, database modules, functions and operators that otherwise wouldn't be possible. Learning to use Rails with a well-maintained structure.sql file will offer an edge that the simpler schema.rb simply cannot.

P.S. If you'd like to read Ruby Magic posts as soon as they get off the press, subscribe to our Ruby Magic newsletter and never miss a single post!

Daniele Pestilli

Daniele Pestilli

Guest author Daniele is a full-stack developer from Italy with an eye for clean, elegant code. He has lived and worked on three continents, and as of late, splits his time working as a digital nomad between Italy and East Asia. Besides coding, Daniele enjoys reading, drawing and playing guitar.

All articles by Daniele Pestilli

Become our next author!

Find out more

AppSignal monitors your apps

AppSignal provides insights for Ruby, Rails, Elixir, Phoenix, Node.js, Express and many other frameworks and libraries. We are located in beautiful Amsterdam. We love stroopwafels. If you do too, let us know. We might send you some!

Discover AppSignal
AppSignal monitors your apps