/datatables

name: datatables
description: MUST use when changing a data table's schema, writing or running its migrations, or working with its roles and permissions.

Data tables: schema changes, migrations and permissions

A data table is a PostgreSQL database managed by Windmill and shared by the whole workspace: scripts, flows and apps reach it by name, `main` being the default. It is configured in the workspace settings (Data Tables), never created by SQL.

Changing the schema

A data table with migrations enabled keeps its schema as an ordered list of versioned SQL migrations, each an `up` and an optional `down`. That list is what git sync and a workspace merge carry, and what rebuilds the schema in another workspace. So first find out whether migrations are enabled on the data table:

• **Enabled**: make every schema change (CREATE, ALTER, DROP, anything but row data) as a new migration. A change made with plain SQL exists in that one database only, and the history no longer describes it.
• **Not enabled**: schema changes are plain SQL. Enabling migrations is a workspace admin's decision, made in the Data Tables settings, not a step to take on the way to something else.

Writing a migration:

• Wrap the statements in `BEGIN; ... END;` so it applies atomically. Leave the frame out only for a statement that cannot run in a transaction, such as `CREATE INDEX CONCURRENTLY`: such a migration can fail half applied.
• Give it a `down` that undoes the `up` whenever the change is reversible. Without one it cannot be rolled back.
• Migrations apply oldest first. One that is applied is not edited: correct it with a new migration.
• Row data is not schema. Insert, update and delete rows with plain SQL, migrations or not.

`wmill datatable migrate new <name>` scaffolds `migrations/datatable/<datatable>/<timestamp>_<name>.up.sql` and `.down.sql`, `wmill datatable migrate up` pushes the local files and applies what is pending, `wmill datatable migrate down` rolls back the most recent one and `wmill datatable migrate status` lists them. All take `-d <datatable>` and default to `main`.

A migration is not a draft

A migration is saved to the workspace and changes the database as soon as it is applied, for everything that reads those tables. To try a schema out without touching tables already in use:

• put the new tables in a schema of their own, created by the same migration (`CREATE SCHEMA IF NOT EXISTS`), and point what you are building at it. A raw app names its schema in its `data` config, which is why a new app gets a new schema by default;
• or work in a workspace fork whose data table was cloned when the fork was created ("Clone schema only" or "Clone schema and data"): migrations applied there change that copy alone, and reach the parent workspace when the fork is merged. A fork that kept the original data table points at the parent's database, so a migration applied in it changes the parent's tables at once.

Altering or dropping a table other scripts or apps use is neither of those: say what depends on it before doing it.

Roles and permissions

Data table roles are a self-hosted Enterprise Edition feature, for data tables on managed Postgres (the instance database or the external managed instance). Without them, and always on a data table backed by a Postgres resource, everyone who can use the data table reaches all of it: there is no per-user, per-schema or per-row access to promise.

A data table under roles is reached as a Postgres role, and Postgres enforces what that role was granted:

• roles are assigned, per data table, to users, groups or folders. `admin` is the connection the data table had before roles: it owns the existing objects and ignores grants;
• a connection either names a role (`wmill.datatable('main', { role: 'analyst' })`, `role='analyst'` in Python, `datatable://main?role=analyst`) and holds that role alone, or names none and is resolved from the data table's default role;
• `permission denied` means the role in use lacks a grant. Say which role and which object: the grant is a data table admin's to give, in the roles drawer of the Database Manager. Do not switch to `admin` to get past it;
• a migration runs as the role its SQL declares on its first line, `-- role <name>`, the same one in its `down`. One that declares none runs as `admin`, which only those who may use `admin` can do.

Read the Permissions section of https://www.windmill.dev/docs/core_concepts/persistent_storage/data_tables before designing who reaches what: it covers tenants, grants, the default role and the roles a connection that names none ends up holding, rows that differ per viewer through row-level security, and how forks and raw apps behave under roles.