Lucky Logo

# Migrating Data

# Introduction

You can think of migrations like version control for your database. These are classes that are scoped with a timestamp to allow you to update your database in versions as well as undo changes that you’ve made previously.

# Anatomy

A new migration file name will always start with a timestamp. This is to keep the order in which they are ran. You may have a migration that adds a column, and then a year later, another one that removes that column. You want to run these in the correct order.

In that file, you’ll find two methods migrate, and rollback.

  • migrate - This method runs your new SQL when moving forward (migrating).
  • rollback - This method should undo everything the migrate method does.

The rollback method should do the opposite of migrate in reverse order. (e.g. If migrate adds a column, then adds an index for that column, rollback should remove the index first, then remove the column.)

# DB Tasks

Lucky comes with several tasks you can run for handling your migrations.

  • db.migrate - Run all pending migrations.
  • - Run only the next pending migration.
  • db.redo - Rollback to the previous migration, then re-run the last migration again.
  • db.rollback - Undo the last migration ran.
  • db.rollback_to MIGRATION_TIMESTAMP - Undo all migrations back to MIGRATION_TIMESTAMP
  • db.rollback_all - Undo all of the migrations back to the beginning.
  • db.migrations.status - Displays the current status of migrations.
  • db.verify_connection - Tests that Avram can connect to your database.
  • db.setup - Create then migrate your database.

Learn more about tasks

# Generating a new migration

To create a new migration, run the lucky gen.migration {PurposeOfMigration}. (e.g. If you need to add a name column to users, you would run lucky gen.migration AddNameToUsers). This would generate a db/migrations/ file.

There is a task for generating a new model lucky gen.model. When you generate a new model, a migration file is created for you.

# The table_for macro

Most migrations will use table_for to generate a table name from the passed in class. The generated table name will be the pluralized and underscored name of the class.

For example:

# table_for will return :completed_projects
create table_for(CompletedProject) do
  # Add columns

The table name generated by table_for will match the model when the model uses the default table name (no arg passed to table). This ensures your table names will match up.

class CompletedProject < Avram::Model
  # By default uses the pluralized and underscored class name.
  table do
    # Define columns

# Non default tables names

If you need something custom you can use a symbol in place of table_for.

So if you have a custom table name in your model:

class ArchivedUsers < Avram::Model
  table :legacy_users do

You would then use the same table name in your migration:

alter :legacy_users do

# Create table or view

# Creating a table

Use the create method for creating a table. You will place all of the table definitions inside of the create block.

def migrate
  create table_for(User) do
    # Add column definitions here. Shown later in the guide

Use this in conjunction with the table macro for your models. See setting up a model for more information.

You can also use a symbol for a table name. For example create :users.

# Create a view

A SQL VIEW is simlar to a table, except read-only, and doesn’t normally include any sort of primary key. These are great for pulling data from a larger table in to a smaller set for quicker access, or if you need to combine data from multiple tables.

It will be up to you to decide where the data will come from.

def migrate
  execute <<-SQL
  CREATE VIEW admin_users AS
    SELECT users.*
    FROM users
    JOIN admins on =;

# Primary keys

You will need to specify your primary key in your create block using the primary_key method. Avram currently supports these key types:

  • Int16 - maps to postgres smallserial.
  • Int32 - maps to postgres serial.
  • Int64 - maps to postgres bigserial.
  • UUID - maps to postgres uuid. Generates V4 UUID

To specify your primary key, you’ll use the primary_key method.

def migrate
  create table_for(User) do
    # creates a primary key column named `id`
    primary_key id : Int64

Avram expects the primary key name to be id, but Avram supports using any primary key column name. (e.g. primary_key custom_name : Int64)

# Timestamps

create table_for(User) do
  # adds `created_at : Time`, and `updated_at : Time` columns

# Add column

To add a new column, you’ll use the add method inside of a create or alter block.

create table_for(User) do
  add email : String
  add birthdate : Time
  add login_count : Int32, default: 0
  add tags : Array(String)
  add preferences : JSON::Any
alter table_for(User) do
  add last_known_ip : String?

# Case-Insensitive (citext)

