Schema Sync (CLI)

How nextjs-cms update-sections turns section configs into MySQL tables, diffs them with the live database, and writes the Drizzle schema.

Code: packages/nextjs-cms/src/cli/

File	Role
`index.ts`, `program/program.ts`	Commander program, env loading (`-d` → `.env.development`)
`commands/*.ts`	Thin command wrappers
`lib/update-sections.ts`	The sync engine
`lib/db-migrate.ts`	System tables via `drizzle-kit push` (used by `setup`)
`lib/db-config.ts`	Credential prompts and env writing
`lib/set-master-admin.ts`, `lib/fix-master-admin.ts`	Master admin
`utils/schema-generator.ts`	Drizzle schema text generation
`utils/add-table-keys.ts`	Unique, index, and full-text key creation
`core/db/table-checker`	`MysqlTableChecker`: introspect tables, columns, keys

The pipeline

updateSections()
 1. load sections            SectionFactory.getSectionsSilently(); build every section + fields
 2. validate configuration   reserved names (_locales, locale), db selects pointing at section tables,
                             gallery table names → abort with a combined report
 3. desired tables           for each section:
                               ├─ the section table          generateFieldSQL() per field
                               ├─ {table}_locales            if localization on + localized content
                               ├─ gallery table              if gallery
                               ├─ destinationDb tables       per field with destinationDb
                               └─ db lookup tables           per select with an external `db`
 4. existing tables          MysqlTableChecker.getExistingTables() + __nextjs_cms_tables registry
 5. reconcile tables         create / rename (prompt) / configure unmanaged (prompt)
 6. reconcile columns        add / rename (prompt) / modify type / drop (prompt)
 7. keys                     primary, unique, index, fulltext (add-table-keys.ts)
 8. remove tables            tables no section needs anymore (prompt)
 9. write Drizzle schema     only after everything succeeded
10. fixMasterAdminPrivileges()

__nextjs_cms_tables records which tables the CMS manages (and for which section), plus stored config such as the localization state. It's how the engine tells "a table the user created by hand" from "a table a section used to own", and how it detects renames.

Field → column

generateFieldSQL(field) is a switch (true) on is(field, X):

Field	Column
`TextField`	`VARCHAR(maxLength ?? 255)` utf8mb4
`TextAreaField`, `RichTextField`, `TagsField`	`LONGTEXT`
`SelectField` static	`ENUM(...)`
`SelectField` db/section	`INT(11)` if the identifier type is number, else `VARCHAR(255)`
`SelectMultipleField`	`VARCHAR(255)`
`CheckboxField`	`BOOLEAN`
`ColorField`	`CHAR(7)`
`DateField`	`DATE` / `DATETIME` / `TIMESTAMP`
`DateRangeField`	two `DATE`/`DATETIME` columns
`NumberField`	smallest integer type fitting min/max (or `maxLength` digits), `FLOAT`, `DOUBLE`, `BIGINT`; `UNSIGNED` if `minValue >= 0`; `AUTO_INCREMENT`
`MapField`	`VARCHAR(200)`
`FileField`	`VARCHAR(50)`
`PasswordField`, slug and others	`VARCHAR(255)`

Then: NOT NULL when required && !isConditional(), otherwise DEFAULT NULL when no default; DEFAULT <formatDefaultValue(defaultValue)> when a default is set. Fields with destinationDb produce no column.

Known issues worth fixing:

formatDefaultValue stringifies non-primitive defaults, so dateField (Date or 'now') and mapField ({ lat, lng }) defaults produce invalid or wrong DEFAULT clauses.
For integers, a missing bound counts as "fits", so minValue: 0 alone selects TINYINT UNSIGNED.
Unsigned INT is emitted as INT UNSIGNED(11); MySQL expects the display width before UNSIGNED.

Prompts and safety

Anything that can lose data is interactive (via @clack/prompts): renames, drops, and managing existing unmanaged tables. Choices can be deferred ("Ask me later"), which leaves the object untouched. New tables and new columns are created without asking.

The Drizzle schema

schema-generator.ts builds mysqlTable(...) definitions for every desired table with the configured naming (pascalCase tables + Table suffix, camelCase columns by default) and collects the needed imports. The file is written only when the whole run succeeded, so a failed run never deletes a working schema.

`setup` and system tables

System tables (admins, admin_privileges, access_tokens, editor_photos, logs, __nextjs_cms_*) are defined with Drizzle in src/db/schema.ts and created by drizzle-kit push scoped to them (DRIZZLE_SCOPE_SYSTEM). setup drops existing system tables after confirmation before pushing.

Testing

lib/update-sections.test.ts mocks the database and SectionFactory to test diff decisions. Add a case there for any change to column generation or reconciliation, and an integration test when SQL itself changes.