Topic
Definition
A table is a named collection of rows that all share the same fixed set of typed columns, and each column definition fixes a name, a data type, and any constraints for every row.
In simpler words
A table is a strict spreadsheet: every row has the same columns, and each column only accepts one kind of value.
Everything else in this week — keys, joins, TypeORM entities — is just more structure on top of tables and columns. Read raw SQL before you read decorators.
Definition
A CREATE TABLE statement declares a table name and an ordered list of columns, each with a data type and optional constraints, before any row exists.
In simpler words
It is the blueprint: nothing is stored yet, but every future row must match this shape.
This repo’s first migration creates two tables this way — read it like a spec, not like magic.
Every column line has the same anatomy: name, type, then constraints (NOT NULL is implicit unless the column is nullable).
CREATE TABLE "tickets" (
"id" uuid NOT NULL DEFAULT gen_random_uuid(),
"title" character varying(200) NOT NULL,
"description" text,
"status" character varying(32) NOT NULL DEFAULT 'open',
"assignee_id" uuid,
"created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
"updated_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT "PK_tickets" PRIMARY KEY ("id")
);description and assignee_id omit NOT NULL — they are the only nullable columns on this table.
Definition
A column’s data type constrains which values Postgres accepts, how much space it reserves, and which operations (sorting, comparing, indexing) are valid on it.
In simpler words
The type is a promise: “this column is always a UUID / always text / always a timestamp,” so the database can reject anything that breaks that promise.
uuid — used for every primary key here; gen_random_uuid() (from the pgcrypto extension) generates one automatically.
character varying(n) — bounded text, good for titles and emails where a max length is a real business rule.
text — unbounded text, used for ticket.description where a length cap would be arbitrary.
timestamptz — always store timestamps with timezone; created_at/updated_at both use it with a now() default.
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- then columns can default to gen_random_uuid()Without pgcrypto, gen_random_uuid() does not exist and the default fails.
Definition
A constraint restricts which values a column or row may take, such as forbidding NULL, requiring uniqueness, or supplying a value when none is given.
In simpler words
Constraints are how the table enforces its own rules, so bad data cannot slip in even if application code has a bug.
NOT NULL (the default unless a column is written without it) — description and assignee_id are the exceptions on tickets.
UNIQUE — users.email has UQ_users_email so two accounts can never share an email.
DEFAULT — tickets.status defaults to 'open'; users.role defaults to 'member'; both save a value even if the app forgets to send one.
"email" character varying NOT NULL,
...
CONSTRAINT "UQ_users_email" UNIQUE ("email")A duplicate INSERT on email raises a Postgres error before any application code runs.
Test
At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥ 80% (enforced by the API) to save progress.
Checking your session…
10 questions · concept 3 · syntax 2 · practical 3 · logic 2