Avram supports the postgres citext column type. To use this, you’ll need to enable to citext extension first. Then specify the column as a String type, and use the option case_sensitive: false.

# Be sure to add this line!
enable_extension "citext"

alter table_for(User) do
  add email : String, case_sensitive: false

# Making columns required or optional

By default columns are required (NOT NULL in SQL terms). You can allow nulls by adding a ? to the type.

For example, this is required:

add name : String

But adding ? will tell Avram to make this column optional (allow NULL):

add name : String?

# Datatypes

There are a few different datatypes that you can use to declare the column type.

  • String - Maps to postgres text.
  • Int16 - Maps to postgres smallint.
  • Int32 - Maps to postgres int.
  • Int64 - Maps to postgres bigint.
  • Time - Maps to postgres timestamptz.
  • Bool - Maps to postgres boolean.
  • Float64 - Maps to postgres decimal. With options for precision and scale.
  • UUID - Maps to postgres uuid.
  • JSON::Any - Maps to postgres jsonb.
  • Array(T) - Maps to postgres array fields where T is any of the other datatypes.

# Advanced Options

The add method takes several options for further customization when adding a field.

  • index - false by default. When true, this will create an index on this column.
  • using - :btree by default. The index method to use when an index is created.
  • unique - false by default. When true, postgres will enforce a unique constraint on this field.
  • default - The default value to use for this column.
create table_for(User) do
  add email : String, index: true, unique: true
  add login_count : Int32, default: 0

# Drop table or view

To drop a table, use the drop method.

def migrate
  drop table_for(User)

Remember, if your migrate method runs create, then the rollback method should run drop.

You can also use a symbol for a table name. For example create :users.

To drop a view, use execute with a DROP VIEW SQL call.

def rollback
  execute "DROP VIEW admin_users;"

# Alter table

If your table exists, and you need to make changes to this table, use the alter method. All of your changes will go in the alter block.

def migrate
  alter table_for(User) do
    remove :old_field

You can also use a symbol for a table name. For example create :users.

# Using fill_existing_with and default values

When using the add method inside an alter block, there’s an additional option fill_existing_with.

  • fill_existing_with will backfill existing records with this value; however, new records will not have any values by default.
  • default will set the column’s default value, and postgres will automatically backfill existing records with this default.

Since these options solve similar problems, they can’t both be used at the same time.

alter table_for(User) do
  # set all existing, and future users `active` to `true`
  add active : Bool, default: true

  # set all existing users `otp_code` to `"fake-otp-code-123".
  # New users will require a value to be set.
  add otp_code : String, fill_existing_with: "fake-otp-code-123"

You can also use fill_existing_with: :nothing if your table is empty.

If a static value will not work, try this:

  • If you have not yet released the app, consider using fill_existing_with: :nothing and resetting the database with lucky db.reset.
  • Consider making the type nilable (example: add otp_code : String?), then fill the values with whatever value you need. Then make it required with make_required :users, :otp_code. See the example below:

# Filling a newly generated column with custom data

This will require 2 separate migrations. The first migration will run and create the new column as a “NULLABLE” column. The second migration will set the value on that column, then ensure the column is not NULLABLE.

  • Run lucky gen.migration AddOtpCodeToUsers
# db/migrations/
def migrate
  alter table_for(User) do
    # Add nullable column first
    add otp_code : String?

def rollback
  alter table_for(User) do
    remove :otp_code
  • Run lucky gen.migration FillOtpCodeAndMakeRequired

For this migration, we will create a custom model that is used to both query, and update the table. This pattern allows us to keep this migration “future-proof” from potential model changes.

# db/migrations/
class TempOTPUser < Avram::Model

  def self.database : Avram::Database.class

  table :users do
    primary_key id : UUID # or whatever your PKEY type is...
    column otp_code : String?

def migrate do |user|
    TempOTPUser::SaveOperation.update!(user, otp_code: Random::Secure.urlsafe_base64)

  make_required table_for(User), :otp_code

def rollback
  make_optional table_for(User), :otp_code

# Change column type

