Developers

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

8 min readLast updated

View as Markdown

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.

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 user anasaki_code, which only has rights on the anasaki_code database — 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 CMS CODE_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)#

viewColumns
core_postsid, type, title, slug, status, excerpt, author_id, featured_media_id, published_at, created_at, updated_at (published only)
core_termsid, taxonomy, name, slug
core_post_termspost_id, term_id (of published posts)
core_usersid, 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 with core_, script_, sc_ or bak_
  • 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 types generates row types from the real tables (OrdersRow) so db.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 doPermission
View tables, structure and data (search, filter, sort), export CSV/JSON, dump .sql, migration history, backup tablesview_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 tablesedit_code_data
Roll back migrationsedit_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.