Tutorial 9: Connect a database

Objective: run the to-do app from Tutorial 8 on PostgreSQL — in development, in tests and in production — and understand how IWC plans migrations.

Prerequisites: Tutorial 8, and a PostgreSQL server you can create databases on (Postgres.app, Homebrew postgresql, Docker, or a hosted database).

What IWC supports

Database	Use
SQLite	Built in. Development (`.iwc/dev.db`), tests (in memory), and production for small apps (in the data folder)
PostgreSQL	Development, tests and production, from a `postgres://` URL

MySQL is not supported. A backup from one database restores into the other, so you can move an app between them.

Step 1: Name the variable that holds the URL

In app.iwc, database.url names an environment variable. Never put the URL itself in a file: credentials in source are compile errors.

app.iwc

{ "name": "Tasks", "database": { "url": { "env": "TASKS_DATABASE_URL" } } }

The complete file from the tasks project:

app.iwc

{
  "name": "Tasks",
  "iwc": 1,
  "theme": {
    "tokens": {
      "color-primary": "#5b4cf5",
      "color-on-primary": "#ffffff",
      "color-muted": "#5d6073",
      "color-surface": "#f6f4ff",
      "color-border": "#e6e6ef",
      "color-danger": "#c0262d",
      "radius": "12px",
      "font-body": "system-ui, sans-serif"
    }
  },
  "styles": ["styles/main.css"],
  "head": { "title": "%s · Tasks" },
  "auth": { "user": "user.schema.iwc", "identity": "email", "defaultRole": "member", "loginPage": "/login" },
  "database": { "url": { "env": "TASKS_DATABASE_URL" } },
  "notFound": "not-found.ui.iwc"
}

When `TASKS_DATABASE_URL` is…	`iwc dev` uses	`iwc start`
not set	SQLite in `.iwc/dev.db`	refuses to start, naming the variable
`postgres://…`	that PostgreSQL database	that PostgreSQL database
`sqlite`	the built-in SQLite file	SQLite in the data folder

An app that declares database.url reads only that variable, never a shared DATABASE_URL, so two apps on one machine can't write into each other's database.

Step 2: Create a database and look at the plan

Terminal

createdb tasks_dev
export TASKS_DATABASE_URL="postgres://localhost:5432/tasks_dev?sslmode=disable"
iwc migrate --dry-run

Output

CREATE TABLE "users" (
  "id" TEXT PRIMARY KEY,
  "name" TEXT NOT NULL,
  "email" TEXT NOT NULL UNIQUE,
  "password" TEXT NOT NULL,
  "role" TEXT NOT NULL DEFAULT 'member',
  "createdAt" TIMESTAMPTZ NOT NULL,
  "updatedAt" TIMESTAMPTZ NOT NULL
);
CREATE TABLE "tasks" (
  "id" TEXT PRIMARY KEY,
  "owner" TEXT NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
  "title" TEXT NOT NULL,
  "notes" TEXT,
  "priority" TEXT NOT NULL DEFAULT 'normal',
  "due" DATE,
  "done" BOOLEAN NOT NULL DEFAULT false,
  "createdAt" TIMESTAMPTZ NOT NULL,
  "updatedAt" TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS "tasks_owner_idx" ON "tasks" ("owner");
CREATE INDEX IF NOT EXISTS "tasks_done_idx" ON "tasks" ("done");

This is the SQL IWC derived from user.schema.iwc and task.schema.iwc: tables are pluralised, relations become foreign keys with their onDelete, index: true and relation fields get indexes, and every table gets id, createdAt and updatedAt.

Step 3: Apply it

Terminal

iwc migrate

Output

…
✓ migrated

iwc dev and iwc start also migrate on start, so this command is for reviewing and applying changes deliberately — in a deploy script, for instance. IWC keeps its own bookkeeping tables (_iwc_jobs, _iwc_audit, _iwc_idempotency…) beside yours.

Step 4: Run the app on PostgreSQL

Terminal

iwc dev

Seed rows are inserted into empty tables in development, so the two seeded users can sign in at once.

Step 5: Run the tests on PostgreSQL

Terminal

createdb tasks_test
IWC_TEST_DATABASE_URL="postgres://localhost:5432/tasks_test?sslmode=disable" iwc test

Output

  ✓ tasks › the task list needs sign-in
  …
  ✓ tasks › the owner cannot be chosen by the client

13 passed, 0 failed

Each run uses a throwaway schema, so tests never touch your data.

How migrations behave

Change a schema and IWC plans the difference:

Change	What happens
New table, field or index	Applied automatically
Field removed from a schema	The column is left in the database, so no data is lost
A field's type changed	Refused, with an explanation
A required field without a `default` added to a table that has rows	Refused: add a `default` or make it optional
The database holds tables IWC did not create	Refused before writing anything; `IWC_ADOPT_DATABASE=1` adopts it on purpose

Queries, filtering, sorting and pagination

You rarely write queries: data sources and db.list take them as properties. On a page:

element (in a .iwc file)

{
  "type": "table",
  "data": {
    "source": "DATABASE.tasks",
    "columns": ["title", "priority", "due"],
    "filter": { "done": false, "priority": { "in": ["normal", "high"] } },
    "sort": { "field": "due", "order": "asc" },
    "paginate": { "size": 20 }
  },
  "cells": [{ "field": "title", "label": "Task" }, { "field": "priority", "label": "Priority" }, { "field": "due", "label": "Due", "format": "date" }]
}

In actions, find, query (with where, sort, limit, offset), aggregate, create, createMany, update and delete steps do the same with transactions. See Database and Actions.

Troubleshooting

Problem	Fix
`iwc start` refuses to start	Set the variable named in `database.url`.
`iwc dev` shows data from somewhere unexpected	A `DATABASE_URL` in your shell; `iwc doctor` warns about it. Declaring `database.url` avoids it.
`password authentication failed` / `connection refused`	Check the URL with `psql "$TASKS_DATABASE_URL"`.
SSL errors on a local server	Add `?sslmode=disable` for local development only.

Next: Create an admin dashboard.

Source: apps/marketing/content/docs/tutorials/database.md