wehub-resource-sync
564 lines
17 KiB
TypeScript
564 lines
17 KiB
TypeScript
import { type Equal, sql } from 'drizzle-orm';
|
|
import {
|
|
customType,
|
|
integer,
|
|
json,
|
|
jsonb,
|
|
pgEnum,
|
|
pgMaterializedView,
|
|
pgSchema,
|
|
pgTable,
|
|
pgView,
|
|
serial,
|
|
text,
|
|
} from 'drizzle-orm/pg-core';
|
|
import type { TopLevelCondition } from 'json-rules-engine';
|
|
import * as v from 'valibot';
|
|
import { test } from 'vitest';
|
|
import { jsonSchema } from '~/column.ts';
|
|
import { CONSTANTS } from '~/constants.ts';
|
|
import { createInsertSchema, createSelectSchema, createUpdateSchema } from '../src';
|
|
import { Expect, expectEnumValues, expectSchemaShape } from './utils.ts';
|
|
|
|
const integerSchema = v.pipe(v.number(), v.minValue(CONSTANTS.INT32_MIN), v.maxValue(CONSTANTS.INT32_MAX), v.integer());
|
|
const textSchema = v.string();
|
|
|
|
test('table - select', (t) => {
|
|
const table = pgTable('test', {
|
|
id: serial().primaryKey(),
|
|
name: text().notNull(),
|
|
});
|
|
|
|
const result = createSelectSchema(table);
|
|
const expected = v.object({ id: integerSchema, name: textSchema });
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('table in schema - select', (tc) => {
|
|
const schema = pgSchema('test');
|
|
const table = schema.table('test', {
|
|
id: serial().primaryKey(),
|
|
name: text().notNull(),
|
|
});
|
|
|
|
const result = createSelectSchema(table);
|
|
const expected = v.object({ id: integerSchema, name: textSchema });
|
|
expectSchemaShape(tc, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('table - insert', (t) => {
|
|
const table = pgTable('test', {
|
|
id: integer().generatedAlwaysAsIdentity().primaryKey(),
|
|
name: text().notNull(),
|
|
age: integer(),
|
|
});
|
|
|
|
const result = createInsertSchema(table);
|
|
const expected = v.object({ name: textSchema, age: v.optional(v.nullable(integerSchema)) });
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('table - update', (t) => {
|
|
const table = pgTable('test', {
|
|
id: integer().generatedAlwaysAsIdentity().primaryKey(),
|
|
name: text().notNull(),
|
|
age: integer(),
|
|
});
|
|
|
|
const result = createUpdateSchema(table);
|
|
const expected = v.object({
|
|
name: v.optional(textSchema),
|
|
age: v.optional(v.nullable(integerSchema)),
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('view qb - select', (t) => {
|
|
const table = pgTable('test', {
|
|
id: serial().primaryKey(),
|
|
name: text().notNull(),
|
|
});
|
|
const view = pgView('test').as((qb) => qb.select({ id: table.id, age: sql``.as('age') }).from(table));
|
|
|
|
const result = createSelectSchema(view);
|
|
const expected = v.object({ id: integerSchema, age: v.any() });
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('view columns - select', (t) => {
|
|
const view = pgView('test', {
|
|
id: serial().primaryKey(),
|
|
name: text().notNull(),
|
|
}).as(sql``);
|
|
|
|
const result = createSelectSchema(view);
|
|
const expected = v.object({ id: integerSchema, name: textSchema });
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('materialized view qb - select', (t) => {
|
|
const table = pgTable('test', {
|
|
id: serial().primaryKey(),
|
|
name: text().notNull(),
|
|
});
|
|
const view = pgMaterializedView('test').as((qb) => qb.select({ id: table.id, age: sql``.as('age') }).from(table));
|
|
|
|
const result = createSelectSchema(view);
|
|
const expected = v.object({ id: integerSchema, age: v.any() });
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('materialized view columns - select', (t) => {
|
|
const view = pgView('test', {
|
|
id: serial().primaryKey(),
|
|
name: text().notNull(),
|
|
}).as(sql``);
|
|
|
|
const result = createSelectSchema(view);
|
|
const expected = v.object({ id: integerSchema, name: textSchema });
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('view with nested fields - select', (t) => {
|
|
const table = pgTable('test', {
|
|
id: serial().primaryKey(),
|
|
name: text().notNull(),
|
|
});
|
|
const view = pgMaterializedView('test').as((qb) =>
|
|
qb.select({
|
|
id: table.id,
|
|
nested: {
|
|
name: table.name,
|
|
age: sql``.as('age'),
|
|
},
|
|
table,
|
|
}).from(table)
|
|
);
|
|
|
|
const result = createSelectSchema(view);
|
|
const expected = v.object({
|
|
id: integerSchema,
|
|
nested: v.object({ name: textSchema, age: v.any() }),
|
|
table: v.object({ id: integerSchema, name: textSchema }),
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('enum - select', (t) => {
|
|
const enum_ = pgEnum('test', ['a', 'b', 'c']);
|
|
|
|
const result = createSelectSchema(enum_);
|
|
const expected = v.enum({ a: 'a', b: 'b', c: 'c' });
|
|
expectEnumValues(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('nullability - select', (t) => {
|
|
const table = pgTable('test', {
|
|
c1: integer(),
|
|
c2: integer().notNull(),
|
|
c3: integer().default(1),
|
|
c4: integer().notNull().default(1),
|
|
});
|
|
|
|
const result = createSelectSchema(table);
|
|
const expected = v.object({
|
|
c1: v.nullable(integerSchema),
|
|
c2: integerSchema,
|
|
c3: v.nullable(integerSchema),
|
|
c4: integerSchema,
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('nullability - insert', (t) => {
|
|
const table = pgTable('test', {
|
|
c1: integer(),
|
|
c2: integer().notNull(),
|
|
c3: integer().default(1),
|
|
c4: integer().notNull().default(1),
|
|
c5: integer().generatedAlwaysAs(1),
|
|
c6: integer().generatedAlwaysAsIdentity(),
|
|
c7: integer().generatedByDefaultAsIdentity(),
|
|
});
|
|
|
|
const result = createInsertSchema(table);
|
|
const expected = v.object({
|
|
c1: v.optional(v.nullable(integerSchema)),
|
|
c2: integerSchema,
|
|
c3: v.optional(v.nullable(integerSchema)),
|
|
c4: v.optional(integerSchema),
|
|
c7: v.optional(integerSchema),
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
});
|
|
|
|
test('nullability - update', (t) => {
|
|
const table = pgTable('test', {
|
|
c1: integer(),
|
|
c2: integer().notNull(),
|
|
c3: integer().default(1),
|
|
c4: integer().notNull().default(1),
|
|
c5: integer().generatedAlwaysAs(1),
|
|
c6: integer().generatedAlwaysAsIdentity(),
|
|
c7: integer().generatedByDefaultAsIdentity(),
|
|
});
|
|
|
|
const result = createUpdateSchema(table);
|
|
const expected = v.object({
|
|
c1: v.optional(v.nullable(integerSchema)),
|
|
c2: v.optional(integerSchema),
|
|
c3: v.optional(v.nullable(integerSchema)),
|
|
c4: v.optional(integerSchema),
|
|
c7: v.optional(integerSchema),
|
|
});
|
|
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('refine table - select', (t) => {
|
|
const table = pgTable('test', {
|
|
c1: integer(),
|
|
c2: integer().notNull(),
|
|
c3: integer().notNull(),
|
|
});
|
|
|
|
const result = createSelectSchema(table, {
|
|
c2: (schema) => v.pipe(schema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
});
|
|
const expected = v.object({
|
|
c1: v.nullable(integerSchema),
|
|
c2: v.pipe(integerSchema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('refine table - select with custom data type', (t) => {
|
|
const customText = customType({ dataType: () => 'text' });
|
|
const table = pgTable('test', {
|
|
c1: integer(),
|
|
c2: integer().notNull(),
|
|
c3: integer().notNull(),
|
|
c4: customText(),
|
|
});
|
|
|
|
const customTextSchema = v.pipe(v.string(), v.minLength(1), v.maxLength(100));
|
|
const result = createSelectSchema(table, {
|
|
c2: (schema) => v.pipe(schema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
c4: customTextSchema,
|
|
});
|
|
const expected = v.object({
|
|
c1: v.nullable(integerSchema),
|
|
c2: v.pipe(integerSchema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
c4: customTextSchema,
|
|
});
|
|
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('refine table - insert', (t) => {
|
|
const table = pgTable('test', {
|
|
c1: integer(),
|
|
c2: integer().notNull(),
|
|
c3: integer().notNull(),
|
|
c4: integer().generatedAlwaysAs(1),
|
|
});
|
|
|
|
const result = createInsertSchema(table, {
|
|
c2: (schema) => v.pipe(schema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
});
|
|
const expected = v.object({
|
|
c1: v.optional(v.nullable(integerSchema)),
|
|
c2: v.pipe(integerSchema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('refine table - update', (t) => {
|
|
const table = pgTable('test', {
|
|
c1: integer(),
|
|
c2: integer().notNull(),
|
|
c3: integer().notNull(),
|
|
c4: integer().generatedAlwaysAs(1),
|
|
});
|
|
|
|
const result = createUpdateSchema(table, {
|
|
c2: (schema) => v.pipe(schema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
});
|
|
const expected = v.object({
|
|
c1: v.optional(v.nullable(integerSchema)),
|
|
c2: v.optional(v.pipe(integerSchema, v.maxValue(1000))),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('refine view - select', (t) => {
|
|
const table = pgTable('test', {
|
|
c1: integer(),
|
|
c2: integer(),
|
|
c3: integer(),
|
|
c4: integer(),
|
|
c5: integer(),
|
|
c6: integer(),
|
|
});
|
|
const view = pgView('test').as((qb) =>
|
|
qb.select({
|
|
c1: table.c1,
|
|
c2: table.c2,
|
|
c3: table.c3,
|
|
nested: {
|
|
c4: table.c4,
|
|
c5: table.c5,
|
|
c6: table.c6,
|
|
},
|
|
table,
|
|
}).from(table)
|
|
);
|
|
|
|
const result = createSelectSchema(view, {
|
|
c2: (schema) => v.pipe(schema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
nested: {
|
|
c5: (schema) => v.pipe(schema, v.maxValue(1000)),
|
|
c6: v.pipe(v.string(), v.transform(Number)),
|
|
},
|
|
table: {
|
|
c2: (schema) => v.pipe(schema, v.maxValue(1000)),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
},
|
|
});
|
|
const expected = v.object({
|
|
c1: v.nullable(integerSchema),
|
|
c2: v.nullable(v.pipe(integerSchema, v.maxValue(1000))),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
nested: v.object({
|
|
c4: v.nullable(integerSchema),
|
|
c5: v.nullable(v.pipe(integerSchema, v.maxValue(1000))),
|
|
c6: v.pipe(v.string(), v.transform(Number)),
|
|
}),
|
|
table: v.object({
|
|
c1: v.nullable(integerSchema),
|
|
c2: v.nullable(v.pipe(integerSchema, v.maxValue(1000))),
|
|
c3: v.pipe(v.string(), v.transform(Number)),
|
|
c4: v.nullable(integerSchema),
|
|
c5: v.nullable(integerSchema),
|
|
c6: v.nullable(integerSchema),
|
|
}),
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
test('all data types', (t) => {
|
|
const table = pgTable('test', ({
|
|
bigint,
|
|
bigserial,
|
|
bit,
|
|
boolean,
|
|
date,
|
|
char,
|
|
cidr,
|
|
doublePrecision,
|
|
geometry,
|
|
halfvec,
|
|
inet,
|
|
integer,
|
|
interval,
|
|
json,
|
|
jsonb,
|
|
line,
|
|
macaddr,
|
|
macaddr8,
|
|
numeric,
|
|
point,
|
|
real,
|
|
serial,
|
|
smallint,
|
|
smallserial,
|
|
text,
|
|
sparsevec,
|
|
time,
|
|
timestamp,
|
|
uuid,
|
|
varchar,
|
|
vector,
|
|
}) => ({
|
|
bigint1: bigint({ mode: 'number' }).notNull(),
|
|
bigint2: bigint({ mode: 'bigint' }).notNull(),
|
|
bigserial1: bigserial({ mode: 'number' }).notNull(),
|
|
bigserial2: bigserial({ mode: 'bigint' }).notNull(),
|
|
bit: bit({ dimensions: 5 }).notNull(),
|
|
boolean: boolean().notNull(),
|
|
date1: date({ mode: 'date' }).notNull(),
|
|
date2: date({ mode: 'string' }).notNull(),
|
|
char1: char({ length: 10 }).notNull(),
|
|
char2: char({ length: 1, enum: ['a', 'b', 'c'] }).notNull(),
|
|
cidr: cidr().notNull(),
|
|
doublePrecision: doublePrecision().notNull(),
|
|
geometry1: geometry({ type: 'point', mode: 'tuple' }).notNull(),
|
|
geometry2: geometry({ type: 'point', mode: 'xy' }).notNull(),
|
|
halfvec: halfvec({ dimensions: 3 }).notNull(),
|
|
inet: inet().notNull(),
|
|
integer: integer().notNull(),
|
|
interval: interval().notNull(),
|
|
json: json().notNull(),
|
|
jsonb: jsonb().notNull(),
|
|
line1: line({ mode: 'abc' }).notNull(),
|
|
line2: line({ mode: 'tuple' }).notNull(),
|
|
macaddr: macaddr().notNull(),
|
|
macaddr8: macaddr8().notNull(),
|
|
numeric: numeric().notNull(),
|
|
point1: point({ mode: 'xy' }).notNull(),
|
|
point2: point({ mode: 'tuple' }).notNull(),
|
|
real: real().notNull(),
|
|
serial: serial().notNull(),
|
|
smallint: smallint().notNull(),
|
|
smallserial: smallserial().notNull(),
|
|
text1: text().notNull(),
|
|
text2: text({ enum: ['a', 'b', 'c'] }).notNull(),
|
|
sparsevec: sparsevec({ dimensions: 3 }).notNull(),
|
|
time: time().notNull(),
|
|
timestamp1: timestamp({ mode: 'date' }).notNull(),
|
|
timestamp2: timestamp({ mode: 'string' }).notNull(),
|
|
uuid: uuid().notNull(),
|
|
varchar1: varchar({ length: 10 }).notNull(),
|
|
varchar2: varchar({ length: 1, enum: ['a', 'b', 'c'] }).notNull(),
|
|
vector: vector({ dimensions: 3 }).notNull(),
|
|
array1: integer().array().notNull(),
|
|
array2: integer().array().array(2).notNull(),
|
|
array3: varchar({ length: 10 }).array().array(2).notNull(),
|
|
}));
|
|
|
|
const result = createSelectSchema(table);
|
|
const expected = v.object({
|
|
bigint1: v.pipe(v.number(), v.minValue(Number.MIN_SAFE_INTEGER), v.maxValue(Number.MAX_SAFE_INTEGER), v.integer()),
|
|
bigint2: v.pipe(v.bigint(), v.minValue(CONSTANTS.INT64_MIN), v.maxValue(CONSTANTS.INT64_MAX)),
|
|
bigserial1: v.pipe(
|
|
v.number(),
|
|
v.minValue(Number.MIN_SAFE_INTEGER),
|
|
v.maxValue(Number.MAX_SAFE_INTEGER),
|
|
v.integer(),
|
|
),
|
|
bigserial2: v.pipe(v.bigint(), v.minValue(CONSTANTS.INT64_MIN), v.maxValue(CONSTANTS.INT64_MAX)),
|
|
bit: v.pipe(v.string(), v.regex(/^[01]+$/), v.maxLength(5 as number)),
|
|
boolean: v.boolean(),
|
|
date1: v.date(),
|
|
date2: v.string(),
|
|
char1: v.pipe(v.string(), v.length(10 as number)),
|
|
char2: v.enum({ a: 'a', b: 'b', c: 'c' }),
|
|
cidr: v.string(),
|
|
doublePrecision: v.pipe(v.number(), v.minValue(CONSTANTS.INT48_MIN), v.maxValue(CONSTANTS.INT48_MAX)),
|
|
geometry1: v.tuple([v.number(), v.number()]),
|
|
geometry2: v.object({ x: v.number(), y: v.number() }),
|
|
halfvec: v.pipe(v.array(v.number()), v.length(3 as number)),
|
|
inet: v.string(),
|
|
integer: v.pipe(v.number(), v.minValue(CONSTANTS.INT32_MIN), v.maxValue(CONSTANTS.INT32_MAX), v.integer()),
|
|
interval: v.string(),
|
|
json: jsonSchema,
|
|
jsonb: jsonSchema,
|
|
line1: v.object({ a: v.number(), b: v.number(), c: v.number() }),
|
|
line2: v.tuple([v.number(), v.number(), v.number()]),
|
|
macaddr: v.string(),
|
|
macaddr8: v.string(),
|
|
numeric: v.string(),
|
|
point1: v.object({ x: v.number(), y: v.number() }),
|
|
point2: v.tuple([v.number(), v.number()]),
|
|
real: v.pipe(v.number(), v.minValue(CONSTANTS.INT24_MIN), v.maxValue(CONSTANTS.INT24_MAX)),
|
|
serial: v.pipe(v.number(), v.minValue(CONSTANTS.INT32_MIN), v.maxValue(CONSTANTS.INT32_MAX), v.integer()),
|
|
smallint: v.pipe(v.number(), v.minValue(CONSTANTS.INT16_MIN), v.maxValue(CONSTANTS.INT16_MAX), v.integer()),
|
|
smallserial: v.pipe(v.number(), v.minValue(CONSTANTS.INT16_MIN), v.maxValue(CONSTANTS.INT16_MAX), v.integer()),
|
|
text1: v.string(),
|
|
text2: v.enum({ a: 'a', b: 'b', c: 'c' }),
|
|
sparsevec: v.string(),
|
|
time: v.string(),
|
|
timestamp1: v.date(),
|
|
timestamp2: v.string(),
|
|
uuid: v.pipe(v.string(), v.uuid()),
|
|
varchar1: v.pipe(v.string(), v.maxLength(10 as number)),
|
|
varchar2: v.enum({ a: 'a', b: 'b', c: 'c' }),
|
|
vector: v.pipe(v.array(v.number()), v.length(3 as number)),
|
|
array1: v.array(integerSchema),
|
|
array2: v.pipe(v.array(v.array(integerSchema)), v.length(2 as number)),
|
|
array3: v.pipe(v.array(v.array(v.pipe(v.string(), v.maxLength(10 as number)))), v.length(2 as number)),
|
|
});
|
|
expectSchemaShape(t, expected).from(result);
|
|
Expect<Equal<typeof result, typeof expected>>();
|
|
});
|
|
|
|
/* Infinitely recursive type */ {
|
|
const TopLevelCondition: v.GenericSchema<TopLevelCondition> = v.custom<TopLevelCondition>(() => true);
|
|
const table = pgTable('test', {
|
|
json: json().$type<TopLevelCondition>().notNull(),
|
|
jsonb: jsonb().$type<TopLevelCondition>(),
|
|
});
|
|
const result = createSelectSchema(table);
|
|
const expected = v.object({
|
|
json: TopLevelCondition,
|
|
jsonb: v.nullable(TopLevelCondition),
|
|
});
|
|
Expect<Equal<v.InferOutput<typeof result>, v.InferOutput<typeof expected>>>();
|
|
}
|
|
|
|
/* Disallow unknown keys in table refinement - select */ {
|
|
const table = pgTable('test', { id: integer() });
|
|
// @ts-expect-error
|
|
createSelectSchema(table, { unknown: v.string() });
|
|
}
|
|
|
|
/* Disallow unknown keys in table refinement - insert */ {
|
|
const table = pgTable('test', { id: integer() });
|
|
// @ts-expect-error
|
|
createInsertSchema(table, { unknown: v.string() });
|
|
}
|
|
|
|
/* Disallow unknown keys in table refinement - update */ {
|
|
const table = pgTable('test', { id: integer() });
|
|
// @ts-expect-error
|
|
createUpdateSchema(table, { unknown: v.string() });
|
|
}
|
|
|
|
/* Disallow unknown keys in view qb - select */ {
|
|
const table = pgTable('test', { id: integer() });
|
|
const view = pgView('test').as((qb) => qb.select().from(table));
|
|
const mView = pgMaterializedView('test').as((qb) => qb.select().from(table));
|
|
const nestedSelect = pgView('test').as((qb) => qb.select({ table }).from(table));
|
|
// @ts-expect-error
|
|
createSelectSchema(view, { unknown: v.string() });
|
|
// @ts-expect-error
|
|
createSelectSchema(mView, { unknown: v.string() });
|
|
// @ts-expect-error
|
|
createSelectSchema(nestedSelect, { table: { unknown: v.string() } });
|
|
}
|
|
|
|
/* Disallow unknown keys in view columns - select */ {
|
|
const view = pgView('test', { id: integer() }).as(sql``);
|
|
const mView = pgView('test', { id: integer() }).as(sql``);
|
|
// @ts-expect-error
|
|
createSelectSchema(view, { unknown: v.string() });
|
|
// @ts-expect-error
|
|
createSelectSchema(mView, { unknown: v.string() });
|
|
}
|