Build a PostgreSQL notes application

Let’s finish with one runnable project that ties the course together. We will create the roles and database, build the schema, run a Node.js query, check permissions, inspect a query plan, and prove a backup restores.

Create the roles and database as the PostgreSQL administrator. Use 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;

notes_owner owns objects but never logs in. notes_app is the runtime role your application uses. The GRANT line lets you run migrations as yourself while SET ROLE notes_owner creates objects under the 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;

You should see CREATE SCHEMA, three CREATE TABLE lines, and CREATE INDEX as each statement succeeds.

Apply the runtime grants and default privileges from the permissions lesson. Set the search path for the runtime role:

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

Install pg, set DATABASE_URL to connect as notes_app, 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. You should see { id: '1', title: 'Plan the week' } printed to the terminal.

Connect as notes_app in psql and confirm that DROP TABLE app.notes fails with a permission error. The runtime role can change rows but cannot destroy the schema.

Inspect the title lookup with EXPLAIN (ANALYZE, BUFFERS). You should see an Index Scan using notes_title_idx, which confirms the index from the CREATE TABLE step is doing its job.

Add an archived_at TIMESTAMPTZ column as an expand step, deploy code that tolerates it, and leave destructive cleanup for another migration. That mirrors how you would evolve a live application without downtime.

Finally, dump notes_app, restore it into notes_restore, and query the inserted note there:

pg_dump --format=custom --file=notes.dump notes_app
createdb --template=template0 notes_restore
pg_restore --exit-on-error --no-owner --dbname=notes_restore notes.dump
psql notes_restore -c "SELECT title FROM app.notes WHERE title = 'Plan the week'"

The lab is complete when the runtime restriction, Node query, migration, plan, and restore all work.