# 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

Source: https://anasaki.live/docs/en/developers/database

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)

```text
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](/docs/en/studio/upgrade)

### `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.

```ts
// 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 &#34;I understand data may be lost&#34; confirmation; the table is backed up as `bak__` for 7 days first
- Roll back the latest batch of migrations on the Database page (runs `down()`)

## Query builder (inside handlers)

```ts
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

> [!WARNING]
> **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](/docs/en/developers/designer-bindings).
