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