Skip to content

Latest commit

 

History

History
128 lines (95 loc) · 4.57 KB

migrations.md

File metadata and controls

128 lines (95 loc) · 4.57 KB

Migrations

{% hint style="info" %} This section is based on Jennifer Docs. {% endhint %}

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

Migration DSL

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

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

  def down
  end
end

upmethod is needed for placing your db changes there, down- 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 alias PostgreSQL MySql Crystal 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.