A place for the data, rules for what belongs there

An address lets someone reach the app; a place to keep their work lets them come back to it. We have already compared browser storage, files and databases. Now we will build a small PostgreSQL schema for a notebook and see what its rules actually enforce.

Use a fresh disposable local database so the tables and ids below start empty. Other databases differ in syntax and guarantees.

Connecting

A database is a program at an address, listening on a port, and PostgreSQL's usual port is 5432. Your backend connects to it with a connection string.

DATABASE_URL=postgres://notes_app:a-long-password@db.example.com:5432/notes

It contains a password, so it is a secret. It lives in the environment, on the server, separately for dev and prod. The browser talks to your server and your server talks to the database. The browser never holds this string.

A small schema

create table users (
  id bigint generated always as identity primary key,
  email text not null unique
);

create table notes (
  id bigint generated always as identity primary key,
  owner_id bigint not null references users (id),
  title text not null,
  body text not null default '',
  created_at timestamptz not null default now()
);

Each column has a type and some rules. not null refuses a missing value. unique refuses a second account with the same email. references users (id) is a foreign key: owner_id must match a user that exists.

owner_id is the relation between the two tables. The database does not keep a list of notes inside each user. It keeps notes that point at their owner, and a user has many notes because many notes point at them.

Try to break it.

insert into users (email) values ('ada@example.com');
insert into notes (owner_id, title) values (1, 'Hem');
insert into notes (owner_id, title) values (999, 'Orphan');

The first two succeed. The third is refused, because there is no user 999. That refusal is the schema doing its job. The bad row never enters, so no code later has to wonder what to do with it.

Querying from code

The notebook's main screen needs the newest notes of the person looking at it. The following is a backend handler fragment using a configured node-postgres Pool called pool and a server-validated session. It illustrates the query boundary; it is not a standalone script. Authentication and sessions are developed in the permission chapter.

const { rows } = await pool.query(
  'select id, title, created_at from notes where owner_id = $1 order by created_at desc limit 20',
  [session.userId]
);

Two details matter. $1 is a placeholder, and the value travels separately from the SQL, so whatever it contains is treated as a value and never as part of the query. Building SQL by pasting strings together is how injection happens. And session.userId comes from the session on the server, never from a number the browser sent claiming who it is. The where clause is the permission check. Leave it out and the query returns everyone's notes.

Why some queries are slow

Without a suitable access path, the database may scan notes to find matching owners. Whether that is expensive depends on the data, hardware and concurrent work; measure the query rather than inferring speed from row count alone.

An index is a separate sorted structure that lets the database jump to matching rows, like the index at the back of a book.

create index notes_owner_created on notes (owner_id, created_at desc);

This one fits the query above exactly. It groups rows by owner, keeps each owner's notes in date order, and the database can read the first twenty and stop. Indexes are built for queries, not for tables. This index does not support a title-only substring search such as where title ilike '%hem%'. That is a different access pattern, requiring its own query and index decision.

An index is not a constant-time lookup. The usual kind, a B-tree, gets slower very gradually as the table grows instead of in proportion to it, which is the whole gain. It also has costs: inserts and relevant updates must maintain index entries, and each index takes disk space. Add an index for a query you actually run, then measure.

explain analyze
select id, title, created_at from notes
where owner_id = 1 order by created_at desc limit 20;

Look for Index Scan or Seq Scan in the output, along with the time. On a tiny table PostgreSQL may scan everything even when the index exists, because reading a few rows directly is cheaper. That is the planner being sensible, not the index failing. Primary keys and unique columns get indexes automatically.

Your schema

Write a schema for the main thing your app stores, with an owner column and the rules a valid row must follow. Then write the query your main screen runs, and run explain analyze on it with realistic amounts of data in a dev database.