McElfresh Blog

Go, PostgreSQL, MySQL, Ruby, Rails, Sinatra, etc.

Create Related Records in Postgres

Posted at — Mar 2, 2024

Start with Dbmate

Dbmate is a SQL database migration tool written in Go. It is the simplest, most usable migration tool I’ve found, and I highly encourage you to consider using it in your project, regardless of your project’s language.

Here are a few Dbmate migrations you can use to create the tables we’ll need for this post:

1$ dbmate new create_users_table

Inside your newly created migration:

 1-- migrate:up
 2
 3CREATE TABLE users (
 4    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 5    email TEXT NOT NULL,
 6    created_at timestamptz DEFAULT NOW(),
 7updated_at timestamptz DEFAULT NOW()
 8);
 9
10CREATE INDEX idx_user__email ON users (email);
11
12-- migrate:down
13
14DROP TABLE users;
1> dbmate new create_items_table

Inside your newly created migration:

 1-- migrate:up
 2
 3CREATE TABLE items (
 4    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 5    name TEXT NOT NULL,
 6    created_at timestamptz NOT NULL DEFAULT NOW(),
 7    updated_at timestamptz NOT NULL DEFAULT NOW()
 8);
 9
10CREATE UNIQUE INDEX idx_name_uniq ON items (name);
11
12-- migrate:down
13
14DROP TABLE items;
1dbmate new create user_items_table

Inside your newly created migration:

 1-- migrate:up
 2
 3CREATE TABLE user_items (
 4    user_id uuid NOT NULL REFERENCES users(id) DEFERRABLE,
 5    item_id uuid NOT NULL REFERENCES items(id) DEFERRABLE,
 6    created_at timestamptz NOT NULL DEFAULT NOW(),
 7    updated_at timestamptz NOT NULL DEFAULT NOW()
 8);
 9
10CREATE UNIQUE INDEX idx_user_items__user_id_item_id_uniq ON user_items (user_id, item_id);
11
12-- migrate:down
13
14DROP TABLE user_items;

Run this query

Common Table Expressions (CTEs), in addition to providing a clean way to express SQL, provide control over the order in which commands are run, as well as the ability to wrap complex commands inside one transaction. Here, we create a users record, and an items record, then a user_items record, all inside one transaction:

 1WITH u AS (
 2    INSERT INTO users (email) VALUES ('user@example.com') RETURNING id
 3), i AS (
 4    INSERT INTO items (name) VALUES ('Widget 01') RETURNING id
 5), u_and_i AS (
 6    SELECT
 7        u.id AS user_id, i.id AS item_id
 8    FROM
 9        u
10    JOIN
11        i ON 1=1
12)
13INSERT INTO
14    user_items (user_id, item_id)
15SELECT
16    user_id, item_id FROM u_and_i