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 | Int64 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

If 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
# shard.yml
dependencies:
  micrate:
    github: "amberframework/micrate"
    version: "= 0.12.0"
  • add an override for a crystal-db to enforce latest version
# shard.override.yml
dependencies:
  db:
    github: crystal-lang/crystal-db
    version: ~> 0.11.0
  • ensure your Jennifer configuration has pool_size set to at least 2
  • 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)
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