aicoded.dev

Schema changes: the draft and the app's migrations

An app's tables come from its schema changes, never from its code: write the next change in the draft .aicoded/migration.sql, and aicoded publish sends it with the commit.

The draft

The draft is .aicoded/migration.sql in the app's folder: the SQL statements of the next schema change, each ended by ;, with comments where they help. room-maintenance creates its two tables in its draft:

CREATE TABLE rooms (
	number SMALLINT UNSIGNED PRIMARY KEY,
	floor TINYINT UNSIGNED NOT NULL
);

-- An author is the viewer's Subject, of at most 512 bytes, and compares exactly, with no case
-- folding and no trailing-space padding, so "Alice" and "alice " are not "alice".
CREATE TABLE tickets (
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	room SMALLINT UNSIGNED NOT NULL,
	title VARCHAR(100) NOT NULL,
	details TEXT NOT NULL,
	status ENUM('open', 'in_progress', 'done') NOT NULL,
	author VARCHAR(512) COLLATE utf8mb4_0900_bin NOT NULL,
	created DATETIME NOT NULL,
	updated DATETIME NOT NULL,
	KEY (author),
	FOREIGN KEY (room) REFERENCES rooms (number)
);

The examples commit their drafts, since they live in the framework's repository and are never published. An app of yours never commits its draft. The rules:

aicoded check, aicoded dev and the delivery pipeline check the draft with the same rules, so a draft that passes aicoded check passes the security check Safe data changes. Each problem names its line, as .aicoded/migration.sql:3.

Data never goes in the draft. Fill a table from the app's code, such as in OnStart when the table is empty, as fill in room-maintenance does.

The statements a draft may hold

A draft changes only the schema of the app's own tables. Most changes only add to it:

Everything else is refused with E-MIG-002, and the fix says what to write instead:

A destructive statement removes, renames or changes what is there. It is allowed in these forms only, and each is the note E-MIG-003 at its line:

Every other DROP, RENAME, TRUNCATE or ALTER, such as DROP DATABASE, DROP VIEW, DROP USER, DROP TEMPORARY TABLE, ALTER TABLE … ENGINE, ALTER TABLE … ALGORITHM or a partition clause, is E-MIG-002, and so is any statement on another database's table or on the table aicoded_migrations.

ALTER TABLE tickets ADD COLUMN priority TINYINT UNSIGNED NOT NULL DEFAULT 2; -- right
ALTER TABLE tickets ADD COLUMN priority TINYINT UNSIGNED NOT NULL; -- wrong: E-MIG-002, no DEFAULT
ALTER TABLE tickets DROP COLUMN details; -- destructive: the note E-MIG-003, and it needs an approval
DROP VIEW open_tickets; -- wrong: E-MIG-002

Destructive changes need an approval

A release starts the new version while the old one still serves, and switches over once the new one is ready; a rollback starts an older version again. Both versions run on the same database, so for a release to go live without anyone noticing, its schema must suit both. A change that only adds does. A dropped or renamed column breaks the version that still uses it, and no rollback brings dropped data back. So a publish whose draft holds a destructive statement Needs approval: an approver decides whether it goes live, and aicoded publish says so. When it goes live, the platform stops the app, applies the steps and starts the new version, so the app is unavailable for a short while, usually seconds. When a step fails, the version that ran starts again. Once a destructive step ran, no rollback goes back past it (E-MIG-007).

Where you can, make the change expand-only instead: to replace a column, add a new one, move the code to it, and stop using the old one. Such a release goes live without an approval while the app keeps serving. Drop the old column in a later publish, once no version that runs uses it.

On your computer

aicoded dev applies, before it starts the app, the migrations the app published, which it fetches from the platform with your sign-in and caches in .aicoded/migrations.json, then the draft. When the draft changes in a way other than growing, it makes the dev database again: its data is test data. A refused draft or a failed step (E-MIG-005) stops the app. aicoded dev tells the details, under Migrations.

aicoded check makes a test database for each app that declares sqldb, when dev.yaml names mysql:, with the same steps. The app's tests reach it as the app's data user, through sqltest.Open(t) from aicoded.dev/framework/sqldb/sqltest, which skips the test elsewhere. Tests that share the database start from a known state. room-maintenance empties its tables:

// testDB returns the test database that aicoded check makes with the tables of
// .aicoded/migration.sql, emptied, or skips the test outside aicoded check.
func testDB(t *testing.T) *sql.DB {
	t.Helper()
	db := sqltest.Open(t)
	_, err := db.ExecContext(t.Context(), "DELETE FROM tickets")
	require.NoError(t, err)
	_, err = db.ExecContext(t.Context(), "DELETE FROM rooms")
	require.NoError(t, err)
	return db
}

Like the app on the platform, the app in aicoded dev and its tests get a user that reads and writes rows only: a CREATE TABLE from the app gets MySQL's access-denied error, and aicoded check refuses one in app code (E-LINT-013).

At publish

aicoded publish, or the publish tool, sends the draft with the commit. The delivery pipeline checks it (Safe data changes). A publish that passes adds the draft's statements, as steps, to the app's migrations: those of the publish it builds on, then these. A publish without a draft keeps the migrations of the one before it, so a change that is not released yet is never lost.

Once the publish passed, aicoded publish deletes the draft and refreshes .aicoded/migrations.json; after --no-wait or the publish tool, aicoded status, the release_status tool or the next publish does it. A publish that failed leaves the draft as it was, to fix and publish again. A draft you changed after it was sent stays. While the publish that carried the draft has not ended, a new publish that still carries a draft is refused (E-MIG-001).

At release

When a release rolls out, the platform applies to the app's database, as the database's owner, the steps the database has not run yet, in order, before it starts the new version. The app itself only ever gets a user that reads and writes rows. The database keeps what ran in its journal, the table aicoded_migrations, which app code may not read or write (E-LINT-013).

A published change never changes

Once a publish passed, its statements are part of the app's migrations as they are, and every database of the app runs them in that order. A mistake in one is fixed by the next draft, such as a new column in place of a wrong one, never by editing what was published. Editing .aicoded/migrations.json changes nothing on the platform, and aicoded dev replaces it with the platform's answer the next time it asks.

See also

Markdown · Source on GitHub · framework f46741f