Schemas & migrations

Two artifacts describe a table, and they are deliberately separate. A migration is a versioned instruction to the database — create this table, add that column — checked in and run in order, so the schema of production is the sum of migrations applied. A schema is the Elixir side: schema "courses" do field ... end declares the fields and gives you a struct, so a row reads as %Course{} with typed fields you pattern-match. They are not redundant — the migration owns the database, the schema owns the struct, and they can differ. One detail carries the whole course’s identity convention into the database: the primary key is a Snowflake bigint that Portal.ID mints, not a database serial, so a row’s id is the same time-ordered value the engine has used since F4.

See the three layers a table touches, watch a column map across them, then read a real migration and schema with a Snowflake primary key.

Three layers A column, mapped across A migration and a schema

Three layers

A migration shapes the table in the database; a schema declares the fields in Elixir; a struct is the value you hold at runtime. The migration runs once per environment, the schema is compiled into your app, and the struct is created for every row you load. Select a layer.

Table layers · select one

layer: Schema

is: a table ↔ struct mapping

Keeping the migration and the schema separate is what lets the database evolve safely: a migration adds a column to existing rows without touching code, and the schema picks it up when you choose to declare it.

A column, mapped across

The three layers line up field by field. A column in the table corresponds to a field in the schema and a key in the struct. The id column is a bigint holding a Snowflake; the schema declares it as the primary key without autogeneration; the struct carries the branded id you decode with Portal.ID.

courses · table → schema → struct

A schema is a lens, not a cage. It names the columns you care about as struct fields; the database can hold more, and a read populates only what the schema declares.

A migration and a schema

The migration declares the table with primary_key: false and an explicit :bigint id, because the Portal mints its own Snowflakes rather than letting the database assign a serial. The schema mirrors that with @primary_key {:id, :id, autogenerate: false} — the id is supplied, not generated.

# migration — define the table; id is a Snowflake bigint, not a serial
defmodule Portal.Repo.Migrations.CreateCourses do
  use Ecto.Migration

  def change do
    create table(:courses, primary_key: false) do
      add :id,        :bigint,  primary_key: true     # Snowflake id (CRS...)
      add :title,     :string,  null: false
      add :published, :boolean, null: false, default: false
      timestamps(type: :utc_datetime)
    end
  end
end

# schema — map the table to a struct we work with as %Course{}
defmodule Portal.Catalog.Course do
  use Ecto.Schema

  @primary_key {:id, :id, autogenerate: false}   # we mint Snowflakes via Portal.ID
  schema "courses" do
    field :title,     :string
    field :published, :boolean, default: false
    timestamps(type: :utc_datetime)
  end
end

the migration

Owns the database: a versioned instruction that creates the :courses table.

the schema

Owns the struct: %Course{} with a Snowflake id and typed fields.

Next: changesets & validation — before a %Course{} is written, a changeset casts and checks the data.