内容简介:In today’s post, we will be covering the significant differences and benefits of usingAfter evincing the main differences between the two formats, this post will outline how to make the change toIn this blog post, I will give examples of a Rails app that m
In today’s post, we will be covering 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 your database’s richest features can make the difference between a successful and unsuccessful enterprise.
After evincing the main differences between the two formats, this post will outline how to make the change 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 this blog post, I will give examples of a Rails app that makes use of structure.sql
with a PostgreSQL database, but the underlying concept 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:
rails g model User first_name:string last_name:string
Rails will generate the following migration:
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 generates a schema.rb
file for you:
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:
schema.rb
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 spin up a new production system rapidly, you might encounter a scenario where running them all in sequence takes too long. Otherwise, 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
Notably, structure.sql
differs from schema.rb
in the following ways:
rails db:setup
Once an application reaches a certain maturity level, 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 the Rails database’s behavior allows users to do so.
Making the Change 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
:
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 exact 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:
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:
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:
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:
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.
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’:
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:
rails db:migrate
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 now. 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 have a look at it, you will see the following was added:
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
:
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 were to require rapidly spinning 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 up front that allows us to preserve our database’s advanced functionality. In turn, we also have to deal with a simpler schema representation and 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’d submit 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
wheren
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 seriousness, 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 offer.
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 !
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.
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
谷歌时代的柏拉图
[美] 丽贝卡·戈尔茨坦 / 李鹏程 / 中信出版集团·新思文化 / 2017-12-10 / 69.00元
我愿意用我所有的科技去换取和苏格拉底相处的一个下午。 ——史蒂夫•乔布斯 谷歌时代,科技昌明,众声喧哗,哲学提出的许多问题,科学似乎都已经给出了答案。若是如此,为什么我们今天还需要哲学?这个由古希腊城邦时代的哲人苏格拉底和柏拉图开创的学科,真的过时了吗? 已经2400岁 的柏拉图对此有话要说。哲学家兼小说家、美国国家人文奖章获得者戈尔茨坦史海钩沉,从经典著作中复活了柏拉图,让他来......一起来看看 《谷歌时代的柏拉图》 这本书的介绍吧!