wehub-resource-sync
1620 lines
41 KiB
TypeScript
1620 lines
41 KiB
TypeScript
import 'dotenv/config';
|
|
|
|
import { RDSDataClient } from '@aws-sdk/client-rds-data';
|
|
import * as dotenv from 'dotenv';
|
|
import { asc, eq, inArray, notInArray, relations, sql, TransactionRollbackError } from 'drizzle-orm';
|
|
import type { AwsDataApiPgDatabase } from 'drizzle-orm/aws-data-api/pg';
|
|
import { drizzle } from 'drizzle-orm/aws-data-api/pg';
|
|
import { migrate } from 'drizzle-orm/aws-data-api/pg/migrator';
|
|
import {
|
|
alias,
|
|
boolean,
|
|
date,
|
|
integer,
|
|
jsonb,
|
|
pgTable,
|
|
pgTableCreator,
|
|
serial,
|
|
text,
|
|
time,
|
|
timestamp,
|
|
uuid,
|
|
} from 'drizzle-orm/pg-core';
|
|
import { Resource } from 'sst';
|
|
import { afterAll, beforeAll, beforeEach, expect, expectTypeOf, test } from 'vitest';
|
|
|
|
import type { Equal } from '../utils.ts';
|
|
import { Expect, randomString } from '../utils.ts';
|
|
|
|
dotenv.config();
|
|
|
|
const ENABLE_LOGGING = false;
|
|
|
|
const usersTable = pgTable('users', {
|
|
id: serial('id').primaryKey(),
|
|
name: text('name').notNull(),
|
|
verified: boolean('verified').notNull().default(false),
|
|
jsonb: jsonb('jsonb').$type<string[]>(),
|
|
bestTexts: text('best_texts')
|
|
.array()
|
|
.default(sql`'{}'`)
|
|
.notNull(),
|
|
createdAt: timestamp('created_at', { withTimezone: true })
|
|
.notNull()
|
|
.defaultNow(),
|
|
});
|
|
|
|
const usersMigratorTable = pgTable('users12', {
|
|
id: serial('id').primaryKey(),
|
|
name: text('name').notNull(),
|
|
email: text('email').notNull(),
|
|
});
|
|
|
|
const todo = pgTable('todo', {
|
|
id: uuid('id').primaryKey(),
|
|
title: text('title').notNull(),
|
|
description: text('description'),
|
|
});
|
|
|
|
const todoRelations = relations(todo, (ctx) => ({
|
|
user: ctx.many(todoUser),
|
|
}));
|
|
|
|
const user = pgTable('user', {
|
|
id: uuid('id').primaryKey(),
|
|
email: text('email').notNull(),
|
|
});
|
|
|
|
const userRelations = relations(user, (ctx) => ({
|
|
todos: ctx.many(todoUser),
|
|
}));
|
|
|
|
const todoUser = pgTable('todo_user', {
|
|
todoId: uuid('todo_id').references(() => todo.id),
|
|
userId: uuid('user_id').references(() => user.id),
|
|
});
|
|
|
|
const todoToGroupRelations = relations(todoUser, (ctx) => ({
|
|
todo: ctx.one(todo, {
|
|
fields: [todoUser.todoId],
|
|
references: [todo.id],
|
|
}),
|
|
user: ctx.one(user, {
|
|
fields: [todoUser.userId],
|
|
references: [user.id],
|
|
}),
|
|
}));
|
|
|
|
const schema = {
|
|
todo,
|
|
todoRelations,
|
|
user,
|
|
userRelations,
|
|
todoUser,
|
|
todoToGroupRelations,
|
|
};
|
|
|
|
let db: AwsDataApiPgDatabase<typeof schema>;
|
|
|
|
beforeAll(async () => {
|
|
const rdsClient = new RDSDataClient();
|
|
|
|
db = drizzle(rdsClient, {
|
|
// @ts-ignore
|
|
database: Resource.Postgres.database,
|
|
// @ts-ignore
|
|
secretArn: Resource.Postgres.secretArn,
|
|
// @ts-ignore
|
|
resourceArn: Resource.Postgres.clusterArn,
|
|
logger: ENABLE_LOGGING,
|
|
schema,
|
|
});
|
|
});
|
|
|
|
beforeEach(async () => {
|
|
await db.execute(sql`drop schema public cascade`);
|
|
await db.execute(sql`create schema public`);
|
|
await db.execute(
|
|
sql`
|
|
create table users (
|
|
id serial primary key,
|
|
name text not null,
|
|
verified boolean not null default false,
|
|
jsonb jsonb,
|
|
best_texts text[] not null default '{}',
|
|
created_at timestamptz not null default now()
|
|
)
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
create table todo (
|
|
id uuid primary key,
|
|
title text not null,
|
|
description text
|
|
)
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
create table "user" (
|
|
id uuid primary key,
|
|
email text not null
|
|
)
|
|
|
|
`,
|
|
);
|
|
|
|
await db.execute(
|
|
sql`
|
|
create table todo_user (
|
|
todo_id uuid references todo(id),
|
|
user_id uuid references "user"(id)
|
|
)
|
|
`,
|
|
);
|
|
});
|
|
|
|
test('select all fields', async () => {
|
|
const insertResult = await db.insert(usersTable).values({ name: 'John' });
|
|
|
|
expect(insertResult.numberOfRecordsUpdated).toBe(1);
|
|
|
|
const result = await db.select().from(usersTable);
|
|
|
|
expect(result[0]!.createdAt).toBeInstanceOf(Date);
|
|
// t.assert(Math.abs(result[0]!.createdAt.getTime() - now) < 100);
|
|
expect(result).toEqual([
|
|
{
|
|
bestTexts: [],
|
|
id: 1,
|
|
name: 'John',
|
|
verified: false,
|
|
jsonb: null,
|
|
createdAt: result[0]!.createdAt,
|
|
},
|
|
]);
|
|
});
|
|
|
|
test('select sql', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const users = await db
|
|
.select({
|
|
name: sql`upper(${usersTable.name})`,
|
|
})
|
|
.from(usersTable);
|
|
|
|
expect(users).toEqual([{ name: 'JOHN' }]);
|
|
});
|
|
|
|
test('select with empty array in inArray', async () => {
|
|
await db
|
|
.insert(usersTable)
|
|
.values([{ name: 'John' }, { name: 'Jane' }, { name: 'Jane' }]);
|
|
const users = await db
|
|
.select({
|
|
name: sql`upper(${usersTable.name})`,
|
|
})
|
|
.from(usersTable)
|
|
.where(inArray(usersTable.id, []));
|
|
|
|
expect(users).toEqual([]);
|
|
});
|
|
|
|
test('select with empty array in notInArray', async () => {
|
|
await db
|
|
.insert(usersTable)
|
|
.values([{ name: 'John' }, { name: 'Jane' }, { name: 'Jane' }]);
|
|
const result = await db
|
|
.select({
|
|
name: sql`upper(${usersTable.name})`,
|
|
})
|
|
.from(usersTable)
|
|
.where(notInArray(usersTable.id, []));
|
|
|
|
expect(result).toEqual([
|
|
{ name: 'JOHN' },
|
|
{ name: 'JANE' },
|
|
{ name: 'JANE' },
|
|
]);
|
|
});
|
|
|
|
test('select typed sql', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const users = await db
|
|
.select({
|
|
name: sql<string>`upper(${usersTable.name})`,
|
|
})
|
|
.from(usersTable);
|
|
|
|
expect(users).toEqual([{ name: 'JOHN' }]);
|
|
});
|
|
|
|
test('select distinct', async () => {
|
|
const usersDistinctTable = pgTable('users_distinct', {
|
|
id: integer('id').notNull(),
|
|
name: text('name').notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${usersDistinctTable}`);
|
|
await db.execute(
|
|
sql`create table ${usersDistinctTable} (id integer, name text)`,
|
|
);
|
|
|
|
await db.insert(usersDistinctTable).values([
|
|
{ id: 1, name: 'John' },
|
|
{ id: 1, name: 'John' },
|
|
{ id: 2, name: 'John' },
|
|
{ id: 1, name: 'Jane' },
|
|
]);
|
|
const users1 = await db
|
|
.selectDistinct()
|
|
.from(usersDistinctTable)
|
|
.orderBy(usersDistinctTable.id, usersDistinctTable.name);
|
|
const users2 = await db
|
|
.selectDistinctOn([usersDistinctTable.id])
|
|
.from(usersDistinctTable)
|
|
.orderBy(usersDistinctTable.id);
|
|
const users3 = await db
|
|
.selectDistinctOn([usersDistinctTable.name], {
|
|
name: usersDistinctTable.name,
|
|
})
|
|
.from(usersDistinctTable)
|
|
.orderBy(usersDistinctTable.name);
|
|
|
|
await db.execute(sql`drop table ${usersDistinctTable}`);
|
|
|
|
expect(users1).toEqual([
|
|
{ id: 1, name: 'Jane' },
|
|
{ id: 1, name: 'John' },
|
|
{ id: 2, name: 'John' },
|
|
]);
|
|
|
|
expect(users2.length).toEqual(2);
|
|
expect(users2[0]?.id).toEqual(1);
|
|
expect(users2[1]?.id).toEqual(2);
|
|
|
|
expect(users3.length).toEqual(2);
|
|
expect(users3[0]?.name).toEqual('Jane');
|
|
expect(users3[1]?.name).toEqual('John');
|
|
});
|
|
|
|
test('insert returning sql', async () => {
|
|
const users = await db
|
|
.insert(usersTable)
|
|
.values({ name: 'John' })
|
|
.returning({
|
|
name: sql`upper(${usersTable.name})`,
|
|
});
|
|
|
|
expect(users).toEqual([{ name: 'JOHN' }]);
|
|
});
|
|
|
|
test('delete returning sql', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const users = await db
|
|
.delete(usersTable)
|
|
.where(eq(usersTable.name, 'John'))
|
|
.returning({
|
|
name: sql`upper(${usersTable.name})`,
|
|
});
|
|
|
|
expect(users).toEqual([{ name: 'JOHN' }]);
|
|
});
|
|
|
|
test('update returning sql', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const users = await db
|
|
.update(usersTable)
|
|
.set({ name: 'Jane' })
|
|
.where(eq(usersTable.name, 'John'))
|
|
.returning({
|
|
name: sql`upper(${usersTable.name})`,
|
|
});
|
|
|
|
expect(users).toEqual([{ name: 'JANE' }]);
|
|
});
|
|
|
|
test('update with returning all fields', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const users = await db
|
|
.update(usersTable)
|
|
.set({ name: 'Jane' })
|
|
.where(eq(usersTable.name, 'John'))
|
|
.returning();
|
|
|
|
expect(users[0]!.createdAt).toBeInstanceOf(Date);
|
|
// t.assert(Math.abs(users[0]!.createdAt.getTime() - now) < 100);
|
|
expect(users).toEqual([
|
|
{
|
|
id: 1,
|
|
bestTexts: [],
|
|
name: 'Jane',
|
|
verified: false,
|
|
jsonb: null,
|
|
createdAt: users[0]!.createdAt,
|
|
},
|
|
]);
|
|
});
|
|
|
|
test('update with returning partial', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const users = await db
|
|
.update(usersTable)
|
|
.set({ name: 'Jane' })
|
|
.where(eq(usersTable.name, 'John'))
|
|
.returning({
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
});
|
|
|
|
expect(users).toEqual([{ id: 1, name: 'Jane' }]);
|
|
});
|
|
|
|
test('delete with returning all fields', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const users = await db
|
|
.delete(usersTable)
|
|
.where(eq(usersTable.name, 'John'))
|
|
.returning();
|
|
|
|
expect(users[0]!.createdAt).toBeInstanceOf(Date);
|
|
// t.assert(Math.abs(users[0]!.createdAt.getTime() - now) < 100);
|
|
expect(users).toEqual([
|
|
{
|
|
bestTexts: [],
|
|
id: 1,
|
|
name: 'John',
|
|
verified: false,
|
|
jsonb: null,
|
|
createdAt: users[0]!.createdAt,
|
|
},
|
|
]);
|
|
});
|
|
|
|
test('delete with returning partial', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const users = await db
|
|
.delete(usersTable)
|
|
.where(eq(usersTable.name, 'John'))
|
|
.returning({
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
});
|
|
|
|
expect(users).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('insert + select', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const result = await db.select().from(usersTable);
|
|
expect(result).toEqual([
|
|
{
|
|
bestTexts: [],
|
|
id: 1,
|
|
name: 'John',
|
|
verified: false,
|
|
jsonb: null,
|
|
createdAt: result[0]!.createdAt,
|
|
},
|
|
]);
|
|
|
|
await db.insert(usersTable).values({ name: 'Jane' });
|
|
const result2 = await db.select().from(usersTable);
|
|
expect(result2).toEqual([
|
|
{
|
|
bestTexts: [],
|
|
id: 1,
|
|
name: 'John',
|
|
verified: false,
|
|
jsonb: null,
|
|
createdAt: result2[0]!.createdAt,
|
|
},
|
|
{
|
|
bestTexts: [],
|
|
id: 2,
|
|
name: 'Jane',
|
|
verified: false,
|
|
jsonb: null,
|
|
createdAt: result2[1]!.createdAt,
|
|
},
|
|
]);
|
|
});
|
|
|
|
test('json insert', async () => {
|
|
await db.insert(usersTable).values({ name: 'John', jsonb: ['foo', 'bar'] });
|
|
const result = await db
|
|
.select({
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
jsonb: usersTable.jsonb,
|
|
})
|
|
.from(usersTable);
|
|
|
|
expect(result).toEqual([{ id: 1, name: 'John', jsonb: ['foo', 'bar'] }]);
|
|
});
|
|
|
|
test('insert with overridden default values', async () => {
|
|
await db.insert(usersTable).values({ name: 'John', verified: true });
|
|
const result = await db.select().from(usersTable);
|
|
|
|
expect(result).toEqual([
|
|
{
|
|
bestTexts: [],
|
|
id: 1,
|
|
name: 'John',
|
|
verified: true,
|
|
jsonb: null,
|
|
createdAt: result[0]!.createdAt,
|
|
},
|
|
]);
|
|
});
|
|
|
|
test('insert many', async () => {
|
|
await db
|
|
.insert(usersTable)
|
|
.values([
|
|
{ name: 'John' },
|
|
{ name: 'Bruce', jsonb: ['foo', 'bar'] },
|
|
{ name: 'Jane' },
|
|
{ name: 'Austin', verified: true },
|
|
]);
|
|
const result = await db
|
|
.select({
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
jsonb: usersTable.jsonb,
|
|
verified: usersTable.verified,
|
|
})
|
|
.from(usersTable);
|
|
|
|
expect(result).toEqual([
|
|
{ id: 1, name: 'John', jsonb: null, verified: false },
|
|
{ id: 2, name: 'Bruce', jsonb: ['foo', 'bar'], verified: false },
|
|
{ id: 3, name: 'Jane', jsonb: null, verified: false },
|
|
{ id: 4, name: 'Austin', jsonb: null, verified: true },
|
|
]);
|
|
});
|
|
|
|
test('insert many with returning', async () => {
|
|
const result = await db
|
|
.insert(usersTable)
|
|
.values([
|
|
{ name: 'John' },
|
|
{ name: 'Bruce', jsonb: ['foo', 'bar'] },
|
|
{ name: 'Jane' },
|
|
{ name: 'Austin', verified: true },
|
|
])
|
|
.returning({
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
jsonb: usersTable.jsonb,
|
|
verified: usersTable.verified,
|
|
});
|
|
|
|
expect(result).toEqual([
|
|
{ id: 1, name: 'John', jsonb: null, verified: false },
|
|
{ id: 2, name: 'Bruce', jsonb: ['foo', 'bar'], verified: false },
|
|
{ id: 3, name: 'Jane', jsonb: null, verified: false },
|
|
{ id: 4, name: 'Austin', jsonb: null, verified: true },
|
|
]);
|
|
});
|
|
|
|
test('select with group by as field', async () => {
|
|
await db
|
|
.insert(usersTable)
|
|
.values([{ name: 'John' }, { name: 'Jane' }, { name: 'Jane' }]);
|
|
|
|
const result = await db
|
|
.select({ name: usersTable.name })
|
|
.from(usersTable)
|
|
.groupBy(usersTable.name);
|
|
|
|
expect(result).toEqual([{ name: 'Jane' }, { name: 'John' }]);
|
|
});
|
|
|
|
test('select with group by as sql', async () => {
|
|
await db
|
|
.insert(usersTable)
|
|
.values([{ name: 'John' }, { name: 'Jane' }, { name: 'Jane' }]);
|
|
|
|
const result = await db
|
|
.select({ name: usersTable.name })
|
|
.from(usersTable)
|
|
.groupBy(sql`${usersTable.name}`);
|
|
|
|
expect(result).toEqual([{ name: 'Jane' }, { name: 'John' }]);
|
|
});
|
|
|
|
test('select with group by as sql + column', async () => {
|
|
await db
|
|
.insert(usersTable)
|
|
.values([{ name: 'John' }, { name: 'Jane' }, { name: 'Jane' }]);
|
|
|
|
const result = await db
|
|
.select({ name: usersTable.name })
|
|
.from(usersTable)
|
|
.groupBy(sql`${usersTable.name}`, usersTable.id);
|
|
|
|
expect(result).toEqual([
|
|
{ name: 'Jane' },
|
|
{ name: 'Jane' },
|
|
{ name: 'John' },
|
|
]);
|
|
});
|
|
|
|
test('select with group by as column + sql', async () => {
|
|
await db
|
|
.insert(usersTable)
|
|
.values([{ name: 'John' }, { name: 'Jane' }, { name: 'Jane' }]);
|
|
|
|
const result = await db
|
|
.select({ name: usersTable.name })
|
|
.from(usersTable)
|
|
.groupBy(usersTable.id, sql`${usersTable.name}`);
|
|
|
|
expect(result).toEqual([
|
|
{ name: 'Jane' },
|
|
{ name: 'Jane' },
|
|
{ name: 'John' },
|
|
]);
|
|
});
|
|
|
|
test('select with group by complex query', async () => {
|
|
await db
|
|
.insert(usersTable)
|
|
.values([{ name: 'John' }, { name: 'Jane' }, { name: 'Jane' }]);
|
|
|
|
const result = await db
|
|
.select({ name: usersTable.name })
|
|
.from(usersTable)
|
|
.groupBy(usersTable.id, sql`${usersTable.name}`)
|
|
.orderBy(asc(usersTable.name))
|
|
.limit(1);
|
|
|
|
expect(result).toEqual([{ name: 'Jane' }]);
|
|
});
|
|
|
|
test('build query', async () => {
|
|
const query = db
|
|
.select({ id: usersTable.id, name: usersTable.name })
|
|
.from(usersTable)
|
|
.groupBy(usersTable.id, usersTable.name)
|
|
.toSQL();
|
|
|
|
expect(query).toEqual({
|
|
sql: 'select "id", "name" from "users" group by "users"."id", "users"."name"',
|
|
params: [],
|
|
// typings: []
|
|
});
|
|
});
|
|
|
|
test('insert sql', async () => {
|
|
await db.insert(usersTable).values({ name: sql`${'John'}` });
|
|
const result = await db
|
|
.select({ id: usersTable.id, name: usersTable.name })
|
|
.from(usersTable);
|
|
expect(result).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('partial join with alias', async () => {
|
|
const customerAlias = alias(usersTable, 'customer');
|
|
|
|
await db.insert(usersTable).values([
|
|
{ id: 10, name: 'Ivan' },
|
|
{ id: 11, name: 'Hans' },
|
|
]);
|
|
const result = await db
|
|
.select({
|
|
user: {
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
},
|
|
customer: {
|
|
id: customerAlias.id,
|
|
name: customerAlias.name,
|
|
},
|
|
})
|
|
.from(usersTable)
|
|
.leftJoin(customerAlias, eq(customerAlias.id, 11))
|
|
.where(eq(usersTable.id, 10));
|
|
|
|
expect(result).toEqual([
|
|
{
|
|
user: { id: 10, name: 'Ivan' },
|
|
customer: { id: 11, name: 'Hans' },
|
|
},
|
|
]);
|
|
});
|
|
|
|
test('full join with alias', async () => {
|
|
const customerAlias = alias(usersTable, 'customer');
|
|
|
|
await db.insert(usersTable).values([
|
|
{ id: 10, name: 'Ivan' },
|
|
{ id: 11, name: 'Hans' },
|
|
]);
|
|
|
|
const result = await db
|
|
.select()
|
|
.from(usersTable)
|
|
.leftJoin(customerAlias, eq(customerAlias.id, 11))
|
|
.where(eq(usersTable.id, 10));
|
|
|
|
expect(result).toEqual([
|
|
{
|
|
users: {
|
|
id: 10,
|
|
bestTexts: [],
|
|
name: 'Ivan',
|
|
verified: false,
|
|
jsonb: null,
|
|
createdAt: result[0]!.users.createdAt,
|
|
},
|
|
customer: {
|
|
bestTexts: [],
|
|
id: 11,
|
|
name: 'Hans',
|
|
verified: false,
|
|
jsonb: null,
|
|
createdAt: result[0]!.customer!.createdAt,
|
|
},
|
|
},
|
|
]);
|
|
});
|
|
|
|
test('select from alias', async () => {
|
|
const pgTable = pgTableCreator((name) => `prefixed_${name}`);
|
|
|
|
const users = pgTable('users', {
|
|
id: serial('id').primaryKey(),
|
|
name: text('name').notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${users}`);
|
|
await db.execute(
|
|
sql`create table ${users} (id serial primary key, name text not null)`,
|
|
);
|
|
|
|
const user = alias(users, 'user');
|
|
const customers = alias(users, 'customer');
|
|
|
|
await db.insert(users).values([
|
|
{ id: 10, name: 'Ivan' },
|
|
{ id: 11, name: 'Hans' },
|
|
]);
|
|
const result = await db
|
|
.select()
|
|
.from(user)
|
|
.leftJoin(customers, eq(customers.id, 11))
|
|
.where(eq(user.id, 10));
|
|
|
|
expect(result).toEqual([
|
|
{
|
|
user: {
|
|
id: 10,
|
|
name: 'Ivan',
|
|
},
|
|
customer: {
|
|
id: 11,
|
|
name: 'Hans',
|
|
},
|
|
},
|
|
]);
|
|
|
|
await db.execute(sql`drop table ${users}`);
|
|
});
|
|
|
|
test('insert with spaces', async () => {
|
|
await db.insert(usersTable).values({ name: sql`'Jo h n'` });
|
|
const result = await db
|
|
.select({ id: usersTable.id, name: usersTable.name })
|
|
.from(usersTable);
|
|
|
|
expect(result).toEqual([{ id: 1, name: 'Jo h n' }]);
|
|
});
|
|
|
|
test('prepared statement', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const statement = db
|
|
.select({
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
})
|
|
.from(usersTable)
|
|
.prepare('statement1');
|
|
const result = await statement.execute();
|
|
|
|
expect(result).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('prepared statement reuse', async () => {
|
|
const stmt = db
|
|
.insert(usersTable)
|
|
.values({
|
|
verified: true,
|
|
name: sql.placeholder('name'),
|
|
})
|
|
.prepare('stmt2');
|
|
|
|
for (let i = 0; i < 10; i++) {
|
|
await stmt.execute({ name: `John ${i}` });
|
|
}
|
|
|
|
const result = await db
|
|
.select({
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
verified: usersTable.verified,
|
|
})
|
|
.from(usersTable);
|
|
|
|
expect(result).toEqual([
|
|
{ id: 1, name: 'John 0', verified: true },
|
|
{ id: 2, name: 'John 1', verified: true },
|
|
{ id: 3, name: 'John 2', verified: true },
|
|
{ id: 4, name: 'John 3', verified: true },
|
|
{ id: 5, name: 'John 4', verified: true },
|
|
{ id: 6, name: 'John 5', verified: true },
|
|
{ id: 7, name: 'John 6', verified: true },
|
|
{ id: 8, name: 'John 7', verified: true },
|
|
{ id: 9, name: 'John 8', verified: true },
|
|
{ id: 10, name: 'John 9', verified: true },
|
|
]);
|
|
});
|
|
|
|
test('prepared statement with placeholder in .where', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
const stmt = db
|
|
.select({
|
|
id: usersTable.id,
|
|
name: usersTable.name,
|
|
})
|
|
.from(usersTable)
|
|
.where(eq(usersTable.id, sql.placeholder('id')))
|
|
.prepare('stmt3');
|
|
const result = await stmt.execute({ id: 1 });
|
|
|
|
expect(result).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('migrator : default migration strategy', async () => {
|
|
await db.execute(sql`drop table if exists all_columns`);
|
|
await db.execute(sql`drop table if exists users12`);
|
|
await db.execute(sql`drop table if exists "drizzle"."__drizzle_migrations"`);
|
|
|
|
await migrate(db, { migrationsFolder: './drizzle2/pg' });
|
|
|
|
await db.insert(usersMigratorTable).values({ name: 'John', email: 'email' });
|
|
|
|
const result = await db.select().from(usersMigratorTable);
|
|
|
|
expect(result).toEqual([{ id: 1, name: 'John', email: 'email' }]);
|
|
|
|
await db.execute(sql`drop table all_columns`);
|
|
await db.execute(sql`drop table users12`);
|
|
await db.execute(sql`drop table "drizzle"."__drizzle_migrations"`);
|
|
});
|
|
|
|
test('migrator : migrate with custom schema', async () => {
|
|
await db.execute(sql`drop table if exists all_columns`);
|
|
await db.execute(sql`drop table if exists users12`);
|
|
await db.execute(sql`drop table if exists "drizzle"."__drizzle_migrations"`);
|
|
|
|
await migrate(db, {
|
|
migrationsFolder: './drizzle2/pg',
|
|
migrationsSchema: 'custom_migrations',
|
|
});
|
|
|
|
// test if the custom migrations table was created
|
|
const { rows } = await db.execute(
|
|
sql`select * from custom_migrations."__drizzle_migrations";`,
|
|
);
|
|
expect(rows).toBeTruthy();
|
|
expect(rows!.length).toBeGreaterThan(0);
|
|
|
|
// test if the migrated table are working as expected
|
|
await db.insert(usersMigratorTable).values({ name: 'John', email: 'email' });
|
|
const result = await db.select().from(usersMigratorTable);
|
|
expect(result).toEqual([{ id: 1, name: 'John', email: 'email' }]);
|
|
|
|
await db.execute(sql`drop table all_columns`);
|
|
await db.execute(sql`drop table users12`);
|
|
await db.execute(
|
|
sql`drop table custom_migrations."__drizzle_migrations"`,
|
|
);
|
|
});
|
|
|
|
test('migrator : migrate with custom table', async () => {
|
|
const customTable = randomString();
|
|
await db.execute(sql`drop table if exists all_columns`);
|
|
await db.execute(sql`drop table if exists users12`);
|
|
await db.execute(sql`drop table if exists "drizzle"."__drizzle_migrations"`);
|
|
|
|
await migrate(db, {
|
|
migrationsFolder: './drizzle2/pg',
|
|
migrationsTable: customTable,
|
|
});
|
|
|
|
// test if the custom migrations table was created
|
|
const { rows } = await db.execute(
|
|
sql`select * from "drizzle".${sql.identifier(customTable)};`,
|
|
);
|
|
expect(rows).toBeTruthy();
|
|
expect(rows!.length).toBeGreaterThan(0);
|
|
|
|
// test if the migrated table are working as expected
|
|
await db.insert(usersMigratorTable).values({ name: 'John', email: 'email' });
|
|
const result = await db.select().from(usersMigratorTable);
|
|
expect(result).toEqual([{ id: 1, name: 'John', email: 'email' }]);
|
|
|
|
await db.execute(sql`drop table all_columns`);
|
|
await db.execute(sql`drop table users12`);
|
|
await db.execute(sql`drop table "drizzle".${sql.identifier(customTable)}`);
|
|
});
|
|
|
|
test('migrator : migrate with custom table and custom schema', async () => {
|
|
const customTable = randomString();
|
|
await db.execute(sql`drop table if exists all_columns`);
|
|
await db.execute(sql`drop table if exists users12`);
|
|
await db.execute(sql`drop table if exists "drizzle"."__drizzle_migrations"`);
|
|
|
|
await migrate(db, {
|
|
migrationsFolder: './drizzle2/pg',
|
|
migrationsTable: customTable,
|
|
migrationsSchema: 'custom_migrations',
|
|
});
|
|
|
|
// test if the custom migrations table was created
|
|
const { rows } = await db.execute(
|
|
sql`select * from custom_migrations.${
|
|
sql.identifier(
|
|
customTable,
|
|
)
|
|
};`,
|
|
);
|
|
expect(rows).toBeTruthy();
|
|
expect(rows!.length).toBeGreaterThan(0);
|
|
|
|
// test if the migrated table are working as expected
|
|
await db.insert(usersMigratorTable).values({ name: 'John', email: 'email' });
|
|
const result = await db.select().from(usersMigratorTable);
|
|
expect(result).toEqual([{ id: 1, name: 'John', email: 'email' }]);
|
|
|
|
await db.execute(sql`drop table all_columns`);
|
|
await db.execute(sql`drop table users12`);
|
|
await db.execute(
|
|
sql`drop table custom_migrations.${
|
|
sql.identifier(
|
|
customTable,
|
|
)
|
|
}`,
|
|
);
|
|
});
|
|
|
|
test('insert via db.execute + select via db.execute', async () => {
|
|
await db.execute(
|
|
sql`insert into ${usersTable} (${
|
|
sql.identifier(
|
|
usersTable.name.name,
|
|
)
|
|
}) values (${'John'})`,
|
|
);
|
|
|
|
const result = await db.execute<{ id: number; name: string }>(
|
|
sql`select id, name from "users"`,
|
|
);
|
|
expectTypeOf(result.rows).toEqualTypeOf<{ id: number; name: string }[]>();
|
|
expect(result.rows).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('insert via db.execute + returning', async () => {
|
|
const inserted = await db.execute(
|
|
sql`insert into ${usersTable} (${
|
|
sql.identifier(
|
|
usersTable.name.name,
|
|
)
|
|
}) values (${'John'}) returning ${usersTable.id}, ${usersTable.name}`,
|
|
);
|
|
expect(inserted.rows).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('insert via db.execute w/ query builder', async () => {
|
|
const inserted = await db.execute(
|
|
db
|
|
.insert(usersTable)
|
|
.values({ name: 'John' })
|
|
.returning({ id: usersTable.id, name: usersTable.name }),
|
|
);
|
|
expect(inserted.rows).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('build query insert with onConflict do update', async () => {
|
|
const query = db
|
|
.insert(usersTable)
|
|
.values({ name: 'John', jsonb: ['foo', 'bar'] })
|
|
.onConflictDoUpdate({ target: usersTable.id, set: { name: 'John1' } })
|
|
.toSQL();
|
|
|
|
expect(query).toEqual({
|
|
sql:
|
|
'insert into "users" ("id", "name", "verified", "jsonb", "best_texts", "created_at") values (default, :1, default, :2, default, default) on conflict ("id") do update set "name" = :3',
|
|
params: ['John', '["foo","bar"]', 'John1'],
|
|
// typings: ['none', 'json', 'none']
|
|
});
|
|
});
|
|
|
|
test('build query insert with onConflict do update / multiple columns', async () => {
|
|
const query = db
|
|
.insert(usersTable)
|
|
.values({ name: 'John', jsonb: ['foo', 'bar'] })
|
|
.onConflictDoUpdate({
|
|
target: [usersTable.id, usersTable.name],
|
|
set: { name: 'John1' },
|
|
})
|
|
.toSQL();
|
|
|
|
expect(query).toEqual({
|
|
sql:
|
|
'insert into "users" ("id", "name", "verified", "jsonb", "best_texts", "created_at") values (default, :1, default, :2, default, default) on conflict ("id","name") do update set "name" = :3',
|
|
params: ['John', '["foo","bar"]', 'John1'],
|
|
// typings: ['none', 'json', 'none']
|
|
});
|
|
});
|
|
|
|
test('build query insert with onConflict do nothing', async () => {
|
|
const query = db
|
|
.insert(usersTable)
|
|
.values({ name: 'John', jsonb: ['foo', 'bar'] })
|
|
.onConflictDoNothing()
|
|
.toSQL();
|
|
|
|
expect(query).toEqual({
|
|
sql:
|
|
'insert into "users" ("id", "name", "verified", "jsonb", "best_texts", "created_at") values (default, :1, default, :2, default, default) on conflict do nothing',
|
|
params: ['John', '["foo","bar"]'],
|
|
// typings: ['none', 'json']
|
|
});
|
|
});
|
|
|
|
test('build query insert with onConflict do nothing + target', async () => {
|
|
const query = db
|
|
.insert(usersTable)
|
|
.values({ name: 'John', jsonb: ['foo', 'bar'] })
|
|
.onConflictDoNothing({ target: usersTable.id })
|
|
.toSQL();
|
|
|
|
expect(query).toEqual({
|
|
sql:
|
|
'insert into "users" ("id", "name", "verified", "jsonb", "best_texts", "created_at") values (default, :1, default, :2, default, default) on conflict ("id") do nothing',
|
|
params: ['John', '["foo","bar"]'],
|
|
// typings: ['none', 'json']
|
|
});
|
|
});
|
|
|
|
test('insert with onConflict do update', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
|
|
await db
|
|
.insert(usersTable)
|
|
.values({ id: 1, name: 'John' })
|
|
.onConflictDoUpdate({ target: usersTable.id, set: { name: 'John1' } });
|
|
|
|
const res = await db
|
|
.select({ id: usersTable.id, name: usersTable.name })
|
|
.from(usersTable)
|
|
.where(eq(usersTable.id, 1));
|
|
|
|
expect(res).toEqual([{ id: 1, name: 'John1' }]);
|
|
});
|
|
|
|
test('insert with onConflict do nothing', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
|
|
await db
|
|
.insert(usersTable)
|
|
.values({ id: 1, name: 'John' })
|
|
.onConflictDoNothing();
|
|
|
|
const res = await db
|
|
.select({ id: usersTable.id, name: usersTable.name })
|
|
.from(usersTable)
|
|
.where(eq(usersTable.id, 1));
|
|
|
|
expect(res).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('insert with onConflict do nothing + target', async () => {
|
|
await db.insert(usersTable).values({ name: 'John' });
|
|
|
|
await db
|
|
.insert(usersTable)
|
|
.values({ id: 1, name: 'John' })
|
|
.onConflictDoNothing({ target: usersTable.id });
|
|
|
|
const res = await db
|
|
.select({ id: usersTable.id, name: usersTable.name })
|
|
.from(usersTable)
|
|
.where(eq(usersTable.id, 1));
|
|
|
|
expect(res).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('transaction', async () => {
|
|
const users = pgTable('users_transactions', {
|
|
id: serial('id').primaryKey(),
|
|
balance: integer('balance').notNull(),
|
|
});
|
|
const products = pgTable('products_transactions', {
|
|
id: serial('id').primaryKey(),
|
|
price: integer('price').notNull(),
|
|
stock: integer('stock').notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${users}`);
|
|
await db.execute(sql`drop table if exists ${products}`);
|
|
|
|
await db.execute(
|
|
sql`create table users_transactions (id serial not null primary key, balance integer not null)`,
|
|
);
|
|
await db.execute(
|
|
sql`create table products_transactions (id serial not null primary key, price integer not null, stock integer not null)`,
|
|
);
|
|
|
|
const user = await db
|
|
.insert(users)
|
|
.values({ balance: 100 })
|
|
.returning()
|
|
.then((rows) => rows[0]!);
|
|
const product = await db
|
|
.insert(products)
|
|
.values({ price: 10, stock: 10 })
|
|
.returning()
|
|
.then((rows) => rows[0]!);
|
|
|
|
await db.transaction(async (tx) => {
|
|
await tx
|
|
.update(users)
|
|
.set({ balance: user.balance - product.price })
|
|
.where(eq(users.id, user.id));
|
|
await tx
|
|
.update(products)
|
|
.set({ stock: product.stock - 1 })
|
|
.where(eq(products.id, product.id));
|
|
});
|
|
|
|
const result = await db.select().from(users);
|
|
|
|
expect(result).toEqual([{ id: 1, balance: 90 }]);
|
|
|
|
await db.execute(sql`drop table ${users}`);
|
|
await db.execute(sql`drop table ${products}`);
|
|
});
|
|
|
|
test('transaction rollback', async () => {
|
|
const users = pgTable('users_transactions_rollback', {
|
|
id: serial('id').primaryKey(),
|
|
balance: integer('balance').notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${users}`);
|
|
|
|
await db.execute(
|
|
sql`create table users_transactions_rollback (id serial not null primary key, balance integer not null)`,
|
|
);
|
|
|
|
await expect(
|
|
db.transaction(async (tx) => {
|
|
await tx.insert(users).values({ balance: 100 });
|
|
tx.rollback();
|
|
}),
|
|
).rejects.toThrowError(TransactionRollbackError);
|
|
|
|
const result = await db.select().from(users);
|
|
|
|
expect(result).toEqual([]);
|
|
|
|
await db.execute(sql`drop table ${users}`);
|
|
});
|
|
|
|
test('nested transaction', async () => {
|
|
const users = pgTable('users_nested_transactions', {
|
|
id: serial('id').primaryKey(),
|
|
balance: integer('balance').notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${users}`);
|
|
|
|
await db.execute(
|
|
sql`create table users_nested_transactions (id serial not null primary key, balance integer not null)`,
|
|
);
|
|
|
|
await db.transaction(async (tx) => {
|
|
await tx.insert(users).values({ balance: 100 });
|
|
|
|
await tx.transaction(async (tx) => {
|
|
await tx.update(users).set({ balance: 200 });
|
|
});
|
|
});
|
|
|
|
const result = await db.select().from(users);
|
|
|
|
expect(result).toEqual([{ id: 1, balance: 200 }]);
|
|
|
|
await db.execute(sql`drop table ${users}`);
|
|
});
|
|
|
|
test('nested transaction rollback', async () => {
|
|
const users = pgTable('users_nested_transactions_rollback', {
|
|
id: serial('id').primaryKey(),
|
|
balance: integer('balance').notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${users}`);
|
|
|
|
await db.execute(
|
|
sql`create table users_nested_transactions_rollback (id serial not null primary key, balance integer not null)`,
|
|
);
|
|
|
|
await db.transaction(async (tx) => {
|
|
await tx.insert(users).values({ balance: 100 });
|
|
|
|
await expect(
|
|
tx.transaction(async (tx2) => {
|
|
await tx2.update(users).set({ balance: 200 });
|
|
tx2.rollback();
|
|
}),
|
|
).rejects.toThrowError(TransactionRollbackError);
|
|
});
|
|
|
|
const result = await db.select().from(users);
|
|
|
|
expect(result).toEqual([{ id: 1, balance: 100 }]);
|
|
|
|
await db.execute(sql`drop table ${users}`);
|
|
});
|
|
|
|
test('select from raw sql', async () => {
|
|
const result = await db.execute(sql`select 1 as id, 'John' as name`);
|
|
|
|
expect(result.rows).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('select from raw sql with mapped values', async () => {
|
|
const result = await db
|
|
.select({
|
|
id: sql<number>`id`,
|
|
name: sql<string>`name`,
|
|
})
|
|
.from(sql`(select 1 as id, 'John' as name) as users`);
|
|
|
|
expect(result).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('insert with array values works', async () => {
|
|
const bestTexts = ['text1', 'text2', 'text3'];
|
|
const [insertResult] = await db
|
|
.insert(usersTable)
|
|
.values({
|
|
name: 'John',
|
|
bestTexts,
|
|
})
|
|
.returning();
|
|
|
|
expect(insertResult?.bestTexts).toEqual(bestTexts);
|
|
});
|
|
|
|
test('update with array values works', async () => {
|
|
const [newUser] = await db
|
|
.insert(usersTable)
|
|
.values({ name: 'John' })
|
|
.returning();
|
|
|
|
const bestTexts = ['text4', 'text5', 'text6'];
|
|
const [insertResult] = await db
|
|
.update(usersTable)
|
|
.set({
|
|
bestTexts,
|
|
})
|
|
.where(eq(usersTable.id, newUser!.id))
|
|
.returning();
|
|
|
|
expect(insertResult?.bestTexts).toEqual(bestTexts);
|
|
});
|
|
|
|
test('insert with array values works', async () => {
|
|
const bestTexts = ['text1', 'text2', 'text3'];
|
|
const [insertResult] = await db
|
|
.insert(usersTable)
|
|
.values({
|
|
name: 'John',
|
|
bestTexts,
|
|
})
|
|
.returning();
|
|
|
|
expect(insertResult?.bestTexts).toEqual(bestTexts);
|
|
});
|
|
|
|
test('update with array values works', async () => {
|
|
const [newUser] = await db
|
|
.insert(usersTable)
|
|
.values({ name: 'John' })
|
|
.returning();
|
|
|
|
const bestTexts = ['text4', 'text5', 'text6'];
|
|
const [insertResult] = await db
|
|
.update(usersTable)
|
|
.set({
|
|
bestTexts,
|
|
})
|
|
.where(eq(usersTable.id, newUser!.id))
|
|
.returning();
|
|
|
|
expect(insertResult?.bestTexts).toEqual(bestTexts);
|
|
});
|
|
|
|
test('insert with array values works', async () => {
|
|
const bestTexts = ['text1', 'text2', 'text3'];
|
|
const [insertResult] = await db
|
|
.insert(usersTable)
|
|
.values({
|
|
name: 'John',
|
|
bestTexts,
|
|
})
|
|
.returning();
|
|
|
|
expect(insertResult?.bestTexts).toEqual(bestTexts);
|
|
});
|
|
|
|
test('update with array values works', async () => {
|
|
const [newUser] = await db
|
|
.insert(usersTable)
|
|
.values({ name: 'John' })
|
|
.returning();
|
|
|
|
const bestTexts = ['text4', 'text5', 'text6'];
|
|
const [insertResult] = await db
|
|
.update(usersTable)
|
|
.set({
|
|
bestTexts,
|
|
})
|
|
.where(eq(usersTable.id, newUser!.id))
|
|
.returning();
|
|
|
|
expect(insertResult?.bestTexts).toEqual(bestTexts);
|
|
});
|
|
|
|
test('all date and time columns', async () => {
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
dateString: date('date_string', { mode: 'string' }).notNull(),
|
|
time: time('time', { precision: 3 }).notNull(),
|
|
datetime: timestamp('datetime').notNull(),
|
|
// datetimeWTZ: timestamp('datetime_wtz', { withTimezone: true }).notNull(),
|
|
datetimeString: timestamp('datetime_string', { mode: 'string' }).notNull(),
|
|
datetimeFullPrecision: timestamp('datetime_full_precision', {
|
|
precision: 6,
|
|
mode: 'string',
|
|
}).notNull(),
|
|
// datetimeWTZString: timestamp('datetime_wtz_string', { withTimezone: true, mode: 'string' }).notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
|
|
await db.execute(sql`
|
|
create table ${table} (
|
|
id serial primary key,
|
|
date_string date not null,
|
|
time time(3) not null,
|
|
datetime timestamp not null,
|
|
-- datetime_wtz timestamp with time zone not null,
|
|
datetime_string timestamp not null,
|
|
datetime_full_precision timestamp(6) not null
|
|
-- datetime_wtz_string timestamp with time zone not null
|
|
)
|
|
`);
|
|
|
|
const someDatetime = new Date('2022-01-01T00:00:00.123Z');
|
|
const fullPrecision = '2022-01-01T00:00:00.123456';
|
|
const someTime = '23:23:12.432';
|
|
|
|
await db.insert(table).values({
|
|
dateString: '2022-01-01',
|
|
time: someTime,
|
|
datetime: someDatetime,
|
|
// datetimeWTZ: someDatetime,
|
|
datetimeString: '2022-01-01T00:00:00.123Z',
|
|
datetimeFullPrecision: fullPrecision,
|
|
// datetimeWTZString: '2022-01-01T00:00:00.123Z',
|
|
});
|
|
|
|
const result = await db.select().from(table);
|
|
|
|
Expect<
|
|
Equal<
|
|
{
|
|
id: number;
|
|
dateString: string;
|
|
time: string;
|
|
datetime: Date;
|
|
// datetimeWTZ: Date;
|
|
datetimeString: string;
|
|
datetimeFullPrecision: string;
|
|
// datetimeWTZString: string;
|
|
}[],
|
|
typeof result
|
|
>
|
|
>;
|
|
|
|
Expect<
|
|
Equal<
|
|
{
|
|
dateString: string;
|
|
time: string;
|
|
datetime: Date;
|
|
// datetimeWTZ: Date;
|
|
datetimeString: string;
|
|
datetimeFullPrecision: string;
|
|
// datetimeWTZString: string;
|
|
id?: number | undefined;
|
|
},
|
|
typeof table.$inferInsert
|
|
>
|
|
>;
|
|
|
|
expect(result).toEqual([
|
|
{
|
|
id: 1,
|
|
dateString: '2022-01-01',
|
|
time: someTime,
|
|
datetime: someDatetime,
|
|
// datetimeWTZ: someDatetime,
|
|
datetimeString: '2022-01-01 00:00:00.123',
|
|
datetimeFullPrecision: fullPrecision.replace('T', ' ').replace('Z', ''),
|
|
// datetimeWTZString: '2022-01-01 00:00:00.123+00',
|
|
},
|
|
]);
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test.skip('all date and time columns with timezone', async () => {
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestamp: timestamp('timestamp_string', {
|
|
mode: 'string',
|
|
withTimezone: true,
|
|
precision: 6,
|
|
}).notNull(),
|
|
timestampAsDate: timestamp('timestamp_date', {
|
|
withTimezone: true,
|
|
precision: 3,
|
|
}).notNull(),
|
|
timestampTimeZones: timestamp('timestamp_date_2', {
|
|
withTimezone: true,
|
|
precision: 3,
|
|
}).notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
|
|
await db.execute(sql`
|
|
create table ${table} (
|
|
id serial primary key,
|
|
timestamp_string timestamp(6) with time zone not null,
|
|
timestamp_date timestamp(3) with time zone not null,
|
|
timestamp_date_2 timestamp(3) with time zone not null
|
|
)
|
|
`);
|
|
|
|
const timestampString = '2022-01-01 00:00:00.123456-0200';
|
|
const timestampDate = new Date();
|
|
const timestampDateWTZ = new Date('2022-01-01 00:00:00.123 +0500');
|
|
|
|
const timestampString2 = '2022-01-01 00:00:00.123456-0400';
|
|
const timestampDate2 = new Date();
|
|
const timestampDateWTZ2 = new Date('2022-01-01 00:00:00.123 +0200');
|
|
|
|
await db.insert(table).values([
|
|
{
|
|
timestamp: timestampString,
|
|
timestampAsDate: timestampDate,
|
|
timestampTimeZones: timestampDateWTZ,
|
|
},
|
|
{
|
|
timestamp: timestampString2,
|
|
timestampAsDate: timestampDate2,
|
|
timestampTimeZones: timestampDateWTZ2,
|
|
},
|
|
]);
|
|
|
|
const result = await db.select().from(table);
|
|
const result2 = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
timestamp_date: string;
|
|
timestamp_date_2: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
// Whatever you put in, you get back when you're using the date mode
|
|
// But when using the string mode, postgres returns a string transformed into UTC
|
|
expect(result).toEqual([
|
|
{
|
|
id: 1,
|
|
timestamp: '2022-01-01 02:00:00.123456+00',
|
|
timestampAsDate: timestampDate,
|
|
timestampTimeZones: timestampDateWTZ,
|
|
},
|
|
{
|
|
id: 2,
|
|
timestamp: '2022-01-01 04:00:00.123456+00',
|
|
timestampAsDate: timestampDate2,
|
|
timestampTimeZones: timestampDateWTZ2,
|
|
},
|
|
]);
|
|
|
|
expect(result2.rows).toEqual([
|
|
{
|
|
id: 1,
|
|
timestamp_string: '2022-01-01 02:00:00.123456+00',
|
|
timestamp_date: timestampDate.toISOString().replace('T', ' ').replace('Z', '') + '+00',
|
|
timestamp_date_2: timestampDateWTZ.toISOString().replace('T', ' ').replace('Z', '')
|
|
+ '+00',
|
|
},
|
|
{
|
|
id: 2,
|
|
timestamp_string: '2022-01-01 04:00:00.123456+00',
|
|
timestamp_date: timestampDate2.toISOString().replace('T', ' ').replace('Z', '') + '+00',
|
|
timestamp_date_2: timestampDateWTZ2.toISOString().replace('T', ' ').replace('Z', '')
|
|
+ '+00',
|
|
},
|
|
]);
|
|
|
|
expect(result[0]?.timestampTimeZones.getTime()).toEqual(
|
|
new Date((result2.rows?.[0] as any).timestamp_date_2 as any).getTime(),
|
|
);
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test('all date and time columns without timezone', async () => {
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestampString: timestamp('timestamp_string', {
|
|
mode: 'string',
|
|
precision: 6,
|
|
}).notNull(),
|
|
timestampString2: timestamp('timestamp_string2', {
|
|
precision: 3,
|
|
mode: 'string',
|
|
}).notNull(),
|
|
timestampDate: timestamp('timestamp_date', { precision: 3 }).notNull(),
|
|
});
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
|
|
await db.execute(sql`
|
|
create table ${table} (
|
|
id serial primary key,
|
|
timestamp_string timestamp(6) not null,
|
|
timestamp_string2 timestamp(3) not null,
|
|
timestamp_date timestamp(3) not null
|
|
)
|
|
`);
|
|
|
|
const timestampString = '2022-01-01 00:00:00.123456';
|
|
// const timestampString2 = '2022-01-02 00:00:00.123 -0300';
|
|
const timestampString2 = '2022-01-02 00:00:00.123';
|
|
const timestampDate = new Date('2022-01-01 00:00:00.123Z');
|
|
|
|
const timestampString_2 = '2022-01-01 00:00:00.123456';
|
|
// const timestampString2_2 = '2022-01-01 00:00:00.123 -0300';
|
|
const timestampString2_2 = '2022-01-01 00:00:00.123';
|
|
// const timestampDate2 = new Date('2022-01-01 00:00:00.123 +0200');
|
|
const timestampDate2 = new Date('2022-01-01 00:00:00.123');
|
|
|
|
await db.insert(table).values([
|
|
{ timestampString, timestampString2, timestampDate },
|
|
{
|
|
timestampString: timestampString_2,
|
|
timestampString2: timestampString2_2,
|
|
timestampDate: timestampDate2,
|
|
},
|
|
]);
|
|
|
|
const result = await db.select().from(table);
|
|
const result2 = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
timestamp_string2: string;
|
|
timestamp_date: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
// Whatever you put in, you get back when you're using the date mode
|
|
// But when using the string mode, postgres returns a string transformed into UTC
|
|
expect(result).toEqual([
|
|
{
|
|
id: 1,
|
|
timestampString: timestampString,
|
|
timestampString2: '2022-01-02 00:00:00.123',
|
|
timestampDate: timestampDate,
|
|
},
|
|
{
|
|
id: 2,
|
|
timestampString: timestampString_2,
|
|
timestampString2: '2022-01-01 00:00:00.123',
|
|
timestampDate: timestampDate2,
|
|
},
|
|
]);
|
|
|
|
expect(result2.rows).toEqual([
|
|
{
|
|
id: 1,
|
|
timestamp_string: timestampString,
|
|
timestamp_string2: '2022-01-02 00:00:00.123',
|
|
timestamp_date: timestampDate
|
|
.toISOString()
|
|
.replace('T', ' ')
|
|
.replace('Z', ''),
|
|
},
|
|
{
|
|
id: 2,
|
|
timestamp_string: timestampString_2,
|
|
timestamp_string2: '2022-01-01 00:00:00.123',
|
|
timestamp_date: timestampDate2
|
|
.toISOString()
|
|
.replace('T', ' ')
|
|
.replace('Z', ''),
|
|
},
|
|
]);
|
|
|
|
expect((result2.rows?.[0] as any).timestamp_string).toEqual(
|
|
'2022-01-01 00:00:00.123456',
|
|
);
|
|
// need to add the 'Z', otherwise javascript assumes it's in local time
|
|
expect(
|
|
new Date(((result2.rows?.[0] as any).timestamp_date + 'Z') as any).getTime(),
|
|
).toEqual(timestampDate.getTime());
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test('Typehints mix for RQB', async () => {
|
|
const uuid = 'd997d46d-5769-4c78-9a35-93acadbe6076';
|
|
|
|
const res = await db.query.user.findMany({
|
|
where: eq(user.id, uuid),
|
|
with: {
|
|
todos: {
|
|
with: {
|
|
todo: true,
|
|
},
|
|
},
|
|
},
|
|
});
|
|
|
|
expect(res).toStrictEqual([]);
|
|
});
|
|
|
|
test('Typehints mix for findFirst', async () => {
|
|
const uuid = 'd997d46d-5769-4c78-9a35-93acadbe6076';
|
|
|
|
await db.insert(user).values({ id: uuid, email: 'd' });
|
|
|
|
const res = await db.query.user.findFirst({
|
|
where: eq(user.id, uuid),
|
|
});
|
|
|
|
expect(res).toStrictEqual({ id: 'd997d46d-5769-4c78-9a35-93acadbe6076', email: 'd' });
|
|
});
|
|
|
|
afterAll(async () => {
|
|
await db.execute(sql`drop table if exists "users"`);
|
|
await db.execute(sql`drop table if exists "todo_user"`);
|
|
await db.execute(sql`drop table if exists "user"`);
|
|
await db.execute(sql`drop table if exists "todo"`);
|
|
await db.execute(sql`drop table if exists "drizzle"."__drizzle_migrations"`);
|
|
});
|