wehub-resource-sync
436 lines
12 KiB
TypeScript
436 lines
12 KiB
TypeScript
import { PGlite } from '@electric-sql/pglite';
|
|
import { relations, sql } from 'drizzle-orm';
|
|
import type { PgliteDatabase } from 'drizzle-orm/pglite';
|
|
import { drizzle } from 'drizzle-orm/pglite';
|
|
import { afterAll, afterEach, beforeAll, expect, test, vi } from 'vitest';
|
|
import { reset, seed } from '../../src/index.ts';
|
|
import * as schema from './pgSchema.ts';
|
|
|
|
let client: PGlite;
|
|
let db: PgliteDatabase;
|
|
|
|
beforeAll(async () => {
|
|
client = new PGlite();
|
|
|
|
db = drizzle(client);
|
|
|
|
await db.execute(sql`CREATE SCHEMA "seeder_lib_pg";`);
|
|
await db.execute(
|
|
sql`
|
|
CREATE TABLE IF NOT EXISTS "seeder_lib_pg"."customer" (
|
|
"id" varchar(256) PRIMARY KEY NOT NULL,
|
|
"company_name" text NOT NULL,
|
|
"contact_name" text NOT NULL,
|
|
"contact_title" text NOT NULL,
|
|
"address" text NOT NULL,
|
|
"city" text NOT NULL,
|
|
"postal_code" text,
|
|
"region" text,
|
|
"country" text NOT NULL,
|
|
"phone" text NOT NULL,
|
|
"fax" text
|
|
);
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
CREATE TABLE IF NOT EXISTS "seeder_lib_pg"."order_detail" (
|
|
"unit_price" numeric NOT NULL,
|
|
"quantity" integer NOT NULL,
|
|
"discount" numeric NOT NULL,
|
|
"order_id" integer NOT NULL,
|
|
"product_id" integer NOT NULL
|
|
);
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
CREATE TABLE IF NOT EXISTS "seeder_lib_pg"."employee" (
|
|
"id" integer PRIMARY KEY NOT NULL,
|
|
"last_name" text NOT NULL,
|
|
"first_name" text,
|
|
"title" text NOT NULL,
|
|
"title_of_courtesy" text NOT NULL,
|
|
"birth_date" timestamp NOT NULL,
|
|
"hire_date" timestamp NOT NULL,
|
|
"address" text NOT NULL,
|
|
"city" text NOT NULL,
|
|
"postal_code" text NOT NULL,
|
|
"country" text NOT NULL,
|
|
"home_phone" text NOT NULL,
|
|
"extension" integer NOT NULL,
|
|
"notes" text NOT NULL,
|
|
"reports_to" integer,
|
|
"photo_path" text
|
|
);
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
CREATE TABLE IF NOT EXISTS "seeder_lib_pg"."order" (
|
|
"id" integer PRIMARY KEY NOT NULL,
|
|
"order_date" timestamp NOT NULL,
|
|
"required_date" timestamp NOT NULL,
|
|
"shipped_date" timestamp,
|
|
"ship_via" integer NOT NULL,
|
|
"freight" numeric NOT NULL,
|
|
"ship_name" text NOT NULL,
|
|
"ship_city" text NOT NULL,
|
|
"ship_region" text,
|
|
"ship_postal_code" text,
|
|
"ship_country" text NOT NULL,
|
|
"customer_id" text NOT NULL,
|
|
"employee_id" integer NOT NULL
|
|
);
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
CREATE TABLE IF NOT EXISTS "seeder_lib_pg"."product" (
|
|
"id" integer PRIMARY KEY NOT NULL,
|
|
"name" text NOT NULL,
|
|
"quantity_per_unit" text NOT NULL,
|
|
"unit_price" numeric NOT NULL,
|
|
"units_in_stock" integer NOT NULL,
|
|
"units_on_order" integer NOT NULL,
|
|
"reorder_level" integer NOT NULL,
|
|
"discontinued" integer NOT NULL,
|
|
"supplier_id" integer NOT NULL
|
|
);
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
CREATE TABLE IF NOT EXISTS "seeder_lib_pg"."supplier" (
|
|
"id" integer PRIMARY KEY NOT NULL,
|
|
"company_name" text NOT NULL,
|
|
"contact_name" text NOT NULL,
|
|
"contact_title" text NOT NULL,
|
|
"address" text NOT NULL,
|
|
"city" text NOT NULL,
|
|
"region" text,
|
|
"postal_code" text NOT NULL,
|
|
"country" text NOT NULL,
|
|
"phone" text NOT NULL
|
|
);
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
DO $$ BEGIN
|
|
ALTER TABLE "seeder_lib_pg"."order_detail" ADD CONSTRAINT "order_detail_order_id_order_id_fk" FOREIGN KEY ("order_id") REFERENCES "seeder_lib_pg"."order"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
DO $$ BEGIN
|
|
ALTER TABLE "seeder_lib_pg"."order_detail" ADD CONSTRAINT "order_detail_product_id_product_id_fk" FOREIGN KEY ("product_id") REFERENCES "seeder_lib_pg"."product"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
DO $$ BEGIN
|
|
ALTER TABLE "seeder_lib_pg"."employee" ADD CONSTRAINT "employee_reports_to_employee_id_fk" FOREIGN KEY ("reports_to") REFERENCES "seeder_lib_pg"."employee"("id") ON DELETE no action ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
DO $$ BEGIN
|
|
ALTER TABLE "seeder_lib_pg"."order" ADD CONSTRAINT "order_customer_id_customer_id_fk" FOREIGN KEY ("customer_id") REFERENCES "seeder_lib_pg"."customer"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
DO $$ BEGIN
|
|
ALTER TABLE "seeder_lib_pg"."order" ADD CONSTRAINT "order_employee_id_employee_id_fk" FOREIGN KEY ("employee_id") REFERENCES "seeder_lib_pg"."employee"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
DO $$ BEGIN
|
|
ALTER TABLE "seeder_lib_pg"."product" ADD CONSTRAINT "product_supplier_id_supplier_id_fk" FOREIGN KEY ("supplier_id") REFERENCES "seeder_lib_pg"."supplier"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
CREATE TABLE IF NOT EXISTS "seeder_lib_pg"."identity_columns_table" (
|
|
"id" integer generated always as identity,
|
|
"id1" integer generated by default as identity,
|
|
"name" text
|
|
);
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
create table "seeder_lib_pg"."users"
|
|
(
|
|
id serial
|
|
primary key,
|
|
name text,
|
|
"invitedBy" integer
|
|
constraint "users_invitedBy_user_id_fk"
|
|
references "seeder_lib_pg"."users"
|
|
);
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
create table "seeder_lib_pg"."posts"
|
|
(
|
|
id serial
|
|
primary key,
|
|
name text,
|
|
content text,
|
|
"userId" integer
|
|
constraint "users_userId_user_id_fk"
|
|
references "seeder_lib_pg"."users"
|
|
);
|
|
`,
|
|
);
|
|
});
|
|
|
|
afterEach(async () => {
|
|
await reset(db, schema);
|
|
});
|
|
|
|
afterAll(async () => {
|
|
await client.close();
|
|
});
|
|
|
|
test('basic seed test', async () => {
|
|
await seed(db, schema);
|
|
|
|
const customers = await db.select().from(schema.customers);
|
|
const details = await db.select().from(schema.details);
|
|
const employees = await db.select().from(schema.employees);
|
|
const orders = await db.select().from(schema.orders);
|
|
const products = await db.select().from(schema.products);
|
|
const suppliers = await db.select().from(schema.suppliers);
|
|
|
|
expect(customers.length).toBe(10);
|
|
expect(details.length).toBe(10);
|
|
expect(employees.length).toBe(10);
|
|
expect(orders.length).toBe(10);
|
|
expect(products.length).toBe(10);
|
|
expect(suppliers.length).toBe(10);
|
|
});
|
|
|
|
test('seed with options.count:11 test', async () => {
|
|
await seed(db, schema, { count: 11 });
|
|
|
|
const customers = await db.select().from(schema.customers);
|
|
const details = await db.select().from(schema.details);
|
|
const employees = await db.select().from(schema.employees);
|
|
const orders = await db.select().from(schema.orders);
|
|
const products = await db.select().from(schema.products);
|
|
const suppliers = await db.select().from(schema.suppliers);
|
|
|
|
expect(customers.length).toBe(11);
|
|
expect(details.length).toBe(11);
|
|
expect(employees.length).toBe(11);
|
|
expect(orders.length).toBe(11);
|
|
expect(products.length).toBe(11);
|
|
expect(suppliers.length).toBe(11);
|
|
});
|
|
|
|
test('redefine(refine) customers count', async () => {
|
|
await seed(db, schema, { count: 11 }).refine(() => ({
|
|
customers: {
|
|
count: 12,
|
|
},
|
|
}));
|
|
|
|
const customers = await db.select().from(schema.customers);
|
|
const details = await db.select().from(schema.details);
|
|
const employees = await db.select().from(schema.employees);
|
|
const orders = await db.select().from(schema.orders);
|
|
const products = await db.select().from(schema.products);
|
|
const suppliers = await db.select().from(schema.suppliers);
|
|
|
|
expect(customers.length).toBe(12);
|
|
expect(details.length).toBe(11);
|
|
expect(employees.length).toBe(11);
|
|
expect(orders.length).toBe(11);
|
|
expect(products.length).toBe(11);
|
|
expect(suppliers.length).toBe(11);
|
|
});
|
|
|
|
test('redefine(refine) all tables count', async () => {
|
|
await seed(db, schema, { count: 11 }).refine(() => ({
|
|
customers: {
|
|
count: 12,
|
|
},
|
|
details: {
|
|
count: 13,
|
|
},
|
|
employees: {
|
|
count: 14,
|
|
},
|
|
orders: {
|
|
count: 15,
|
|
},
|
|
products: {
|
|
count: 16,
|
|
},
|
|
suppliers: {
|
|
count: 17,
|
|
},
|
|
}));
|
|
|
|
const customers = await db.select().from(schema.customers);
|
|
const details = await db.select().from(schema.details);
|
|
const employees = await db.select().from(schema.employees);
|
|
const orders = await db.select().from(schema.orders);
|
|
const products = await db.select().from(schema.products);
|
|
const suppliers = await db.select().from(schema.suppliers);
|
|
|
|
expect(customers.length).toBe(12);
|
|
expect(details.length).toBe(13);
|
|
expect(employees.length).toBe(14);
|
|
expect(orders.length).toBe(15);
|
|
expect(products.length).toBe(16);
|
|
expect(suppliers.length).toBe(17);
|
|
});
|
|
|
|
test("redefine(refine) orders count using 'with' in customers", async () => {
|
|
await seed(db, schema, { count: 11 }).refine(() => ({
|
|
customers: {
|
|
count: 4,
|
|
with: {
|
|
orders: 2,
|
|
},
|
|
},
|
|
orders: {
|
|
count: 13,
|
|
},
|
|
}));
|
|
|
|
const customers = await db.select().from(schema.customers);
|
|
const details = await db.select().from(schema.details);
|
|
const employees = await db.select().from(schema.employees);
|
|
const orders = await db.select().from(schema.orders);
|
|
const products = await db.select().from(schema.products);
|
|
const suppliers = await db.select().from(schema.suppliers);
|
|
|
|
expect(customers.length).toBe(4);
|
|
expect(details.length).toBe(11);
|
|
expect(employees.length).toBe(11);
|
|
expect(orders.length).toBe(8);
|
|
expect(products.length).toBe(11);
|
|
expect(suppliers.length).toBe(11);
|
|
});
|
|
|
|
test("sequential using of 'with'", async () => {
|
|
const currSchema = {
|
|
customers: schema.customers,
|
|
details: schema.details,
|
|
employees: schema.employees,
|
|
orders: schema.orders,
|
|
products: schema.products,
|
|
suppliers: schema.suppliers,
|
|
};
|
|
await seed(db, currSchema, { count: 11 }).refine(() => ({
|
|
customers: {
|
|
count: 4,
|
|
with: {
|
|
orders: 2,
|
|
},
|
|
},
|
|
orders: {
|
|
count: 12,
|
|
with: {
|
|
details: 3,
|
|
},
|
|
},
|
|
}));
|
|
|
|
const customers = await db.select().from(schema.customers);
|
|
const details = await db.select().from(schema.details);
|
|
const employees = await db.select().from(schema.employees);
|
|
const orders = await db.select().from(schema.orders);
|
|
const products = await db.select().from(schema.products);
|
|
const suppliers = await db.select().from(schema.suppliers);
|
|
|
|
expect(customers.length).toBe(4);
|
|
expect(details.length).toBe(24);
|
|
expect(employees.length).toBe(11);
|
|
expect(orders.length).toBe(8);
|
|
expect(products.length).toBe(11);
|
|
expect(suppliers.length).toBe(11);
|
|
});
|
|
|
|
test('seeding with identity columns', async () => {
|
|
await seed(db, { identityColumnsTable: schema.identityColumnsTable });
|
|
|
|
const result = await db.select().from(schema.identityColumnsTable);
|
|
|
|
expect(result.length).toBe(10);
|
|
});
|
|
|
|
test('seeding with self relation', async () => {
|
|
await seed(db, { users: schema.users });
|
|
|
|
const result = await db.select().from(schema.users);
|
|
|
|
expect(result.length).toBe(10);
|
|
const predicate = result.every((row) => Object.values(row).every((val) => val !== undefined && val !== null));
|
|
expect(predicate).toBe(true);
|
|
});
|
|
|
|
test('overlapping a foreign key constraint with a one-to-many relation', async () => {
|
|
const postsRelation = relations(schema.posts, ({ one }) => ({
|
|
user: one(schema.users, { fields: [schema.posts.userId], references: [schema.users.id] }),
|
|
}));
|
|
|
|
const consoleMock = vi.spyOn(console, 'warn').mockImplementation(() => {});
|
|
|
|
await reset(db, { users: schema.users, posts: schema.posts, postsRelation });
|
|
await seed(db, { users: schema.users, posts: schema.posts, postsRelation });
|
|
// expecting to get a warning
|
|
expect(consoleMock).toBeCalled();
|
|
expect(consoleMock).toBeCalledWith(expect.stringMatching(/^You are providing a one-to-many relation.+/));
|
|
|
|
const users = await db.select().from(schema.users);
|
|
const posts = await db.select().from(schema.posts);
|
|
|
|
expect(users.length).toBe(10);
|
|
let predicate = users.every((row) => Object.values(row).every((val) => val !== undefined && val !== null));
|
|
expect(predicate).toBe(true);
|
|
|
|
expect(posts.length).toBe(10);
|
|
predicate = posts.every((row) => Object.values(row).every((val) => val !== undefined && val !== null));
|
|
expect(predicate).toBe(true);
|
|
});
|