INSERT INTO site_configs(key, value) VALUES ('mytest', 'example');

Database migrations are managed with [`migrate`](https://github.com/golang-migrate/migrate).

To add new migrations, use the following command:

$ ./coderd/database/migrations/create_fixture.sh my name

Then write queries into the generated `.up.sql` and `.down.sql` files and commit

them into the repository. The down script should make a best-effort to retain as

much data as possible.

There are two types of fixtures that are used to test that migrations don't

break existing Coder deployments:

- Partial fixtures [`migrations/testdata/fixtures`](./coderd/database/migrations/testdata/fixtures)

- Full database dumps [`migrations/testdata/full_dumps`](./coderd/database/migrations/testdata/full_dumps)

Both types behave like database migrations (they also [`migrate`](https://github.com/golang-migrate/migrate)). Their behavior mirrors Coder migrations such that when migration

number `000022` is applied, fixture `000022` is applied afterwards.

Partial fixtures are used to conveniently add data to newly created tables so

that we can ensure that this data is migrated without issue.

Full database dumps are for testing the migration of fully-fledged Coder

deployments. These are usually done for a specific version of Coder and are

often fixed in time. A full database dump may be necessary when testing the

migration of multiple features or complex configurations.

To add a new partial fixture, run the following command:

$ ./coderd/database/migrations/create_fixture.sh my fixture

Then add some queries to insert data and commit the file to the repo. See

for an example.

To create a full dump, run a fully fledged Coder deployment and use it to

generate data in the database. Then shut down the deployment and take a snapshot

of the database.

$ mkdir -p coderd/database/migrations/testdata/full_dumps/v0.12.2 && cd $_

$ pg_dump "postgres://coder@localhost:..." -a --inserts >000069_dump_v0.12.2.up.sql

Make sure sensitive data in the dump is desensitized, for instance names,

emails, OAuth tokens and other secrets. Then commit the dump to the project.

To find out what the latest migration for a version of Coder is, use the

following command:

$ git ls-files v0.12.2 -- coderd/database/migrations/*.up.sql

This helps in naming the dump (e.g. `000069` above).