Site Code
The Site Code database
Your site's own MySQL tables — a separate database that cannot touch the CMS tables, migrations, the query builder, db.docs, core_* views and the database admin page
On this page
Site Code has MySQL tables of its own, designed with migrations and used through a synchronous query builder. Manage the data under Site Code → Database.
A separate database (recommended)#
MySQL
├── anasaki the CMS tables — the code's user has no rights here
└── anasaki_code developer tables + read-only core_* views
- All code (migrations,
db.*, the SQL console) runs as the MySQL useranasaki_code, which only has rights on theanasaki_codedatabase — so MySQL itself keeps it away from the CMS tables - A docker compose install creates it automatically on first start (
docker/mysql-init/10-site-code.sh+CODE_DB_PASSWORD) and gives the CMSCODE_DB_DSN - Sites installed before that: run the 4 SQL lines the Database page shows, then set
CODE_DB_PASSWORD— full steps in Updating
core_* views (read-only)#
| view | Columns |
|---|---|
core_posts | id, type, title, slug, status, excerpt, author_id, featured_media_id, published_at, created_at, updated_at (published only) |
core_terms | id, taxonomy, name, slug |
core_post_terms | post_id, term_id (of published posts) |
core_users | id, name, email, role, status, created_at (no passwords or tokens) |
Fallback mode#
Without CODE_DB_DSN, or when it cannot connect, your tables live in the CMS database prefixed with sc_ (added and stripped automatically). Migrations, the query builder, db.docs and the admin page still work, but db.query, db.raw, schema.raw, the SQL console and the core_* views do not (use db.posts instead). Switching from fallback to a separate database does not move the old sc_ tables (dump → import them yourself).
Migrations#
Create files in migrations/ (the New migration button in the editor names them migrations/NNN_name.ts). They run in file name order.
// migrations/001_create_orders.ts
export default migration({
up(schema) {
schema.create('orders', (t) => {
t.id(); // BIGINT UNSIGNED AUTO_INCREMENT PK
t.string('code', 20).unique();
t.foreignId('user_id').nullable().references('core_users');
t.enum('status', ['pending', 'paid']).default('pending');
t.decimal('total', 12, 2);
t.json('shipping').nullable();
t.timestamps(); // created_at, updated_at
t.index(['status', 'created_at']);
});
},
down(schema) { schema.drop('orders'); },
});
- Columns:
id, string, text, integer, bigInteger, decimal, boolean, date, datetime, json, enum, uuid, foreignId, timestamps()· modifiers:nullable, default, unique, index, unsigned, comment, after, change, useCurrent, references(...).onDelete(...)· make a column case-sensitive with.binary() - Table names match
^[a-z][a-z0-9_]{0,62}$and must not start withcore_,script_,sc_orbak_ - On publish, pending migrations run before the new code goes live. If one fails, the publish stops and the old code keeps running (MySQL DDL is not transactional — fix it with a new migration, or fix the failed file and publish again)
- Changes that may lose data (drop / rename / changing a column type) need the "I understand data may be lost" confirmation; the table is backed up as
bak_<table>_<time>for 7 days first - Roll back the latest batch of migrations on the Database page (runs
down())
Query builder (inside handlers)#
const page = db.from('orders as o')
.leftJoin('core_users as u', 'u.id', 'o.user_id')
.select('o.id', 'o.code', 'o.total', 'u.name as customer')
.where('o.status', 'paid').where('o.total', '>', 100)
.orderBy('o.created_at', 'desc')
.paginate(1, 20); // { items, total, page, perPage }
const id = db.table('orders').insert({ code: 'A1', total: 10 });
db.table('orders').where('id', id).update({ total: 12 }); // update/delete need where() or .all()
db.transaction((tx) => { tx.table('a').insert({}); tx.table('b').insert({}); }); // throw = rollback
const notes = db.docs('notes'); // JSON document storage
notes.insert({ title: 'x' });
notes.find({ where: { title: 'x' }, orderBy: '-createdAt' });
- Every value is a bound parameter (no SQL injection) — for raw SQL use
db.query(sql, params)/db.raw(...)(separate database only) - Results: DECIMAL → string, DATETIME → ISO string, JSON → object
- Limits: 5 seconds per statement, 10,000 rows / 10 MB per result (use
paginate— at most 100 per page) anasaki-code typesgenerates row types from the real tables (OrdersRow) sodb.table('orders')is typed
Test route uses the real database — data the code writes or deletes while testing really changes.
The Database page#
| What you can do | Permission |
|---|---|
View tables, structure and data (search, filter, sort), export CSV/JSON, dump .sql, migration history, backup tables | view_code_data |
| Read-only SQL console (SELECT/SHOW/EXPLAIN… in a read-only transaction) | view_code_data |
| Add/edit/delete rows, import CSV, writing SQL, delete backup tables | edit_code_data |
| Roll back migrations | edit_code_data + publish_scripts |
Who can reach a table through the designer is set under Access (the default for tables = nobody) — see Binding data to the designer.
