Introduce the db integration (prerelease) (#10201)

* Initial DB migration code

* chore: update package.json

* chore: update lockfile

* feat: add db as top-level config value

* Small package change

* Add a very basic test

* deps: remove unused

* chore: astro/db scripts, exports, deps

* chore: set tsconfig to monorepo defaults

* feat: MVP to use libsql in dev

* fix: test fixture paths

* fix: test file paths

* chore: remove CLI tests

* fix: add astro-scripts to db

* fix: package.json merge

* fix: load seed file separately for build

* feat: create db on filesystem for build

* fix: ul test. It passes now!

* Squashed commit of the following:

commit acdddd728c56f25e42975db7f367ab8a998e8c41
Author: Princesseuh <3019731+Princesseuh@users.noreply.github.com>
Date:   Wed Jan 10 14:06:16 2024 -0500

    fix: proper type augment for the config

commit b41ca9aacf291d1e5f0a27b6d6339ce4fc608ec3
Author: Nate Moore <nate@astro.build>
Date:   Tue Jan 9 14:33:42 2024 -0600

    wip: type augmentation

* feat: data() fn with basic type safety

* wip: update from seed file to data()

* fix: bad collectionSchema data type

* refactor: move dev to use filesystem to run seed at right time

* chore: remove seed file logic

* docs: add basic README

* CLI sync command

* Runtime checking of writable

* docs: add join example

* Implement defineWritableCollection

* feat: use studio connection for studio builds

* fix: add defineWritableCollection export

* refactor: use getTableName() util

* feat(db): add support for pass-through `astro db` command

* chore: add error map

* fix: add drizzle import

* refactor: studio -> db cli

* feat: add ticketing example

* fix: bad types in astro config

* fix: remove invalid `data()` on writable collection

* fix: vite warning on load-astro-config

* wip: add seeding for readable collections (nonstable ids!)

* merge migration work into branch

* cleanup migration commands

* migrate seed data to new db push command

* add migrations table to db

* fix remote db bugs

* fix: warn writable collections when studio false

* chore: delete README contents (now on Notion)

* chore: remove blank README

* chore: add dev dependency on db

* Removed unused deps

* 0.1.0

* Add config-augment.d.ts to published artifacts"

* Fixes merge issues with main

* fix: support promise response from data()

* feat: basic glob fixture

* Add a main field

* Give a help message when no db command is provided

* feat: `db push --token` for GitHub CI secrets

* fix getPackage for db package

* 0.1.2

* wip: build a table type

* chore: update lockfile

* chore: temporarily remove `Table` type

* feat: better Table object for type inference

* format

* add primaryKey support

* improve snapshot parsing support

* cleanup primary key support, db push

* add simple db shell

* cleanup old copy paste code

* feat: scrappy global data() prototype

* feat(test): recipes example

* fix: use Extract to narrow keyof to strings

* 0.1.3

* Create a runtime version of createRemoteDatabaseClient

* 0.1.4

* Grab the dbUrl from the environment

* 0.1.5

* Expose the database to the build output

* 0.1.6

* 0.1.7

* 0.1.15

* wip: data() -> set() concept

* fix: just infer insert keys for now

* refactor: rewrite to injected set() function

* deps: chokidar, drizzle

* feat: glob support with { db, table } signature

* chore: move basics to new data set

* refactor: set -> seed

* feat: expose Model.table

* refactor: clean up types

* feat: migrations now working!

* upgrade @libsql/client

* format

* expose relevant types

* 0.1.16

* feat: config design

* feat: add indexes from collectionToTable

* feat: add indexes to setupDbTables

* fix: remove unique constraint on recipeId

* Use an import statement to grab the database file

* 0.1.17

* Remove unused import

* Rename to ?fileurl

* 0.1.18

* feat: index migrations

* move migration logic to turso, add back sync support

* feat: add queries unit tests and fix related bugs

* refactor: move field queries to field-queries.test

* feat: index query tests

* refactor: reorganize the rats nest of files

* Make the DB_URL be root relative

* Upgrade to latest libsql

* 0.1.19

* 0.1.20

* Make work in webcontainer

* 0.1.22

* Remove content database from the static build

* 0.1.23

* chore: remove `optional: true` from pk

* chore: disable console linting for CLI

* fix: remove `id` column from Table type

* chore: remove `AstroId` type

* fix(ex): add `id` col to ticketing

* 0.2.0

* 0.2.1

* add keywords

* 0.2.2

* feat: API shape

* feat: FINALLY collection and name attached

* refactor: move to arrow function signature

* fix: foreignKeys references signature

* chore: unused imports

* feat: add foreignkeys to create table

* chore: lint

* chore: enable foreign keys in local mode only

* refactor: objShallowEqual -> deep diff

* fix: correct `hasDefault` inference

* fix: correct type Config reference

* fix: respect primaryKey from hasDefault

* fix: mark data keys as optional until we have type inference

* improve conflict and dataloss handling

- moved prompts to db push
- moved prompt logic out of lower-level functions
- improved logic overall
- improved user-facing prompt messages

* improve error messaging around studio config missing

* make it more clear when remove vs. local db is in use

* fix bug in prompt logic

* feat: better field.x() types

* feat: better seed() types

* chore: remove `as any` on seed values

* feat: good enough return type on seed :)

