aboutsummaryrefslogtreecommitdiff
path: root/packages/db/test/unit
diff options
context:
space:
mode:
Diffstat (limited to 'packages/db/test/unit')
-rw-r--r--packages/db/test/unit/column-queries.test.js496
-rw-r--r--packages/db/test/unit/db-client.test.js60
-rw-r--r--packages/db/test/unit/index-queries.test.js283
-rw-r--r--packages/db/test/unit/reference-queries.test.js169
-rw-r--r--packages/db/test/unit/remote-info.test.js119
-rw-r--r--packages/db/test/unit/reset-queries.test.js54
6 files changed, 1181 insertions, 0 deletions
diff --git a/packages/db/test/unit/column-queries.test.js b/packages/db/test/unit/column-queries.test.js
new file mode 100644
index 000000000..e4bb027a4
--- /dev/null
+++ b/packages/db/test/unit/column-queries.test.js
@@ -0,0 +1,496 @@
+import assert from 'node:assert/strict';
+import { describe, it } from 'node:test';
+import {
+ getMigrationQueries,
+ getTableChangeQueries,
+} from '../../dist/core/cli/migration-queries.js';
+import { MIGRATION_VERSION } from '../../dist/core/consts.js';
+import { tableSchema } from '../../dist/core/schemas.js';
+import { NOW, column, defineTable } from '../../dist/runtime/virtual.js';
+
+const TABLE_NAME = 'Users';
+
+// `parse` to resolve schema transformations
+// ex. convert column.date() to ISO strings
+const userInitial = tableSchema.parse(
+ defineTable({
+ columns: {
+ name: column.text(),
+ age: column.number(),
+ email: column.text({ unique: true }),
+ mi: column.text({ optional: true }),
+ },
+ }),
+);
+
+function userChangeQueries(oldTable, newTable) {
+ return getTableChangeQueries({
+ tableName: TABLE_NAME,
+ oldTable,
+ newTable,
+ });
+}
+
+function configChangeQueries(oldTables, newTables) {
+ return getMigrationQueries({
+ oldSnapshot: { schema: oldTables, version: MIGRATION_VERSION },
+ newSnapshot: { schema: newTables, version: MIGRATION_VERSION },
+ });
+}
+
+describe('column queries', () => {
+ describe('getMigrationQueries', () => {
+ it('should be empty when tables are the same', async () => {
+ const oldTables = { [TABLE_NAME]: userInitial };
+ const newTables = { [TABLE_NAME]: userInitial };
+ const { queries } = await configChangeQueries(oldTables, newTables);
+ assert.deepEqual(queries, []);
+ });
+
+ it('should create table for new tables', async () => {
+ const oldTables = {};
+ const newTables = { [TABLE_NAME]: userInitial };
+ const { queries } = await configChangeQueries(oldTables, newTables);
+ assert.deepEqual(queries, [
+ `CREATE TABLE "${TABLE_NAME}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL, "email" text NOT NULL UNIQUE, "mi" text)`,
+ ]);
+ });
+
+ it('should drop table for removed tables', async () => {
+ const oldTables = { [TABLE_NAME]: userInitial };
+ const newTables = {};
+ const { queries } = await configChangeQueries(oldTables, newTables);
+ assert.deepEqual(queries, [`DROP TABLE "${TABLE_NAME}"`]);
+ });
+
+ it('should error if possible table rename is detected', async () => {
+ const rename = 'Peeps';
+ const oldTables = { [TABLE_NAME]: userInitial };
+ const newTables = { [rename]: userInitial };
+ let error = null;
+ try {
+ await configChangeQueries(oldTables, newTables);
+ } catch (e) {
+ error = e.message;
+ }
+ assert.match(error, /Potential table rename detected/);
+ });
+
+ it('should error if possible column rename is detected', async () => {
+ const blogInitial = tableSchema.parse({
+ columns: {
+ title: column.text(),
+ },
+ });
+ const blogFinal = tableSchema.parse({
+ columns: {
+ title2: column.text(),
+ },
+ });
+ let error = null;
+ try {
+ await configChangeQueries({ [TABLE_NAME]: blogInitial }, { [TABLE_NAME]: blogFinal });
+ } catch (e) {
+ error = e.message;
+ }
+ assert.match(error, /Potential column rename detected/);
+ });
+ });
+
+ describe('getTableChangeQueries', () => {
+ it('should be empty when tables are the same', async () => {
+ const { queries } = await userChangeQueries(userInitial, userInitial);
+ assert.deepEqual(queries, []);
+ });
+
+ it('should return warning if column type change introduces data loss', async () => {
+ const blogInitial = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ date: column.text(),
+ },
+ });
+ const blogFinal = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ date: column.date(),
+ },
+ });
+ const { queries, confirmations } = await userChangeQueries(blogInitial, blogFinal);
+ assert.deepEqual(queries, [
+ 'DROP TABLE "Users"',
+ 'CREATE TABLE "Users" (_id INTEGER PRIMARY KEY, "date" text NOT NULL)',
+ ]);
+ assert.equal(confirmations.length, 1);
+ });
+
+ it('should return warning if new required column added', async () => {
+ const blogInitial = tableSchema.parse({
+ ...userInitial,
+ columns: {},
+ });
+ const blogFinal = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ date: column.date({ optional: false }),
+ },
+ });
+ const { queries, confirmations } = await userChangeQueries(blogInitial, blogFinal);
+ assert.deepEqual(queries, [
+ 'DROP TABLE "Users"',
+ 'CREATE TABLE "Users" (_id INTEGER PRIMARY KEY, "date" text NOT NULL)',
+ ]);
+ assert.equal(confirmations.length, 1);
+ });
+
+ it('should return warning if non-number primary key with no default added', async () => {
+ const blogInitial = tableSchema.parse({
+ ...userInitial,
+ columns: {},
+ });
+ const blogFinal = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ id: column.text({ primaryKey: true }),
+ },
+ });
+ const { queries, confirmations } = await userChangeQueries(blogInitial, blogFinal);
+ assert.deepEqual(queries, [
+ 'DROP TABLE "Users"',
+ 'CREATE TABLE "Users" ("id" text PRIMARY KEY)',
+ ]);
+ assert.equal(confirmations.length, 1);
+ });
+
+ it('should be empty when type updated to same underlying SQL type', async () => {
+ const blogInitial = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ title: column.text(),
+ draft: column.boolean(),
+ },
+ });
+ const blogFinal = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ ...blogInitial.columns,
+ draft: column.number(),
+ },
+ });
+ const { queries } = await userChangeQueries(blogInitial, blogFinal);
+ assert.deepEqual(queries, []);
+ });
+
+ it('should respect user primary key without adding a hidden id', async () => {
+ const user = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ id: column.number({ primaryKey: true }),
+ },
+ });
+
+ const userFinal = tableSchema.parse({
+ ...user,
+ columns: {
+ ...user.columns,
+ name: column.text({ unique: true, optional: true }),
+ },
+ });
+
+ const { queries } = await userChangeQueries(user, userFinal);
+ assert.equal(queries[0] !== undefined, true);
+ const tempTableName = getTempTableName(queries[0]);
+
+ assert.deepEqual(queries, [
+ `CREATE TABLE \"${tempTableName}\" (\"name\" text UNIQUE, \"age\" integer NOT NULL, \"email\" text NOT NULL UNIQUE, \"mi\" text, \"id\" integer PRIMARY KEY)`,
+ `INSERT INTO \"${tempTableName}\" (\"name\", \"age\", \"email\", \"mi\", \"id\") SELECT \"name\", \"age\", \"email\", \"mi\", \"id\" FROM \"Users\"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+
+ describe('Lossy table recreate', () => {
+ it('when changing a column type', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ age: column.text(),
+ },
+ };
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+
+ assert.deepEqual(queries, [
+ 'DROP TABLE "Users"',
+ `CREATE TABLE "Users" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" text NOT NULL, "email" text NOT NULL UNIQUE, "mi" text)`,
+ ]);
+ });
+
+ it('when adding a required column without a default', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ phoneNumber: column.text(),
+ },
+ };
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+
+ assert.deepEqual(queries, [
+ 'DROP TABLE "Users"',
+ `CREATE TABLE "Users" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL, "email" text NOT NULL UNIQUE, "mi" text, "phoneNumber" text NOT NULL)`,
+ ]);
+ });
+ });
+
+ describe('Lossless table recreate', () => {
+ it('when adding a primary key', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ id: column.number({ primaryKey: true }),
+ },
+ };
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+ assert.equal(queries[0] !== undefined, true);
+
+ const tempTableName = getTempTableName(queries[0]);
+ assert.deepEqual(queries, [
+ `CREATE TABLE \"${tempTableName}\" (\"name\" text NOT NULL, \"age\" integer NOT NULL, \"email\" text NOT NULL UNIQUE, \"mi\" text, \"id\" integer PRIMARY KEY)`,
+ `INSERT INTO \"${tempTableName}\" (\"name\", \"age\", \"email\", \"mi\") SELECT \"name\", \"age\", \"email\", \"mi\" FROM \"Users\"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+
+ it('when dropping a primary key', async () => {
+ const user = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ id: column.number({ primaryKey: true }),
+ },
+ };
+
+ const { queries } = await userChangeQueries(user, userInitial);
+ assert.equal(queries[0] !== undefined, true);
+
+ const tempTableName = getTempTableName(queries[0]);
+ assert.deepEqual(queries, [
+ `CREATE TABLE \"${tempTableName}\" (_id INTEGER PRIMARY KEY, \"name\" text NOT NULL, \"age\" integer NOT NULL, \"email\" text NOT NULL UNIQUE, \"mi\" text)`,
+ `INSERT INTO \"${tempTableName}\" (\"name\", \"age\", \"email\", \"mi\") SELECT \"name\", \"age\", \"email\", \"mi\" FROM \"Users\"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+
+ it('when adding an optional unique column', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ phoneNumber: column.text({ unique: true, optional: true }),
+ },
+ };
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+ assert.equal(queries.length, 4);
+
+ const tempTableName = getTempTableName(queries[0]);
+ assert.equal(typeof tempTableName, 'string');
+ assert.deepEqual(queries, [
+ `CREATE TABLE "${tempTableName}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL, "email" text NOT NULL UNIQUE, "mi" text, "phoneNumber" text UNIQUE)`,
+ `INSERT INTO "${tempTableName}" ("_id", "name", "age", "email", "mi") SELECT "_id", "name", "age", "email", "mi" FROM "Users"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+
+ it('when dropping unique column', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ },
+ };
+ delete userFinal.columns.email;
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+ assert.equal(queries.length, 4);
+ assert.equal(queries.length, 4);
+
+ const tempTableName = getTempTableName(queries[0]);
+ assert.equal(typeof tempTableName, 'string');
+ assert.deepEqual(queries, [
+ `CREATE TABLE "${tempTableName}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL, "mi" text)`,
+ `INSERT INTO "${tempTableName}" ("_id", "name", "age", "mi") SELECT "_id", "name", "age", "mi" FROM "Users"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+
+ it('when updating to a runtime default', async () => {
+ const initial = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ age: column.date(),
+ },
+ });
+
+ const userFinal = tableSchema.parse({
+ ...initial,
+ columns: {
+ ...initial.columns,
+ age: column.date({ default: NOW }),
+ },
+ });
+
+ const { queries } = await userChangeQueries(initial, userFinal);
+ assert.equal(queries.length, 4);
+
+ const tempTableName = getTempTableName(queries[0]);
+ assert.equal(typeof tempTableName, 'string');
+ assert.deepEqual(queries, [
+ `CREATE TABLE "${tempTableName}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" text NOT NULL DEFAULT CURRENT_TIMESTAMP, "email" text NOT NULL UNIQUE, "mi" text)`,
+ `INSERT INTO "${tempTableName}" ("_id", "name", "age", "email", "mi") SELECT "_id", "name", "age", "email", "mi" FROM "Users"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+
+ it('when adding a column with a runtime default', async () => {
+ const userFinal = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ birthday: column.date({ default: NOW }),
+ },
+ });
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+ assert.equal(queries.length, 4);
+
+ const tempTableName = getTempTableName(queries[0]);
+ assert.equal(typeof tempTableName, 'string');
+ assert.deepEqual(queries, [
+ `CREATE TABLE "${tempTableName}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL, "email" text NOT NULL UNIQUE, "mi" text, "birthday" text NOT NULL DEFAULT CURRENT_TIMESTAMP)`,
+ `INSERT INTO "${tempTableName}" ("_id", "name", "age", "email", "mi") SELECT "_id", "name", "age", "email", "mi" FROM "Users"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+
+ /**
+ * REASON: to follow the "expand" and "contract" migration model,
+ * you'll need to update the schema from NOT NULL to NULL.
+ * It's up to the user to ensure all data follows the new schema!
+ *
+ * @see https://planetscale.com/blog/safely-making-database-schema-changes#backwards-compatible-changes
+ */
+ it('when changing a column to required', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ mi: column.text(),
+ },
+ };
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+
+ assert.equal(queries.length, 4);
+
+ const tempTableName = getTempTableName(queries[0]);
+ assert.equal(typeof tempTableName, 'string');
+ assert.deepEqual(queries, [
+ `CREATE TABLE "${tempTableName}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL, "email" text NOT NULL UNIQUE, "mi" text NOT NULL)`,
+ `INSERT INTO "${tempTableName}" ("_id", "name", "age", "email", "mi") SELECT "_id", "name", "age", "email", "mi" FROM "Users"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+
+ it('when changing a column to unique', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ age: column.number({ unique: true }),
+ },
+ };
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+ assert.equal(queries.length, 4);
+
+ const tempTableName = getTempTableName(queries[0]);
+ assert.equal(typeof tempTableName, 'string');
+ assert.deepEqual(queries, [
+ `CREATE TABLE "${tempTableName}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL UNIQUE, "email" text NOT NULL UNIQUE, "mi" text)`,
+ `INSERT INTO "${tempTableName}" ("_id", "name", "age", "email", "mi") SELECT "_id", "name", "age", "email", "mi" FROM "Users"`,
+ 'DROP TABLE "Users"',
+ `ALTER TABLE "${tempTableName}" RENAME TO "Users"`,
+ ]);
+ });
+ });
+
+ describe('ALTER ADD COLUMN', () => {
+ it('when adding an optional column', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ birthday: column.date({ optional: true }),
+ },
+ };
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+ assert.deepEqual(queries, ['ALTER TABLE "Users" ADD COLUMN "birthday" text']);
+ });
+
+ it('when adding a required column with default', async () => {
+ const defaultDate = new Date('2023-01-01');
+ const userFinal = tableSchema.parse({
+ ...userInitial,
+ columns: {
+ ...userInitial.columns,
+ birthday: column.date({ default: new Date('2023-01-01') }),
+ },
+ });
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+ assert.deepEqual(queries, [
+ `ALTER TABLE "Users" ADD COLUMN "birthday" text NOT NULL DEFAULT '${defaultDate.toISOString()}'`,
+ ]);
+ });
+ });
+
+ describe('ALTER DROP COLUMN', () => {
+ it('when removing optional or required columns', async () => {
+ const userFinal = {
+ ...userInitial,
+ columns: {
+ name: userInitial.columns.name,
+ email: userInitial.columns.email,
+ },
+ };
+
+ const { queries } = await userChangeQueries(userInitial, userFinal);
+ assert.deepEqual(queries, [
+ 'ALTER TABLE "Users" DROP COLUMN "age"',
+ 'ALTER TABLE "Users" DROP COLUMN "mi"',
+ ]);
+ });
+ });
+ });
+});
+
+/** @param {string} query */
+function getTempTableName(query) {
+ return /Users_[a-z\d]+/.exec(query)?.[0];
+}
diff --git a/packages/db/test/unit/db-client.test.js b/packages/db/test/unit/db-client.test.js
new file mode 100644
index 000000000..22df2610e
--- /dev/null
+++ b/packages/db/test/unit/db-client.test.js
@@ -0,0 +1,60 @@
+import assert from 'node:assert';
+import test, { describe } from 'node:test';
+import { parseOpts } from '../../dist/runtime/db-client.js';
+
+describe('db client config', () => {
+ test('parse config options from URL (docs example url)', () => {
+ const remoteURLToParse = new URL(
+ 'file://local-copy.db?encryptionKey=your-encryption-key&syncInterval=60&syncUrl=libsql%3A%2F%2Fyour.server.io',
+ );
+ const options = Object.fromEntries(remoteURLToParse.searchParams.entries());
+
+ const config = parseOpts(options);
+
+ assert.deepEqual(config, {
+ encryptionKey: 'your-encryption-key',
+ syncInterval: 60,
+ syncUrl: 'libsql://your.server.io',
+ });
+ });
+
+ test('parse config options from URL (test booleans without value)', () => {
+ const remoteURLToParse = new URL('file://local-copy.db?readYourWrites&offline&tls');
+ const options = Object.fromEntries(remoteURLToParse.searchParams.entries());
+
+ const config = parseOpts(options);
+
+ assert.deepEqual(config, {
+ readYourWrites: true,
+ offline: true,
+ tls: true,
+ });
+ });
+
+ test('parse config options from URL (test booleans with value)', () => {
+ const remoteURLToParse = new URL(
+ 'file://local-copy.db?readYourWrites=true&offline=true&tls=true',
+ );
+ const options = Object.fromEntries(remoteURLToParse.searchParams.entries());
+
+ const config = parseOpts(options);
+
+ assert.deepEqual(config, {
+ readYourWrites: true,
+ offline: true,
+ tls: true,
+ });
+ });
+
+ test('parse config options from URL (test numbers)', () => {
+ const remoteURLToParse = new URL('file://local-copy.db?syncInterval=60&concurrency=2');
+ const options = Object.fromEntries(remoteURLToParse.searchParams.entries());
+
+ const config = parseOpts(options);
+
+ assert.deepEqual(config, {
+ syncInterval: 60,
+ concurrency: 2,
+ });
+ });
+});
diff --git a/packages/db/test/unit/index-queries.test.js b/packages/db/test/unit/index-queries.test.js
new file mode 100644
index 000000000..4b4722baa
--- /dev/null
+++ b/packages/db/test/unit/index-queries.test.js
@@ -0,0 +1,283 @@
+import assert from 'node:assert/strict';
+import { describe, it } from 'node:test';
+import { getTableChangeQueries } from '../../dist/core/cli/migration-queries.js';
+import { dbConfigSchema, tableSchema } from '../../dist/core/schemas.js';
+import { column } from '../../dist/runtime/virtual.js';
+
+const userInitial = tableSchema.parse({
+ columns: {
+ name: column.text(),
+ age: column.number(),
+ email: column.text({ unique: true }),
+ mi: column.text({ optional: true }),
+ },
+ indexes: {},
+ writable: false,
+});
+
+describe('index queries', () => {
+ it('generates index names by table and combined column names', async () => {
+ // Use dbConfigSchema.parse to resolve generated idx names
+ const dbConfig = dbConfigSchema.parse({
+ tables: {
+ oldTable: userInitial,
+ newTable: {
+ ...userInitial,
+ indexes: [
+ { on: ['name', 'age'], unique: false },
+ { on: ['email'], unique: true },
+ ],
+ },
+ },
+ });
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: dbConfig.tables.oldTable,
+ newTable: dbConfig.tables.newTable,
+ });
+
+ assert.deepEqual(queries, [
+ 'CREATE INDEX "newTable_age_name_idx" ON "user" ("age", "name")',
+ 'CREATE UNIQUE INDEX "newTable_email_idx" ON "user" ("email")',
+ ]);
+ });
+
+ it('generates index names with consistent column ordering', async () => {
+ const initial = dbConfigSchema.parse({
+ tables: {
+ user: {
+ ...userInitial,
+ indexes: [
+ { on: ['email'], unique: true },
+ { on: ['name', 'age'], unique: false },
+ ],
+ },
+ },
+ });
+
+ const final = dbConfigSchema.parse({
+ tables: {
+ user: {
+ ...userInitial,
+ indexes: [
+ // flip columns
+ { on: ['age', 'name'], unique: false },
+ // flip index order
+ { on: ['email'], unique: true },
+ ],
+ },
+ },
+ });
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: initial.tables.user,
+ newTable: final.tables.user,
+ });
+
+ assert.equal(queries.length, 0);
+ });
+
+ it('does not trigger queries when changing from legacy to new format', async () => {
+ const initial = dbConfigSchema.parse({
+ tables: {
+ user: {
+ ...userInitial,
+ indexes: {
+ emailIdx: { on: ['email'], unique: true },
+ nameAgeIdx: { on: ['name', 'age'], unique: false },
+ },
+ },
+ },
+ });
+
+ const final = dbConfigSchema.parse({
+ tables: {
+ user: {
+ ...userInitial,
+ indexes: [
+ { on: ['email'], unique: true, name: 'emailIdx' },
+ { on: ['name', 'age'], unique: false, name: 'nameAgeIdx' },
+ ],
+ },
+ },
+ });
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: initial.tables.user,
+ newTable: final.tables.user,
+ });
+
+ assert.equal(queries.length, 0);
+ });
+
+ it('adds indexes', async () => {
+ const dbConfig = dbConfigSchema.parse({
+ tables: {
+ oldTable: userInitial,
+ newTable: {
+ ...userInitial,
+ indexes: [
+ { on: ['name'], unique: false, name: 'nameIdx' },
+ { on: ['email'], unique: true, name: 'emailIdx' },
+ ],
+ },
+ },
+ });
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: dbConfig.tables.oldTable,
+ newTable: dbConfig.tables.newTable,
+ });
+
+ assert.deepEqual(queries, [
+ 'CREATE INDEX "nameIdx" ON "user" ("name")',
+ 'CREATE UNIQUE INDEX "emailIdx" ON "user" ("email")',
+ ]);
+ });
+
+ it('drops indexes', async () => {
+ const dbConfig = dbConfigSchema.parse({
+ tables: {
+ oldTable: {
+ ...userInitial,
+ indexes: [
+ { on: ['name'], unique: false, name: 'nameIdx' },
+ { on: ['email'], unique: true, name: 'emailIdx' },
+ ],
+ },
+ newTable: {
+ ...userInitial,
+ indexes: {},
+ },
+ },
+ });
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: dbConfig.tables.oldTable,
+ newTable: dbConfig.tables.newTable,
+ });
+
+ assert.deepEqual(queries, ['DROP INDEX "nameIdx"', 'DROP INDEX "emailIdx"']);
+ });
+
+ it('drops and recreates modified indexes', async () => {
+ const dbConfig = dbConfigSchema.parse({
+ tables: {
+ oldTable: {
+ ...userInitial,
+ indexes: [
+ { unique: false, on: ['name'], name: 'nameIdx' },
+ { unique: true, on: ['email'], name: 'emailIdx' },
+ ],
+ },
+ newTable: {
+ ...userInitial,
+ indexes: [
+ { unique: true, on: ['name'], name: 'nameIdx' },
+ { on: ['email'], name: 'emailIdx' },
+ ],
+ },
+ },
+ });
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: dbConfig.tables.oldTable,
+ newTable: dbConfig.tables.newTable,
+ });
+
+ assert.deepEqual(queries, [
+ 'DROP INDEX "nameIdx"',
+ 'DROP INDEX "emailIdx"',
+ 'CREATE UNIQUE INDEX "nameIdx" ON "user" ("name")',
+ 'CREATE INDEX "emailIdx" ON "user" ("email")',
+ ]);
+ });
+
+ describe('legacy object config', () => {
+ it('adds indexes', async () => {
+ /** @type {import('../../dist/core/types.js').DBTable} */
+ const userFinal = {
+ ...userInitial,
+ indexes: {
+ nameIdx: { on: ['name'], unique: false },
+ emailIdx: { on: ['email'], unique: true },
+ },
+ };
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: userInitial,
+ newTable: userFinal,
+ });
+
+ assert.deepEqual(queries, [
+ 'CREATE INDEX "nameIdx" ON "user" ("name")',
+ 'CREATE UNIQUE INDEX "emailIdx" ON "user" ("email")',
+ ]);
+ });
+
+ it('drops indexes', async () => {
+ /** @type {import('../../dist/core/types.js').DBTable} */
+ const initial = {
+ ...userInitial,
+ indexes: {
+ nameIdx: { on: ['name'], unique: false },
+ emailIdx: { on: ['email'], unique: true },
+ },
+ };
+
+ /** @type {import('../../dist/core/types.js').DBTable} */
+ const final = {
+ ...userInitial,
+ indexes: {},
+ };
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: initial,
+ newTable: final,
+ });
+
+ assert.deepEqual(queries, ['DROP INDEX "nameIdx"', 'DROP INDEX "emailIdx"']);
+ });
+
+ it('drops and recreates modified indexes', async () => {
+ /** @type {import('../../dist/core/types.js').DBTable} */
+ const initial = {
+ ...userInitial,
+ indexes: {
+ nameIdx: { on: ['name'], unique: false },
+ emailIdx: { on: ['email'], unique: true },
+ },
+ };
+
+ /** @type {import('../../dist/core/types.js').DBTable} */
+ const final = {
+ ...userInitial,
+ indexes: {
+ nameIdx: { on: ['name'], unique: true },
+ emailIdx: { on: ['email'] },
+ },
+ };
+
+ const { queries } = await getTableChangeQueries({
+ tableName: 'user',
+ oldTable: initial,
+ newTable: final,
+ });
+
+ assert.deepEqual(queries, [
+ 'DROP INDEX "nameIdx"',
+ 'DROP INDEX "emailIdx"',
+ 'CREATE UNIQUE INDEX "nameIdx" ON "user" ("name")',
+ 'CREATE INDEX "emailIdx" ON "user" ("email")',
+ ]);
+ });
+ });
+});
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..04f5f84aa
--- /dev/null
+++ b/packages/db/test/unit/reference-queries.test.js
@@ -0,0 +1,169 @@
+import assert from 'node:assert/strict';
+import { describe, it } from 'node:test';
+import { getTableChangeQueries } from '../../dist/core/cli/migration-queries.js';
+import { tablesSchema } from '../../dist/core/schemas.js';
+import { column, defineTable } from '../../dist/runtime/virtual.js';
+
+const BaseUser = defineTable({
+ columns: {
+ id: column.number({ primaryKey: true }),
+ name: column.text(),
+ age: column.number(),
+ email: column.text({ unique: true }),
+ mi: column.text({ optional: true }),
+ },
+});
+
+const BaseSentBox = defineTable({
+ columns: {
+ to: column.number(),
+ toName: column.text(),
+ subject: column.text(),
+ body: column.text(),
+ },
+});
+
+/**
+ * @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(oldTable, newTable) {
+ return getTableChangeQueries({
+ tableName: 'User',
+ oldTable,
+ newTable,
+ });
+}
+
+describe('reference queries', () => {
+ it('adds references with lossless table recreate', async () => {
+ const { SentBox: Initial } = resolveReferences();
+ const { SentBox: Final } = resolveReferences({
+ SentBox: defineTable({
+ columns: {
+ ...BaseSentBox.columns,
+ to: column.number({ references: () => BaseUser.columns.id }),
+ },
+ }),
+ });
+
+ const { queries } = await userChangeQueries(Initial, Final);
+
+ assert.equal(queries[0] !== undefined, true);
+ const tempTableName = getTempTableName(queries[0]);
+ assert.notEqual(typeof tempTableName, 'undefined');
+
+ assert.deepEqual(queries, [
+ `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: defineTable({
+ columns: {
+ ...BaseSentBox.columns,
+ to: column.number({ references: () => BaseUser.columns.id }),
+ },
+ }),
+ });
+ const { SentBox: Final } = resolveReferences();
+
+ const { queries } = await userChangeQueries(Initial, Final);
+
+ assert.equal(queries[0] !== undefined, true);
+ const tempTableName = getTempTableName(queries[0]);
+ assert.notEqual(typeof tempTableName, 'undefined');
+
+ assert.deepEqual(queries, [
+ `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: defineTable({
+ columns: {
+ ...BaseSentBox.columns,
+ from: column.number({ references: () => BaseUser.columns.id, optional: true }),
+ },
+ }),
+ });
+
+ const { queries } = await userChangeQueries(Initial, Final);
+ assert.equal(queries[0] !== undefined, true);
+ const tempTableName = getTempTableName(queries[0]);
+
+ assert.deepEqual(queries, [
+ `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: defineTable({
+ ...BaseSentBox,
+ foreignKeys: [{ columns: ['to'], references: () => [BaseUser.columns.id] }],
+ }),
+ });
+ const { SentBox: Final } = resolveReferences({
+ SentBox: defineTable({
+ ...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);
+
+ assert.notEqual(typeof addedForeignKey.queries[0], 'undefined');
+ assert.notEqual(typeof updatedForeignKey.queries[0], 'undefined');
+ assert.deepEqual(
+ addedForeignKey.queries,
+ expected(getTempTableName(addedForeignKey.queries[0])),
+ );
+
+ assert.deepEqual(
+ updatedForeignKey.queries,
+ expected(getTempTableName(updatedForeignKey.queries[0])),
+ );
+ });
+});
+
+/** @param {string} query */
+function getTempTableName(query) {
+ return /User_[a-z\d]+/.exec(query)?.[0];
+}
diff --git a/packages/db/test/unit/remote-info.test.js b/packages/db/test/unit/remote-info.test.js
new file mode 100644
index 000000000..2c58f28b7
--- /dev/null
+++ b/packages/db/test/unit/remote-info.test.js
@@ -0,0 +1,119 @@
+import assert from 'node:assert';
+import test, { after, beforeEach, describe } from 'node:test';
+import { getManagedRemoteToken, getRemoteDatabaseInfo } from '../../dist/core/utils.js';
+import { clearEnvironment } from '../test-utils.js';
+
+describe('RemoteDatabaseInfo', () => {
+ beforeEach(() => {
+ clearEnvironment();
+ });
+
+ test('default remote info', () => {
+ const dbInfo = getRemoteDatabaseInfo();
+
+ assert.deepEqual(dbInfo, {
+ type: 'studio',
+ url: 'https://db.services.astro.build',
+ });
+ });
+
+ test('configured Astro Studio remote', () => {
+ process.env.ASTRO_STUDIO_REMOTE_DB_URL = 'https://studio.astro.build';
+ const dbInfo = getRemoteDatabaseInfo();
+
+ assert.deepEqual(dbInfo, {
+ type: 'studio',
+ url: 'https://studio.astro.build',
+ });
+ });
+
+ test('configured libSQL remote', () => {
+ process.env.ASTRO_DB_REMOTE_URL = 'libsql://libsql.self.hosted';
+ const dbInfo = getRemoteDatabaseInfo();
+
+ assert.deepEqual(dbInfo, {
+ type: 'libsql',
+ url: 'libsql://libsql.self.hosted',
+ });
+ });
+
+ test('configured both libSQL and Studio remote', () => {
+ process.env.ASTRO_DB_REMOTE_URL = 'libsql://libsql.self.hosted';
+ process.env.ASTRO_STUDIO_REMOTE_DB_URL = 'https://studio.astro.build';
+ const dbInfo = getRemoteDatabaseInfo();
+
+ assert.deepEqual(dbInfo, {
+ type: 'studio',
+ url: 'https://studio.astro.build',
+ });
+ });
+});
+
+describe('RemoteManagedToken', () => {
+ // Avoid conflicts with other tests
+ beforeEach(() => {
+ clearEnvironment();
+ process.env.ASTRO_STUDIO_APP_TOKEN = 'studio token';
+ process.env.ASTRO_DB_APP_TOKEN = 'db token';
+ });
+ after(() => {
+ clearEnvironment();
+ });
+
+ test('given token for default remote', async () => {
+ const { token } = await getManagedRemoteToken('given token');
+ assert.equal(token, 'given token');
+ });
+
+ test('token for default remote', async () => {
+ const { token } = await getManagedRemoteToken();
+
+ assert.equal(token, 'studio token');
+ });
+
+ test('given token for configured Astro Studio remote', async () => {
+ process.env.ASTRO_STUDIO_REMOTE_DB_URL = 'https://studio.astro.build';
+ const { token } = await getManagedRemoteToken('given token');
+ assert.equal(token, 'given token');
+ });
+
+ test('token for configured Astro Studio remote', async () => {
+ process.env.ASTRO_STUDIO_REMOTE_DB_URL = 'https://studio.astro.build';
+ const { token } = await getManagedRemoteToken();
+
+ assert.equal(token, 'studio token');
+ });
+
+ test('given token for configured libSQL remote', async () => {
+ process.env.ASTRO_DB_REMOTE_URL = 'libsql://libsql.self.hosted';
+ const { token } = await getManagedRemoteToken('given token');
+ assert.equal(token, 'given token');
+ });
+
+ test('token for configured libSQL remote', async () => {
+ process.env.ASTRO_DB_REMOTE_URL = 'libsql://libsql.self.hosted';
+ const { token } = await getManagedRemoteToken();
+
+ assert.equal(token, 'db token');
+ });
+
+ test('token for given Astro Studio remote', async () => {
+ process.env.ASTRO_DB_REMOTE_URL = 'libsql://libsql.self.hosted';
+ const { token } = await getManagedRemoteToken(undefined, {
+ type: 'studio',
+ url: 'https://studio.astro.build',
+ });
+
+ assert.equal(token, 'studio token');
+ });
+
+ test('token for given libSQL remote', async () => {
+ process.env.ASTRO_STUDIO_REMOTE_URL = 'libsql://libsql.self.hosted';
+ const { token } = await getManagedRemoteToken(undefined, {
+ type: 'libsql',
+ url: 'libsql://libsql.self.hosted',
+ });
+
+ assert.equal(token, 'db token');
+ });
+});
diff --git a/packages/db/test/unit/reset-queries.test.js b/packages/db/test/unit/reset-queries.test.js
new file mode 100644
index 000000000..9fb99f91e
--- /dev/null
+++ b/packages/db/test/unit/reset-queries.test.js
@@ -0,0 +1,54 @@
+import assert from 'node:assert/strict';
+import { describe, it } from 'node:test';
+import { getMigrationQueries } from '../../dist/core/cli/migration-queries.js';
+import { MIGRATION_VERSION } from '../../dist/core/consts.js';
+import { tableSchema } from '../../dist/core/schemas.js';
+import { column, defineTable } from '../../dist/runtime/virtual.js';
+
+const TABLE_NAME = 'Users';
+
+// `parse` to resolve schema transformations
+// ex. convert column.date() to ISO strings
+const userInitial = tableSchema.parse(
+ defineTable({
+ columns: {
+ name: column.text(),
+ age: column.number(),
+ email: column.text({ unique: true }),
+ mi: column.text({ optional: true }),
+ },
+ }),
+);
+
+describe('force reset', () => {
+ describe('getMigrationQueries', () => {
+ it('should drop table and create new version', async () => {
+ const oldTables = { [TABLE_NAME]: userInitial };
+ const newTables = { [TABLE_NAME]: userInitial };
+ const { queries } = await getMigrationQueries({
+ oldSnapshot: { schema: oldTables, version: MIGRATION_VERSION },
+ newSnapshot: { schema: newTables, version: MIGRATION_VERSION },
+ reset: true,
+ });
+
+ assert.deepEqual(queries, [
+ `DROP TABLE IF EXISTS "${TABLE_NAME}"`,
+ `CREATE TABLE "${TABLE_NAME}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL, "email" text NOT NULL UNIQUE, "mi" text)`,
+ ]);
+ });
+
+ it('should not drop table when previous snapshot did not have it', async () => {
+ const oldTables = {};
+ const newTables = { [TABLE_NAME]: userInitial };
+ const { queries } = await getMigrationQueries({
+ oldSnapshot: { schema: oldTables, version: MIGRATION_VERSION },
+ newSnapshot: { schema: newTables, version: MIGRATION_VERSION },
+ reset: true,
+ });
+
+ assert.deepEqual(queries, [
+ `CREATE TABLE "${TABLE_NAME}" (_id INTEGER PRIMARY KEY, "name" text NOT NULL, "age" integer NOT NULL, "email" text NOT NULL UNIQUE, "mi" text)`,
+ ]);
+ });
+ });
+});
generated by cgit v1.2.3 (git 2.46.0) at 2025-09-29 23:18:14 +0000