Migration

DSL

Generator will create template file for you with next name pattern “timestamp_your_underscored_migration_name.cr”. Empty file looks like this:

class YourCamelCasedMigrationName < Jennifer::Migration::Base
  def up
  end

  def down
  end
end

up method is needed for placing your db changes there, down - for reverting your changes back.

Regular example for creating table:

create_table(:addresses) do |t|
  # creates field contact_id with Int type, allows null values and creates foreign key
  t.reference :contact

  t.string :street, {:size => 20, :sql_type => "char"} # creates string field with CHAR(20) db type
  t.bool :main, {:default => false} # sets false as default value
end

There are next methods which presents corresponding types:

Method PostgreSQL MySql Crystal type
#integer int int Int32
#short SMALLINT SMALLINT Int16
#bigint BIGINT BIGINT Int64
#tinyint - TINYINT Int8
#float real float Float32
#double double precision double Float64
#numeric NUMERIC - PG::Numeric
#decimal DECIMAL DECIMAL PG::Numeric (pg); Float64 (mysql)
#string varchar(254) varchar(254) String
#char char - String
#text TEXT TEXT String
#bool boolean bool Bool
#timestamp timestamp datetime Time
#date_time timestamp datetime Time
#date date date Time
#blob blob blob Bytes
#json json json JSON::Any
#enum - ENUM String

In Postgres enum type is defined using custom user datatype which also is mapped to the String.

PostgreSQL specific datatypes:

Method Datatype Type
#oid OID UInt32
#jsonb JSONB JSON::Any
#xml XML String
#blchar BLCHAR String
#uuid UUID UUID
#timestampz TIMESTAMPZ Time
#point POINT PG::Geo::Point
#lseg lseg PG::Geo::LineSegment
#path PATH PG::Geo::Path
#box BOX PG::Geo::Box
#polygon POLYGON PG::Geo::Polygon
#line LINE PG::Geo::Line
#circle CIRCLE PG::Geo::Circle

Also if you use postgres array types are available as well: Array(Int32), Array(Char), Array(Float32), Array(Float64), Array(Int16), Array(Int32), Array(Int64), Array(String).

All those methods accepts additional options:

  • :sql_type - gets exact (except size) field type;
  • :null - present nullable if field (by default is false for all types and field);
  • :primary - marks field as primary key field (could be several ones but this provides some bugs with query generation for such model - for now try to avoid this).
  • :default - default value for field
  • :auto_increment - marks field to use auto increment (properly works only with Int32 fields, another crystal types have cut functionality for it);
  • :array - mark field to be array type (postgres only)

Also there is #field method which allows to directly define SQL type.

To define reference to other table you can use #reference:

create_table :pictures do |t|
  t.reference :user
  t.reference :attachable, { :polymorphic => true } # for polymorphic relation
end

For more details about this and other methods see Jennifer::Migration::TableBuilder::CreateTable

To drop table just write:

drop_table(:addresses)

To create materialized view (postgres only):

create_materialized_view("female_contacts", Contact.all.where { _gender == "female" })

And to drop it:

drop_materialized_view("female_contacts")

To alter existing table use next methods:

  • #change_column - to change column definition;
  • #add_column - adds new column;
  • #drop_column - drops existing column;
  • #add_index - adds new index;
  • #drop_index - drops existing index;
  • #add_foreign_key - adds foreign key constraint;
  • drop_foreign_key - drops foreign key constraint;
  • #rename_table - renames table.

For more details about this and other methods see Jennifer::Migration::TableBuilder::CreateTable

Also next support methods are available:

  • #table_exists?
  • #index_exists?
  • #column_exists?
  • #foreign_key_exists?
  • #enum_exists? (for postgres ENUM only)
  • #material_view_exists?

Here is a quick example:

def up
  change_table(:contacts) do |t|
    t.change_column(:age, :short, {:default => 0})
    t.add_column(:description, :text)
    t.add_index(:description, type: :uniq, order: :asc)
  end

  change_table(:addresses) do |t|
    t.add_column(:details, :json)
  end
end

def down
  change_table(:contacts) do |t|
    t.change_column(:age, :integer, {:default => 0})
    t.drop_column(:description)
  end

  change_table(:addresses) do |t|
    t.drop_column(:details)
  end
end

Also plain SQL could be executed as well:

exec("ALTER TABLE addresses CHANGE street st VARCHAR(20)")

All changes are executed one by one so you also could add data changes here (in #up and/or #down).

To be sure that your db is up to date, add Jennifer::Migration::Runner.migrate in spec_helper.cr.

Enum

Now enums are supported as well but each adapter has own implementation. For mysql is enough just write down all values:

create_table(:contacts) do |t|
  t.enum(:gender, ["male", "female"])
end

Postgres provides much more flexible and complex behavior. Using it you need to create enum firstly:

create_enum(:gender_enum, ["male", "female"])

create_table(:contacts) do |t|
  t.string :name, {:size => 30}
  t.integer :age
  t.field :gender, :gender_enum
  t.timestamps
end

change_enum(:gender_enum, {:add_values => ["unknown"]})
change_enum(:gender_enum, {:rename_values => ["unknown", "other"]})
change_enum(:gender_enum, {:remove_values => ["other"]})

For more details check source code and PostgreSQL docs.

Micrate

It it is more convenient to you to store migrations in a plain SQL it is possible to use micrate together with Jennifer. To do so you need to add it to you dependencies and add micrate.cr file at the root (or any other convenient place) of your project with the following content:

require "micrate"
# Load here the part your your app responsible for Jennifer initialization
# require "./config/db.cr"

# These overrides are required to specify custom `db_dir`
module Micrate
  # Add here the path from your app root to the directory with `migration` folder
  # inside
  def self.db_dir
    "db"
  end

  private def self.migrations_by_version
    Dir.entries(migrations_dir)
      .select { |name| File.file?(File.join(migrations_dir, name)) }
      .select { |name| /^\d+_.+\.sql$/ =~ name }
      .map { |name| Migration.from_file(name) }
      .index_by { |migration| migration.version }
  end
end

Micrate::DB.connection_url = Jennifer::Adapter.default_adapter.connection_string(:db)
puts Dir.
Micrate::Cli.run

After this all migration files located in the specified directory is accessible for Micrate and you can use commands like

$ crystal /micrate.cr -- up