What Gets Synced
Schema sync detects and applies changes across tables, columns, indexes, foreign keys, views, and table options. This page details each category — what sync looks for, how it handles each change, and what you need to know about destructive operations.
Tables
Schema sync manages three table lifecycle operations:
Create
Any type annotated with @db.table that does not yet exist in the database triggers a CREATE TABLE statement. All columns, constraints, indexes, and foreign keys are created in a single operation.
@db.table 'products'
export interface Product {
@meta.id
id: number
name: string
price: number
}Rename
The @db.table.renamed annotation tells sync to rename an existing table instead of dropping the old one and creating a new one:
@db.table 'app_users'
@db.table.renamed 'old_users'
export interface User {
@meta.id
id: number
email: string
}Sync will execute ALTER TABLE old_users RENAME TO app_users rather than destroying the original table and its data.
TIP
Keep @db.table.renamed in your .as file until the rename has been deployed to all environments (dev, staging, production). Once every environment has the new name, you can safely remove the annotation.
Drop
Tables present in the database but no longer defined in your .as schema are dropped during sync.
DANGER
Dropping a table destroys all data in it. Use --safe mode to prevent table drops, or use --dry-run to preview what will be removed before applying changes.
Since 0.1.128 drops are dependency-ordered and refused when unsafe:
- Order — removed tables are dropped children before parents, using the live foreign keys of the database (the stored snapshot is the fallback for adapters that cannot introspect them). Removed views are dropped first of all, before any table change. Mutually referencing tables (a foreign-key cycle) are dropped as one group (
entry.dropGroup): SQLite disables FK enforcement around the group, MySQL drops withFOREIGN_KEY_CHECKS=0on a dedicated connection, PostgreSQL issues one multi-tableDROP TABLE a, b. - Refusal — a removed table that a model or managed view still in the inventory references is refused before any DDL (
Cannot drop "<table>": it is still referenced by …). This is the partial-inventory mistake the model manifest exists to prevent; dropping the parent is never the right default. - Unmanaged referencers — at drop time the live foreign keys are checked again; if a table outside the inventory (or a table whose earlier step errored) still references the one being dropped, that entry becomes an
errorentry, stays tracked, and the hash is withheld — no cascading drop, no dangling constraint. - Safe mode — drops are skipped and the objects stay tracked, so the next executing run (
--forceor the next schema change) drops them.
Columns
Column-level changes are detected by computeColumnDiff(), which compares the desired field definitions from your .as types against the existing columns in the database. Seven change types are tracked:
Add
New fields in a type generate ALTER TABLE ADD COLUMN statements. Added columns are nullable by default unless a default value is specified via @db.default.*. On MongoDB there is no column to add: a literal @db.default is written into existing documents that don't hold the field yet — since 0.1.137 a document that already holds a value keeps it (before, every document was overwritten):
@db.table 'users'
export interface User {
@meta.id
id: number
email: string
// New column — added on next sync
@db.default.value 'free'
plan: string
}Rename
The @db.column.renamed annotation triggers a column rename instead of a drop-and-add:
@db.table 'users'
export interface User {
@meta.id
id: number
// Rename email_address → email
@db.column.renamed 'email_address'
email: string
}Sync executes ALTER TABLE users RENAME COLUMN email_address TO email, preserving the existing data.
Type Change
When an existing column's type no longer matches the desired type (e.g., TEXT to INTEGER), sync detects the mismatch. How it handles the change depends on the adapter:
- Adapters with
supportsColumnModify(MySQL, PostgreSQL) can modify the column type in-place viaALTER TABLE ... MODIFY COLUMN. - Other adapters (SQLite) cannot modify columns. You must specify
@db.sync.method 'recreate'or'drop'on the table, or the sync will produce an error.
WARNING
Without either adapter support for in-place modification or a @db.sync.method annotation, type changes result in an error status. Sync will not apply any changes to the table until you resolve the conflict. See Structural Changes below.
Union Columns of atscript-db ≤ 0.1.154
Since 0.1.155 a T | null object or a union field has its own column layout on the SQL adapters (Nullable and Union Fields). Before, the whole value sat in one text column. The first sync of such a table moves the values before any other column change:
- an object field now flattened: the new
__columns are added and filled from the old column's JSON (in rows whose new columns are allNULL), then the old column and its unused dot-named columns are dropped with the other removed columns —~ address → address__street, address__zip — copy JSON valuesin the plan; - a field now stored as JSON that is a union with a string member (
extra: Address | string): text that is not JSON is rewritten as a JSON string before the column becomes a JSON column —~ extra — text values as JSON; - malformed JSON, or a value that does not convert to its new column's type, makes the table an error entry — the old column and its data stay. Fields the copy cannot fill (
@db.encrypted,db.geoPoint, a MySQLTIMESTAMP/DATETIMEcolumn) refuse the table in the plan. The new columns are added nullable and without defaults, filled by the copy, then given theirNOT NULLand@db.default(on SQLite by recreating the table) — aNOT NULLcolumn's default would fill every row and hide it from the copy. In--safemode the copy runs, theNOT NULLand defaults are skipped and pending ('nullable-defaults', in the plan too). The same copy runs when an object field loses@db.json. A column the stored snapshot knows as a plain scalar is not copied.
See Upgrading for the choices.
Nullable Change
Sync detects when a field changes between optional and required:
// Before: optional
bio?: string
// After: required
bio: stringAdapters with supportsColumnModify handle this in-place. On SQLite, nullable changes require table recreation (see Structural Changes). In --safe mode neither runs: on an adapter that needs DDL for the change it is skipped and pending (entry.skipped includes 'nullable-defaults', the table's snapshot and the hash are withheld until a run without --safe); a snapshot-only adapter just records the new shape. MongoDB has no column constraint: the snapshot is updated and a unique index over the field gets or loses its present-only filter, in safe mode too (since 0.1.156; before, the collection was recreated). Default changes follow the same rule.
Default Change
Changes to @db.default.* values are detected when the existing column already has a recorded default. If the column had no default previously (no baseline exists), changes cannot be detected — except a @db.default.now / @db.default.uuid on PostgreSQL and MySQL (since 0.1.155): the engine applies those, so a column without its DEFAULT would reject inserts that omit the field, and sync adds it.
Some adapters handle default changes in-place; others require table recreation.
Defaults are compared by value, not by text (since 0.1.162). The model's @db.default and the default the database reports are put in a canonical form for the field's type first: booleans as 1 / 0 ('true' matches a reported 1 or true), numbers numerically ('1.0' matches 1, a MySQL DECIMAL(10,2) reports '0' as 0.00 and '2.345' as 2.35), and JSON-stored values (@db.json, arrays) as parsed JSON with sorted keys (PostgreSQL jsonb reports {"a": 1}). Engine quoting is unwrapped too, such as MySQL's _utf8mb4\'[]\' for the DEFAULT ('…') of a TEXT / JSON column and SQLite's 'it''s'. Before 0.1.162 these defaults were reported as changed on every run with force: true: MySQL and PostgreSQL re-ran MODIFY / ALTER COLUMN, and SQLite recreated the table.
MySQL
"No model default" is rendered as no DEFAULT clause at all — never DEFAULT NULL (since 0.1.128). A required column added to a populated table is filled through an invented type default that is dropped again immediately, so a later default change never trips over a default the adapter itself invented. See MySQL — In-Place Column Modification.
Drop
Fields removed from your .as type are dropped from the database table.
DANGER
Dropping a column permanently deletes all data stored in it. Use --safe mode to suppress column drops, or review carefully with --dry-run.
Derived Columns
A @db.column.derived field (since 0.1.141) is a generated column on the SQL adapters and no column at all on document adapters. Sync treats it accordingly:
- Add —
ALTER TABLE … ADD COLUMN … GENERATED ALWAYS AS (…)on a populated table: every existing row shows its value at once and the column's indexes are created as usual. Printed+ customerId (string) derived — add. - Change — the column is dropped and re-added (its managed indexes dropped first and recreated after) when the extraction changed: the source path or the leaf type (
~ customerId — derived column (expression changed) — drop + add), the live column's type drifted (type changed), or the field turned from a regular column into a derived one or back (regular → derived/derived → regular, printed with!: the kind change is destructive — a regular column's stored values are lost, and the values of a column that becomes regular start asNULL). The expression is compared against the table's stored snapshot, so a change made in the model is detected without introspecting the database's own rendering of it. - Rename —
@db.column.renamedrenames the generated column in place; when the expression changed as well, the rename runs first and the rebuild follows under the new name. - Drop — like any column (indexes first).
- Never diffed for nullability or defaults — a generated column is nullable and has no
DEFAULT; a@db.defaulton it is a compile error. - Safe mode — a rebuild is skipped and stays pending:
! customerId — derived column (expression changed) — skipped (safe mode),entry.skippedincludes'derived', the snapshot and the hash are withheld until a run without--safe(which then applies it without--force). Adds and renames still run. - MongoDB and the memory adapter store nothing for a derived field — only the index over the source path is managed; see MongoDB and memory.
- An adapter that cannot drop or add columns (
dropColumns/syncColumnsmissing) reports the rebuild as anerrorentry.
Tables without derived columns hash exactly as before the upgrade: the snapshot records the extraction only for derived fields. How each SQL adapter introspects a generated column is on its page (SQLite, MySQL, PostgreSQL).
Indexes
Sync manages indexes prefixed with atscript__. Indexes you create with any other name are left alone — useful for custom partial / expression indexes that schema sync shouldn't touch.
@db.table 'users'
export interface User {
@meta.id
id: number
@db.index.unique
email: string
@db.index
created_at: string
}Adding @db.index.* creates the matching managed index; removing the annotation drops it.
Definition Changes
Index identity is the index name, but sync compares the definition too: if a named index's column list changed — a composite index gained or lost a member, or the column order changed — the index is dropped and recreated to match the model. Renaming the index (changing the annotation argument) also drops the old index and creates the new one.
Reported Index Work
Since 0.1.162, index work on a table that already exists is part of the entry. It sets the status to alter, lists entry.indexChanges ({ name, action: 'add' | 'drop' | 'rebuild' }) and prints one line per index:
~ articles — alter
+ index atscript__plain__by_rank — add
- index atscript__plain__by_title — drop
~ index atscript__plain__by_team_rank — rebuildThe run prints the same lines in applied wording (added / dropped / rebuilt). Before 0.1.162 this work ran on every forced sync without being reported. The plan reads the live table as it is before the run's column changes. The plan predicts no index work for a table the run creates, recreates (the recreate rebuilds every index) or renames, and the run does not list the indexes of such a table either.
A converged schema reports no index work. Only the atscript__ indexes are compared, so engine-made indexes never show up: MySQL's automatic foreign-key indexes and SQLite's sqlite_autoindex_*. On MySQL, the key-length prefix of a text column compares as the model renders it. An index the database cannot build is not counted as missing, such as a geo index without PostGIS or a MySQL SPATIAL index over an optional field. PostgreSQL's pgvector HNSW indexes are created once rather than rebuilt on every sync. Sort direction is not compared. An adapter without index reporting, such as the memory adapter, plans none.
Dropping Indexed Columns
When a column is removed, sync drops the managed indexes that reference it before dropping the column (engines like SQLite refuse DROP COLUMN while an index still references it). A composite index that loses one member this way is recreated afterwards with only the surviving columns. On SQLite the same pre-drop cleanup covers FTS5 fulltext artifacts and vec0 vector shadow tables whose triggers reference the column.
Index Creation Failures
Index DDL can fail on data conflicts — most commonly adding @db.index.unique to a column that already contains duplicate values. Sync does not throw: the table's entry reports status: 'error', the data is left untouched, and the schema hash is not persisted — so the next sync run retries automatically. Clean up the conflicting data and re-run.
One failing index does not stop the table's other index changes. Contention is the exception (since 0.1.177): a lock timeout, deadlock or serialization failure (another connection holding a lock) ends the table's index pass at once. The entry then reports that retryable error instead of one failure per remaining index, and the next run retries. Since 0.1.178 the failures collected before the contention stay on that error: the DbError lists the contention entries first, then one entry per earlier failure (path: "", message: "<label> failed", the engine's text in details), and the entry's error line names them after the contention message:
Index/FK sync failed on users: database is locked; create index "a" failed: duplicate rowsBefore 0.1.178 they were dropped, and showed up only on the next run.
Two annotation-driven special cases participate in the schema hash like any other change:
@db.index.geosyncs to a managed2dsphereindex on MongoDB and a GiST index on PostgreSQL (PostGIS). MySQL creates aSPATIALindex only for required fields (NOT NULLrule — optional fields warn and skip); SQLite creates no physical index (haversine search is scan-based). Tables that storeddb.geoPointas JSON before native geo support are migrated in place on the next sync (PostgreSQLALTER ... USING, MySQL temp-column swap), preserving data and NULLs.- Toggling
@db.encryptedchanges the field's storage type (ciphertext is unbounded text), so sync runs — but sync never encrypts/decrypts existing data, and key material is never written to the database.
Foreign Keys
Foreign key changes are detected by computeForeignKeyDiff(), which compares the desired FK constraints (from @db.rel.FK annotations) against the stored snapshot. A change made only in the database is invisible to that diff.
On MySQL and PostgreSQL, every run that executes the table (a schema change, or force: true) also checks the live constraints, matched by their local columns. Since 0.1.162 the plan and the run report what that check finds. A constraint dropped by hand is re-added (+ FK(teamId) → teams — add), and a constraint the model does not declare is dropped. The check only covers whether a constraint exists on those columns: a target or ON DELETE / ON UPDATE action edited by hand is not detected. Change the model, or drop the constraint so the next forced run re-creates it. SQLite and MongoDB rely on the snapshot alone.
The snapshot diff tracks three change types:
Add
New @db.rel.FK fields generate foreign key constraints:
@db.table 'tasks'
export interface Task {
@meta.id
id: number
// FK target is encoded in the chain reference type (User.id).
// The optional string argument is an *alias* (used to pair with @db.rel.to / @db.rel.from
// when multiple FKs point to the same target type), NOT the target field.
@db.rel.FK
user_id: User.id
}Remove
FK fields removed from the schema cause the corresponding constraint to be dropped.
Change
When a foreign key is retargeted (different table or different fields) or its @db.rel.onDelete / @db.rel.onUpdate actions change, sync detects the property difference and updates the constraint.
Adapter Differences
How FK changes are applied depends on the adapter:
- MySQL, PostgreSQL — support standalone FK operations via
syncForeignKeys(). Stale or changed FKs are dropped first (to unblock column alterations), then all desired FKs are synced after column operations complete. - SQLite — cannot
ALTERforeign keys. Any FK change requires full table recreation. Sync handles this automatically when FK changes are detected on an adapter withoutsyncForeignKeyssupport.
Creation Order, Cycles and Unknown Targets
Since 0.1.128 tables are created and altered parents before children, whatever the order of the sync inventory — MySQL and PostgreSQL emit inline FOREIGN KEY constraints, so a child created before its parent used to fail on those engines. The order is a pure function of the model's foreign keys (entry.dependsOn lists it), so every pod computes the same plan.
- Cycles (
A.bId → B,B.aId → A) are created without the inline constraints to each other and the constraints are added in a deferred pass once every member exists. SQLite accepts forward references and needs no deferral. - Unknown targets — a
@db.rel.FKwhose target table is neither in the inventory nor present in the database is refused before any DDL (FK <table>.<cols> references "<target>" which is neither in the sync inventory nor present in the database). A target that exists in the database but is not managed by sync (an unmanaged table) is allowed.
For more on foreign key annotations, see Foreign Keys.
Table Options
Table-level options are adapter-specific settings detected by computeTableOptionDiff(). Changes are only detected when both the desired value (from annotations) and the existing value (from introspection or snapshot) are present — new options without a prior baseline are treated as initial state and do not trigger changes.
Table option changes fall into two categories:
- Non-destructive — applied in-place via
ALTER TABLE(e.g., changing MySQL engine or charset). These are safe and do not affect data. - Destructive — require the table to be recreated (e.g., changing MongoDB capped collection size). With
@db.sync.method 'recreate', data is preserved via server-side copy; with'drop'(or no method), the table is dropped and recreated and data is lost. See Structural Changes. Like a type change under'drop', a drop-and-recreate for a destructive option change is covered by the execution order: removed tables that still reference the table are dropped right before it (since 0.1.128). In--safemode the recreate is skipped and pending (entry.skippedincludes'table-options'; the table's snapshot and the hash are withheld until a run without--safe).
MySQL
MySQL tracks engine, charset, and collation as table options. Non-destructive changes (e.g., switching from InnoDB to MyISAM) are applied via ALTER TABLE. Destructive changes require table recreation.
MongoDB
MongoDB tracks capped collection parameters (size, max documents). Since these cannot be modified in place, changes require recreation. With @db.sync.method 'recreate', data is preserved: a temporary collection is created with the new options, the documents are copied into it server-side via $merge, and it replaces the original in one atomic rename (since 0.1.139 — a failed step leaves the original untouched). With @db.sync.method 'drop', data is lost (the collection is dropped and recreated empty).
Views
Schema sync manages views according to their type (see View Types for definitions):
- Managed views — created, dropped, and recreated by sync when the definition changes. Since most databases do not support
ALTER VIEW, changes trigger a drop + recreate. - Materialized views — same lifecycle as managed views, but created with the materialized flag where supported.
- External views — validated only (existence + column check). Never created, modified, or dropped by sync. A failed check reports an
'error'entry but is advisory — it does not block hash persistence or wedge re-runs.
A view's definition is its entry table, its joins including their ON conditions and kind, every view column with the physical source column it reads and its aggregate (so renaming or flattening a source column recreates the view; reordering view fields does not), its @db.view.filter, its @db.view.having, the materialized flag and its field set. @db.ignore fields are excluded from the definition and from the generated SELECT.
Upgrading to 0.1.128
Managed views that have joins, a filter or a having clause hash differently after the upgrade and are recreated once on the first sync (plain views without any of those are untouched). Three consequences to plan for:
- PostgreSQL —
DROP VIEW+CREATE VIEWdiscards the view'sGRANTs; re-grant read-only roles after the first sync. - PostgreSQL — sync never drops with
CASCADE, so a user-created view that depends on a managed view makes the recreate fail with anerrorentry (and the hash is withheld) until that dependent view is dropped or the managed view is excluded from the inventory. - MongoDB —
@db.view.materializedviews are plain views on MongoDB, so the recreate is metadata-only.
Upgrading to 0.1.136
Every managed view hashes differently after the upgrade and is recreated once on the first sync — plan() / --dry-run shows each one as an alter on that first run. Plan for the same consequences as in the 0.1.128 upgrade above (PostgreSQL GRANTs, user-created dependent views, metadata-only on MongoDB), plus: PostgreSQL materialized views are re-materialized (a full scan of their sources). The recreate is also when MongoDB inner joins start dropping unmatched documents and invalid view predicates start failing the sync — see Upgrading → 0.1.136.
Upgrading to 0.1.137
Every managed MongoDB view is recreated once on the first sync (metadata-only; via viewRenderRevision()); SQL view hashes are unchanged — see Upgrading → 0.1.137.
Views whose definition changed (or that are being renamed), and views removed from the schema, are dropped before table changes apply; changed ones are recreated after. This matters when a sync both drops a column and updates a view that referenced it: without the early drop, SQLite and PostgreSQL would refuse the column drop while the old view definition still depends on it. Update the view definition in the same deploy that removes the column.
Before creating a managed view, sync checks what already exists under that name: a physical table there (typically left behind by an older build that mistook the view for a table) is a pre-flight refusal, not a silent no-op — and symmetrically a view under a declared table's name.
Views That Read Views
Since 0.1.141 a managed view may read other views (Views over views). Sync treats them as one dependency graph:
- Order — views are created after the views they read (otherwise in inventory order); the plan lists a view's sources — tables and views — in
entry.dependsOn. Dropped views go dependents-first: removed views (ordered from their stored definitions) before the managed views whose definition changed, and those in reverse creation order. PostgreSQL refuses to drop a view another view depends on, and sync never drops withCASCADE. - Cascade — a view's stored definition does not embed the definitions of the views it reads. Instead, when a view is dropped and recreated (its definition changed, or it is renamed), every managed view that reads it is recreated with it, unchanged. The entry is an
alterwithrecreated: trueandcascadeFrom: ["<upstream view>"], printed as· upstream view "x" recreated, in the plan and in the run. - Refusals (no DDL runs): a removed view that a managed view still reads (
Cannot drop view "x": it is still referenced by view "y"), a source that is neither in the inventory nor present in the database (View "y" reads "x" which is neither in the sync inventory nor present in the database), and managed views that read each other in a cycle. - Failures — when creating a view fails, the views that read it are not created; each reports an
errorentry naming the failed upstream view (Upstream view "x" failed — "y" was not created over it) and the hash is withheld, so the next run retries the whole chain. - External views as sources are checked for presence only; keep them in the inventory as external views to have their columns verified too.
- A join alias is not an object: it is neither created nor tracked, one listed in the inventory is skipped, and
dependsOnnames the aliased table.
View Renames
The @db.view.renamed annotation handles view renames by dropping the old view and creating a new one with the updated name:
@db.view 'premium_users'
@db.view.renamed 'vip_users'
@db.view.for User
@db.view.filter `tier = 'premium'`
export interface PremiumUser {
id: User.id
email: User.email
}For more on view definitions and types, see Views.
Rename Tracking
Renames require explicit tracking because, without it, a renamed field looks like a deletion followed by an addition — which means data loss.
Three annotations handle renames across different schema objects:
| Annotation | Target | Effect |
|---|---|---|
@db.table.renamed 'oldName' | Table | ALTER TABLE oldName RENAME TO newName |
@db.column.renamed 'oldName' | Column | ALTER TABLE ... RENAME COLUMN oldName TO newName |
@db.view.renamed 'oldName' | View | DROP VIEW oldName + CREATE VIEW newName |
Lifecycle
The lifecycle for all rename annotations follows the same pattern:
- Add the annotation with the old name as the argument.
- Deploy to all environments — sync will execute the rename on each database it runs against.
- Remove the annotation once every environment has been updated.
TIP
Do not remove a @db.*.renamed annotation prematurely. If an environment has not yet been synced, removing the annotation will cause sync to treat the old and new names as unrelated — dropping the old object (and its data) and creating a new empty one.
Rename Conflicts
If both the old name and the new name exist in the database simultaneously, sync produces an error status instead of proceeding. This protects against situations where a rename was partially applied or where two columns genuinely exist with both names.
Column rename conflict on users: cannot rename "email_address" → "email"
because "email" already exists.You must resolve the conflict manually (drop the stale column, or adjust the annotation) before sync will proceed.
Example: Full Rename Workflow
// Step 1: Rename the column
@db.table 'users'
export interface User {
@meta.id
id: number
@db.column.renamed 'email_address'
email: string
}After deploying to all environments:
// Step 2: Remove the annotation
@db.table 'users'
export interface User {
@meta.id
id: number
email: string
}Structural Changes
Some changes cannot be applied with a simple ALTER TABLE statement — for example, changing a column type on SQLite, modifying nullable constraints, or changing destructive table options. The @db.sync.method annotation controls how sync handles these cases.
'drop' — Drop and Recreate
@db.table 'sessions'
@db.sync.method 'drop'
export interface Session {
@meta.id
id: string
data: string
expires_at: string
}Recipe: dropping a column safely
Atscript never auto-rolls-back, so a column you remove in code is removed from the DB on the next sync. Two-step pattern for a live system:
- Stop reading/writing the column — deploy the application change first, leaving the column in the schema (no
.aschange yet). - Drop the column — remove the field from the
.asfile and run sync. Use--safein production to require manual approval; pair with--dry-runto preview.
Recipe: changing an FK target
- Add the new FK field (with chain reference to the new target) alongside the old one.
- Backfill rows so the new FK is populated.
- Drop the old FK field on the next deploy.
Changing the chain-reference type of an existing FK field is a drop+add; do it in two deploys when downtime is not acceptable.
The table is dropped and recreated from scratch.
DANGER
All data in the table is permanently destroyed. Use 'drop' only for ephemeral data like sessions, caches, or temporary tables where data loss is acceptable.
'recreate' — Copy and Swap
@db.table 'users'
@db.sync.method 'recreate'
export interface User {
@meta.id
id: number
email: string
age: number // was: string
}Sync copies the data into a new table with the updated schema and swaps it in. Data is preserved wherever the old and new types are compatible; incompatible columns may lose data during the copy.
On PostgreSQL (since 0.1.137) a column whose type changed is converted to its new type the way an in-place type change converts it — see PostgreSQL → Column Type Changes. A value that does not convert fails the recreate inside its transaction: the table keeps its rows, old types and constraints, and its entry is an error. Before 0.1.137 a type change on a required column always failed the copy (invalid input syntax for type double precision: "").
On MySQL (since 0.1.140) the copy runs in strict sql_mode whatever the server's setting, so a value that does not convert fails the copy instead of being coerced — see MySQL → Conversions are strict.
On SQLite (since 0.1.138) the recreate runs in one transaction: a failed step rolls it back and the table keeps its rows. On MySQL, whose DDL is not transactional, a copy that fails drops the <table>__tmp_<ts> table and leaves the original with its rows; a failure after the original was dropped keeps the temp table, which holds the only copy of the rows, and the error names it. Rename it back to recover. Either way the entry is an error.
On PostgreSQL (since 0.1.129) the swap keeps the constraint names stable — the recreated table's own <table>_pkey / <table>_<col>_fkey names and the inbound foreign keys of other tables, which are captured (whether they reference the primary key or a UNIQUE column) and restored under their original names; a self-referencing foreign key is simply re-added — and it never drops with CASCADE: a user-created view or an unmanaged constraint that depends on the table makes the recreate fail inside its transaction, and the table's error entry names the dependent object (… — view report_v depends on table users). Drop it (or exclude the table from the inventory) and re-run. MySQL's recreate relinks inbound foreign keys but does not preserve their names.
When Required
Structural changes are required in the following scenarios:
| Scenario | Adapters Affected | Required Method |
|---|---|---|
| Column type change | SQLite | 'recreate' or 'drop' |
| Nullable change | SQLite | 'recreate' or 'drop' |
| Default value change | SQLite | 'recreate' or 'drop' |
| FK constraint change | SQLite | Automatic recreation |
| Destructive table option change | All | 'recreate' or 'drop' |
Adapters with supportsColumnModify (MySQL, PostgreSQL) can handle type, nullable, and default changes in-place without requiring @db.sync.method.
Primary Key Changes
Since 0.1.128 sync detects a change of the primary-key field set — @meta.id moved to another field, a single key becoming composite, a composite key losing or gaining a member (set semantics: reordering a composite key is not a change, consistent with the schema hash; a @db.column.renamed key column is compared under its new name). The plan shows it as ! PK (id) → (code) — rebuild (table is empty) (or — rebuild (rows satisfy the new key)), and entry.pkChange = { from, to, rebuild, populated? }.
- Empty table — the key is rebuilt in place, after new columns are added and before stale columns are dropped (a key column removed in the same sync is dropped after the swap). The rebuild counts as destructive in the plan.
- Populated table (since 0.1.155) — pre-flight counts, in one query, the rows the new key columns cannot hold as a primary key: rows with a
NULL(on MongoDB: missing) in one of them, and every row sharing its values with another. None → the key is rebuilt like on an empty table, rows kept (pkChange.populated: true). Otherwise the run is refused before any DDL, in every mode:Primary key of "<table>" changed (<from> → <to>) but <n> rows have a NULL or duplicate (<to>) — fix or remove them (or migrate manually) and re-run.A new key column added in the same sync holds no values yet, so that change stays refused on a populated table (… but the table has rows; schema sync cannot rebuild a populated primary key …), as does any populated key change on an adapter withoutcountKeyViolations. A row written by the application between pre-flight and the swap that breaks the new key fails the engine's uniqueness check rather than corrupting anything. A foreign key retargeted to the new key is added back in the referencing table's step: on PostgreSQL and MySQL, child rows with no matching parent row under the new key fail that step (the child's entry reports it; the parent keeps its rebuilt key). Before 0.1.155 every populated key change was refused. - Inbound foreign keys — every live foreign key that references the old key must be retargeted to the new key by a model in the same sync (its own
fkChanged); otherwise the run is refused. A referencing table that is removed in the same sync is not a blocker: it is dropped right before the rebuild. Sync drops those constraints before any table operation and the children re-add them afterwards (children run after parents). A referencing table that is itself renamed (@db.table.renamed) in the same sync is refused as well — rename it in a separate run first, then retarget. - Renamed table — the populated check probes the table under its old name; an adapter whose
hasRowscannot answer for another name (the base-class default) is refused rather than guessed at. - Auto-increment — a
@db.default.incrementcolumn that leaves the key is refused (MySQL cannot keepAUTO_INCREMENToff a key; the compiler warns about@db.default.incrementwithout@meta.id). - Safe mode — the rebuild is skipped with a warning;
pkChange.rebuildisfalseandentry.skippedincludes'pk-rebuild'in both the plan and the result (the populated-table refusal still applies). The table's snapshot and the schema hash are withheld, so the next run without--saferebuilds the key (no--forceneeded).
| Adapter | Rebuild |
|---|---|
| MySQL | One ALTER TABLE … MODIFY <new key columns> NOT NULL[, MODIFY <old key columns>], DROP PRIMARY KEY, ADD PRIMARY KEY (…) — a new increment key column is added without AUTO_INCREMENT and receives it here; an old key column's own changes (losing AUTO_INCREMENT, VARCHAR(255) → TEXT) are made in the same statement |
| PostgreSQL | One ALTER TABLE … DROP CONSTRAINT <pk>, ADD PRIMARY KEY (…); a demoted identity column gets ALTER COLUMN … DROP IDENTITY |
| SQLite | Table recreation with the rows copied; a type change of a demoted key column (non-key numbers are REAL) is applied by the same recreation (since 0.1.155; before, it needed @db.sync.method 'recreate') |
| MongoDB | No physical key: _id is fixed and a @meta.id move is an index change reconciled by index sync (__pk unique index); the populated check applies. An old key field removed in the same sync loses its __pk index before it is unset (since 0.1.156) |
| Memory | Not applicable (no column introspection) |
Method Comparison
| Method | Data | Use Case |
|---|---|---|
'drop' | Lost — table is destroyed and recreated empty | Caches, sessions, temporary data |
'recreate' | Preserved — copied to new table where types are compatible | Important data with schema changes |
| (none) | Error — sync refuses to proceed | Default behavior when structural changes are needed on adapters without in-place support |
Recipes
Many-to-Many (Junction Table)
Define an explicit junction @db.table with one @db.rel.FK per side; use @db.rel.via on each side to navigate through it:
@db.table 'tags'
export interface Tag {
@meta.id
id: number
label: string
@db.rel.via PostTag
posts: Post[]
}
@db.table 'posts'
export interface Post {
@meta.id
id: number
title: string
@db.rel.via PostTag
tags: Tag[]
}
// Junction table — must be its own @db.table with FKs on each side.
@db.table 'post_tags'
export interface PostTag {
@meta.id
id: number
@db.rel.FK
@db.rel.onDelete 'cascade'
postId: Post.id
@db.rel.FK
@db.rel.onDelete 'cascade'
tagId: Tag.id
}The junction must be a @db.table. @db.rel.via cannot be combined with @db.rel.to or @db.rel.from — it is self-sufficient. See Navigation Properties for the runtime semantics.
Vector Search
Mark a field with @db.search.vector <dimensions>, "<similarity>". The dimensions are a positive integer that must match the embedding model output (Vector Search).
@db.table 'documents'
export interface Document {
@meta.id
id: number
title: string
@db.search.vector 1536, "cosine"
@db.search.vector.threshold 0.7
embedding: db.vector
@db.search.filter "embedding"
category: string
}On sync, the adapter provisions its native vector type/index (pgvector for PostgreSQL, Atlas vector index for MongoDB, VECTOR(N) for MySQL 9+, vec0 shadow table for SQLite with sqlite-vec loaded). See Vector Search for the full query API.
What Is NOT Synced
Schema sync handles structural schema changes only. The following are outside its scope:
- Data migration and seed data — sync creates and modifies schema, but does not transform or populate data
- Stored procedures, triggers, and functions — database-level logic is not managed by
.asdefinitions - Database users, roles, and permissions — access control must be managed through your database administration tools
- Database-level settings — charset, timezone, connection limits, and other server-level configuration
- Custom DDL — any database objects not representable through Atscript annotations (e.g., custom types, sequences, partitions)
Next Steps
- CLI — command-line usage and flags
- Configuration — config file setup and adapter options
- Tables & Fields — defining tables, columns, and constraints
- Indexes — index annotations and types
- Foreign Keys — FK annotations and relation patterns
- Views — view definitions and types