Database schema management in Ruby apps

Database schema management Vsevolod Romashov Qlean 7even

Migrations • bin/rails generate migration AddPartNumberToProducts • bundle exec hanami generate migration create_books

class ChangeDifferentThingsInUsers < ActiveRecord ::Migration def up remove_column :users, :phone change_column_null :users, :email, false add_foreign_key :posts, :users, column: :author_id end def down remove_foreign_key :posts, column: :author_id change_column_null :users, :email, true add_column :users, :phone, null: false end end

DbSchema https://github.com/7even/db_schema

# db/schema.rb DbSchema.describe do |db| db.table :users do |t| t.primary_key :id t.varchar :first_name, null: false t.varchar :last_name t.timestamptz :created_at, default: :'now()' end db.table :posts do |t| t.primary_key :id t.varchar :title, null: false t.integer :user_id, references: :users t.index :user_id end end

# lib/db_schema.rb DbSchema.configure( adapter: 'postgresql', host: ENV['db_host'], port: ENV['db_port'], database: ENV['db_name'], user: ENV['db_user'], password: ENV['db_password'] ) load 'db/schema.rb'

Columns • All built-in data types (including extensions) + custom enum types • NOT NULL • Default values (strings, numbers, Date, Time, SQL expressions) • Type-speciﬁc attributes

Indexes • "Common" indexes • Unique indexes • Multiple indexes • Partial indexes • Expression indexes • GIN, GiST etc. • Ordered indexes

Foreign Keys • Single-column • Multi-column • ON DELETE • ON UPDATE

• Check constraints • Enum types • Extensions

Desired schema Actual schema Operation ✅ ❌ Create ❌ ✅ Drop ✅ ✅ Change (?)

Create Drop Change Table ✅ ✅ ✅ Primary Key ✅ ✅ Column ✅ ✅ ✅ Index ✅ ✅ ❗ Foreign Key ✅ ✅ ❗ Check Constraint ✅ ✅ ❗

Change Type ✅ Allow Null ✅ Disallow Null ✅ Change Column Default ✅ Make Column a Primary Key ❌ Drop Primary Key Constraint ❌ Change Column

Create Drop Change Enum ✅ ✅ ❗ Extension ✅ ✅

Change Enum Add New Value ✅ Drop Existing Value ❌ Reorder Values ❌

Problems • Renaming tables & columns • Some datatype changes • Adding a NOT NULL column pre-ﬁlled with data • Data migrations

db.migrate do |migration| migration.apply_if do |schema| schema.table(:users).has_field?(:first_name) end migration.run do |migrator| migrator.create_column :users, :name, :varchar User.find_each do |user| name = [user.first_name, user.last_name].compact.join(' ') user.update(name: name) end migrator.drop_column :users, :first_name migrator.drop_column :users, :last_name migrator.disallow_null :users, :name end end

Roadmap • full-ﬂedged primary keys support • MySQL/SQLite support • schema dumper • model annotations

Database schema management in Ruby apps

More Related Content

What's hot

Viewers also liked

Similar to Database schema management in Ruby apps

Recently uploaded

Database schema management in Ruby apps