konnosDashboard
utils.ts154 lines · main
1import { randomUUID } from 'crypto'
2import pg, { Pool } from 'pg'
3import { parse as parseArray } from 'postgres-array'
4
5// Those types override are in sync with `postgres-meta` since the queries
6// will get executed via `execQuery` on a pg connection with the same configuration
7// see: https://github.com/supabase/postgres-meta/blob/ca06061b4708971628134f95e49f254c2dfdfa7d/src/lib/db.ts#L6-L23
8pg.types.setTypeParser(pg.types.builtins.INT8, (x) => {
9 const asNumber = Number(x)
10 if (Number.isSafeInteger(asNumber)) {
11 return asNumber
12 } else {
13 return x
14 }
15})
16pg.types.setTypeParser(pg.types.builtins.DATE, (x) => x)
17pg.types.setTypeParser(pg.types.builtins.INTERVAL, (x) => x)
18pg.types.setTypeParser(pg.types.builtins.TIMESTAMP, (x) => x)
19pg.types.setTypeParser(pg.types.builtins.TIMESTAMPTZ, (x) => x)
20pg.types.setTypeParser(1115, parseArray) // _timestamp
21pg.types.setTypeParser(1182, parseArray) // _date
22pg.types.setTypeParser(1185, parseArray) // _timestamptz
23pg.types.setTypeParser(600, (x) => x) // point
24pg.types.setTypeParser(1017, (x) => x) // _point
25
26const ROOT_DB_URL = process.env.DATABASE_URL ?? 'postgresql://postgres:postgres@localhost:5432'
27const ROOT_DB_NAME = process.env.DATABASE_NAME ?? 'postgres'
28
29// Replace postgres.js root connection with pg connection
30const rootPool = new Pool({
31 connectionString: `${ROOT_DB_URL}/${ROOT_DB_NAME}`,
32 max: 1,
33})
34
35export async function createTestDatabase() {
36 const dbName = `test_${randomUUID().replace(/-/g, '_')}`
37
38 try {
39 await rootPool.query(`CREATE DATABASE ${dbName};`)
40
41 const pool = new Pool({
42 connectionString: `${ROOT_DB_URL}/${dbName}`,
43 max: 1,
44 idleTimeoutMillis: 20000,
45 connectionTimeoutMillis: 10000,
46 })
47
48 return {
49 dbName,
50 client: 'pg' as const,
51 executeQuery: async <T = any>(query: string): Promise<T> => {
52 try {
53 const res = await pool.query(query)
54 return res.rows as T
55 } catch (error) {
56 if (error instanceof Error) {
57 throw new Error(`Failed to execute query: ${error.message}`)
58 }
59 throw error
60 }
61 },
62 cleanup: async () => {
63 await pool.end()
64 await rootPool.query(`DROP DATABASE ${dbName};`)
65 },
66 }
67 } catch (error) {
68 if (error instanceof Error) {
69 throw new Error(`Failed to create test database: ${error.message}`)
70 }
71 throw error
72 }
73}
74
75// Update cleanup function to use pg pool
76export async function cleanupRoot() {
77 await rootPool.end()
78}
79
80export async function createDatabaseWithAuthSchema(
81 db: Awaited<ReturnType<typeof createTestDatabase>>,
82 options?: { includeIdentities?: boolean }
83) {
84 const { includeIdentities = false } = options || {}
85
86 await db.executeQuery(`
87 CREATE SCHEMA IF NOT EXISTS auth;
88
89 CREATE TABLE IF NOT EXISTS auth.users (
90 instance_id uuid NULL,
91 id uuid NOT NULL UNIQUE,
92 aud varchar(255) NULL,
93 "role" varchar(255) NULL,
94 email varchar(255) NULL,
95 encrypted_password varchar(255) NULL,
96 email_confirmed_at timestamptz NULL,
97 invited_at timestamptz NULL,
98 confirmation_token varchar(255) NULL,
99 confirmation_sent_at timestamptz NULL,
100 recovery_token varchar(255) NULL,
101 recovery_sent_at timestamptz NULL,
102 email_change_token varchar(255) NULL,
103 email_change varchar(255) NULL,
104 email_change_sent_at timestamptz NULL,
105 last_sign_in_at timestamptz NULL,
106 raw_app_meta_data jsonb NULL,
107 raw_user_meta_data jsonb NULL,
108 is_super_admin bool NULL,
109 created_at timestamptz NULL,
110 updated_at timestamptz NULL,
111 phone text NULL,
112 phone_confirmed_at timestamptz NULL,
113 phone_change text NULL,
114 phone_change_token varchar(255) NULL,
115 phone_change_sent_at timestamptz NULL,
116 confirmed_at timestamptz NULL,
117 email_change_token_current varchar(255) NULL,
118 email_change_confirm_status smallint NULL,
119 banned_until timestamptz NULL,
120 reauthentication_token varchar(255) NULL,
121 reauthentication_sent_at timestamptz NULL,
122 is_sso_user bool NOT NULL DEFAULT false,
123 deleted_at timestamptz NULL,
124 is_anonymous bool NOT NULL DEFAULT false,
125 CONSTRAINT users_pkey PRIMARY KEY (id)
126 );
127
128 CREATE INDEX IF NOT EXISTS users_instance_id_idx ON auth.users USING btree (instance_id);
129 CREATE INDEX IF NOT EXISTS users_instance_id_email_idx ON auth.users USING btree (instance_id, lower(email));
130 CREATE INDEX IF NOT EXISTS confirmation_token_idx ON auth.users USING btree (confirmation_token) WHERE confirmation_token IS NOT NULL;
131 CREATE INDEX IF NOT EXISTS recovery_token_idx ON auth.users USING btree (recovery_token) WHERE recovery_token IS NOT NULL;
132 CREATE INDEX IF NOT EXISTS email_change_token_current_idx ON auth.users USING btree (email_change_token_current) WHERE email_change_token_current IS NOT NULL;
133 CREATE INDEX IF NOT EXISTS email_change_token_new_idx ON auth.users USING btree (email_change_token) WHERE email_change_token IS NOT NULL;
134 CREATE INDEX IF NOT EXISTS reauthentication_token_idx ON auth.users USING btree (reauthentication_token) WHERE reauthentication_token IS NOT NULL;
135 CREATE INDEX IF NOT EXISTS users_is_anonymous_idx ON auth.users USING btree (is_anonymous);
136 `)
137
138 if (includeIdentities) {
139 await db.executeQuery(`
140 CREATE TABLE IF NOT EXISTS auth.identities (
141 id text NOT NULL,
142 user_id uuid NOT NULL,
143 identity_data jsonb NOT NULL,
144 provider text NOT NULL,
145 last_sign_in_at timestamptz NULL,
146 created_at timestamptz NULL,
147 updated_at timestamptz NULL,
148 CONSTRAINT identities_pkey PRIMARY KEY (provider, id)
149 );
150
151 CREATE INDEX IF NOT EXISTS identities_user_id_idx ON auth.identities USING btree (user_id);
152 `)
153 }
154}