To change your column type, you’ll use the change_type macro. This is very useful for when you need to change a column from one type to another. One example may be updating your primary key from Int32 to Int64.

alter table_for(User) do
  # update your `id` column from postgres `integer` to `bigint`
  change_type id : Int64

You can also update some of the options passed to a column such as a float precision.

alter table_for(Transaction) do
  change_type amount : Float64, precision: 4, scale: 2

# Rename column

The rename method must go in the alter block.

alter table_for(User) do
  rename :old_name, :new_name
  rename :birthday, :birthdate

# Remove column

The remove method must go in the alter block.

alter table_for(User) do
  remove :middle_name
  remove :last_login_ip

# Add index

The easiest way is to add the index: true option on the add method. However, if you’re adding indices after the table is created, you can use the create_index method.

def migrate
  create_index :users, [:status], unique: false

The name of the index will be generated for you automatically. If you would like to use your own custom index name, you can pass the name option.

def migrate
  create_index :users,
               [:status, :joined_at, :email],
               name: "special_status_index"

Postgres imposes a 63 byte limit on identifiers. If you create an index on a lot of columns, you will want to create a custom index name to avoid it being truncated. read more

# Remove index

Use the drop_index method to remove the index.

def migrate
  drop_index :users, [:status]

# Associations

If your tables have a one-to-many or a many-to-many relation, you can use these methods to create a foreign key constraint, or remove that constraint from your tables.

Learn more about associations with models

# Add belongs_to

The add_belongs_to method will create that foreign key constraint.

For example, we can tell our database that a Comment should reference a User as its author:

def migrate
  create table_for(Comment) do
    # This will create an author_id column with an index and a foreign_key
    # set to the users table.
    # When the associated author is deleted, their comments are also deleted
    # because we set on_delete: :cascade
    add_belongs_to author : User, on_delete: :cascade

This will generate a column called author_id on the comments table with a foreign key constraint pointing to an entry in the users table. It will also ensure that when a User is removed from the database, all associated Comments are also removed:

comments_author_id_fkey" FOREIGN KEY (author_id)

You must include the on_delete option which can be one of

  • :cascade - if the parent (i.e. User) is deleted, then also delete the associated records in this table (i.e. comments)
  • :restrict - if the parent is deleted, and there are associated records, then restrict the parent (i.e. User) from being deleted.
  • :nullify - if the parent is deleted, it should nullify the foreign key for all associated records. This should only be used if the belongs_to is optional.
  • :do_nothing - just do nothing.

If the foreign key is UUID, you will need to specify the foreign_key_type option so the proper type is added.

def migrate
  create table_for(Comment) do
    primary_key id : UUID
    add_belongs_to author : User, on_delete: :cascade, foreign_key_type: UUID

# Rename belongs_to

When you need to rename the association, you can use rename_belongs_to.

alter table_for(Employee) do
  # rename `boss_id` to `manager_id`
  rename_belongs_to :boss, :manager

# Remove belongs_to

When you need to remove the association, you can use remove_belongs_to.

def migrate
  alter table_for(Comment) do
    # This will drop the author_id column
    remove_belongs_to :author

# Postgres Extensions

Postgres extensions allow you to enhance your database setup with new functionality. Some common extensions are adding UUID functions, or using postgis to do geographic queries. Avram includes a few methods for enabling and disabling these extensions.

# Enable extension

def migrate
  enable_extension "uuid-ossp"

# Disable extension

def rollback
  disable_extension "uuid-ossp"

# Update extension

def migrate
  # Update to the latest version of the extension
  update_extension "hstore"

  # or update to a specific version
  update_extension "hstore", to: "2.0"

# Custom SQL

Sometimes SQL can get really complicated, and sometimes it may just be something simple that Avram doesn’t support just yet. Don’t worry, you still have access to run raw SQL directly.

Use the execute method to run any SQL directly.

def migrate
  execute <<-SQL
  CREATE TABLE admins (
    id character varying(18) NOT NULL,
    email character varying NOT NULL,
    password_digest character varying NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL

You can also use execute for doing things like CREATE FUNCTION or CREATE EXTENSION.

See a problem? Have an idea for improvement? Edit this page on GitHub