# SQL Yodeler

> Schema management and migrations for ClickHouse and Postgres. The schema is declared in TypeScript with chant's sql lexicon; the yodel CLI writes versioned migrations from it, lints them, and applies them behind an approval bound to a digest of the plan.

Agents setting SQL Yodeler up in a repository: start with https://intentius.io/sql-yodeler/agents/. A person new to it: https://intentius.io/sql-yodeler/getting-started/.

A page with a prompt carries it under "Optional: hand this page to your coding agent", and this file lists it under the page. Every prompt forbids applying to a shared environment, approving and merging; those stay with people.

## Pages

- [Set up with a coding agent](https://intentius.io/sql-yodeler/agents/): the prompt to hand a coding agent, the steps it follows to add SQL Yodeler to a repository, and what it leaves to people
  - Optional agent prompt: Set up SQL Yodeler in this repository. Read https://intentius.io/sql-yodeler/llms.txt first, then https://intentius.io/sql-yodeler/agents/ and follow it. Open a pull request with the result. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [SQL Yodeler](https://intentius.io/sql-yodeler/): What SQL Yodeler is, and the docs pages
- [Start a new schema](https://intentius.io/sql-yodeler/for/new-schema/): For a team starting a schema from nothing: declare it in TypeScript in a project from a starter template, and yodel writes the first migration from it on a local database.
- [Adopt a live database](https://intentius.io/sql-yodeler/for/adopt/): For a team whose database already exists: read it into declarations and a baseline migration, then change it only through migrations.
- [Lint and plan on pull requests](https://intentius.io/sql-yodeler/for/lint-and-plan/): For a team that wants every migration reviewed in its pull request before SQL Yodeler applies anything.
- [Coming from another tool](https://intentius.io/sql-yodeler/for/coming-from/): For a team whose database another migration tool manages today: the concepts mapped, then the database adopted with init --from.
- [Setting up CI for many teams](https://intentius.io/sql-yodeler/for/platform/): For a platform team that runs the pipelines of many schema projects: one renderer, the same jobs on each forge, and a pinned image.
- [Working with a coding agent](https://intentius.io/sql-yodeler/for/agents/): For someone who hands SQL Yodeler's setup to a coding agent, and keeps approvals and applies for people.
- [Security review](https://intentius.io/sql-yodeler/for/security/): What each job can reach, who can approve an apply, and the record every change leaves.
- [Evaluating SQL Yodeler](https://intentius.io/sql-yodeler/for/evaluate/): For someone deciding whether SQL Yodeler fits: two finished example projects, the claims that run against real servers, and a first migration on a local database.
- [Declaring the schema](https://intentius.io/sql-yodeler/schema/): the schema as TypeScript in `src/`: one export per object in the database's own DDL, references with `${}`, splitting it across files, generating objects with code, composites, what the build checks and the editor
- [Data migrations](https://intentius.io/sql-yodeler/steps/): ClickHouse rebuilds, backfills, Postgres `PostgresMigrationOp` steps, `yodel cleanup` of what they keep, and manual steps
  - Optional agent prompt: Write the data change I describe as a step of a migration, following https://intentius.io/sql-yodeler/steps/. For a backfill, run `npx yodel new <name> --backfill`, fill in `backfills/<name>.json` (the table, the key, the batch size and the SQL of one batch between `{from}` and `{to}`), and run the same command again. For a Postgres column rename, write `-- previously: <old name>` on the new column's line in src/ before `npx yodel new <name>`. Run `npx yodel lint`, and open a pull request with src/, backfills/ and the new migration. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Your first migration](https://intentius.io/sql-yodeler/getting-started/): from an empty directory to applied migrations on a local database: the template, the emulator, the variables, plan, approve and apply
  - Optional agent prompt: Follow https://intentius.io/sql-yodeler/getting-started/ in a new directory: make the project, start the local emulator, and write and plan the first migration. Run `npx yodel apply dev` against the emulator, and when it stops at the approval, show me the approve command it printed and stop there. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Starting a project](https://intentius.io/sql-yodeler/adoption/): a new project from a starter template, `yodel init --from` to adopt a database that already exists, and `yodel init --baseline` for another environment that holds the same schema
  - Optional agent prompt: Take the existing database of the environment I name into versioned migrations with SQL Yodeler, following https://intentius.io/sql-yodeler/adoption/. Make the project from the starter template with the existing database or schema name first, and ask me before running `YODEL_CREDENTIALS=writer npx yodel init --from <env> --force`: it writes one row to that environment's history. Open a pull request with src/, the baseline migration and the config. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Coming from another migration tool](https://intentius.io/sql-yodeler/from-other-tools/): for a database golang-migrate, goose, Flyway or dbmate manages: the concepts mapped, adopting it with `yodel init --from`, the old tool's files and table, and rollback with `yodel revert`
  - Optional agent prompt: Move this repository's database migrations from the tool it uses now to SQL Yodeler, following https://intentius.io/sql-yodeler/from-other-tools/. Make the project from the starter template, move the old migration files out of migrations/, and ask me before running `YODEL_CREDENTIALS=writer npx yodel init --from <env> --force` against the environment I name: it writes one row to that environment's history. Leave the old tool's history table in the database. Open a pull request with src/, the baseline migration, the config and the pipelines, with the old tool's migration jobs removed and listed in its description. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Lint and plan on pull requests only](https://intentius.io/sql-yodeler/lint-and-plan/): `ci: { apply: false }`: lint, the replay check and the plan comment on every pull request with readers only, no apply pipeline, and turning apply on later
  - Optional agent prompt: Set up SQL Yodeler's pull request checks without its apply pipeline, following https://intentius.io/sql-yodeler/lint-and-plan/: set `ci: { apply: false }` in yodel.config.ts, run `npm run ci`, delete the apply files it no longer writes, and check that `npx yodel ci --check` passes. List the readers' secrets the forge needs in the pull request description, and add no writer's secret. Open a pull request with the result. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Setting up each forge](https://intentius.io/sql-yodeler/forges/): GitHub, GitLab and Forgejo side by side: the pipeline files, where the readers' and the writer's secrets go, the tokens for the plan comment, the drift watch and resuming, cloud roles over OIDC, runners and pull requests from forks
  - Optional agent prompt: Prepare this repository's pipelines for the forges I name, following https://intentius.io/sql-yodeler/forges/: set `ci.forges` in yodel.config.ts, run `npm run ci`, and check that `npx yodel ci --check` passes. In the pull request description, list by name every secret, variable, environment, protection, token and runner the matrix says each forge needs. Put no secret's value in a file or in the description; I add them. Open a pull request with the result. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Schema from an ORM](https://intentius.io/sql-yodeler/orm/): `sources` in `yodel.config.ts`: tables an ORM defines, read from the DDL it prints, planned and applied with the rest
  - Optional agent prompt: Bring the tables our ORM defines into SQL Yodeler, following https://intentius.io/sql-yodeler/orm/: add a source under `sources` in yodel.config.ts with the command that prints the ORM's DDL, and remove any declaration in src/ of a table the ORM now owns. Run `npx yodel new <name>` and `npx yodel lint`, and open a pull request with the config, src/ and the new migration. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Generated schema reference](https://intentius.io/sql-yodeler/schema-docs/): `yodel docs`: an HTML reference and a Mermaid ERD generated from the declared schema
  - Optional agent prompt: Following https://intentius.io/sql-yodeler/schema-docs/, run `npx yodel docs` and open a pull request with schema-docs/. In its description, name the tables and relations the diagram shows that the pull request changes. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [The two workflows](https://intentius.io/sql-yodeler/workflows/): the declarative path (`yodel plan`, `yodel apply` against the declared schema) and the versioned path (`yodel new`, then `yodel apply`)
  - Optional agent prompt: Make the schema change I describe with SQL Yodeler, following https://intentius.io/sql-yodeler/workflows/: edit the declarations in src/, run `npx yodel new <name>` and `npx yodel lint`, and open a pull request with src/ and the new migration. Do not edit a migration that is committed on the default branch; write a new one. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Migrations](https://intentius.io/sql-yodeler/migrations/): the migrations directory, the history table, the apply lock, resume, out-of-order refusal, `yodel status`, `yodel rebase`, `yodel repair`, `yodel checkpoint`, and rollback with `yodel revert`
  - Optional agent prompt: `yodel lint` reports a fork on this branch. Following https://intentius.io/sql-yodeler/migrations/ (Forks and yodel rebase), run `npx yodel rebase` with `--env` for each environment I name, so a migration that ran is never moved, then `npx yodel lint`, and push the branch. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Access control](https://intentius.io/sql-yodeler/access/): Postgres row-level security, policies, roles and grants, and ClickHouse users, roles, row policies and grants: `access` per environment, what the schema owns and what the environment owns, the plan's access section, adopting them
  - Optional agent prompt: Declare the access I describe in src/, next to the tables it is about, following https://intentius.io/sql-yodeler/access/: row-level security, policies, roles and grants on Postgres; users, roles, row policies and grants on ClickHouse. Keep to what the page says the schema owns. Set `access: true` in yodel.config.ts for the environments I name, run `npx yodel new <name>` and `npx yodel lint`, and open a pull request with src/, the config and the new migration. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Approval](https://intentius.io/sql-yodeler/approval/): the migrations Op and its gate, the plan digest, the pull request comment, and what happens when the digest moves
  - Optional agent prompt: An apply was refused or is waiting for approval. Following https://intentius.io/sql-yodeler/approval/, read the job log and `npx yodel plan <env>` with the reader's credentials, and tell me what the plan would run, what moved since the last approval, and the approve command. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Reports and the audit log](https://intentius.io/sql-yodeler/audit/): `yodel report`: every environment's migrations side by side, the audit log derived from the history and the gate ledger, its gaps, `audit.jsonl` and the HTML run view
  - Optional agent prompt: Following https://intentius.io/sql-yodeler/audit/, fetch the `chant/lifecycle` branch and run `npx yodel report` with the readers' credentials. Tell me which migrations are applied where, who approved each apply, and each gap the report lists, with what the page says it means. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Drift](https://intentius.io/sql-yodeler/drift/): `yodel drift` and the scheduled `WatchOp`
  - Optional agent prompt: The drift watch reported drift in the environment I name. Following https://intentius.io/sql-yodeler/drift/, run `npx yodel drift <env>` with the reader's credentials and tell me what changed out of band. Then ask me whether to put the live object back by hand or keep the change. To keep it, declare it in src/, run `npx yodel new <name>` and `npx yodel lint`, and open a pull request. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Installing and configuring](https://intentius.io/sql-yodeler/configuration/): installing `yodel`, the project layout, `chant.config.ts` profiles, `yodel.config.ts`, the environment variables
- [Lint](https://intentius.io/sql-yodeler/lint/): the rules, `yodel:allow` silences, rule levels, `--env`, and the replay check (`--replay`)
  - Optional agent prompt: Fix the `yodel lint` findings on this branch, following https://intentius.io/sql-yodeler/lint/. Change a migration that is applied nowhere, or write the change another way; add a `-- yodel:allow` line only with a reason I give you, and record the checksum with `npx yodel lint --update-checksum <id>`. Never run `yodel apply` against a shared environment, never run `chant approve`, `yodel approve` or `yodel override`, never edit the `chant/lifecycle` branch or `.chant/allowed_signers`, never merge; approvals and applies belong to people.
- [Topology](https://intentius.io/sql-yodeler/topology/): ClickHouse on a single node, a cluster, a `Replicated` database and ClickHouse Cloud
- [The commands](https://intentius.io/sql-yodeler/cli/): which command runs what (`yodel`, chant's CLI, the template's `just` targets), `yodel --help`, the exit codes, and the JSON Schemas of `--json` output
- [Glossary](https://intentius.io/sql-yodeler/glossary/): the chant terms yodel's commands and output use, in yodel's terms
- [Claims status](https://intentius.io/sql-yodeler/claims/): every scenario claim, what it says, and whether it passed plain and was caught broken on ClickHouse and on Postgres, with the pages each one proves

## Full text

- [llms-full.txt](https://intentius.io/sql-yodeler/llms-full.txt): every page above in one file