* feat: defineData()

* fix: add back promptResponse injection

* fix: use schema.parse to resolve dates

* fix: correctly respect primary key on INSERT INTO

* add short-lived db tokens

* add help output

* add better token error logging

* fix studio tests

* add shortcut link command from studio web ui

* Add support for SQL defaults

You can now use sql`CURRENT_TIMESTAMP`, `NOW`, and a couple of other
helpers, to set defaults.

* chore: todo

* feat: ignore `optional` and `default` when pk is present

* refactor: type `false` instead of type `never`

* feat: prevent `optional` on text pk

* fix db URL import for windows

* fix: add back textField multiline

* fix: remove explicit AUTOINCREMENT on int pk

* feat(db-cli): clean up CLI logging, support --json flag for `astro db verify`, extract shared logic to a utility

* prepare to run seed on all db push commands

* chore: expose setMeta for unit testing

* feat(test): reference add and remove tests

* feat: add references checks to migratiosn

* feat: remove useForeignKey checks

* feat: add pragma when pushing migrations

* feat(test): foreignKeys

* fix: transform collection config to be JSON serializable

* refactor: _setMeta -> preprocess for `table`

* refactor: reference tests

* chore: remove console log

* fix: handle serialized SQL object correctly

* refactor: store raw sql instead

* seed on every push

* Move field schema only a `schema` object

* Fix references test

* 0.3.0

* add default URLs to db package

* 0.3.1

* Fix input types

* fix: primaryKey type check

* 0.3.2

* fix: respect default in table types

* fix: avoid dropping tables on production seed

* fix: escape name on drop table

* feat: allow verify to mock migration file

* Handle unauthorized linking

* Fix verbiage of unauthorized link warning

* Add some color to the unauthorized message

* 0.3.3

* Improve the unauthorized error output

* 0.3.4

* fix: better error message

* Seed the Themes in build too

* Push skipped test

* chore: remove dead isJsonSerializable check

* fix: use `dateType` for dates (oops)

* refactor: clarify date coerce comment

* refactor: remove unused coerce

* chore: unskip date test

* feat: seed -> seedReturning

* refactor: throw when seeding writable in prod

* Add unsafeWritable option

* refactor: use FieldsConfig for Table generic

* chore: lint

* fix: use z.input for AstroConfigWithDB type

* fix: add defaults for boolean config options

* Support new CLI command structure

* Allow writable in the tests

* fix: handle defaults for safe type changes

* refactor: avoid selecting ['schema'] on input types

* 0.3.5

* Rename field->column, collection->table

* Rename collections->tables

* rename to defineReadableTable

* deps: upgrade ticketing-example

* fix: stray console.log

* deps: bump preact again

* chore: preact->react

* fix: parse params.event as number

* fix: correct event references

* Allow integrations to define schema

* fix: file-url plugin failure on repeated generateBundle() runs

* update url

* Cleanup

* Linting

* fix windows file permission issue

When runnng `astro dev`, the watcher would close trying to delete the `content.db` file due to a file permission error. This change makes the local DB client a disposable to allow cleanup after usage.

* Formatting

* "fix" Symbol.dispose usage

---------

