db ยท diff

git:20261107.75dd463 to git:20261108.89427fb

5 added, 0 removed. Audit C to C.

What this update changes in the checks

No check changed: every fixed check gives the same answer before and after this update.

A tripped check is a label, not a verdict: read the matched text and the lines below. This week's updates that newly trip a check.

---
name: db
description: Run Caligrafy Quill SQL scripts (application/models/**/create_db.sql or any .sql file) and migrations (application/migrations, php caligrafer.php migrate) against the project's MySQL - inside the docker-compose mysql container, or a local MySQL using the .env DB_* settings - and inspect tables.
when_to_use: The user asks to create or update database tables, add or change a column, write or run a migration, run a create_db.sql script, import SQL, check which tables exist, or fix [ERROR 2003] / "table doesn't exist" errors in a Caligrafy Quill project.
argument-hint: "<path/to/script.sql | tables | query \"SQL\">"
---
# Caligrafy database tasks
Arguments: `$ARGUMENTS`. Read [gotchas.md](../caligrafy/reference/gotchas.md) G3, G10, G11, G13, G120 first.
**A table that already has data, or a project that is online (2.18.0+):** a change to it (a column, an index, data
to fix) is a migration, not an edit of `create_db.sql`: `php caligrafer.php migrate make <name>` writes
`application/migrations/<YmdHis>_<name>.sql`; put the SQL in it, then `php caligrafer.php migrate` (in Docker:
`docker compose exec dev-box php caligrafer.php migrate`) and `migrate status` (all `ran`). Never edit a migration that
ran (it shows `changed` and never runs again, G120): write a new one. The deploy step runs pending migrations online.
One change per file, or statements safe to run twice. `create_db.sql` stays for creating the tables of a new database.
## 1. Find the database settings (never print passwords)
Read `.env`: `DB_ACTIVATE`, `DB_HOST`, `DB_DATABASE`, `DB_USERNAME`; use `DB_PASSWORD` only inside commands. If `DB_ACTIVATE` is not `true`, the app will not connect (G20 - tell the user). For connection errors, `php caligrafer.php doctor --only db` reports the exact cause without printing the password.
## 2. Pick the target
- **Docker** (default when `DB_HOST=mysql` or this project's `mysql` service is running): find it with `docker-compose ps mysql` (or `docker compose ps mysql`). If it is not running, offer `/caligrafy-quill:server start`. Use
```bash
docker-compose exec -T mysql mysql -uroot -proot -e "CREATE DATABASE IF NOT EXISTS \`<DB_DATABASE>\` DEFAULT CHARACTER SET utf8mb4;"
docker-compose exec -T mysql mysql -uroot -proot <DB_DATABASE> < <script.sql>
```
(credentials: `root`/`root` from docker-compose.yaml unless the user changed them; prefer `.env` values when they differ.)
- **Local MySQL / MAMP**: `mysql -h <DB_HOST> -u <DB_USERNAME> -p<DB_PASSWORD> <DB_DATABASE> < <script.sql>` (`DB_PORT` is ignored by the framework, G3; pass `-P` to the CLI if needed).
## 3. Before running a script
1. Read it. If it contains `DROP TABLE`, `DROP DATABASE`, `TRUNCATE` or `DELETE` (for example `application/models/Auth/create_db.sql` of projects created before the security release drops `users`, G13), list the tables it would drop, check whether they contain rows (`SELECT COUNT(*) FROM <table>`), and get explicit confirmation before running it.
2. If the script has its own `CREATE DATABASE` / `USE <db>`, that database wins over `DB_DATABASE` - point this out when they differ.
3. Check FK constraint names are unique and FK column types match the parent id signedness (G10).
4. Parent tables must be created before child tables.
## 4. Run and verify
- Run the script (commands above). On error, show the MySQL message and fix the SQL, not the data.
- Verify: `SHOW TABLES;` and `DESCRIBE <table>;` for each created table.
- `tables`: list tables with row counts (`SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = '<db>';`).
- `query "SQL"`: run read-only queries freely; ask before any statement that writes.
- After a migration: `php caligrafer.php migrate status` shows it `ran`, and `doctor` has no Migrations WARN.
- Remind the user that each table needs a matching Model class with one public property per column (`/caligrafy-quill:new-resource`).
+ - From the app's code (2.19.0+), rows are read and written with the query builder, never with SQL strings built from
+ input: `Database::table('<table>')->where(...)->get()` / `->insert([...])` / `->where(...)->update([...])`, and
+ `<Model>::query()` on the model's table (the class name lower-cased + plural unless `protected static $tableName`,
+ G123). Several writes that belong together go in `Database::transaction(fn () => ...)` (MySQL ends it at any DDL, G128).
+ A table change itself stays a migration.