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.
• Declare the schemas it changes on a leading line of its up, -- schemas sales, billing, and qualify every name with its schema (sales.orders). A migration waits only on earlier migrations whose schemas overlap its own. One that declares none applies to the whole database and every later migration waits for it, so leave the line out only for a change that really spans the database, such as an extension.
• 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.