Build a PostgreSQL notes application

Let’s finish with one runnable project.

Create the roles and database as the PostgreSQL administrator, using your real administrator role in the membership grant:

CREATE ROLE notes_owner NOLOGIN;
CREATE ROLE notes_app LOGIN;
\password notes_app
GRANT notes_owner TO CURRENT_USER
  WITH INHERIT FALSE, SET TRUE;
CREATE DATABASE notes_app OWNER notes_owner;

Connect to notes_app, then create the schema as its owner:

\connect notes_app
SET ROLE notes_owner;

CREATE SCHEMA app AUTHORIZATION notes_owner;

CREATE TABLE app.notes (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE app.tags (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE app.note_tags (
  note_id BIGINT NOT NULL REFERENCES app.notes(id) ON DELETE CASCADE,
  tag_id BIGINT NOT NULL REFERENCES app.tags(id) ON DELETE CASCADE,
  PRIMARY KEY (note_id, tag_id)
);

CREATE INDEX notes_title_idx ON app.notes(title);
RESET ROLE;

Apply the runtime grants and default privileges from the permissions lesson. Set its path:

ALTER ROLE notes_app IN DATABASE notes_app
SET search_path = app, pg_catalog;

Install pg, set DATABASE_URL, and save this as app.mjs:

import pg from 'pg'

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 5,
  connectionTimeoutMillis: 5000,
  statement_timeout: 10000,
  application_name: 'notes-lab',
})

try {
  const inserted = await pool.query(
    `INSERT INTO app.notes (title, body)
     VALUES ($1, $2)
     RETURNING id, title`,
    ['Plan the week', 'Choose three priorities']
  )

  const found = await pool.query(
    'SELECT id, title FROM app.notes WHERE id = $1',
    [inserted.rows[0].id]
  )

  console.log(found.rows[0])
} finally {
  await pool.end()
}

Run it with node app.mjs. Then connect as notes_app and confirm that DROP TABLE app.notes fails.

Inspect the title lookup with EXPLAIN (ANALYZE, BUFFERS). Add an archived_at TIMESTAMPTZ column as an expand step, deploy code that tolerates it, and leave destructive cleanup for another migration.

Finally, dump notes_app, restore it into notes_restore, and query the inserted note there. The lab is complete when the runtime restriction, Node query, migration, plan, and restore all work.