import { describe, it, expect } from "vitest"; import { DatabaseSync } from "node:sqlite"; import { z } from "zod"; import { datasourceDropSQL, datasourceToSQL, generateMigration, datasourceExamplestoSQL, datasourceItemsToSQL, } from "../to-sql.js"; import type { Datasource } from "../types.js"; import { defineDatasource } from "../utils.js"; /** * NOTE: All schemas in these tests must be Zod schemas, not JSON Schema objects. * * Conversion guide: * - `z.string()` = required string * - `z.string().optional()` = optional string * - `z.number()` = required number (REAL in SQL) * - `z.bigint()` = required bigint (INTEGER in SQL) * - `z.boolean()` = required boolean * - `z.string().default("value")` = string with default * - `z.array(z.string())` = array type * - `z.object({...})` = object type */ describe("datasourceToSQL", () => { describe("basic table creation", () => { it("should create a simple table with system columns", () => { const datasource: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z.object({ name: z.string(), age: z.number().optional(), }), indexes: [], }); const sql = datasourceToSQL(datasource); console.log("sql: ", sql); expect(sql).toContain('CREATE TABLE IF NOT EXISTS "users"'); expect(sql).toContain('"$id" TEXT NOT NULL PRIMARY KEY'); expect(sql).toContain( "\"$creationDate\" DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f', 'now') || 'Z')", ); expect(sql).toContain( "\"$lastModificationDate\" DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f', 'now') || 'Z')", ); expect(sql).toContain('"name" TEXT NOT NULL'); expect(sql).toContain('"age" REAL'); }); it("should create a table with only system columns when schema is empty", () => { const datasource: Datasource = defineDatasource({ id: "empty_table", label: "Empty Table", provider: "internal", schema: z.object({}), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('CREATE TABLE IF NOT EXISTS "empty_table"'); expect(sql).toContain('"$id" TEXT NOT NULL PRIMARY KEY'); }); }); describe("drop before create (schema-drift safety)", () => { it("emits DROP TABLE IF EXISTS before CREATE TABLE", () => { const datasource: Datasource = defineDatasource({ id: "gallery_images", label: "Gallery Images", provider: "internal", schema: z.object({ title: z.string(), order: z.number(), }), indexes: [], }); const sql = datasourceToSQL(datasource); const dropIdx = sql.indexOf('DROP TABLE IF EXISTS "gallery_images"'); const createIdx = sql.indexOf('CREATE TABLE IF NOT EXISTS "gallery_images"'); expect(dropIdx).toBeGreaterThanOrEqual(0); expect(createIdx).toBeGreaterThan(dropIdx); }); it("drops FTS5 virtual tables before the base table", () => { const datasource: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", schema: z.object({ title: z.string(), content: z.string(), }), indexes: [{ name: "search_idx", fields: ["title", "content"], fulltext: true }], }); const sql = datasourceToSQL(datasource); const dropFtsIdx = sql.indexOf('DROP TABLE IF EXISTS "articles_search_idx"'); const dropBaseIdx = sql.indexOf('DROP TABLE IF EXISTS "articles"'); const createBaseIdx = sql.indexOf('CREATE TABLE IF NOT EXISTS "articles"'); expect(dropFtsIdx).toBeGreaterThanOrEqual(0); expect(dropBaseIdx).toBeGreaterThan(dropFtsIdx); expect(createBaseIdx).toBeGreaterThan(dropBaseIdx); }); it("recreates a table cleanly when run twice with different schemas", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "items", label: "Items", provider: "internal", schema: z.object({ name: z.string() }), indexes: [], }); db.exec(datasourceToSQL(v1)); db.prepare('INSERT INTO "items" ("$id", "name") VALUES (?, ?)').run("a", "Alice"); const v2: Datasource = defineDatasource({ id: "items", label: "Items", provider: "internal", schema: z.object({ name: z.string(), order: z.number() }), indexes: [], }); db.exec(datasourceToSQL(v2)); // After re-running, the "order" column must exist and the table must be empty. const cols = db.prepare('PRAGMA table_info("items")').all() as { name: string }[]; expect(cols.map((c) => c.name)).toContain("order"); const rows = db.prepare('SELECT COUNT(*) as n FROM "items"').get() as { n: number }; expect(rows.n).toBe(0); }); }); describe("column type mapping", () => { it("should map string type to TEXT", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // stringField: { type: "string" }, // }, // }, schema: z.object({ stringField: z.string(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"stringField" TEXT'); }); it("should map string().datetime() type to DATETIME", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", schema: z.object({ createdAt: z.string().datetime(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"createdAt" DATETIME'); }); it("should map string().datetime().optional() type to DATETIME", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", schema: z.object({ publishedAt: z.string().datetime().optional(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"publishedAt" DATETIME'); expect(sql).not.toContain('"publishedAt" DATETIME NOT NULL'); }); it("should map number type to REAL", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // numberField: { type: "number" }, // }, // }, schema: z.object({ numberField: z.number(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"numberField" REAL'); }); it("should map integer type to INTEGER", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // integerField: { type: "integer" }, // }, // }, schema: z.object({ integerField: z.bigint(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"integerField" INTEGER'); }); it("should map number().int() type to INTEGER", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", schema: z.object({ displayOrder: z.number().int(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"displayOrder" INTEGER'); }); it("should map number().int().optional() type to INTEGER", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", schema: z.object({ order: z.number().int().optional(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"order" INTEGER'); expect(sql).not.toContain('"order" INTEGER NOT NULL'); }); it("should map number().int().default() type to INTEGER with default", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", schema: z.object({ count: z.number().int().default(0), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"count" INTEGER DEFAULT 0'); }); it("should map boolean type to BOOLEAN", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // booleanField: { type: "boolean" }, // }, // }, schema: z.object({ booleanField: z.boolean(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"booleanField" BOOLEAN'); }); it("should map array type to TEXT", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // arrayField: { type: "array" }, // }, // }, schema: z.object({ arrayField: z.array(z.string()), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"arrayField" TEXT'); }); it("should map object type to TEXT", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // objectField: { type: "object" }, // }, // }, schema: z.object({ objectField: z.object({}).passthrough(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"objectField" TEXT'); }); }); describe("NOT NULL constraints and defaults", () => { it("should add NOT NULL for required fields", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // requiredField: { type: "string" }, // optionalField: { type: "string" }, // }, // required: ["requiredField"], // }, schema: z.object({ requiredField: z.string(), optionalField: z.string().optional(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"requiredField" TEXT NOT NULL'); expect(sql).toContain('"optionalField" TEXT'); expect(sql).not.toContain('"optionalField" TEXT NOT NULL'); }); it("should handle string default values", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // status: { type: "string", default: "pending" }, // }, // }, schema: z.object({ status: z.string().default("pending"), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain("\"status\" TEXT DEFAULT 'pending'"); }); it("should handle boolean default values", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // active: { type: "boolean", default: true }, // inactive: { type: "boolean", default: false }, // }, // }, schema: z.object({ active: z.boolean().default(true), inactive: z.boolean().default(false), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"active" BOOLEAN DEFAULT TRUE'); expect(sql).toContain('"inactive" BOOLEAN DEFAULT FALSE'); }); it("should handle number default values", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // count: { type: "integer", default: 0 }, // price: { type: "number", default: 9.99 }, // }, // }, schema: z.object({ count: z.bigint().default(BigInt(0)), price: z.number().default(9.99), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"count" INTEGER DEFAULT 0'); expect(sql).toContain('"price" REAL DEFAULT 9.99'); }); it("should handle null default values", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // nullable: { type: "string", default: null }, // }, // }, schema: z.object({ nullable: z.string().nullable().default(null), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"nullable" TEXT DEFAULT NULL'); }); it("should escape single quotes in string default values", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // message: { type: "string", default: "It's working" }, // }, // }, schema: z.object({ message: z.string().default("It's working"), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain("\"message\" TEXT DEFAULT 'It''s working'"); }); }); describe("indexes", () => { it("should create a regular index for a single field", () => { const datasource: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", // schema: { // type: "object", // properties: { // email: { type: "string" }, // }, // }, schema: z.object({ email: z.string(), }), indexes: [{ name: "email_idx", fields: ["email"] }], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('CREATE INDEX IF NOT EXISTS "email_idx" ON "users" ("email")'); }); it("should create a compound index for multiple fields", () => { const datasource: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", // schema: { // type: "object", // properties: { // authorId: { type: "string" }, // publishedAt: { type: "string" }, // }, // }, schema: z.object({ authorId: z.string(), publishedAt: z.string(), }), indexes: [ { name: "author_published_idx", fields: ["authorId", "publishedAt"], }, ], }); const sql = datasourceToSQL(datasource); expect(sql).toContain( 'CREATE INDEX IF NOT EXISTS "author_published_idx" ON "posts" ("authorId", "publishedAt")', ); }); it("should create a unique index", () => { const datasource: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", // schema: { // type: "object", // properties: { // sku: { type: "string" }, // }, // }, schema: z.object({ sku: z.string(), }), indexes: [{ name: "sku_unique", fields: ["sku"], unique: true }], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('CREATE UNIQUE INDEX IF NOT EXISTS "sku_unique" ON "products" ("sku")'); }); it("should create FTS5 virtual table for fulltext indexes", () => { const datasource: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // content: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), content: z.string(), }), indexes: [ { name: "search_idx", fields: ["title", "content"], fulltext: true, }, ], }); const sql = datasourceToSQL(datasource); expect(sql).toContain( 'CREATE VIRTUAL TABLE IF NOT EXISTS "articles_search_idx" USING fts5("title", "content", content="articles")', ); }); it("should create multiple indexes", () => { const datasource: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", // schema: { // type: "object", // properties: { // sku: { type: "string" }, // category: { type: "string" }, // name: { type: "string" }, // }, // }, schema: z.object({ sku: z.string(), category: z.string(), name: z.string(), }), indexes: [ { name: "sku_unique", fields: ["sku"], unique: true }, { name: "category_idx", fields: ["category"] }, { name: "name_search", fields: ["name"], fulltext: true }, ], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('CREATE UNIQUE INDEX IF NOT EXISTS "sku_unique" ON "products" ("sku")'); expect(sql).toContain('CREATE INDEX IF NOT EXISTS "category_idx" ON "products" ("category")'); expect(sql).toContain( 'CREATE VIRTUAL TABLE IF NOT EXISTS "products_name_search" USING fts5("name", content="products")', ); }); }); describe("foreign keys", () => { it("should create a foreign key constraint", () => { const datasource: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", schema: z.object({ authorId: z.string(), }), indexes: [], foreignKeys: [ { field: "authorId", foreignDatasourceId: "authors", foreignField: "$id", }, ], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('FOREIGN KEY ("authorId") REFERENCES "authors"("$id")'); }); it("should create multiple foreign key constraints", () => { const datasource: Datasource = defineDatasource({ id: "comments", label: "Comments", provider: "internal", // schema: { // type: "object", // properties: { // postId: { type: "string" }, // authorId: { type: "string" }, // }, // }, schema: z.object({ postId: z.string(), authorId: z.string(), }), indexes: [], foreignKeys: [ { field: "postId", foreignDatasourceId: "posts", foreignField: "$id", }, { field: "authorId", foreignDatasourceId: "users", foreignField: "$id", }, ], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('FOREIGN KEY ("postId") REFERENCES "posts"("$id")'); expect(sql).toContain('FOREIGN KEY ("authorId") REFERENCES "users"("$id")'); }); }); describe("edge cases and escaping", () => { it("should properly escape table names with special characters", () => { const datasource: Datasource = defineDatasource({ id: "my_table", label: "My Table", provider: "internal", // schema: { // type: "object", // properties: { // field: { type: "string" }, // }, // }, schema: z.object({ field: z.string(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('CREATE TABLE IF NOT EXISTS "my_table"'); }); it("should properly escape field names with special characters", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // "field-name": { type: "string" }, // }, // }, schema: z.object({ "field-name": z.string(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"field-name" TEXT'); }); it("should handle no indexes", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // field: { type: "string" }, // }, // }, schema: z.object({ field: z.string(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('CREATE TABLE IF NOT EXISTS "test"'); expect(sql).toContain("CREATE INDEX"); expect(sql).not.toContain("CREATE VIRTUAL TABLE"); }); it("should handle no foreign keys", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // field: { type: "string" }, // }, // }, schema: z.object({ field: z.string(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).not.toContain("FOREIGN KEY"); }); it("should handle all fields required", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // field1: { type: "string" }, // field2: { type: "number" }, // field3: { type: "boolean" }, // }, // required: ["field1", "field2", "field3"], // }, schema: z.object({ field1: z.string(), field2: z.number(), field3: z.boolean(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"field1" TEXT NOT NULL'); expect(sql).toContain('"field2" REAL NOT NULL'); expect(sql).toContain('"field3" BOOLEAN NOT NULL'); }); it("should handle no fields required", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // field1: { type: "string" }, // field2: { type: "number" }, // }, // }, schema: z.object({ field1: z.string().optional(), field2: z.number().optional(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain('"field1" TEXT'); expect(sql).not.toContain('"field1" TEXT NOT NULL'); expect(sql).toContain('"field2" REAL'); expect(sql).not.toContain('"field2" REAL NOT NULL'); }); }); describe("SQL formatting", () => { it("should format SQL with proper line breaks and indentation", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // field1: { type: "string" }, // field2: { type: "number" }, // }, // }, schema: z.object({ field1: z.string(), field2: z.number(), }), indexes: [], }); const sql = datasourceToSQL(datasource); expect(sql).toContain("CREATE TABLE"); expect(sql).toContain("\n "); expect(sql).toMatch(/,\n {2}/); }); it("should separate statements with semicolons and newlines", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", // schema: { // type: "object", // properties: { // field: { type: "string" }, // }, // }, schema: z.object({ field: z.string(), }), indexes: [{ name: "field_idx", fields: ["field"] }], }); const sql = datasourceToSQL(datasource); expect(sql).toMatch(/;\n\n/); expect(sql.endsWith(";")).toBe(true); }); }); describe("complete example", () => { it("should generate complete SQL for a blog posts datasource", () => { const datasource: Datasource = defineDatasource({ id: "blog_posts", label: "Blog Posts", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // content: { type: "string" }, // excerpt: { type: "string" }, // authorId: { type: "string" }, // featured: { type: "boolean", default: false }, // publishedAt: { type: "string" }, // }, // required: ["title", "content", "authorId"], // }, schema: z.object({ title: z.string(), content: z.string(), excerpt: z.string().optional(), authorId: z.string(), featured: z.boolean().default(false), publishedAt: z.string().optional(), }), indexes: [ { name: "author_idx", fields: ["authorId"] }, { name: "published_idx", fields: ["publishedAt"] }, { name: "content_search", fields: ["title", "content"], fulltext: true, }, ], foreignKeys: [ { field: "authorId", foreignDatasourceId: "authors", foreignField: "$id", }, ], }); const sql = datasourceToSQL(datasource); // Verify CREATE TABLE expect(sql).toContain('CREATE TABLE IF NOT EXISTS "blog_posts"'); expect(sql).toContain('"$id" TEXT NOT NULL PRIMARY KEY'); expect(sql).toContain('"title" TEXT NOT NULL'); expect(sql).toContain('"content" TEXT NOT NULL'); expect(sql).toContain('"authorId" TEXT NOT NULL'); expect(sql).toContain('"featured" BOOLEAN DEFAULT FALSE'); // Verify foreign key expect(sql).toContain('FOREIGN KEY ("authorId") REFERENCES "authors"("$id")'); // Verify indexes expect(sql).toContain('CREATE INDEX IF NOT EXISTS "author_idx" ON "blog_posts" ("authorId")'); expect(sql).toContain('CREATE INDEX IF NOT EXISTS "published_idx" ON "blog_posts" ("publishedAt")'); expect(sql).toContain( 'CREATE VIRTUAL TABLE IF NOT EXISTS "blog_posts_content_search" USING fts5("title", "content", content="blog_posts")', ); }); }); describe("SQLite integration tests", () => { it("should create table and insert data with basic types", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", // schema: { // type: "object", // properties: { // name: { type: "string" }, // age: { type: "integer" }, // rating: { type: "number" }, // active: { type: "boolean" }, // }, // required: ["name"], // }, schema: z.object({ name: z.string(), age: z.bigint().optional(), rating: z.number().optional(), active: z.boolean().optional(), }), indexes: [], }); const sql = datasourceToSQL(datasource); db.exec(sql); // Insert test data db.exec(` INSERT INTO "users" ("$id", "name", "age", "rating", "active") VALUES ('1', 'John Doe', 30, 4.5, 1) `); // Verify data const row = db.prepare('SELECT * FROM "users" WHERE "$id" = ?').get("1") as Record; expect(row).toBeDefined(); expect(row.$id).toBe("1"); expect(row.name).toBe("John Doe"); expect(row.age).toBe(30); expect(row.rating).toBe(4.5); expect(row.active).toBe(1); // SQLite stores boolean as integer }); it("should enforce PRIMARY KEY constraint on $id", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "items", label: "Items", provider: "internal", // schema: { // type: "object", // properties: { // name: { type: "string" }, // }, // }, schema: z.object({ name: z.string(), }), indexes: [], }); db.exec(datasourceToSQL(datasource)); // Insert first row db.exec('INSERT INTO "items" ("$id", "name") VALUES (\'1\', \'Item 1\')'); // Try to insert duplicate $id (should fail) expect(() => { db.exec('INSERT INTO "items" ("$id", "name") VALUES (\'1\', \'Item 2\')'); }).toThrow(); }); it("should enforce NOT NULL constraints", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", // schema: { // type: "object", // properties: { // name: { type: "string" }, // description: { type: "string" }, // }, // required: ["name"], // }, schema: z.object({ name: z.string(), description: z.string().optional(), }), indexes: [], }); db.exec(datasourceToSQL(datasource)); // Insert with required field (should succeed) db.exec('INSERT INTO "products" ("$id", "name") VALUES (\'1\', \'Product 1\')'); // Try to insert without required field (should fail) expect(() => { db.exec('INSERT INTO "products" ("$id", "description") VALUES (\'2\', \'Desc\')'); }).toThrow(); }); it("should apply default values correctly", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "settings", label: "Settings", provider: "internal", // schema: { // type: "object", // properties: { // key: { type: "string" }, // enabled: { type: "boolean", default: true }, // count: { type: "integer", default: 0 }, // status: { type: "string", default: "pending" }, // }, // required: ["key"], // }, schema: z.object({ key: z.string(), enabled: z.boolean().default(true), count: z.bigint().default(BigInt(0)), status: z.string().default("pending"), }), indexes: [], }); db.exec(datasourceToSQL(datasource)); // Insert without providing default values db.exec('INSERT INTO "settings" ("$id", "key") VALUES (\'1\', \'theme\')'); // Verify defaults were applied const row = db.prepare('SELECT * FROM "settings" WHERE "$id" = ?').get("1") as Record; expect(row.enabled).toBe(1); // TRUE = 1 expect(row.count).toBe(0); expect(row.status).toBe("pending"); }); it("should create and use regular indexes", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // authorId: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), authorId: z.string(), }), indexes: [{ name: "author_idx", fields: ["authorId"] }], }); db.exec(datasourceToSQL(datasource)); // Insert test data db.exec('INSERT INTO "posts" ("$id", "title", "authorId") VALUES (\'1\', \'Post 1\', \'a1\')'); db.exec('INSERT INTO "posts" ("$id", "title", "authorId") VALUES (\'2\', \'Post 2\', \'a1\')'); // Query using index (should work) const rows = db.prepare('SELECT * FROM "posts" WHERE "authorId" = ?').all("a1") as Record< string, unknown >[]; expect(rows).toHaveLength(2); }); it("should enforce unique indexes", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", // schema: { // type: "object", // properties: { // sku: { type: "string" }, // name: { type: "string" }, // }, // }, schema: z.object({ sku: z.string(), name: z.string(), }), indexes: [{ name: "sku_unique", fields: ["sku"], unique: true }], }); db.exec(datasourceToSQL(datasource)); // Insert first row db.exec('INSERT INTO "products" ("$id", "sku", "name") VALUES (\'1\', \'SKU123\', \'Product 1\')'); // Try to insert duplicate sku (should fail) expect(() => { db.exec('INSERT INTO "products" ("$id", "sku", "name") VALUES (\'2\', \'SKU123\', \'Product 2\')'); }).toThrow(); }); it("should create FTS5 fulltext search indexes", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // content: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), content: z.string(), }), indexes: [{ name: "search_idx", fields: ["title", "content"], fulltext: true }], }); db.exec(datasourceToSQL(datasource)); // Insert test data db.exec(` INSERT INTO "articles" ("$id", "title", "content") VALUES ('1', 'Introduction to SQLite', 'SQLite is a powerful database engine') `); db.exec(` INSERT INTO "articles" ("$id", "title", "content") VALUES ('2', 'Advanced JavaScript', 'Learn about async/await patterns') `); // Populate FTS5 table db.exec( 'INSERT INTO "articles_search_idx" ("title", "content") SELECT "title", "content" FROM "articles"', ); // Full-text search (should work) const rows = db .prepare('SELECT * FROM "articles_search_idx" WHERE "articles_search_idx" MATCH ?') .all("SQLite") as Record[]; expect(rows).toHaveLength(1); }); it("should handle complex datasource with all features", () => { const db = new DatabaseSync(":memory:"); // Create referenced table first db.exec(` CREATE TABLE "authors" ( "$id" TEXT NOT NULL PRIMARY KEY, "name" TEXT NOT NULL ) `); db.exec('INSERT INTO "authors" ("$id", "name") VALUES (\'a1\', \'John Doe\')'); const datasource: Datasource = defineDatasource({ id: "blog_posts", label: "Blog Posts", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // content: { type: "string" }, // authorId: { type: "string" }, // featured: { type: "boolean", default: false }, // views: { type: "integer", default: 0 }, // }, // required: ["title", "content", "authorId"], // }, schema: z.object({ title: z.string(), content: z.string(), authorId: z.string(), featured: z.boolean().default(false), views: z.bigint().default(BigInt(0)), }), indexes: [ { name: "author_idx", fields: ["authorId"] }, { name: "content_search", fields: ["title", "content"], fulltext: true }, ], foreignKeys: [ { field: "authorId", foreignDatasourceId: "authors", foreignField: "$id", }, ], }); db.exec(datasourceToSQL(datasource)); // Insert test data db.exec(` INSERT INTO "blog_posts" ("$id", "title", "content", "authorId") VALUES ('1', 'First Post', 'This is the content', 'a1') `); // Verify all features work const row = db.prepare('SELECT * FROM "blog_posts" WHERE "$id" = ?').get("1") as Record< string, unknown >; expect(row.title).toBe("First Post"); expect(row.content).toBe("This is the content"); expect(row.authorId).toBe("a1"); expect(row.featured).toBe(0); // FALSE = 0 expect(row.views).toBe(0); // Verify foreign key (SQLite may not enforce without PRAGMA, but constraint exists) // This will fail if foreign keys are enabled db.exec("PRAGMA foreign_keys = ON"); expect(() => { db.exec(` INSERT INTO "blog_posts" ("$id", "title", "content", "authorId") VALUES ('2', 'Second', 'Content', 'nonexistent') `); }).toThrow(); }); it("should handle nullable columns correctly", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "tasks", label: "Tasks", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // description: { type: "string" }, // dueDate: { type: "string" }, // }, // required: ["title"], // }, schema: z.object({ title: z.string(), description: z.string().optional(), dueDate: z.string().optional(), }), indexes: [], }); db.exec(datasourceToSQL(datasource)); // Insert with only required field db.exec('INSERT INTO "tasks" ("$id", "title") VALUES (\'1\', \'Task 1\')'); // Verify nullable fields are NULL const row = db.prepare('SELECT * FROM "tasks" WHERE "$id" = ?').get("1") as Record; expect(row.title).toBe("Task 1"); expect(row.description).toBeNull(); expect(row.dueDate).toBeNull(); }); it("should handle compound indexes", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "events", label: "Events", provider: "internal", // schema: { // type: "object", // properties: { // userId: { type: "string" }, // eventType: { type: "string" }, // timestamp: { type: "string" }, // }, // }, schema: z.object({ userId: z.string(), eventType: z.string(), timestamp: z.string(), }), indexes: [{ name: "user_event_idx", fields: ["userId", "eventType"] }], }); db.exec(datasourceToSQL(datasource)); // Insert test data db.exec(` INSERT INTO "events" ("$id", "userId", "eventType", "timestamp") VALUES ('1', 'u1', 'click', '2024-01-01') `); db.exec(` INSERT INTO "events" ("$id", "userId", "eventType", "timestamp") VALUES ('2', 'u1', 'view', '2024-01-02') `); // Query using compound index const rows = db .prepare('SELECT * FROM "events" WHERE "userId" = ? AND "eventType" = ?') .all("u1", "click") as Record[]; expect(rows).toHaveLength(1); expect(rows[0].eventType).toBe("click"); }); it("should allow running statements multiple times (drop + recreate is idempotent)", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", // schema: { // type: "object", // properties: { // name: { type: "string" }, // sku: { type: "string" }, // }, // required: ["name"], // }, schema: z.object({ name: z.string(), sku: z.string(), }), indexes: [ { name: "sku_idx", fields: ["sku"], unique: true }, { name: "name_search", fields: ["name"], fulltext: true }, ], }); const sql = datasourceToSQL(datasource); // Execute once db.exec(sql); // Insert data db.exec('INSERT INTO "products" ("$id", "name", "sku") VALUES (\'1\', \'Product 1\', \'SKU1\')'); // Execute again - should not throw (drop + recreate is safe). // Data is intentionally wiped — a re-applied create migration drops the // table and the seed step reinserts rows, so dropping existing rows is fine. expect(() => db.exec(sql)).not.toThrow(); const rows = db.prepare('SELECT * FROM "products"').all() as Record[]; expect(rows).toHaveLength(0); }); }); describe("generateMigration", () => { describe("valid migrations", () => { it("should generate migration for adding optional column", () => { const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", // schema: { // type: "object", // properties: { // name: { type: "string" }, // }, // required: ["name"], // }, schema: z.object({ name: z.string(), }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, // schema: { // type: "object", // properties: { // name: { type: "string" }, // email: { type: "string" }, // }, // required: ["name"], // }, schema: z.object({ name: z.string(), email: z.string().optional(), }), }); const migration = generateMigration(v1, v2); expect(migration).toContain('ALTER TABLE "users" ADD COLUMN "email" TEXT'); }); it("should generate migration for adding column with default value", () => { const v1: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", // schema: { // type: "object", // properties: { // name: { type: "string" }, // }, // }, schema: z.object({ name: z.string(), }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, // schema: { // type: "object", // properties: { // name: { type: "string" }, // active: { type: "boolean", default: true }, // }, // }, schema: z.object({ name: z.string(), active: z.boolean().default(true), }), }); const migration = generateMigration(v1, v2); expect(migration).toContain('ALTER TABLE "products" ADD COLUMN "active" BOOLEAN DEFAULT TRUE'); }); it("should generate migration for adding index", () => { const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, indexes: [{ name: "title_idx", fields: ["title"] }], }); const migration = generateMigration(v1, v2); expect(migration).toContain('CREATE INDEX "title_idx" ON "posts" ("title")'); }); it("should generate migration for adding unique index", () => { const v1: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", // schema: { // type: "object", // properties: { // sku: { type: "string" }, // }, // }, schema: z.object({ sku: z.string(), }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, indexes: [{ name: "sku_unique", fields: ["sku"], unique: true }], }); const migration = generateMigration(v1, v2); expect(migration).toContain('CREATE UNIQUE INDEX "sku_unique" ON "products" ("sku")'); }); it("should generate migration for adding fulltext index", () => { const v1: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // content: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), content: z.string(), }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, indexes: [{ name: "search_idx", fields: ["title", "content"], fulltext: true }], }); const migration = generateMigration(v1, v2); expect(migration).toContain( 'CREATE VIRTUAL TABLE "articles_search_idx" USING fts5("title", "content", content="articles")', ); }); it("should generate migration for dropping index", () => { const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), }), indexes: [{ name: "title_idx", fields: ["title"] }], }); const v2: Datasource = defineDatasource({ ...v1, indexes: [], }); const migration = generateMigration(v1, v2); expect(migration).toContain('DROP INDEX "title_idx"'); }); it("should generate migration for dropping fulltext index", () => { const v1: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), }), indexes: [{ name: "search_idx", fields: ["title"], fulltext: true }], }); const v2: Datasource = defineDatasource({ ...v1, indexes: [], }); const migration = generateMigration(v1, v2); expect(migration).toContain('DROP TABLE "articles_search_idx"'); }); it("should generate migration for modifying index", () => { const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // content: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), content: z.string(), }), indexes: [{ name: "search_idx", fields: ["title"] }], }); const v2: Datasource = defineDatasource({ ...v1, indexes: [{ name: "search_idx", fields: ["title", "content"] }], }); const migration = generateMigration(v1, v2); expect(migration).toContain('DROP INDEX "search_idx"'); expect(migration).toContain('CREATE INDEX "search_idx" ON "posts" ("title", "content")'); }); it("should return empty string when no changes", () => { const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", // schema: { // type: "object", // properties: { // name: { type: "string" }, // }, // }, schema: z.object({ name: z.string(), }), indexes: [], }); const migration = generateMigration(v1, v1); expect(migration).toBeNull(); }); }); describe("previously-forbidden changes (now generate smart SQL)", () => { it("should still throw for mismatched table IDs", () => { const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z.object({}), indexes: [], }); const v2: Datasource = defineDatasource({ id: "customers", label: "Customers", provider: "internal", schema: z.object({}), indexes: [], }); expect(() => generateMigration(v1, v2)).toThrow(/Cannot migrate between different tables/); }); it("should generate table recreation for adding a NOT NULL column without default", () => { const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z.object({ name: z.string() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), email: z.string() }), }); const migration = generateMigration(v1, v2); expect(migration).toContain("CREATE TABLE"); expect(migration).toContain('"email" TEXT NOT NULL'); expect(migration).toContain("INSERT INTO"); expect(migration).toContain("DROP TABLE"); expect(migration).toContain("RENAME TO"); }); it("should generate DROP COLUMN migration for removing a column", () => { const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z.object({ name: z.string(), email: z.string() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string() }), }); const migration = generateMigration(v1, v2); expect(migration).toContain('ALTER TABLE "users" DROP COLUMN "email"'); }); it("should drop column's index before dropping the column", () => { const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z.object({ name: z.string(), email: z.string() }), indexes: [{ name: "email_idx", fields: ["email"] }], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string() }), indexes: [], }); const migration = generateMigration(v1, v2) as string; const dropIndexPos = migration.indexOf("DROP INDEX"); const dropColumnPos = migration.indexOf("DROP COLUMN"); expect(dropIndexPos).toBeGreaterThanOrEqual(0); expect(dropColumnPos).toBeGreaterThan(dropIndexPos); }); it("should generate table recreation for changing column type (string → number)", () => { const v1: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", schema: z.object({ price: z.string() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ price: z.number() }), }); const migration = generateMigration(v1, v2); expect(migration).toContain("CREATE TABLE"); expect(migration).toContain('"price" REAL'); // CAST expression for string→number expect(migration).toContain("CAST"); expect(migration).toContain("DROP TABLE"); expect(migration).toContain("RENAME TO"); }); it("should use correct CAST expression for string → integer type change", () => { const v1: Datasource = defineDatasource({ id: "items", label: "Items", provider: "internal", schema: z.object({ qty: z.string() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ qty: z.number().int() }), }); const migration = generateMigration(v1, v2); expect(migration).toContain("GLOB '[0-9]*'"); expect(migration).toContain("GLOB '-[0-9]*'"); expect(migration).toContain("CAST"); expect(migration).toContain("INTEGER"); }); it("should detect string → datetime as a type change requiring recreation", () => { const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", schema: z.object({ publishedAt: z.string().optional() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ publishedAt: z.iso.datetime().optional() }), }); const migration = generateMigration(v1, v2); // string→datetime: both TEXT, so a pass-through cast is used inside recreation expect(migration).toContain("CREATE TABLE"); expect(migration).toContain("RENAME TO"); }); it("should detect number → integer as a type change requiring recreation", () => { const v1: Datasource = defineDatasource({ id: "scores", label: "Scores", provider: "internal", schema: z.object({ value: z.number() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ value: z.number().int() }), }); const migration = generateMigration(v1, v2); expect(migration).toContain("CREATE TABLE"); expect(migration).toContain("CAST"); expect(migration).toContain("INTEGER"); }); it("should generate table recreation with COALESCE for making column required", () => { const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z.object({ name: z.string(), email: z.string().optional() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), email: z.string() }), }); const migration = generateMigration(v1, v2); expect(migration).toContain("CREATE TABLE"); expect(migration).toContain("COALESCE"); expect(migration).toContain('"email" TEXT NOT NULL'); expect(migration).toContain("DROP TABLE"); expect(migration).toContain("RENAME TO"); }); it("should use fallback 0 in COALESCE for type_change to required NOT NULL column", () => { // price changes from optional string → required integer (no default) const v1: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", schema: z.object({ name: z.string(), price: z.string().optional() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), price: z.number().int() }), }); const migration = generateMigration(v1, v2); // The target column is NOT NULL with no default: COALESCE uses generic fallback 0 expect(migration).toContain("COALESCE("); expect(migration).toContain(", 0)"); expect(migration).toContain('"price" INTEGER NOT NULL'); }); it("should omit COALESCE when type_change target column has a declared default (column is optional)", () => { // price changes from optional string → integer with a default (column stays optional in SQL) const v1: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", schema: z.object({ name: z.string(), price: z.string().optional() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), price: z.number().int().default(42) }), }); const migration = generateMigration(v1, v2); // Column has .default(42) → isOptional=true → no NOT NULL → no COALESCE needed // NULLs in the cast result are fine, and the DB-level DEFAULT 42 handles new rows expect(migration).not.toContain("COALESCE("); expect(migration).toContain('"price" INTEGER DEFAULT 42'); expect(migration).toContain("CAST"); }); it("should generate table recreation for removing a foreign key", () => { const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", schema: z.object({ authorId: z.string() }), indexes: [], foreignKeys: [{ field: "authorId", foreignDatasourceId: "users", foreignField: "$id" }], }); const v2: Datasource = defineDatasource({ ...v1, foreignKeys: [], }); const migration = generateMigration(v1, v2); expect(migration).toContain("CREATE TABLE"); // New table should NOT contain the FK constraint expect(migration).not.toContain('FOREIGN KEY ("authorId")'); expect(migration).toContain("DROP TABLE"); expect(migration).toContain("RENAME TO"); }); it("should generate table recreation for adding a foreign key", () => { const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", schema: z.object({ authorId: z.string() }), indexes: [], foreignKeys: [], }); const v2: Datasource = defineDatasource({ ...v1, foreignKeys: [{ field: "authorId", foreignDatasourceId: "users", foreignField: "$id" }], }); const migration = generateMigration(v1, v2); expect(migration).toContain("CREATE TABLE"); // New table SHOULD contain the FK constraint expect(migration).toContain('FOREIGN KEY ("authorId")'); expect(migration).toContain('REFERENCES "users"'); expect(migration).toContain("DROP TABLE"); expect(migration).toContain("RENAME TO"); }); }); describe("SQLite integration tests", () => { it("should execute ADD COLUMN migration successfully", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z.object({ name: z.string(), }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), email: z.string().optional(), }), }); // Create table with v1 const createSQL = datasourceToSQL(v1); db.exec(createSQL); // Insert test data db.exec('INSERT INTO "users" ("$id", "name") VALUES (\'1\', \'John Doe\')'); // Generate and execute migration const migrationSQL = generateMigration(v1, v2) as string; db.exec(migrationSQL); // Verify data is intact and new column exists const row = db.prepare('SELECT * FROM "users" WHERE "$id" = ?').get("1") as Record; expect(row).toHaveProperty("email"); expect(row.email).toBeNull(); // Should be NULL for existing rows expect(row.name).toBe("John Doe"); }); it("should execute ADD COLUMN with DEFAULT migration successfully", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", // schema: { // type: "object", // properties: { // name: { type: "string" }, // }, // }, schema: z.object({ name: z.string(), }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, // schema: { // type: "object", // properties: { // name: { type: "string" }, // active: { type: "boolean", default: true }, // }, // }, schema: z.object({ name: z.string(), active: z.boolean().default(true), }), }); // Create and populate table db.exec(datasourceToSQL(v1)); db.exec('INSERT INTO "products" ("$id", "name") VALUES (\'1\', \'Product 1\')'); // Execute migration db.exec(generateMigration(v1, v2) as string); // Verify default value was applied const row = db.prepare('SELECT * FROM "products" WHERE "$id" = ?').get("1") as Record< string, unknown >; expect(row.active).toBe(1); // SQLite stores TRUE as 1 }); it("should execute DROP INDEX migration successfully", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), }), indexes: [{ name: "title_idx", fields: ["title"] }], }); const v2: Datasource = defineDatasource({ ...v1, indexes: [], }); // Create table with index db.exec(datasourceToSQL(v1)); db.exec('INSERT INTO "posts" ("$id", "title") VALUES (\'1\', \'Test Post\')'); // Execute migration to drop index db.exec(generateMigration(v1, v2) as string); // Verify data is intact const row = db.prepare('SELECT * FROM "posts" WHERE "$id" = ?').get("1") as Record; expect(row.title).toBe("Test Post"); }); it("should execute ADD INDEX migration successfully", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // }, // }, schema: z.object({ title: z.string(), }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, indexes: [{ name: "title_idx", fields: ["title"] }], }); // Create table without index db.exec(datasourceToSQL(v1)); db.exec('INSERT INTO "posts" ("$id", "title") VALUES (\'1\', \'Test Post\')'); // Execute migration to add index db.exec(generateMigration(v1, v2) as string); // Verify data is intact const row = db.prepare('SELECT * FROM "posts" WHERE "$id" = ?').get("1") as Record; expect(row.title).toBe("Test Post"); }); it("should handle complex migration with multiple changes", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // }, // required: ["title"], // }, schema: z.object({ title: z.string(), }), indexes: [{ name: "old_idx", fields: ["title"] }], }); const v2: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", // schema: { // type: "object", // properties: { // title: { type: "string" }, // content: { type: "string" }, // published: { type: "boolean", default: false }, // }, // required: ["title"], // }, schema: z.object({ title: z.string(), content: z.string().optional(), published: z.boolean().default(false), }), indexes: [ { name: "title_idx", fields: ["title"] }, { name: "search_idx", fields: ["title", "content"], fulltext: true }, ], }); // Create table with v1 db.exec(datasourceToSQL(v1)); db.exec('INSERT INTO "articles" ("$id", "title") VALUES (\'1\', \'Test Article\')'); // Execute complex migration db.exec(generateMigration(v1, v2) as string); // Verify all changes were applied correctly const row = db.prepare('SELECT * FROM "articles" WHERE "$id" = ?').get("1") as Record< string, unknown >; expect(row.title).toBe("Test Article"); expect(row).toHaveProperty("content"); expect(row).toHaveProperty("published"); expect(row.published).toBe(0); // FALSE = 0 }); it("should migrate column type change (string → integer) preserving data", () => { const v1: Datasource = defineDatasource({ id: "items", label: "Items", provider: "internal", schema: z.object({ name: z.string(), qty: z.string() }), indexes: [], }); // Nullable target: non-parseable values become NULL const v2nullable: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), qty: z.number().int().optional() }), }); // Non-nullable target: non-parseable values get fallback (0) const v2required: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), qty: z.number().int() }), }); // Test nullable case { const db2 = new DatabaseSync(":memory:"); db2.exec(datasourceToSQL(v1)); db2.exec(`INSERT INTO "items" ("$id", "name", "qty") VALUES ('1', 'Widget', '42')`); db2.exec(`INSERT INTO "items" ("$id", "name", "qty") VALUES ('2', 'Gadget', 'not-a-number')`); db2.exec(generateMigration(v1, v2nullable) as string); const row1 = db2.prepare('SELECT * FROM "items" WHERE "$id" = ?').get("1") as Record< string, unknown >; const row2 = db2.prepare('SELECT * FROM "items" WHERE "$id" = ?').get("2") as Record< string, unknown >; expect(row1.qty).toBe(42); // parseable string → integer expect(row2.qty).toBeNull(); // non-parseable → NULL (column is nullable) expect(row1.name).toBe("Widget"); } // Test required case { const db3 = new DatabaseSync(":memory:"); db3.exec(datasourceToSQL(v1)); db3.exec(`INSERT INTO "items" ("$id", "name", "qty") VALUES ('1', 'Widget', '42')`); db3.exec(`INSERT INTO "items" ("$id", "name", "qty") VALUES ('2', 'Gadget', 'not-a-number')`); db3.exec(generateMigration(v1, v2required) as string); const row1 = db3.prepare('SELECT * FROM "items" WHERE "$id" = ?').get("1") as Record< string, unknown >; const row2 = db3.prepare('SELECT * FROM "items" WHERE "$id" = ?').get("2") as Record< string, unknown >; expect(row1.qty).toBe(42); // parseable string → integer expect(row2.qty).toBe(0); // non-parseable → fallback 0 (column is NOT NULL) } // Test GLOB edge-case values (standalone "-", "." should not become 0) { const db4 = new DatabaseSync(":memory:"); const v1edge: Datasource = defineDatasource({ id: "edge", label: "Edge", provider: "internal", schema: z.object({ val: z.string() }), indexes: [], }); const v2intEdge: Datasource = defineDatasource({ ...v1edge, schema: z.object({ val: z.number().int().optional() }), }); const v2floatEdge: Datasource = defineDatasource({ ...v1edge, schema: z.object({ val: z.number().optional() }), }); // Integer edge cases db4.exec(datasourceToSQL(v1edge)); db4.exec(`INSERT INTO "edge" ("$id", "val") VALUES ('dash', '-')`); db4.exec(`INSERT INTO "edge" ("$id", "val") VALUES ('neg', '-7')`); db4.exec(generateMigration(v1edge, v2intEdge) as string); const dashRow = db4.prepare('SELECT * FROM "edge" WHERE "$id" = ?').get("dash") as Record< string, unknown >; const negRow = db4.prepare('SELECT * FROM "edge" WHERE "$id" = ?').get("neg") as Record< string, unknown >; expect(dashRow.val).toBeNull(); // "-" alone → NULL (not 0) expect(negRow.val).toBe(-7); // "-7" → valid negative integer // Float edge cases const db5 = new DatabaseSync(":memory:"); const v1float: Datasource = defineDatasource({ id: "floatedge", label: "FloatEdge", provider: "internal", schema: z.object({ val: z.string() }), indexes: [], }); db5.exec(datasourceToSQL(v1float)); db5.exec(`INSERT INTO "floatedge" ("$id", "val") VALUES ('dot', '.')`); db5.exec(`INSERT INTO "floatedge" ("$id", "val") VALUES ('dotdash', '-.')`); db5.exec(`INSERT INTO "floatedge" ("$id", "val") VALUES ('valid', '3.14')`); db5.exec(`INSERT INTO "floatedge" ("$id", "val") VALUES ('validneg', '-2.5')`); const v2floatEdge2: Datasource = defineDatasource({ id: "floatedge", label: "FloatEdge", provider: "internal", schema: z.object({ val: z.number().optional() }), indexes: [], }); db5.exec(generateMigration(v1float, v2floatEdge2) as string); const dotRow = db5.prepare('SELECT * FROM "floatedge" WHERE "$id" = ?').get("dot") as Record< string, unknown >; const dotDashRow = db5 .prepare('SELECT * FROM "floatedge" WHERE "$id" = ?') .get("dotdash") as Record; const validRow = db5.prepare('SELECT * FROM "floatedge" WHERE "$id" = ?').get("valid") as Record< string, unknown >; const validNegRow = db5 .prepare('SELECT * FROM "floatedge" WHERE "$id" = ?') .get("validneg") as Record; expect(dotRow.val).toBeNull(); // "." alone → NULL expect(dotDashRow.val).toBeNull(); // "-." → NULL expect(validRow.val).toBeCloseTo(3.14); expect(validNegRow.val).toBeCloseTo(-2.5); } }); it("should fill NULLs with fallback when making a column required", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "scores", label: "Scores", provider: "internal", schema: z.object({ player: z.string(), score: z.number().optional() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ player: z.string(), score: z.number() }), }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "scores" ("$id", "player", "score") VALUES ('1', 'Alice', 99.5)`); db.exec(`INSERT INTO "scores" ("$id", "player") VALUES ('2', 'Bob')`); // score is NULL db.exec(generateMigration(v1, v2) as string); const row1 = db.prepare('SELECT * FROM "scores" WHERE "$id" = ?').get("1") as Record; const row2 = db.prepare('SELECT * FROM "scores" WHERE "$id" = ?').get("2") as Record; expect(row1.score).toBe(99.5); // original value preserved expect(row2.score).toBe(0.0); // NULL filled with fallback }); it("should remove column and preserve remaining data", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z.object({ name: z.string(), email: z.string(), phone: z.string().optional() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), phone: z.string().optional() }), }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "users" ("$id", "name", "email") VALUES ('1', 'Alice', 'alice@example.com')`); db.exec(generateMigration(v1, v2) as string); // Verify email column is gone const tableInfo = db.prepare('PRAGMA table_info("users")').all() as { name: string }[]; const colNames = tableInfo.map((c) => c.name); expect(colNames).not.toContain("email"); expect(colNames).toContain("name"); // Verify data is preserved const row = db.prepare('SELECT * FROM "users" WHERE "$id" = ?').get("1") as Record; expect(row.name).toBe("Alice"); }); it("should rebuild FTS5 index after table recreation so it is queryable", () => { const db = new DatabaseSync(":memory:"); // The FTS5 virtual table name = datasource.id + "_" + index.name = "articles_title_fts" // Note: FTS5 content tables with content= do NOT auto-index base table inserts; // they must be rebuilt explicitly. Our migration emits the rebuild statement. const v1: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", schema: z.object({ title: z.string(), views: z.string() }), indexes: [{ name: "title_fts", fields: ["title"], fulltext: true, unique: false }], }); // Change views: string → integer (triggers recreation) const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ title: z.string(), views: z.number().int().optional() }), }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "articles" ("$id", "title", "views") VALUES ('1', 'Hello World', '42')`); db.exec(`INSERT INTO "articles" ("$id", "title", "views") VALUES ('2', 'Goodbye Cruel World', '7')`); // The migration must execute without error and include the FTS5 rebuild statement const migration = generateMigration(v1, v2) as string; expect(migration).toContain( `INSERT INTO "articles_title_fts"("articles_title_fts") VALUES ('rebuild')`, ); db.exec(migration); // After migration + rebuild: FTS5 should be queryable with the migrated data const helloRows = db .prepare(`SELECT rowid FROM "articles_title_fts" WHERE "articles_title_fts" MATCH 'Hello'`) .all() as { rowid: number }[]; expect(helloRows).toHaveLength(1); const worldRows = db .prepare(`SELECT rowid FROM "articles_title_fts" WHERE "articles_title_fts" MATCH 'World'`) .all() as { rowid: number }[]; expect(worldRows).toHaveLength(2); }); it("should remove foreign key constraint so previously-invalid inserts succeed", () => { const db = new DatabaseSync(":memory:"); db.exec("PRAGMA foreign_keys = ON"); db.exec(` CREATE TABLE "authors" ( "$id" TEXT NOT NULL PRIMARY KEY, "name" TEXT NOT NULL ) `); db.exec(`INSERT INTO "authors" ("$id", "name") VALUES ('a1', 'Alice')`); const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", schema: z.object({ title: z.string(), authorId: z.string() }), indexes: [], foreignKeys: [{ field: "authorId", foreignDatasourceId: "authors", foreignField: "$id" }], }); const v2: Datasource = defineDatasource({ ...v1, foreignKeys: [], }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "posts" ("$id", "title", "authorId") VALUES ('1', 'Post 1', 'a1')`); // FK violation before migration expect(() => { db.exec(`INSERT INTO "posts" ("$id", "title", "authorId") VALUES ('2', 'Post 2', 'nonexistent')`); }).toThrow(); db.exec(generateMigration(v1, v2) as string); // After FK removal, same insert should succeed expect(() => { db.exec(`INSERT INTO "posts" ("$id", "title", "authorId") VALUES ('3', 'Post 3', 'nonexistent')`); }).not.toThrow(); // Original data preserved const row = db.prepare('SELECT * FROM "posts" WHERE "$id" = ?').get("1") as Record; expect(row.title).toBe("Post 1"); }); it("should add foreign key constraint so new violating inserts fail after migration", () => { const db = new DatabaseSync(":memory:"); db.exec("PRAGMA foreign_keys = ON"); db.exec(` CREATE TABLE "authors" ( "$id" TEXT NOT NULL PRIMARY KEY, "name" TEXT NOT NULL ) `); db.exec(`INSERT INTO "authors" ("$id", "name") VALUES ('a1', 'Alice')`); const v1: Datasource = defineDatasource({ id: "posts", label: "Posts", provider: "internal", schema: z.object({ title: z.string(), authorId: z.string() }), indexes: [], foreignKeys: [], }); const v2: Datasource = defineDatasource({ ...v1, foreignKeys: [{ field: "authorId", foreignDatasourceId: "authors", foreignField: "$id" }], }); db.exec(datasourceToSQL(v1)); // Only insert rows with valid authorIds so the migration data copy succeeds db.exec(`INSERT INTO "posts" ("$id", "title", "authorId") VALUES ('1', 'Post 1', 'a1')`); db.exec(generateMigration(v1, v2) as string); // After FK addition: inserting a non-existent authorId should fail expect(() => { db.exec(`INSERT INTO "posts" ("$id", "title", "authorId") VALUES ('3', 'Post 3', 'ghost')`); }).toThrow(); // Valid authorId still works expect(() => { db.exec(`INSERT INTO "posts" ("$id", "title", "authorId") VALUES ('4', 'Post 4', 'a1')`); }).not.toThrow(); // Original data preserved const row = db.prepare('SELECT * FROM "posts" WHERE "$id" = ?').get("1") as Record; expect(row.title).toBe("Post 1"); }); it("should fill existing rows with type fallback when adding a NOT NULL column without default", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "accounts", label: "Accounts", provider: "internal", schema: z.object({ name: z.string() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), role: z.string() }), }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "accounts" ("$id", "name") VALUES ('1', 'Alice')`); db.exec(generateMigration(v1, v2) as string); // Existing row gets the string fallback '' const row = db.prepare('SELECT * FROM "accounts" WHERE "$id" = ?').get("1") as Record< string, unknown >; expect(row.name).toBe("Alice"); expect(row.role).toBe(""); // Column is NOT NULL: inserting NULL should fail expect(() => { db.exec(`INSERT INTO "accounts" ("$id", "name", "role") VALUES ('2', 'Bob', NULL)`); }).toThrow(); // Valid insert works db.exec(`INSERT INTO "accounts" ("$id", "name", "role") VALUES ('3', 'Carol', 'admin')`); const carol = db.prepare('SELECT * FROM "accounts" WHERE "$id" = ?').get("3") as Record< string, unknown >; expect(carol.role).toBe("admin"); }); it("should drop indexed column without SQLite error and verify column and index are gone", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "contacts", label: "Contacts", provider: "internal", schema: z.object({ name: z.string(), email: z.string() }), indexes: [{ name: "contacts_email_idx", fields: ["email"], unique: false, fulltext: false }], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string() }), indexes: [], }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "contacts" ("$id", "name", "email") VALUES ('1', 'Alice', 'alice@example.com')`); // Must not throw (SQLite requires index drop before column drop) db.exec(generateMigration(v1, v2) as string); const colNames = (db.prepare('PRAGMA table_info("contacts")').all() as { name: string }[]).map( (c) => c.name, ); expect(colNames).not.toContain("email"); expect(colNames).toContain("name"); const indexes = (db.prepare('PRAGMA index_list("contacts")').all() as { name: string }[]).map( (i) => i.name, ); expect(indexes).not.toContain("contacts_email_idx"); const row = db.prepare('SELECT * FROM "contacts" WHERE "$id" = ?').get("1") as Record< string, unknown >; expect(row.name).toBe("Alice"); }); it("should migrate string → number (REAL) type change preserving data", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", schema: z.object({ label: z.string(), price: z.string() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ label: z.string(), price: z.number().optional() }), }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "products" ("$id", "label", "price") VALUES ('1', 'A', '9.99')`); db.exec(`INSERT INTO "products" ("$id", "label", "price") VALUES ('2', 'B', 'not-a-price')`); db.exec(`INSERT INTO "products" ("$id", "label", "price") VALUES ('3', 'C', '-2.5')`); db.exec(generateMigration(v1, v2) as string); const r1 = db.prepare('SELECT * FROM "products" WHERE "$id" = ?').get("1") as Record; const r2 = db.prepare('SELECT * FROM "products" WHERE "$id" = ?').get("2") as Record; const r3 = db.prepare('SELECT * FROM "products" WHERE "$id" = ?').get("3") as Record; expect(r1.price).toBeCloseTo(9.99); expect(r2.price).toBeNull(); // non-parseable → NULL expect(r3.price).toBeCloseTo(-2.5); }); it("should migrate string → datetime as identity pass-through (both TEXT in SQLite)", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "events", label: "Events", provider: "internal", schema: z.object({ title: z.string(), happenedAt: z.string().optional() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ title: z.string(), happenedAt: z.iso.datetime().optional() }), }); const isoDate = "2024-06-15T12:00:00.000Z"; db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "events" ("$id", "title", "happenedAt") VALUES ('1', 'Launch', '${isoDate}')`); db.exec(`INSERT INTO "events" ("$id", "title") VALUES ('2', 'TBD')`); db.exec(generateMigration(v1, v2) as string); const r1 = db.prepare('SELECT * FROM "events" WHERE "$id" = ?').get("1") as Record; const r2 = db.prepare('SELECT * FROM "events" WHERE "$id" = ?').get("2") as Record; expect(r1.happenedAt).toBe(isoDate); // unchanged — both TEXT expect(r2.happenedAt).toBeNull(); }); it("should migrate number (REAL) → integer type change truncating values", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "scores", label: "Scores", provider: "internal", schema: z.object({ label: z.string(), score: z.number() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ label: z.string(), score: z.number().int().optional() }), }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "scores" ("$id", "label", "score") VALUES ('1', 'A', 3.7)`); db.exec(`INSERT INTO "scores" ("$id", "label", "score") VALUES ('2', 'B', 10.0)`); db.exec(`INSERT INTO "scores" ("$id", "label", "score") VALUES ('3', 'C', -1.9)`); db.exec(generateMigration(v1, v2) as string); const r1 = db.prepare('SELECT * FROM "scores" WHERE "$id" = ?').get("1") as Record; const r2 = db.prepare('SELECT * FROM "scores" WHERE "$id" = ?').get("2") as Record; const r3 = db.prepare('SELECT * FROM "scores" WHERE "$id" = ?').get("3") as Record; expect(r1.score).toBe(3); // 3.7 truncated toward zero expect(r2.score).toBe(10); expect(r3.score).toBe(-1); // -1.9 truncated toward zero }); it("should migrate type change to column with declared default: existing rows cast, new rows use DEFAULT", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "items", label: "Items", provider: "internal", schema: z.object({ name: z.string(), qty: z.string().optional() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), qty: z.number().int().default(42) }), }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "items" ("$id", "name", "qty") VALUES ('1', 'A', '7')`); db.exec(`INSERT INTO "items" ("$id", "name", "qty") VALUES ('2', 'B', 'bad')`); db.exec(`INSERT INTO "items" ("$id", "name") VALUES ('3', 'C')`); // qty is NULL db.exec(generateMigration(v1, v2) as string); const r1 = db.prepare('SELECT * FROM "items" WHERE "$id" = ?').get("1") as Record; const r2 = db.prepare('SELECT * FROM "items" WHERE "$id" = ?').get("2") as Record; const r3 = db.prepare('SELECT * FROM "items" WHERE "$id" = ?').get("3") as Record; expect(r1.qty).toBe(7); // parseable string → integer expect(r2.qty).toBeNull(); // non-parseable → NULL (not 42; DEFAULT only applies to new inserts) expect(r3.qty).toBeNull(); // NULL remains NULL // New insert without qty gets the DEFAULT value 42 db.exec(`INSERT INTO "items" ("$id", "name") VALUES ('4', 'D')`); const r4 = db.prepare('SELECT * FROM "items" WHERE "$id" = ?').get("4") as Record; expect(r4.qty).toBe(42); }); it("should make column optional via recreation: existing data preserved, NULL now accepted", () => { const db = new DatabaseSync(":memory:"); const v1: Datasource = defineDatasource({ id: "members", label: "Members", provider: "internal", schema: z.object({ name: z.string(), code: z.string() }), indexes: [], }); const v2: Datasource = defineDatasource({ ...v1, schema: z.object({ name: z.string(), code: z.string().optional() }), }); db.exec(datasourceToSQL(v1)); db.exec(`INSERT INTO "members" ("$id", "name", "code") VALUES ('1', 'Alice', 'ALPHA')`); db.exec(generateMigration(v1, v2) as string); // Existing data preserved const row = db.prepare('SELECT * FROM "members" WHERE "$id" = ?').get("1") as Record; expect(row.name).toBe("Alice"); expect(row.code).toBe("ALPHA"); // Column is now nullable in the DB schema const colInfo = ( db.prepare('PRAGMA table_info("members")').all() as { name: string; notnull: number }[] ).find((c) => c.name === "code"); expect(colInfo?.notnull).toBe(0); // 0 = nullable // Inserting NULL now succeeds expect(() => { db.exec(`INSERT INTO "members" ("$id", "name") VALUES ('2', 'Bob')`); }).not.toThrow(); }); }); }); describe("datasourceExamplestoSQL", () => { it("should generate INSERT statements from examples", () => { const datasource: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", schema: z .object({ name: z.string(), price: z.number(), }) .meta({ fixtures: [ { $id: "1", name: "Product 1", price: 99.99, }, { $id: "2", name: "Product 2", price: 149.99, }, ], }), indexes: [], }); const sql = datasourceExamplestoSQL(datasource); expect(sql).toContain('INSERT INTO "products"'); expect(sql).toContain("'Product 1'"); expect(sql).toContain("99.99"); expect(sql).toContain("'Product 2'"); expect(sql).toContain("149.99"); }); it("should handle all data types correctly", () => { const datasource: Datasource = defineDatasource({ id: "test", label: "Test", provider: "internal", schema: z .object({ stringField: z.string(), numberField: z.number(), integerField: z.number().int(), booleanField: z.boolean(), }) .meta({ fixtures: [ { $id: "1", stringField: "hello", numberField: 3.14, integerField: 42, booleanField: true, }, ], }), indexes: [], }); const sql = datasourceExamplestoSQL(datasource); expect(sql).toContain("'hello'"); expect(sql).toContain("3.14"); expect(sql).toContain("42"); expect(sql).toContain("1"); // true becomes 1 }); it("should escape single quotes in strings", () => { const datasource: Datasource = defineDatasource({ id: "quotes", label: "Quotes", provider: "internal", schema: z .object({ text: z.string(), }) .meta({ fixtures: [ { $id: "1", text: "It's a quote", }, ], }), indexes: [], }); const sql = datasourceExamplestoSQL(datasource); expect(sql).toContain("'It''s a quote'"); }); it("should handle null values", () => { const datasource: Datasource = defineDatasource({ id: "nulls", label: "Nulls", provider: "internal", schema: z .object({ optional: z.string().nullable(), }) .meta({ fixtures: [ { $id: "1", optional: null, }, ], }), indexes: [], }); const sql = datasourceExamplestoSQL(datasource); expect(sql).toContain("NULL"); }); it("should return empty string when no examples", () => { const datasource: Datasource = defineDatasource({ id: "empty", label: "Empty", provider: "internal", // schema: { // type: "object", // properties: { // field: { type: "string" }, // }, // examples: [], // }, schema: z .object({ field: z.string(), }) .meta({ fixtures: [], }), indexes: [], }); const sql = datasourceExamplestoSQL(datasource); expect(sql).toBe(null); }); it("should return null when examples is undefined", () => { const datasource: Datasource = defineDatasource({ id: "no_examples", label: "No Examples", provider: "internal", // schema: { // type: "object", // properties: { // field: { type: "string" }, // }, // }, schema: z.object({ field: z.string(), }), indexes: [], }); const sql = datasourceExamplestoSQL(datasource); expect(sql).toBe(null); }); it("should include system properties in INSERT", () => { const datasource: Datasource = defineDatasource({ id: "users", label: "Users", provider: "internal", schema: z .object({ name: z.string(), }) .meta({ fixtures: [ { $id: "abc123", $creationDate: "2024-01-01T00:00:00Z", $lastModificationDate: "2024-01-02T00:00:00Z", name: "John Doe", }, ], }), indexes: [], }); const sql = datasourceExamplestoSQL(datasource); expect(sql).toContain('"$id"'); expect(sql).toContain('"$creationDate"'); expect(sql).toContain('"$lastModificationDate"'); expect(sql).toContain("'abc123'"); expect(sql).toContain("'2024-01-01T00:00:00Z'"); expect(sql).toContain("'John Doe'"); }); it("should handle boolean false correctly", () => { const datasource: Datasource = defineDatasource({ id: "flags", label: "Flags", provider: "internal", schema: z .object({ active: z.boolean(), }) .meta({ fixtures: [ { $id: "1", active: false, }, ], }), indexes: [], }); const sql = datasourceExamplestoSQL(datasource); expect(sql).toContain("0"); // false becomes 0 }); describe("SQLite integration", () => { it("should execute INSERT statements successfully", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", schema: z .object({ name: z.string(), price: z.number(), inStock: z.boolean().optional(), }) .meta({ fixtures: [ { $id: "p1", name: "Wireless Keyboard", price: 89.99, inStock: true, }, { $id: "p2", name: "Gaming Mouse", price: 59.99, inStock: false, }, ], }), indexes: [], }); // Create table db.exec(datasourceToSQL(datasource)); // Insert examples const insertSQL = datasourceExamplestoSQL(datasource); if (insertSQL) { db.exec(insertSQL); } // Verify data was inserted const rows = db.prepare('SELECT * FROM "products"').all() as Record[]; expect(rows).toHaveLength(2); const product1 = rows.find((r) => r.$id === "p1"); expect(product1).toBeDefined(); expect(product1?.name).toBe("Wireless Keyboard"); expect(product1?.price).toBe(89.99); expect(product1?.inStock).toBe(1); // true = 1 const product2 = rows.find((r) => r.$id === "p2"); expect(product2).toBeDefined(); expect(product2?.name).toBe("Gaming Mouse"); expect(product2?.price).toBe(59.99); expect(product2?.inStock).toBe(0); // false = 0 }); it("should handle complex data with all system properties", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "blog_posts", label: "Blog Posts", provider: "internal", schema: z .object({ title: z.string(), content: z.string().optional(), views: z.number().int().optional(), }) .meta({ fixtures: [ { $id: "post1", $creationDate: "2024-01-01T00:00:00Z", $lastModificationDate: "2024-01-02T00:00:00Z", title: "First Post", content: "Content here", views: 100, }, ], }), indexes: [], }); // Create and populate db.exec(datasourceToSQL(datasource)); const insertSQL = datasourceExamplestoSQL(datasource); if (insertSQL) { db.exec(insertSQL); } // Verify const row = db.prepare('SELECT * FROM "blog_posts" WHERE "$id" = ?').get("post1") as Record< string, unknown >; expect(row.$id).toBe("post1"); expect(row.$creationDate).toBe("2024-01-01T00:00:00Z"); expect(row.$lastModificationDate).toBe("2024-01-02T00:00:00Z"); expect(row.title).toBe("First Post"); expect(row.content).toBe("Content here"); expect(row.views).toBe(100); }); it("should handle null values in INSERT", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "tasks", label: "Tasks", provider: "internal", schema: z .object({ title: z.string(), description: z.string().nullable(), }) .meta({ fixtures: [ { $id: "t1", title: "Task 1", description: null, }, ], }), indexes: [], }); db.exec(datasourceToSQL(datasource)); const insertSQL = datasourceExamplestoSQL(datasource); if (insertSQL) { db.exec(insertSQL); } const row = db.prepare('SELECT * FROM "tasks" WHERE "$id" = ?').get("t1") as Record; expect(row.title).toBe("Task 1"); expect(row.description).toBeNull(); }); }); }); describe("datasourceItemsToSQL", () => { const datasource: Datasource = defineDatasource({ id: "products", label: "Products", provider: "internal", schema: z.object({ name: z.string(), price: z.number(), }), indexes: [], }); it("returns null when no items", () => { expect(datasourceItemsToSQL(datasource, [])).toBeNull(); }); it("uses item.id as $id and item.createdAt/updatedAt for system dates", () => { const sql = datasourceItemsToSQL(datasource, [ { id: "item_abc", data: { name: "Widget", price: 12.5 }, createdAt: "2024-01-01T00:00:00Z", updatedAt: "2024-02-02T00:00:00Z", }, ]); expect(sql).toContain('"$id"'); expect(sql).toContain("'item_abc'"); expect(sql).toContain('"$creationDate"'); expect(sql).toContain("'2024-01-01T00:00:00Z'"); expect(sql).toContain('"$lastModificationDate"'); expect(sql).toContain("'2024-02-02T00:00:00Z'"); }); it("generates a nanoid $id when item.id is missing and uses current timestamp when dates absent", () => { const sql = datasourceItemsToSQL(datasource, [{ id: "", data: { name: "X", price: 1 } }]); // $id column is present (auto-generated) expect(sql).toMatch(/"\$id"/); expect(sql).toMatch(/"\$creationDate"/); expect(sql).toMatch(/"\$lastModificationDate"/); }); it("does not duplicate system columns when already present in item.data", () => { const sql = datasourceItemsToSQL(datasource, [ { id: "ignored", data: { $id: "explicit_id", $creationDate: "2024-03-03T00:00:00Z", $lastModificationDate: "2024-04-04T00:00:00Z", name: "Y", price: 2, }, }, ]) as string; // Each system column should appear exactly once in the column list const idCount = (sql.match(/"\$id"/g) ?? []).length; const cCount = (sql.match(/"\$creationDate"/g) ?? []).length; const mCount = (sql.match(/"\$lastModificationDate"/g) ?? []).length; expect(idCount).toBe(1); expect(cCount).toBe(1); expect(mCount).toBe(1); expect(sql).toContain("'explicit_id'"); expect(sql).not.toContain("'ignored'"); }); describe("SQLite integration", () => { it("inserts API items into a table with NOT NULL $id PRIMARY KEY", () => { const db = new DatabaseSync(":memory:"); db.exec(datasourceToSQL(datasource)); const insertSQL = datasourceItemsToSQL(datasource, [ { id: "real_1", data: { name: "Keyboard", price: 50 }, createdAt: "2024-01-01T00:00:00Z", updatedAt: "2024-01-02T00:00:00Z", }, { id: "real_2", data: { name: "Mouse", price: 25 }, }, ]); expect(insertSQL).not.toBeNull(); db.exec(insertSQL as string); const rows = db.prepare('SELECT * FROM "products" ORDER BY "$id"').all() as Record[]; expect(rows).toHaveLength(2); expect(rows[0].$id).toBe("real_1"); expect(rows[0].name).toBe("Keyboard"); expect(rows[0].$creationDate).toBe("2024-01-01T00:00:00Z"); expect(rows[0].$lastModificationDate).toBe("2024-01-02T00:00:00Z"); expect(rows[1].$id).toBe("real_2"); expect(rows[1].name).toBe("Mouse"); // Auto-generated dates should be valid ISO strings expect(typeof rows[1].$creationDate).toBe("string"); expect(typeof rows[1].$lastModificationDate).toBe("string"); }); }); }); }); describe("datasourceDropSQL", () => { it("drops the datasource's table and its full-text tables", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "articles", label: "Articles", provider: "internal", schema: z.object({ title: z.string(), content: z.string() }), indexes: [{ name: "search_idx", fields: ["title", "content"], fulltext: true }], }); db.exec(datasourceToSQL(datasource)); const tables = () => (db.prepare("SELECT name FROM sqlite_master WHERE type = 'table'").all() as { name: string }[]).map( (t) => t.name, ); expect(tables()).toEqual(expect.arrayContaining(["articles", "articles_search_idx"])); db.exec(datasourceDropSQL(datasource)); expect(tables().filter((t) => t.startsWith("articles"))).toEqual([]); }); it("can run on a database where the table never existed", () => { const db = new DatabaseSync(":memory:"); const datasource: Datasource = defineDatasource({ id: "projects", label: "Projects", provider: "internal", schema: z.object({ title: z.string() }), indexes: [], }); expect(() => db.exec(datasourceDropSQL(datasource))).not.toThrow(); }); });