Migrations

This section is based on Jennifer Docs.

To generate a migration run crystal src/sam.cr generate:migration your_migration_name

Migration DSL

The generator will create template file for you with a consistent name pattern timestamp_migration_name.cr. The empty file looks like this:

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

  def down
  end
end

The up method is where your database changes go, whereas, down is used for reverting your changes back.

Example for creating table:

create_table(:addresses) do |t|
  t.reference :contact # creates field contact_id with Int type and allows null values
  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

Data Types and Mappings

internal aliasPostgreSQLMySqlCrystal type

#integer

int

int

Int32

#string

varchar(254)

varchar(254)

String

#bool

boolean

bool

Bool

#char

char

-

String

#float

real

float

Float32

#double

double precision

double

Float64

#short

smallint

smallint

Int16

#timestamp

timestamp

timestamp

Time

#date_time

datetime

datetime

Time

#blob

blob

blob

Bytes

#var_string

varchar(254)

varstring

String

#json

json

json

JSON::Any

#enum

enum

enum

String

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

All of them accepts additional options:

  • :sql_type- gets exact (except size) field type;

  • :null- represent nullable if field (by default isfalsefor 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 withInt32fields, another crystal types have cut functionality for it);

  • :array- mark field to be array type (Postgres only)

Also there is#fieldmethod which allows to directly define sql type (very suitable for enums in Postgres).

To drop table just write

drop_table(:addresses) # drops if exists

To alter existing table use next methods:

  • #change_column(name, [new_name], options)- to change column definition; Postgres has slighly another implementation of this than mysql one - check source code for details;

  • #add_column(name, type, options)- add new column;

  • #drop_column(name)- drops existing column

  • #add_index(name : String, field : Symbol, type : Symbol, order : Symbol?, length : Int32?)- adds new index (Postgres doesn't support length parameter and only support:uniquetype);

  • #drop_index(name : String)- drops existing index;

  • #rename_table(new_name)- renames table.

Also next support methods are available:

  • #table_exists?(name)

  • #index_exists?(table, name)

  • #column_exists?(table, name)

  • #data_type_exists?(name)for Postgres ENUM

Also plain SQL could be executed as well:

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

All changes are executed one by one so you also could add data changes here (inupmethod) but if execution ofupmethod fails -downmethod will be called and all process will stop - be ready for such behavior.

To be sure that your db is up to date before run tests of your application, add:

Jennifer::Migration::Runner.migrate

Enum

Now enums are supported as well but it has different implementation for adapters. For mysql is enough just write down all values:

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

Postgres provide much more flexible and complex behavior. Using it you need to create it 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.

Last updated