Co-authored-by: Nate Moore <nate@astro.build>
Co-authored-by: bholmesdev <hey@bholmes.dev>
Co-authored-by: Fred K. Schott <fkschott@gmail.com>
Co-authored-by: itsMapleLeaf <19603573+itsMapleLeaf@users.noreply.github.com>

1 files changed, 178 insertions, 0 deletions

diff --git a/packages/db/test/unit/reference-queries.test.js b/packages/db/test/unit/reference-queries.test.js
new file mode 100644
index 000000000..4c2e3af8d
--- /dev/null
+++ b/packages/db/test/unit/reference-queries.test.js
@@ -0,0 +1,178 @@
+import { expect } from 'chai';
+import { describe, it } from 'mocha';
+import { getCollectionChangeQueries } from '../../dist/core/cli/migration-queries.js';
+import { column, defineReadableTable, tablesSchema } from '../../dist/core/types.js';
+
+const BaseUser = defineReadableTable({
+	columns: {
+		id: column.number({ primaryKey: true }),
+		name: column.text(),
+		age: column.number(),
+		email: column.text({ unique: true }),
+		mi: column.text({ optional: true }),
+	},
+});
+
+const BaseSentBox = defineReadableTable({
+	columns: {
+		to: column.number(),
+		toName: column.text(),
+		subject: column.text(),
+		body: column.text(),
+	},
+});
+
+const defaultAmbiguityResponses = {
+	collectionRenames: {},
+	columnRenames: {},
+};
+
+/**
+ * @typedef {import('../../dist/core/types.js').DBTable} DBTable
+ * @param {{ User: DBTable, SentBox: DBTable }} params
+ * @returns
+ */
+function resolveReferences(
+	{ User = BaseUser, SentBox = BaseSentBox } = {
+		User: BaseUser,
+		SentBox: BaseSentBox,
+	}
+) {
+	return tablesSchema.parse({ User, SentBox });
+}
+
+function userChangeQueries(
+	oldCollection,
+	newCollection,
+	ambiguityResponses = defaultAmbiguityResponses
+) {
+	return getCollectionChangeQueries({
+		collectionName: 'User',
+		oldCollection,
+		newCollection,
+		ambiguityResponses,
+	});
+}
+
+describe('reference queries', () => {
+	it('adds references with lossless table recreate', async () => {
+		const { SentBox: Initial } = resolveReferences();
+		const { SentBox: Final } = resolveReferences({
+			SentBox: defineReadableTable({
+				columns: {
+					...BaseSentBox.columns,
+					to: column.number({ references: () => BaseUser.columns.id }),
+				},
+			}),
+		});
+
+		const { queries } = await userChangeQueries(Initial, Final);
+
+		expect(queries[0]).to.not.be.undefined;
+		const tempTableName = getTempTableName(queries[0]);
+		expect(tempTableName).to.not.be.undefined;
+
+		expect(queries).to.deep.equal([
+			`CREATE TABLE \"${tempTableName}\" (_id INTEGER PRIMARY KEY, \"to\" integer NOT NULL REFERENCES \"User\" (\"id\"), \"toName\" text NOT NULL, \"subject\" text NOT NULL, \"body\" text NOT NULL)`,
+			`INSERT INTO \"${tempTableName}\" (\"_id\", \"to\", \"toName\", \"subject\", \"body\") SELECT \"_id\", \"to\", \"toName\", \"subject\", \"body\" FROM \"User\"`,
+			'DROP TABLE "User"',
+			`ALTER TABLE \"${tempTableName}\" RENAME TO \"User\"`,
+		]);
+	});
+
+	it('removes references with lossless table recreate', async () => {
+		const { SentBox: Initial } = resolveReferences({
+			SentBox: defineReadableTable({
+				columns: {
+					...BaseSentBox.columns,
+					to: column.number({ references: () => BaseUser.columns.id }),
+				},
+			}),
+		});
+		const { SentBox: Final } = resolveReferences();
+
+		const { queries } = await userChangeQueries(Initial, Final);
+
+		expect(queries[0]).to.not.be.undefined;
+		const tempTableName = getTempTableName(queries[0]);
+		expect(tempTableName).to.not.be.undefined;
+
+		expect(queries).to.deep.equal([
+			`CREATE TABLE \"${tempTableName}\" (_id INTEGER PRIMARY KEY, \"to\" integer NOT NULL, \"toName\" text NOT NULL, \"subject\" text NOT NULL, \"body\" text NOT NULL)`,
+			`INSERT INTO \"${tempTableName}\" (\"_id\", \"to\", \"toName\", \"subject\", \"body\") SELECT \"_id\", \"to\", \"toName\", \"subject\", \"body\" FROM \"User\"`,
+			'DROP TABLE "User"',
+			`ALTER TABLE \"${tempTableName}\" RENAME TO \"User\"`,
+		]);
+	});
+
+	it('does not use ADD COLUMN when adding optional column with reference', async () => {
+		const { SentBox: Initial } = resolveReferences();
+		const { SentBox: Final } = resolveReferences({
+			SentBox: defineReadableTable({
+				columns: {
+					...BaseSentBox.columns,
+					from: column.number({ references: () => BaseUser.columns.id, optional: true }),
+				},
+			}),
+		});
+
+		const { queries } = await userChangeQueries(Initial, Final);
+		expect(queries[0]).to.not.be.undefined;
+		const tempTableName = getTempTableName(queries[0]);
+
+		expect(queries).to.deep.equal([
+			`CREATE TABLE \"${tempTableName}\" (_id INTEGER PRIMARY KEY, \"to\" integer NOT NULL, \"toName\" text NOT NULL, \"subject\" text NOT NULL, \"body\" text NOT NULL, \"from\" integer REFERENCES \"User\" (\"id\"))`,
+			`INSERT INTO \"${tempTableName}\" (\"_id\", \"to\", \"toName\", \"subject\", \"body\") SELECT \"_id\", \"to\", \"toName\", \"subject\", \"body\" FROM \"User\"`,
+			'DROP TABLE "User"',
+			`ALTER TABLE \"${tempTableName}\" RENAME TO \"User\"`,
+		]);
+	});
+
+	it('adds and updates foreign key with lossless table recreate', async () => {
+		const { SentBox: InitialWithoutFK } = resolveReferences();
+		const { SentBox: InitialWithDifferentFK } = resolveReferences({
+			SentBox: defineReadableTable({
+				...BaseSentBox,
+				foreignKeys: [{ columns: ['to'], references: () => [BaseUser.columns.id] }],
+			}),
+		});
+		const { SentBox: Final } = resolveReferences({
+			SentBox: defineReadableTable({
+				...BaseSentBox,
+				foreignKeys: [
+					{
+						columns: ['to', 'toName'],
+						references: () => [BaseUser.columns.id, BaseUser.columns.name],
+					},
+				],
+			}),
+		});
+
+		const expected = (tempTableName) => [
+			`CREATE TABLE \"${tempTableName}\" (_id INTEGER PRIMARY KEY, \"to\" integer NOT NULL, \"toName\" text NOT NULL, \"subject\" text NOT NULL, \"body\" text NOT NULL, FOREIGN KEY (\"to\", \"toName\") REFERENCES \"User\"(\"id\", \"name\"))`,
+			`INSERT INTO \"${tempTableName}\" (\"_id\", \"to\", \"toName\", \"subject\", \"body\") SELECT \"_id\", \"to\", \"toName\", \"subject\", \"body\" FROM \"User\"`,
+			'DROP TABLE "User"',
+			`ALTER TABLE \"${tempTableName}\" RENAME TO \"User\"`,
+		];
+
+		const addedForeignKey = await userChangeQueries(InitialWithoutFK, Final);
+		const updatedForeignKey = await userChangeQueries(InitialWithDifferentFK, Final);
+
+		expect(addedForeignKey.queries[0]).to.not.be.undefined;
+		expect(updatedForeignKey.queries[0]).to.not.be.undefined;
+
+		expect(addedForeignKey.queries).to.deep.equal(
+			expected(getTempTableName(addedForeignKey.queries[0]))
+		);
+
+		expect(updatedForeignKey.queries).to.deep.equal(
+			expected(getTempTableName(updatedForeignKey.queries[0]))
+		);
+	});
+});
+
+/** @param {string | undefined} query */
+function getTempTableName(query) {
+	// eslint-disable-next-line regexp/no-unused-capturing-group
+	return query.match(/User_([a-z\d]+)/)?.[0];
+}