45 lines
1.8 KiB
Markdown
45 lines
1.8 KiB
Markdown
# SQL Migrations
|
|
|
|
Incremental schema changes applied in order by timestamp prefix.
|
|
|
|
## Naming Convention
|
|
|
|
Migration directories follow the format:
|
|
|
|
```
|
|
YYYY-MM-DD-HHMMSS-NNNN_description_goes_here/
|
|
up.sql
|
|
down.sql
|
|
```
|
|
|
|
These are created with the `diesel` tool, and in particular:
|
|
|
|
> `diesel migration generate description_goes_here`
|
|
|
|
- `NNNN` is a zero-padded sequence number for ordering multiple migrations on the same timestamp.
|
|
- `up.sql` applies the migration forward.
|
|
- `down.sql` reverses it.
|
|
|
|
## Table Conventions
|
|
|
|
- Primary key `id` columns must be `BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT`. Do not use signed IDs.
|
|
- Always create tables with `ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin`.
|
|
- Application-owned state/status/type columns must not have schema defaults. Use
|
|
`NOT NULL` without `DEFAULT` when required, and have application inserts explicitly
|
|
supply the value. Document the corresponding Rust enum; application queries bind its
|
|
variants instead of hardcoding enum strings in SQL. See
|
|
`crates/schema/database/mysql_queries/AGENTS.md` for the query rules.
|
|
- All SQL files should start with the standard IntelliJ noinspection comments:
|
|
```sql
|
|
-- noinspection SqlDialectInspectionForFile
|
|
-- noinspection SqlNoDataSourceInspectionForFile
|
|
-- noinspection SqlResolveForFile
|
|
```
|
|
|
|
## Rules
|
|
|
|
- Keep documentation in this directory as regular files, not symlinks. Diesel can treat a documentation symlink as a migration directory and reject the entire migration list.
|
|
- Every `up.sql` must have a corresponding `down.sql` that cleanly reverses the migration.
|
|
- For `CREATE TABLE` migrations, the `down.sql` is typically `DROP TABLE IF EXISTS table_name;`.
|
|
- For `ALTER TABLE` migrations, the `down.sql` reverses the alteration (e.g. `DROP COLUMN`).
|