wehub-resource-sync
513 lines
15 KiB
TypeScript
513 lines
15 KiB
TypeScript
import retry from 'async-retry';
|
|
import { sql } from 'drizzle-orm';
|
|
import { pgTable, serial, timestamp } from 'drizzle-orm/pg-core';
|
|
import type { PgRemoteDatabase } from 'drizzle-orm/pg-proxy';
|
|
import { drizzle as proxyDrizzle } from 'drizzle-orm/pg-proxy';
|
|
import { migrate } from 'drizzle-orm/pg-proxy/migrator';
|
|
import * as pg from 'pg';
|
|
import { afterAll, beforeAll, beforeEach, expect, test } from 'vitest';
|
|
import { skipTests } from '~/common';
|
|
import { createDockerDB, tests, usersMigratorTable, usersTable } from './pg-common';
|
|
import { TestCache, TestGlobalCache, tests as cacheTests } from './pg-common-cache';
|
|
|
|
// eslint-disable-next-line drizzle-internal/require-entity-kind
|
|
class ServerSimulator {
|
|
constructor(private db: pg.Client) {
|
|
const { types } = pg;
|
|
|
|
types.setTypeParser(types.builtins.TIMESTAMPTZ, (val) => val);
|
|
types.setTypeParser(types.builtins.TIMESTAMP, (val) => val);
|
|
types.setTypeParser(types.builtins.DATE, (val) => val);
|
|
types.setTypeParser(types.builtins.INTERVAL, (val) => val);
|
|
types.setTypeParser(1231, (val) => val);
|
|
types.setTypeParser(1115, (val) => val);
|
|
types.setTypeParser(1185, (val) => val);
|
|
types.setTypeParser(1187, (val) => val);
|
|
types.setTypeParser(1182, (val) => val);
|
|
}
|
|
|
|
async query(sql: string, params: any[], method: 'all' | 'execute') {
|
|
if (method === 'all') {
|
|
try {
|
|
const result = await this.db.query({
|
|
text: sql,
|
|
values: params,
|
|
rowMode: 'array',
|
|
});
|
|
|
|
return { data: result.rows as any };
|
|
} catch (e: any) {
|
|
return { error: e };
|
|
}
|
|
} else if (method === 'execute') {
|
|
try {
|
|
const result = await this.db.query({
|
|
text: sql,
|
|
values: params,
|
|
});
|
|
|
|
return { data: result.rows as any };
|
|
} catch (e: any) {
|
|
return { error: e };
|
|
}
|
|
} else {
|
|
return { error: 'Unknown method value' };
|
|
}
|
|
}
|
|
|
|
async migrations(queries: string[]) {
|
|
await this.db.query('BEGIN');
|
|
try {
|
|
for (const query of queries) {
|
|
await this.db.query(query);
|
|
}
|
|
await this.db.query('COMMIT');
|
|
} catch (e) {
|
|
await this.db.query('ROLLBACK');
|
|
throw e;
|
|
}
|
|
|
|
return {};
|
|
}
|
|
}
|
|
|
|
const ENABLE_LOGGING = false;
|
|
|
|
let db: PgRemoteDatabase;
|
|
let dbGlobalCached: PgRemoteDatabase;
|
|
let cachedDb: PgRemoteDatabase;
|
|
let client: pg.Client;
|
|
let serverSimulator: ServerSimulator;
|
|
|
|
beforeAll(async () => {
|
|
let connectionString;
|
|
if (process.env['PG_CONNECTION_STRING']) {
|
|
connectionString = process.env['PG_CONNECTION_STRING'];
|
|
} else {
|
|
const { connectionString: conStr } = await createDockerDB();
|
|
connectionString = conStr;
|
|
}
|
|
client = await retry(async () => {
|
|
client = new pg.Client(connectionString);
|
|
await client.connect();
|
|
return client;
|
|
}, {
|
|
retries: 20,
|
|
factor: 1,
|
|
minTimeout: 250,
|
|
maxTimeout: 250,
|
|
randomize: false,
|
|
onRetry() {
|
|
client?.end();
|
|
},
|
|
});
|
|
serverSimulator = new ServerSimulator(client);
|
|
const proxyHandler = async (sql: string, params: any[], method: any) => {
|
|
try {
|
|
const response = await serverSimulator.query(sql, params, method);
|
|
|
|
if (response.error !== undefined) {
|
|
throw response.error;
|
|
}
|
|
|
|
return { rows: response.data };
|
|
} catch (e: any) {
|
|
console.error('Error from pg proxy server:', e.message);
|
|
throw e;
|
|
}
|
|
};
|
|
db = proxyDrizzle(proxyHandler, {
|
|
logger: ENABLE_LOGGING,
|
|
});
|
|
|
|
cachedDb = proxyDrizzle(proxyHandler, { logger: ENABLE_LOGGING, cache: new TestCache() });
|
|
dbGlobalCached = proxyDrizzle(proxyHandler, { logger: ENABLE_LOGGING, cache: new TestGlobalCache() });
|
|
});
|
|
|
|
afterAll(async () => {
|
|
await client?.end();
|
|
});
|
|
|
|
beforeEach((ctx) => {
|
|
ctx.pg = {
|
|
db,
|
|
};
|
|
ctx.cachedPg = {
|
|
db: cachedDb,
|
|
dbGlobalCached,
|
|
};
|
|
});
|
|
|
|
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"`);
|
|
|
|
// './drizzle2/pg-proxy/first' ??
|
|
await migrate(db, async (queries) => {
|
|
try {
|
|
await serverSimulator.migrations(queries);
|
|
} catch (e) {
|
|
console.error(e);
|
|
throw new Error('Proxy server cannot run migrations');
|
|
}
|
|
}, { 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('all date and time columns without timezone first case mode string', async () => {
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestamp: timestamp('timestamp_string', { mode: 'string', precision: 6 }).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
|
|
)
|
|
`);
|
|
|
|
// 1. Insert date in string format without timezone in it
|
|
await db.insert(table).values([
|
|
{ timestamp: '2022-01-01 02:00:00.123456' },
|
|
]);
|
|
|
|
// 2, Select in string format and check that values are the same
|
|
const result = await db.select().from(table);
|
|
|
|
expect(result).toEqual([{ id: 1, timestamp: '2022-01-01 02:00:00.123456' }]);
|
|
|
|
// 3. Select as raw query and check that values are the same
|
|
const result2 = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
expect(result2).toEqual([{ id: 1, timestamp_string: '2022-01-01 02:00:00.123456' }]);
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test('all date and time columns without timezone second case mode string', async () => {
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestamp: timestamp('timestamp_string', { mode: 'string', precision: 6 }).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
|
|
)
|
|
`);
|
|
|
|
// 1. Insert date in string format with timezone in it
|
|
await db.insert(table).values([
|
|
{ timestamp: '2022-01-01T02:00:00.123456-02' },
|
|
]);
|
|
|
|
// 2, Select as raw query and check that values are the same
|
|
const result = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
expect(result).toEqual([{ id: 1, timestamp_string: '2022-01-01 02:00:00.123456' }]);
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test('all date and time columns without timezone third case mode date', async () => {
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestamp: timestamp('timestamp_string', { mode: '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(3) not null
|
|
)
|
|
`);
|
|
|
|
const insertedDate = new Date('2022-01-01 20:00:00.123+04');
|
|
|
|
// 1. Insert date as new date
|
|
await db.insert(table).values([
|
|
{ timestamp: insertedDate },
|
|
]);
|
|
|
|
// 2, Select as raw query as string
|
|
const result = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
// 3. Compare both dates using orm mapping - Need to add 'Z' to tell JS that it is UTC
|
|
expect(new Date(result[0]!.timestamp_string + 'Z').getTime()).toBe(insertedDate.getTime());
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test('test mode string for timestamp with timezone', async () => {
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestamp: timestamp('timestamp_string', { mode: 'string', withTimezone: true, precision: 6 }).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
|
|
)
|
|
`);
|
|
|
|
const timestampString = '2022-01-01 00:00:00.123456-0200';
|
|
|
|
// 1. Insert date in string format with timezone in it
|
|
await db.insert(table).values([
|
|
{ timestamp: timestampString },
|
|
]);
|
|
|
|
// 2. Select date in string format and check that the values are the same
|
|
const result = await db.select().from(table);
|
|
|
|
// 2.1 Notice that postgres will return the date in UTC, but it is exactly the same
|
|
expect(result).toEqual([{ id: 1, timestamp: '2022-01-01 02:00:00.123456+00' }]);
|
|
|
|
// 3. Select as raw query and checke that values are the same
|
|
const result2 = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
// 3.1 Notice that postgres will return the date in UTC, but it is exactlt the same
|
|
expect(result2).toEqual([{ id: 1, timestamp_string: '2022-01-01 02:00:00.123456+00' }]);
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test('test mode date for timestamp with timezone', async () => {
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestamp: timestamp('timestamp_string', { mode: 'date', 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(3) with time zone not null
|
|
)
|
|
`);
|
|
|
|
const timestampString = new Date('2022-01-01 00:00:00.456-0200');
|
|
|
|
// 1. Insert date in string format with timezone in it
|
|
await db.insert(table).values([
|
|
{ timestamp: timestampString },
|
|
]);
|
|
|
|
// 2. Select date in string format and check that the values are the same
|
|
const result = await db.select().from(table);
|
|
|
|
// 2.1 Notice that postgres will return the date in UTC, but it is exactly the same
|
|
expect(result).toEqual([{ id: 1, timestamp: timestampString }]);
|
|
|
|
// 3. Select as raw query and checke that values are the same
|
|
const result2 = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
// 3.1 Notice that postgres will return the date in UTC, but it is exactlt the same
|
|
expect(result2).toEqual([{ id: 1, timestamp_string: '2022-01-01 02:00:00.456+00' }]);
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test('test mode string for timestamp with timezone in UTC timezone', async () => {
|
|
// get current timezone from db
|
|
const timezone = await db.execute<{ TimeZone: string }>(sql`show timezone`);
|
|
|
|
// set timezone to UTC
|
|
await db.execute(sql`set time zone 'UTC'`);
|
|
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestamp: timestamp('timestamp_string', { mode: 'string', withTimezone: true, precision: 6 }).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
|
|
)
|
|
`);
|
|
|
|
const timestampString = '2022-01-01 00:00:00.123456-0200';
|
|
|
|
// 1. Insert date in string format with timezone in it
|
|
await db.insert(table).values([
|
|
{ timestamp: timestampString },
|
|
]);
|
|
|
|
// 2. Select date in string format and check that the values are the same
|
|
const result = await db.select().from(table);
|
|
|
|
// 2.1 Notice that postgres will return the date in UTC, but it is exactly the same
|
|
expect(result).toEqual([{ id: 1, timestamp: '2022-01-01 02:00:00.123456+00' }]);
|
|
|
|
// 3. Select as raw query and checke that values are the same
|
|
const result2 = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
// 3.1 Notice that postgres will return the date in UTC, but it is exactlt the same
|
|
expect(result2).toEqual([{ id: 1, timestamp_string: '2022-01-01 02:00:00.123456+00' }]);
|
|
|
|
await db.execute(sql`set time zone '${sql.raw(timezone[0]!.TimeZone)}'`);
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
test('test mode string for timestamp with timezone in different timezone', async () => {
|
|
// get current timezone from db
|
|
const timezone = await db.execute<{ TimeZone: string }>(sql`show timezone`);
|
|
|
|
// set timezone to HST (UTC - 10)
|
|
await db.execute(sql`set time zone '-10'`);
|
|
|
|
const table = pgTable('all_columns', {
|
|
id: serial('id').primaryKey(),
|
|
timestamp: timestamp('timestamp_string', { mode: 'string', withTimezone: true, precision: 6 }).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
|
|
)
|
|
`);
|
|
|
|
const timestampString = '2022-01-01 00:00:00.123456-1000';
|
|
|
|
// 1. Insert date in string format with timezone in it
|
|
await db.insert(table).values([
|
|
{ timestamp: timestampString },
|
|
]);
|
|
|
|
// 2. Select date in string format and check that the values are the same
|
|
const result = await db.select().from(table);
|
|
|
|
expect(result).toEqual([{ id: 1, timestamp: '2022-01-01 00:00:00.123456-10' }]);
|
|
|
|
// 3. Select as raw query and checke that values are the same
|
|
const result2 = await db.execute<{
|
|
id: number;
|
|
timestamp_string: string;
|
|
}>(sql`select * from ${table}`);
|
|
|
|
expect(result2).toEqual([{ id: 1, timestamp_string: '2022-01-01 00:00:00.123456-10' }]);
|
|
|
|
await db.execute(sql`set time zone '${sql.raw(timezone[0]!.TimeZone)}'`);
|
|
|
|
await db.execute(sql`drop table if exists ${table}`);
|
|
});
|
|
|
|
skipTests([
|
|
'migrator : default migration strategy',
|
|
'migrator : migrate with custom schema',
|
|
'migrator : migrate with custom table',
|
|
'migrator : migrate with custom table and custom schema',
|
|
'insert via db.execute + select via db.execute',
|
|
'insert via db.execute + returning',
|
|
'insert via db.execute w/ query builder',
|
|
'all date and time columns without timezone first case mode string',
|
|
'all date and time columns without timezone third case mode date',
|
|
'test mode string for timestamp with timezone',
|
|
'test mode date for timestamp with timezone',
|
|
'test mode string for timestamp with timezone in UTC timezone',
|
|
'test mode string for timestamp with timezone in different timezone',
|
|
'transaction',
|
|
'transaction rollback',
|
|
'nested transaction',
|
|
'nested transaction rollback',
|
|
'test $onUpdateFn and $onUpdate works updating',
|
|
]);
|
|
|
|
beforeEach(async () => {
|
|
await db.execute(sql`drop schema if exists 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,
|
|
created_at timestamptz not null default now()
|
|
)
|
|
`,
|
|
);
|
|
});
|
|
|
|
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"`,
|
|
);
|
|
expect(result).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('insert via db.execute + returning', async () => {
|
|
const inserted = await db.execute<{ id: number; name: string }>(
|
|
sql`insert into ${usersTable} (${
|
|
sql.identifier(
|
|
usersTable.name.name,
|
|
)
|
|
}) values (${'John'}) returning ${usersTable.id}, ${usersTable.name}`,
|
|
);
|
|
expect(inserted).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
test('insert via db.execute w/ query builder', async () => {
|
|
const inserted = await db.execute<Pick<typeof usersTable.$inferSelect, 'id' | 'name'>>(
|
|
db
|
|
.insert(usersTable)
|
|
.values({ name: 'John' })
|
|
.returning({ id: usersTable.id, name: usersTable.name }),
|
|
);
|
|
expect(inserted).toEqual([{ id: 1, name: 'John' }]);
|
|
});
|
|
|
|
tests();
|
|
cacheTests();
|