Miguel is a tool for sane management of database schemas. It aims to help with these goals:
- Have just one up-to-date description of the desired database schema using a concise DSL.
- Apply that schema to the database anytime, no matter how either may have diverged.
- Adjust and repeat as often as needed.
To achieve this, it provides the following features:
- Sequel-like DSL for schema description with some enhancements.
- Load schema from given description file or from given database.
- Show changes necessary to turn one schema into another.
- Render those changes as Sequel's change or up/down migrations.
- Alternatively apply those changes directly to the database.
Describing the schema
The schema is described using a DSL similar to Sequel's standard schema syntax. It looks like this:
# Example schema for Miguel. Miguel::Schema.define do # The user, the core of every web site. table :users do primary_key :id # The login id, usually user email address. String :login # The encrypted password. String :password # First name(s). String :first_name # Last name(s). String :last_name # Arbitrary JSON encoded info. Text? :info timestamps unique :login index :first_name index :last_name index :create_time end # User's emails, as every user can have multiple emails. table :user_emails do primary_key :id # The email address itself. String :email # To which user does the email belong. foreign_key :user_id, :users # Flag set when this email is verified. False :verified # Flag set when this email is marked as their primary email by the user. False :primary timestamps unique :email index :user_id index :create_time end # User's profile, collecting various info about the user. table :user_profiles do primary_key :user_id foreign_key [:user_id], :users String? :company String? :street String? :city String? :state String? :country String? :zip String? :phone String? :fax String? :url index :country index :state end # User's followers. join_table :user_id, :users, :follower_id, :users, :user_followers end
One enhancement is that it allows you to define
NULL columns simply by adding
? to the type name.
Anything else is implicitly
NOT NULL, which is a really wise default for many reasons.
Another enhancement is that it allows you to set defaults and
define custom shortcuts for types which you use frequently.
See documentation of the
set_defaults method for details.
The preset defaults are like this:
set_defaults :global, null: false set_defaults :Bool, :TrueClass set_defaults :True, :TrueClass, default: true set_defaults :False, :TrueClass, default: false set_defaults :Signed, :integer, unsigned: false set_defaults :Unsigned, :integer, unsigned: true set_defaults :String, text: false set_defaults :Text, :String, text: true set_defaults :Time, :timestamp, default: '2000-01-01 00:00:00' set_defaults :Time?, :timestamp, default: nil set_defaults :unique, :index, unique: true set_defaults :Key, :integer, unsigned: false set_defaults :primary_key, type: :integer, unsigned: false set_defaults :foreign_key, key: :id, type: :integer, unsigned: false
If you prefer unsigned keys instead and your database engine supports it,
you can pass the
unsigned_keys: true option to
Schema.define to make it happen.
If you don't want any of these defaults set up for you,
use_defaults: false option to
timestamps helper can be used to create the
update_time timestamps for you.
If you pass the
mysql_timestamps: true option to
update_time timestamp will have the MySQL auto-update feature enabled,
and timestamps will use the
'0000-00-00 00:00:00' default by default.
The latter can be also enabled and disabled explicitly by setting
zero_timestamps option to
Using the command
Using the command should be pretty straightforward.
miguel -h and follow the examples.
You can basically:
show- show schema loaded from given
.rbfile or from given database.
dump- dump migration which creates such schema.
down- dump migration which reverses given schema entirely.
diff- dump migration for migrating from one schema to another.
apply- apply given schema to given database.
clear- entirely wipe out schema of given database.
You don't have to worry about changing things accidentally,
the command will always ask for a confirmation before changing anything in the database
(unless you use the
Databases can be specified either by their Sequel URL like
or by the common database
.yml config file:
# Example db.yml. adapter: mysql2 user: dev password: sup3rsecr3t host: localhost database: main encoding: utf8
Note that you can use the
--env option to specify an environment other than
.yml contains configs for multiple environments.
--migration <format> option to choose how you want the migration displayed.
bare format (the default) shows just the changes themselves,
change format creates the one-way Sequel's change migration,
relying on Sequel's ability to reverse it,
full format creates the two-way Sequel's up/down migration.
It's up to you if you will use
diff each time to create the migration files for you,
amend them if needed,
and then let the
sequel command use them normally,
or if you will just
apply the schema directly
and rely on your VCS to keep its previous versions for you,
leaving dozens of piecewise migration files finally behind.
The database specific type support is geared towards MySQL and SQLite. Postgres is supported as well, but note that it lacks support for some common types (e.g., unsigned integers) compared to other databases. Generic types should however work with any database, even though your mileage may vary.
Changing primary keys can be as problematic as with normal Sequel migrations, so it's best to set them once and stick with them.
It is currently not possible to describe renaming of columns or tables. If you need that, simply rename them directly in the database or by using standard Sequel migration, and adjust the schema description accordingly.
Copyright © 2015-2019 Patrik Rak
Miguel is released under the MIT license.