Database Schema Migrations

DBMate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers. We have pre-installed it in your devcontainer

After that we can setup our migrations folder and then create a users migration that matches the auth schema used elsewhere in this guide.

Create a Migration

dbmate new user_tables
# Creating migration: crates/db/migrations/20220330110026_user_tables.sql

Edit the SQL file that was generated for you and add the following.

-- migrate:up
CREATE SCHEMA IF NOT EXISTS auth;

CREATE TABLE IF NOT EXISTS auth.users (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    external_id text UNIQUE NOT NULL,
    email text UNIQUE,
    first_name text,
    last_name text,
    created_at timestamptz NOT NULL DEFAULT NOW(),
    updated_at timestamptz NOT NULL DEFAULT NOW()
);

INSERT INTO auth.users(external_id, email, first_name, last_name) VALUES
  ('auth0|user-1', '[email protected]', 'Test1', 'User'),
  ('auth0|user-2', '[email protected]', 'Test2', 'User'),
  ('auth0|user-3', '[email protected]', 'Test3', 'User');

-- migrate:down
DROP TABLE IF EXISTS auth.users;
DROP SCHEMA IF EXISTS auth;

Run the Migrations

List the migrations so we can see which have run.

dbmate status
#[ ] 20220330110026_user_tables.sql
#
#Applied: 0
#Pending: 1

Run our new migration.

dbmate up
#Applying: 20220330110026_user_tables.sql

And check that it worked.

psql $DATABASE_URL -c 'SELECT count(*) FROM auth.users;'

And you should see

 count 
-------
      3
(1 row)

Your project folders should now look like this.

├── .devcontainer/
│   └── ...
└── crates/
│         web-server/
│         │  └── main.rs
│         └── Cargo.toml
│         db/
│         ├── migrations
│         │   └── 20220330110026_user_tables.sql
│         └── schema.sql
├── Cargo.toml
└── Cargo.lock