ecto_by_example

Migrations

Create a table with some basic Ecto data types

create table(:pokemons) do
  add :name, :string, null: false
  add :level, :integer, null: false, default: 1
  add :special, :float, null: false, default: 1.0
  add :wild, :boolean, null: false, default: true
end

                                 Table "public.pokemons"
 Column  |          Type          |                       Modifiers
---------+------------------------+-------------------------------------------------------
 id      | integer                | not null default nextval('pokemons_id_seq'::regclass)
 name    | character varying(255) | not null
 level   | integer                | not null default 1
 special | double precision       | not null default 1.0
 wild    | boolean                | not null default true
Indexes:
    "pokemons_pkey" PRIMARY KEY, btree (id)

Create a table with some database-specific data types

create table(:pokemons) do
  add :name, :varchar, null: false
  add :level, :smallint, null: false, default: 1
  add :special, :decimal, null: false, default: 1.0
  add :wild, :boolean, null: false, default: true
end

                               Table "public.pokemons"
 Column  |       Type        |                       Modifiers
---------+-------------------+-------------------------------------------------------
 id      | integer           | not null default nextval('pokemons_id_seq'::regclass)
 name    | character varying | not null
 level   | smallint          | not null default 1
 special | numeric           | not null default 1.0
 wild    | boolean           | not null default true
Indexes:
    "pokemons_pkey" PRIMARY KEY, btree (id)

Create a table with timestamps generated by Ecto

create table(:trainers) do
  add :name, :varchar, null: false

  timestamps()
end

                                      Table "public.trainers"
   Column    |            Type             |                       Modifiers
-------------+-----------------------------+-------------------------------------------------------
 id          | integer                     | not null default nextval('trainers_id_seq'::regclass)
 name        | character varying           | not null
 inserted_at | timestamp without time zone | not null
 updated_at  | timestamp without time zone | not null
Indexes:
    "trainers_pkey" PRIMARY KEY, btree (id)

Create a table with custom timestamps

create table(:trainers) do
  add :name, :varchar, null: false
  add :created_at, :timestamptz, null: false
  add :updated_at, :timestamptz, null: false
end

                                    Table "public.trainers"
   Column   |           Type           |                       Modifiers
------------+--------------------------+-------------------------------------------------------
 id         | integer                  | not null default nextval('trainers_id_seq'::regclass)
 name       | character varying        | not null
 created_at | timestamp with time zone | not null
 updated_at | timestamp with time zone | not null
Indexes:
    "trainers_pkey" PRIMARY KEY, btree (id)

Create a table with custom timestamps with defaults

create table(:trainers) do
  add :name, :varchar, null: false
  add :created_at, :timestamptz, null: false, default: fragment("now()")
  add :updated_at, :timestamptz, null: false, default: fragment("now()")
end

                                    Table "public.trainers"
   Column   |           Type           |                       Modifiers
------------+--------------------------+-------------------------------------------------------
 id         | integer                  | not null default nextval('trainers_id_seq'::regclass)
 name       | character varying        | not null
 created_at | timestamp with time zone | not null default now()
 updated_at | timestamp with time zone | not null default now()
Indexes:
    "trainers_pkey" PRIMARY KEY, btree (id)

Create a table without a primary key

create table(:labels, primary_key: false) do
  add :name, :varchar, null: false
end

         Table "public.labels"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 name   | character varying | not null

Create a table with a natural primary key

create table(:labels, primary_key: false) do
  add :name, :varchar, primary_key: true
end

         Table "public.labels"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 name   | character varying | not null
Indexes:
    "labels_pkey" PRIMARY KEY, btree (name)

Create a table with a UUID primary key

create table(:labels, primary_key: false) do
  add :id, :uuid, primary_key: true
  add :name, :varchar, null: false
end

         Table "public.labels"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 id     | uuid              | not null
 name   | character varying | not null
Indexes:
    "labels_pkey" PRIMARY KEY, btree (id)

Create tables with a one-to-many relationship

create table(:trainers) do
  add :name, :varchar, null: false
end

create table(:pokemons) do
  add :name, :string, null: false
  add :level, :integer, null: false, default: 1
  add :trainer_id, references(:trainers)
end

                              Table "public.trainers"
 Column |       Type        |                       Modifiers
--------+-------------------+-------------------------------------------------------
 id     | integer           | not null default nextval('trainers_id_seq'::regclass)
 name   | character varying | not null
Indexes:
    "trainers_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "pokemons" CONSTRAINT "pokemons_trainer_id_fkey" FOREIGN KEY (trainer_id) REFERENCES trainers(id)

                                   Table "public.pokemons"
   Column   |          Type          |                       Modifiers
------------+------------------------+-------------------------------------------------------
 id         | integer                | not null default nextval('pokemons_id_seq'::regclass)
 name       | character varying(255) | not null
 level      | integer                | not null default 1
 trainer_id | integer                |
Indexes:
    "pokemons_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "pokemons_trainer_id_fkey" FOREIGN KEY (trainer_id) REFERENCES trainers(id)

Create tables with a custom foreign-key one-to-many relationship

create table(:trainers, primary_key: false) do
  add :identifier, :uuid, primary_key: true
  add :name, :varchar, null: false
end

create table(:pokemons) do
  add :name, :string, null: false
  add :level, :integer, null: false, default: 1
  add :trainer_id, references(:trainers, column: :identifier, type: :uuid)
end

          Table "public.trainers"
   Column   |       Type        | Modifiers
------------+-------------------+-----------
 identifier | uuid              | not null
 name       | character varying | not null
Indexes:
    "trainers_pkey" PRIMARY KEY, btree (identifier)
Referenced by:
    TABLE "pokemons" CONSTRAINT "pokemons_trainer_id_fkey" FOREIGN KEY (trainer_id) REFERENCES trainers(identifier)

                                   Table "public.pokemons"
   Column   |          Type          |                       Modifiers
------------+------------------------+-------------------------------------------------------
 id         | integer                | not null default nextval('pokemons_id_seq'::regclass)
 name       | character varying(255) | not null
 level      | integer                | not null default 1
 trainer_id | uuid                   |
Indexes:
    "pokemons_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "pokemons_trainer_id_fkey" FOREIGN KEY (trainer_id) REFERENCES trainers(identifier)