konnosDashboard
dolt-compat.integration.test.ts218 lines · main
1/**
2 * DoltGres compatibility alarm — the "smoke alarm" (sprint plan S0.2).
3 *
4 * Runs against a REAL DoltGres instance and pins exactly which SQL the data
5 * plane may and may not use. This is the regression net that stops the
6 * "little bugs keep coming back" cycle: every incompatibility is asserted
7 * here, so it fails in a test instead of in production / in ISY's face.
8 *
9 * Two halves:
10 * 1. ACCEPTED today — must keep working (a regression = real breakage).
11 * 2. REJECTED today — documents the constraint the data-plane code works
12 * around. If DoltGres starts SUPPORTING one of these, this test flips
13 * to failing and tells us "you can now delete the workaround".
14 *
15 * How to run (needs the local DoltGres container up on :5433):
16 * docker compose -f infra/test/docker-compose.dolt.yml up -d # or the dev container
17 * BRIVEN_DATA_PLANE_URL=postgres://postgres:password@127.0.0.1:5433/postgres \
18 * bun test src/db/dolt-compat.integration.test.ts
19 *
20 * Without BRIVEN_DATA_PLANE_URL set, the whole suite SKIPS (so the normal
21 * no-database unit run stays green).
22 */
23import { afterAll, beforeAll, describe, expect, test } from 'bun:test';
24import pg from 'pg';
25
26const URL = process.env.BRIVEN_DATA_PLANE_URL ?? process.env.DOLT_TEST_URL;
27const HAS_DB = Boolean(URL);
28
29// Unique-ish suffix so reruns don't collide (Date.now is fine in a test file).
30const TAG = `cmp_${Date.now().toString(36)}`;
31
32let client: pg.Client;
33
34/** Run a statement, return rows. Throws on SQL the engine rejects. */
35async function run(sql: string, params?: unknown[]): Promise<pg.QueryResult> {
36 return client.query(sql, params as never);
37}
38
39/** Assert a statement is ACCEPTED (no throw). */
40async function accepts(sql: string, params?: unknown[]): Promise<void> {
41 await run(sql, params);
42}
43
44/** Assert a statement is REJECTED (throws). Returns the error message. */
45async function rejects(sql: string, params?: unknown[]): Promise<string> {
46 try {
47 await run(sql, params);
48 } catch (err) {
49 return (err as Error).message;
50 }
51 throw new Error(`Expected DoltGres to REJECT this SQL, but it was accepted:\n${sql}`);
52}
53
54describe.skipIf(!HAS_DB)('DoltGres compatibility alarm', () => {
55 beforeAll(async () => {
56 client = new pg.Client({ connectionString: URL });
57 await client.connect();
58 await run(`DROP TABLE IF EXISTS "${TAG}"`);
59 await run(
60 `CREATE TABLE "${TAG}" (id text PRIMARY KEY, name text, score int, created_at timestamp, body jsonb)`,
61 );
62 await run(`INSERT INTO "${TAG}" (id, name, score) VALUES ('a','Alice',10),('b','Bob',20)`);
63 });
64
65 afterAll(async () => {
66 if (client) {
67 await run(`DROP TABLE IF EXISTS "${TAG}"`).catch(() => {});
68 await client.end();
69 }
70 });
71
72 // ───────────────────────── ACCEPTED today — must keep working ─────────────
73 describe('accepted (must keep working)', () => {
74 test('ON CONFLICT DO NOTHING — schema-apply / idempotent inserts', async () => {
75 await accepts(
76 `INSERT INTO "${TAG}" (id, name, score) VALUES ('a','dup',0) ON CONFLICT (id) DO NOTHING`,
77 );
78 });
79
80 test('jsonb cast + read', async () => {
81 await accepts(`UPDATE "${TAG}" SET body = '{"k":1}'::jsonb WHERE id = 'a'`);
82 const r = await run(`SELECT body->>'k' AS k FROM "${TAG}" WHERE id = 'a'`);
83 expect(r.rows[0].k).toBe('1');
84 });
85
86 test('date_trunc + interval', async () => {
87 await accepts(`SELECT date_trunc('hour', now() - interval '1 hour') AS h`);
88 });
89
90 test('generate_series (dashboard buckets)', async () => {
91 const r = await run(`SELECT count(*)::int AS n FROM generate_series(1, 24) g`);
92 expect(r.rows[0].n).toBe(24);
93 });
94
95 test('COUNT(DISTINCT) + CTE', async () => {
96 const r = await run(
97 `WITH t AS (SELECT score FROM "${TAG}") SELECT count(DISTINCT score)::int AS n FROM t`,
98 );
99 expect(r.rows[0].n).toBe(2);
100 });
101
102 test('::regclass + pg_total_relation_size (storage usage SQL)', async () => {
103 await accepts(`SELECT pg_total_relation_size('"${TAG}"'::regclass) AS bytes`);
104 });
105
106 test('gen_random_uuid()', async () => {
107 await accepts(`SELECT gen_random_uuid() AS u`);
108 });
109
110 test('information_schema 3-way FK join (Studio relations)', async () => {
111 await accepts(
112 `SELECT tc.constraint_name
113 FROM information_schema.table_constraints tc
114 JOIN information_schema.key_column_usage kcu
115 ON tc.constraint_name = kcu.constraint_name
116 JOIN information_schema.constraint_column_usage ccu
117 ON ccu.constraint_name = tc.constraint_name
118 WHERE tc.constraint_type = 'FOREIGN KEY'`,
119 );
120 });
121
122 test('DELETE ... RETURNING (guards sprint S1.6 — inline runtime must match isolate)', async () => {
123 await run(`INSERT INTO "${TAG}" (id, name) VALUES ('del','tmp')`);
124 const r = await run(`DELETE FROM "${TAG}" WHERE id = 'del' RETURNING id`);
125 expect(r.rows[0].id).toBe('del');
126 });
127
128 test('case-insensitive search via lower()+LIKE (the S1.1 ILIKE replacement)', async () => {
129 const r = await run(
130 `SELECT id FROM "${TAG}" WHERE lower(name) LIKE '%' || lower($1) || '%' ORDER BY id`,
131 ['ALI'],
132 );
133 expect(r.rows.map((x) => x.id)).toEqual(['a']);
134 });
135
136 test('plain TRUNCATE (the S1.3 replacement) on a scratch table', async () => {
137 await run(`DROP TABLE IF EXISTS "${TAG}_t"`);
138 await run(`CREATE TABLE "${TAG}_t" (id int)`);
139 await run(`INSERT INTO "${TAG}_t" VALUES (1)`);
140 await accepts(`TRUNCATE "${TAG}_t"`);
141 const r = await run(`SELECT count(*)::int AS n FROM "${TAG}_t"`);
142 expect(r.rows[0].n).toBe(0);
143 await run(`DROP TABLE "${TAG}_t"`);
144 });
145
146 test('manual upsert via ON CONFLICT DO NOTHING + UPDATE (the S2.4 replacement)', async () => {
147 await run(
148 `INSERT INTO "${TAG}" (id, name) VALUES ('a','x') ON CONFLICT (id) DO NOTHING`,
149 );
150 await run(`UPDATE "${TAG}" SET name = 'AliceUpdated' WHERE id = 'a'`);
151 const r = await run(`SELECT name FROM "${TAG}" WHERE id = 'a'`);
152 expect(r.rows[0].name).toBe('AliceUpdated');
153 });
154
155 test('expanded keyset cursor a<$1 OR (a=$1 AND b<$2) (the S2.5 replacement)', async () => {
156 await accepts(
157 `SELECT id FROM "${TAG}" WHERE (score < $1 OR (score = $1 AND id < $2)) ORDER BY score DESC, id DESC`,
158 [20, 'b'],
159 );
160 });
161 });
162
163 // ───────────────────── REJECTED today — workaround required ───────────────
164 // If one of these starts PASSING, the test fails on purpose → delete the
165 // matching workaround and update BRIVEN-BUGS-REPORT.md.
166 describe('rejected (workaround required — flip = good news)', () => {
167 test('ILIKE → unsupported (drives S1.1)', async () => {
168 const msg = await rejects(`SELECT id FROM "${TAG}" WHERE name ILIKE '%ali%'`);
169 expect(msg.toLowerCase()).toContain('ilike');
170 });
171
172 test('SET LOCAL → unsupported (drives S1.2)', async () => {
173 await rejects(`SET LOCAL statement_timeout = '5s'`);
174 });
175
176 test('TRUNCATE ... RESTART IDENTITY → syntax error (drives S1.3)', async () => {
177 await run(`DROP TABLE IF EXISTS "${TAG}_r"`);
178 await run(`CREATE TABLE "${TAG}_r" (id int)`);
179 await rejects(`TRUNCATE "${TAG}_r" RESTART IDENTITY`);
180 await run(`DROP TABLE "${TAG}_r"`);
181 });
182
183 test('ON CONFLICT DO UPDATE (excluded) → unsupported (drives S2.4)', async () => {
184 await rejects(
185 `INSERT INTO "${TAG}" (id, name) VALUES ('a','z') ON CONFLICT (id) DO UPDATE SET name = excluded.name`,
186 );
187 });
188
189 test('row-tuple comparison (a,b) < ($1,$2) → unsupported (drives S2.5)', async () => {
190 await rejects(`SELECT id FROM "${TAG}" WHERE (score, id) < ($1, $2)`, [20, 'b']);
191 });
192
193 test('citext type → does not exist (drives S2.3)', async () => {
194 await rejects(`CREATE TABLE "${TAG}_c" (email citext)`);
195 });
196
197 test('CREATE EXTENSION → unsupported (drives S2.3)', async () => {
198 await rejects(`CREATE EXTENSION IF NOT EXISTS citext`);
199 });
200
201 test('vector(N) column → syntax error (drives S1.5 gate)', async () => {
202 await rejects(`CREATE TABLE "${TAG}_v" (embedding vector(3))`);
203 });
204 });
205
206 // ───────────── documented limitations (flip = good news) ──────────────────
207 // Accepted SQL that returns a degenerate result on DoltGres. If the value
208 // changes, the test fails on purpose → DoltGres gained the capability and we
209 // can rely on it (e.g. storage-byte metering — sprint S2.2).
210 describe('documented limitations', () => {
211 test('pg_total_relation_size returns 0 (no on-disk size accounting yet)', async () => {
212 const r = await run(`SELECT pg_total_relation_size('"${TAG}"'::regclass)::bigint AS bytes`);
213 // DoltGres does not report relation byte sizes today. getStorageUsage
214 // counts tables accurately but `bytes` stays 0 until this flips.
215 expect(Number(r.rows[0].bytes)).toBe(0);
216 });
217 });
218});