Extremely Popular
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348import { Client, ConnectionError, Pool, PostgresError } from "../mod.ts";import { assert, assertEquals, assertObjectMatch, assertRejects,} from "./test_deps.ts";import { getMainConfiguration } from "./config.ts";import { PoolClient, QueryClient } from "../client.ts";
function testClient( name: string, t: (getClient: () => Promise<QueryClient>) => void | Promise<void>,) { async function clientWrapper() { const clients: Client[] = []; try { await t(async () => { const client = new Client(getMainConfiguration()); await client.connect(); clients.push(client); return client; }); } finally { for (const client of clients) { await client.end(); } } }
async function poolWrapper() { const pool = new Pool(getMainConfiguration(), 10); const clients: PoolClient[] = []; try { await t(async () => { const client = await pool.connect(); clients.push(client); return client; }); } finally { for (const client of clients) { client.release(); } await pool.end(); } }
Deno.test({ fn: clientWrapper, name: `Client: ${name}` }); Deno.test({ fn: poolWrapper, name: `Pool: ${name}` });}
testClient("Array query", async function (generateClient) { const client = await generateClient();
const result = await client.queryArray("SELECT UNNEST(ARRAY[1, 2])"); assertEquals(result.rows.length, 2);});
testClient("Object query", async function (generateClient) { const client = await generateClient();
const result = await client.queryObject( "SELECT ARRAY[1, 2, 3] AS ID, 'DATA' AS TYPE", );
assertEquals(result.rows, [{ id: [1, 2, 3], type: "DATA" }]);});
testClient("Array arguments", async function (generateClient) { const client = await generateClient();
{ const value = "1"; const result = await client.queryArray( "SELECT $1", [value], ); assertEquals(result.rows, [[value]]); }
{ const value = "2"; const result = await client.queryArray({ args: [value], text: "SELECT $1", }); assertEquals(result.rows, [[value]]); }
{ const value = "3"; const result = await client.queryObject( "SELECT $1 AS ID", [value], ); assertEquals(result.rows, [{ id: value }]); }
{ const value = "4"; const result = await client.queryObject({ args: [value], text: "SELECT $1 AS ID", }); assertEquals(result.rows, [{ id: value }]); }});
testClient("Object arguments", async function (generateClient) { const client = await generateClient();
{ const value = "1"; const result = await client.queryArray( "SELECT $id", { id: value }, ); assertEquals(result.rows, [[value]]); }
{ const value = "2"; const result = await client.queryArray({ args: { id: value }, text: "SELECT $ID", }); assertEquals(result.rows, [[value]]); }
{ const value = "3"; const result = await client.queryObject( "SELECT $id as ID", { id: value }, ); assertEquals(result.rows, [{ id: value }]); }
{ const value = "4"; const result = await client.queryObject({ args: { id: value }, text: "SELECT $ID AS ID", }); assertEquals(result.rows, [{ id: value }]); }});
testClient( "Throws on duplicate object arguments", async function (generateClient) { const client = await generateClient();
const value = "some_value"; const { rows: res } = await client.queryArray( "SELECT $value, $VaLue, $VALUE", { value }, ); assertEquals(res, [[value, value, value]]);
await assertRejects( () => client.queryArray("SELECT $A", { a: 1, A: 2 }), Error, "The arguments provided for the query must be unique (insensitive)", ); },);
testClient( "Array query handles recovery after error state", async function (generateClient) { const client = await generateClient();
await client.queryArray`CREATE TEMP TABLE PREPARED_STATEMENT_ERROR (X INT)`;
await assertRejects(() => client.queryArray( "INSERT INTO PREPARED_STATEMENT_ERROR VALUES ($1)", ["TEXT"], ) );
const { rows } = await client.queryObject<{ result: number }>({ fields: ["result"], text: "SELECT 1", });
assertEquals(rows[0], { result: 1 }); },);
testClient( "Array query can handle multiple query failures at once", async function (generateClient) { const client = await generateClient();
await assertRejects( () => client.queryArray( "SELECT 1; SELECT '2'::INT; SELECT 'A'::INT", ), PostgresError, "invalid input syntax for type integer", );
const { rows } = await client.queryObject<{ result: number }>({ fields: ["result"], text: "SELECT 1", });
assertEquals(rows[0], { result: 1 }); },);
testClient( "Array query handles error during data processing", async function (generateClient) { const client = await generateClient();
await assertRejects( () => client.queryObject`SELECT 'A' AS X, 'B' AS X`, );
const value = "193"; const { rows: result_2 } = await client.queryObject`SELECT ${value} AS B`; assertEquals(result_2[0], { b: value }); },);
testClient( "Array query can return multiple queries", async function (generateClient) { const client = await generateClient();
const { rows: result } = await client.queryObject<{ result: number }>({ text: "SELECT 1; SELECT '2'::INT", fields: ["result"], });
assertEquals(result, [{ result: 1 }, { result: 2 }]); },);
testClient( "Array query handles empty query", async function (generateClient) { const client = await generateClient();
const { rows: result } = await client.queryArray(""); assertEquals(result, []); },);
testClient( "Prepared query handles recovery after error state", async function (generateClient) { const client = await generateClient();
await client.queryArray`CREATE TEMP TABLE PREPARED_STATEMENT_ERROR (X INT)`;
await assertRejects(() => client.queryArray( "INSERT INTO PREPARED_STATEMENT_ERROR VALUES ($1)", ["TEXT"], ), PostgresError);
const result = "handled";
const { rows } = await client.queryObject({ args: [result], fields: ["result"], text: "SELECT $1", });
assertEquals(rows[0], { result }); },);
testClient( "Prepared query handles error during data processing", async function (generateClient) { const client = await generateClient();
await assertRejects( () => client.queryObject`SELECT ${1} AS A, ${2} AS A`, );
const value = "z"; const { rows: result_2 } = await client.queryObject`SELECT ${value} AS B`; assertEquals(result_2[0], { b: value }); },);
testClient( "Handles array with semicolon separator", async (generateClient) => { const client = await generateClient(); const item_1 = "Test;Azer"; const item_2 = "123;456";
const { rows: result_1 } = await client.queryArray( `SELECT ARRAY[$1, $2]`, [item_1, item_2], ); assertEquals(result_1[0], [[item_1, item_2]]); },);
testClient( "Handles parameter status messages on array query", async (generateClient) => { const client = await generateClient();
const { rows: result_1 } = await client.queryArray `SET TIME ZONE 'HongKong'`;
assertEquals(result_1, []);
const { rows: result_2 } = await client.queryObject({ fields: ["result"], text: "SET TIME ZONE 'HongKong'; SELECT 1", });
assertEquals(result_2, [{ result: 1 }]); },);
testClient( "Handles parameter status messages on prepared query", async (generateClient) => { const client = await generateClient();
const result = 10;
await client.queryArray `CREATE OR REPLACE FUNCTION PG_TEMP.CHANGE_TIMEZONE(RES INTEGER) RETURNS INT AS $$ BEGIN SET TIME ZONE 'HongKong'; END; $$ LANGUAGE PLPGSQL;`;
await assertRejects( () => client.queryArray("SELECT * FROM PG_TEMP.CHANGE_TIMEZONE($1)", [ result, ]), PostgresError, "control reached end of function without RETURN", );
await client.queryArray `CREATE OR REPLACE FUNCTION PG_TEMP.CHANGE_TIMEZONE(RES INTEGER) RETURNS INT AS $$ BEGIN SET TIME ZONE 'HongKong'; RETURN RES; END; $$ LANGUAGE PLPGSQL;`;
const { rows: result_1 } = await client.queryObject({ args: [result], fields: ["result"], text: "SELECT * FROM PG_TEMP.CHANGE_TIMEZONE($1)", });
assertEquals(result_1, [{ result }]); },);
testClient( "Handles parameter status after error", async (generateClient) => { const client = await generateClient();
await client.queryArray `CREATE OR REPLACE FUNCTION PG_TEMP.CHANGE_TIMEZONE() RETURNS INT AS $$ BEGIN SET TIME ZONE 'HongKong'; END; $$ LANGUAGE PLPGSQL;`;
await assertRejects( () => client.queryArray`SELECT * FROM PG_TEMP.CHANGE_TIMEZONE()`, PostgresError, "control reached end of function without RETURN", ); },);
testClient("Terminated connections", async function (generateClient) { const client = await generateClient(); await client.end();
await assertRejects( async () => { await client.queryArray`SELECT 1`; }, Error, "Connection to the database has been terminated", );});
// This test depends on the assumption that all clients will default to// one reconneciton by defaulttestClient("Default reconnection", async (generateClient) => { const client = await generateClient();
await assertRejects( () => client.queryArray`SELECT PG_TERMINATE_BACKEND(${client.session.pid})`, ConnectionError, );
const { rows: result } = await client.queryObject<{ res: number }>({ text: `SELECT 1`, fields: ["res"], }); assertEquals( result[0].res, 1, );
assertEquals(client.connected, true);});
testClient("Handling of debug notices", async function (generateClient) { const client = await generateClient();
// Create temporary function await client.queryArray `CREATE OR REPLACE FUNCTION PG_TEMP.CREATE_NOTICE () RETURNS INT AS $$ BEGIN RAISE NOTICE 'NOTICED'; RETURN (SELECT 1); END; $$ LANGUAGE PLPGSQL;`;
const { rows, warnings } = await client.queryArray( "SELECT * FROM PG_TEMP.CREATE_NOTICE();", ); assertEquals(rows[0][0], 1); assertEquals(warnings[0].message, "NOTICED");});
// This query doesn't recreate the table and outputs// a notice insteadtestClient("Handling of query notices", async function (generateClient) { const client = await generateClient();
await client.queryArray( "CREATE TEMP TABLE NOTICE_TEST (ABC INT);", ); const { warnings } = await client.queryArray( "CREATE TEMP TABLE IF NOT EXISTS NOTICE_TEST (ABC INT);", );
assert(warnings[0].message.includes("already exists"));});
testClient( "Handling of messages between data fetching", async function (generateClient) { const client = await generateClient();
await client.queryArray `CREATE OR REPLACE FUNCTION PG_TEMP.MESSAGE_BETWEEN_DATA(MESSAGE VARCHAR) RETURNS VARCHAR AS $$ BEGIN RAISE NOTICE '%', MESSAGE; RETURN MESSAGE; END; $$ LANGUAGE PLPGSQL;`;
const message_1 = "MESSAGE_1"; const message_2 = "MESSAGE_2"; const message_3 = "MESSAGE_3";
const { rows: result, warnings } = await client.queryObject({ args: [message_1, message_2, message_3], fields: ["result"], text: `SELECT * FROM PG_TEMP.MESSAGE_BETWEEN_DATA($1) UNION ALL SELECT * FROM PG_TEMP.MESSAGE_BETWEEN_DATA($2) UNION ALL SELECT * FROM PG_TEMP.MESSAGE_BETWEEN_DATA($3)`, });
assertEquals(result.length, 3); assertEquals(warnings.length, 3);
assertEquals(result[0], { result: message_1 }); assertObjectMatch(warnings[0], { message: message_1 });
assertEquals(result[1], { result: message_2 }); assertObjectMatch(warnings[1], { message: message_2 });
assertEquals(result[2], { result: message_3 }); assertObjectMatch(warnings[2], { message: message_3 }); },);
testClient("nativeType", async function (generateClient) { const client = await generateClient();
const result = await client.queryArray<[Date]> `SELECT '2019-02-10T10:30:40.005+04:30'::TIMESTAMPTZ`; const row = result.rows[0];
const expectedDate = Date.UTC(2019, 1, 10, 6, 0, 40, 5);
assertEquals(row[0].toUTCString(), new Date(expectedDate).toUTCString());});
testClient("Binary data is parsed correctly", async function (generateClient) { const client = await generateClient();
const { rows: result_1 } = await client.queryArray `SELECT E'foo\\\\000\\\\200\\\\\\\\\\\\377'::BYTEA`;
const expectedBytes = new Uint8Array([102, 111, 111, 0, 128, 92, 255]);
assertEquals(result_1[0][0], expectedBytes);
const { rows: result_2 } = await client.queryArray( "SELECT $1::BYTEA", [expectedBytes], ); assertEquals(result_2[0][0], expectedBytes);});
testClient("Result object metadata", async function (generateClient) { const client = await generateClient();
await client.queryArray`CREATE TEMP TABLE METADATA (VALUE INTEGER)`; await client.queryArray `INSERT INTO METADATA VALUES (100), (200), (300), (400), (500), (600)`;
let result;
// simple select result = await client.queryArray("SELECT * FROM METADATA WHERE VALUE = 100"); assertEquals(result.command, "SELECT"); assertEquals(result.rowCount, 1);
// parameterized select result = await client.queryArray( "SELECT * FROM METADATA WHERE VALUE IN ($1, $2)", [200, 300], ); assertEquals(result.command, "SELECT"); assertEquals(result.rowCount, 2);
// simple delete result = await client.queryArray( "DELETE FROM METADATA WHERE VALUE IN (100, 200)", ); assertEquals(result.command, "DELETE"); assertEquals(result.rowCount, 2);
// parameterized delete result = await client.queryArray( "DELETE FROM METADATA WHERE VALUE = $1", [300], ); assertEquals(result.command, "DELETE"); assertEquals(result.rowCount, 1);
// simple insert result = await client.queryArray("INSERT INTO METADATA VALUES (4), (5)"); assertEquals(result.command, "INSERT"); assertEquals(result.rowCount, 2);
// parameterized insert result = await client.queryArray("INSERT INTO METADATA VALUES ($1)", [3]); assertEquals(result.command, "INSERT"); assertEquals(result.rowCount, 1);
// simple update result = await client.queryArray( "UPDATE METADATA SET VALUE = 500 WHERE VALUE IN (500, 600)", ); assertEquals(result.command, "UPDATE"); assertEquals(result.rowCount, 2);
// parameterized update result = await client.queryArray( "UPDATE METADATA SET VALUE = 400 WHERE VALUE = $1", [400], ); assertEquals(result.command, "UPDATE"); assertEquals(result.rowCount, 1);});
testClient("Long column alias is truncated", async function (generateClient) { const client = await generateClient();
const { rows: result, warnings } = await client.queryObject(` SELECT 1 AS "very_very_very_very_very_very_very_very_very_very_very_long_name" `);
assertEquals(result, [ { "very_very_very_very_very_very_very_very_very_very_very_long_nam": 1 }, ]);
assert(warnings[0].message.includes("will be truncated"));});
testClient("Query array with template string", async function (generateClient) { const client = await generateClient();
const [value_1, value_2] = ["A", "B"];
const { rows } = await client.queryArray<[string, string]> `SELECT ${value_1}, ${value_2}`;
assertEquals(rows[0], [value_1, value_2]);});
testClient( "Object query field names aren't transformed when camelcase is disabled", async function (generateClient) { const client = await generateClient(); const record = { pos_x: "100", pos_y: "200", prefix_name_suffix: "square", };
const { rows: result } = await client.queryObject({ args: [record.pos_x, record.pos_y, record.prefix_name_suffix], camelcase: false, text: "SELECT $1 AS POS_X, $2 AS POS_Y, $3 AS PREFIX_NAME_SUFFIX", });
assertEquals(result[0], record); },);
testClient( "Object query field names are transformed when camelcase is enabled", async function (generateClient) { const client = await generateClient(); const record = { posX: "100", posY: "200", prefixNameSuffix: "point", };
const { rows: result } = await client.queryObject({ args: [record.posX, record.posY, record.prefixNameSuffix], camelcase: true, text: "SELECT $1 AS POS_X, $2 AS POS_Y, $3 AS PREFIX_NAME_SUFFIX", });
assertEquals(result[0], record); },);
testClient( "Object query result is mapped to explicit fields", async function (generateClient) { const client = await generateClient();
const result = await client.queryObject({ text: "SELECT ARRAY[1, 2, 3], 'DATA'", fields: ["ID", "type"], });
assertEquals(result.rows, [{ ID: [1, 2, 3], type: "DATA" }]); },);
testClient( "Object query explicit fields override camelcase", async function (generateClient) { const client = await generateClient();
const record = { field_1: "A", field_2: "B", field_3: "C" };
const { rows: result } = await client.queryObject({ args: [record.field_1, record.field_2, record.field_3], camelcase: true, fields: ["field_1", "field_2", "field_3"], text: "SELECT $1 AS POS_X, $2 AS POS_Y, $3 AS PREFIX_NAME_SUFFIX", });
assertEquals(result[0], record); },);
testClient( "Object query throws if explicit fields aren't unique", async function (generateClient) { const client = await generateClient();
await assertRejects( () => client.queryObject({ text: "SELECT 1", fields: ["FIELD_1", "FIELD_1"], }), TypeError, "The fields provided for the query must be unique", ); },);
testClient( "Object query throws if implicit fields aren't unique 1", async function (generateClient) { const client = await generateClient();
await assertRejects( () => client.queryObject`SELECT 1 AS "a", 2 AS A`, Error, `Field names "a" are duplicated in the result of the query`, );
await assertRejects( () => client.queryObject({ camelcase: true, text: `SELECT 1 AS "fieldX", 2 AS field_x`, }), Error, `Field names "fieldX" are duplicated in the result of the query`, ); },);
testClient( "Object query doesn't throw when explicit fields only have one letter", async function (generateClient) { const client = await generateClient();
const { rows: result_1 } = await client.queryObject<{ a: number }>({ text: "SELECT 1", fields: ["a"], });
assertEquals( result_1[0].a, 1, );
await assertRejects( async () => { await client.queryObject({ text: "SELECT 1", fields: ["1"], }); }, TypeError, "The fields provided for the query must contain only letters and underscores", ); },);
testClient( "Object query throws if explicit fields aren't valid", async function (generateClient) { const client = await generateClient();
await assertRejects( async () => { await client.queryObject({ text: "SELECT 1", fields: ["123_"], }); }, TypeError, "The fields provided for the query must contain only letters and underscores", );
await assertRejects( async () => { await client.queryObject({ text: "SELECT 1", fields: ["1A"], }); }, TypeError, "The fields provided for the query must contain only letters and underscores", );
await assertRejects( async () => { await client.queryObject({ text: "SELECT 1", fields: ["A$"], }); }, TypeError, "The fields provided for the query must contain only letters and underscores", ); },);
testClient( "Object query throws if result columns don't match explicit fields", async function (generateClient) { const client = await generateClient();
await assertRejects( async () => { await client.queryObject({ text: "SELECT 1", fields: ["FIELD_1", "FIELD_2"], }); }, RangeError, "The fields provided for the query don't match the ones returned as a result (1 expected, 2 received)", ); },);
testClient( "Object query throws when multiple query results don't have the same number of rows", async function (generateClient) { const client = await generateClient();
await assertRejects( () => client.queryObject<{ result: number }>({ text: "SELECT 1; SELECT '2'::INT, '3'", fields: ["result"], }), RangeError, "The result fields returned by the database don't match the defined structure of the result", ); },);
testClient( "Query object with template string", async function (generateClient) { const client = await generateClient();
const value = { x: "A", y: "B" };
const { rows } = await client.queryObject<{ x: string; y: string }> `SELECT ${value.x} AS x, ${value.y} AS y`;
assertEquals(rows[0], value); },);
testClient("Transaction", async function (generateClient) { const client = await generateClient();
const transaction_name = "x"; const transaction = client.createTransaction(transaction_name);
await transaction.begin(); assertEquals( client.session.current_transaction, transaction_name, "Client is locked out during transaction", ); await transaction.queryArray`CREATE TEMP TABLE TEST (X INTEGER)`; const savepoint = await transaction.savepoint("table_creation"); await transaction.queryArray`INSERT INTO TEST (X) VALUES (1)`; const query_1 = await transaction.queryObject<{ x: number }> `SELECT X FROM TEST`; assertEquals( query_1.rows[0].x, 1, "Operation was not executed inside transaction", ); await transaction.rollback(savepoint); const query_2 = await transaction.queryObject<{ x: number }> `SELECT X FROM TEST`; assertEquals( query_2.rowCount, 0, "Rollback was not succesful inside transaction", ); await transaction.commit(); assertEquals( client.session.current_transaction, null, "Client was not released after transaction", );});
testClient( "Transaction with repeatable read isolation level", async function (generateClient) { const client_1 = await generateClient();
const client_2 = await generateClient();
await client_1.queryArray`DROP TABLE IF EXISTS FOR_TRANSACTION_TEST`; await client_1.queryArray`CREATE TABLE FOR_TRANSACTION_TEST (X INTEGER)`; await client_1.queryArray`INSERT INTO FOR_TRANSACTION_TEST (X) VALUES (1)`;
const transaction_rr = client_1.createTransaction( "transactionIsolationLevelRepeatableRead", { isolation_level: "repeatable_read" }, ); await transaction_rr.begin();
// This locks the current value of the test table await transaction_rr.queryObject<{ x: number }> `SELECT X FROM FOR_TRANSACTION_TEST`;
// Modify data outside the transaction await client_2.queryArray`UPDATE FOR_TRANSACTION_TEST SET X = 2`;
const { rows: query_1 } = await client_2.queryObject<{ x: number }> `SELECT X FROM FOR_TRANSACTION_TEST`; assertEquals(query_1, [{ x: 2 }]);
const { rows: query_2 } = await transaction_rr.queryObject< { x: number } >`SELECT X FROM FOR_TRANSACTION_TEST`; assertEquals( query_2, [{ x: 1 }], "Repeatable read transaction should not be able to observe changes that happened after the transaction start", );
await transaction_rr.commit();
const { rows: query_3 } = await client_1.queryObject<{ x: number }> `SELECT X FROM FOR_TRANSACTION_TEST`; assertEquals( query_3, [{ x: 2 }], "Main session should be able to observe changes after transaction ended", );
await client_1.queryArray`DROP TABLE FOR_TRANSACTION_TEST`; },);
testClient( "Transaction with serializable isolation level", async function (generateClient) { const client_1 = await generateClient();
const client_2 = await generateClient();
await client_1.queryArray`DROP TABLE IF EXISTS FOR_TRANSACTION_TEST`; await client_1.queryArray`CREATE TABLE FOR_TRANSACTION_TEST (X INTEGER)`; await client_1.queryArray`INSERT INTO FOR_TRANSACTION_TEST (X) VALUES (1)`;
const transaction_rr = client_1.createTransaction( "transactionIsolationLevelRepeatableRead", { isolation_level: "serializable" }, ); await transaction_rr.begin();
// This locks the current value of the test table await transaction_rr.queryObject<{ x: number }> `SELECT X FROM FOR_TRANSACTION_TEST`;
// Modify data outside the transaction await client_2.queryArray`UPDATE FOR_TRANSACTION_TEST SET X = 2`;
await assertRejects( () => transaction_rr.queryArray`UPDATE FOR_TRANSACTION_TEST SET X = 3`, undefined, undefined, "A serializable transaction should throw if the data read in the transaction has been modified externally", );
const { rows: query_3 } = await client_1.queryObject<{ x: number }> `SELECT X FROM FOR_TRANSACTION_TEST`; assertEquals( query_3, [{ x: 2 }], "Main session should be able to observe changes after transaction ended", );
await client_1.queryArray`DROP TABLE FOR_TRANSACTION_TEST`; },);
testClient("Transaction read only", async function (generateClient) { const client = await generateClient();
await client.queryArray`DROP TABLE IF EXISTS FOR_TRANSACTION_TEST`; await client.queryArray`CREATE TABLE FOR_TRANSACTION_TEST (X INTEGER)`; const transaction = client.createTransaction("transactionReadOnly", { read_only: true, }); await transaction.begin();
await assertRejects( () => transaction.queryArray`DELETE FROM FOR_TRANSACTION_TEST`, undefined, "cannot execute DELETE in a read-only transaction", );
await client.queryArray`DROP TABLE FOR_TRANSACTION_TEST`;});
testClient("Transaction snapshot", async function (generateClient) { const client_1 = await generateClient(); const client_2 = await generateClient();
await client_1.queryArray`DROP TABLE IF EXISTS FOR_TRANSACTION_TEST`; await client_1.queryArray`CREATE TABLE FOR_TRANSACTION_TEST (X INTEGER)`; await client_1.queryArray`INSERT INTO FOR_TRANSACTION_TEST (X) VALUES (1)`; const transaction_1 = client_1.createTransaction( "transactionSnapshot1", { isolation_level: "repeatable_read" }, ); await transaction_1.begin();
// This locks the current value of the test table await transaction_1.queryObject<{ x: number }> `SELECT X FROM FOR_TRANSACTION_TEST`;
// Modify data outside the transaction await client_2.queryArray`UPDATE FOR_TRANSACTION_TEST SET X = 2`;
const { rows: query_1 } = await transaction_1.queryObject<{ x: number }> `SELECT X FROM FOR_TRANSACTION_TEST`; assertEquals( query_1, [{ x: 1 }], "External changes shouldn't affect repeatable read transaction", );
const snapshot = await transaction_1.getSnapshot();
const transaction_2 = client_2.createTransaction( "transactionSnapshot2", { isolation_level: "repeatable_read", snapshot }, ); await transaction_2.begin();
const { rows: query_2 } = await transaction_2.queryObject<{ x: number }> `SELECT X FROM FOR_TRANSACTION_TEST`; assertEquals( query_2, [{ x: 1 }], "External changes shouldn't affect repeatable read transaction with previous snapshot", );
await transaction_1.commit(); await transaction_2.commit();
await client_1.queryArray`DROP TABLE FOR_TRANSACTION_TEST`;});
testClient("Transaction locks client", async function (generateClient) { const client = await generateClient();
const transaction = client.createTransaction("x");
await transaction.begin(); await transaction.queryArray`SELECT 1`; await assertRejects( () => client.queryArray`SELECT 1`, undefined, "This connection is currently locked", "The connection is not being locked by the transaction", ); await transaction.commit();
await client.queryArray`SELECT 1`; assertEquals( client.session.current_transaction, null, "Client was not released after transaction", );});
testClient("Transaction commit chain", async function (generateClient) { const client = await generateClient();
const name = "transactionCommitChain"; const transaction = client.createTransaction(name);
await transaction.begin();
await transaction.commit({ chain: true }); assertEquals( client.session.current_transaction, name, "Client shouldn't have been released on chained commit", );
await transaction.commit(); assertEquals( client.session.current_transaction, null, "Client was not released after transaction ended", );});
testClient( "Transaction lock is released on savepoint-less rollback", async function (generateClient) { const client = await generateClient();
const name = "transactionLockIsReleasedOnRollback"; const transaction = client.createTransaction(name);
await client.queryArray`CREATE TEMP TABLE MY_TEST (X INTEGER)`; await transaction.begin(); await transaction.queryArray`INSERT INTO MY_TEST (X) VALUES (1)`;
const { rows: query_1 } = await transaction.queryObject<{ x: number }> `SELECT X FROM MY_TEST`; assertEquals(query_1, [{ x: 1 }]);
await transaction.rollback({ chain: true });
assertEquals( client.session.current_transaction, name, "Client shouldn't have been released after chained rollback", );
await transaction.rollback();
const { rowCount: query_2 } = await client.queryObject<{ x: number }> `SELECT X FROM MY_TEST`; assertEquals(query_2, 0);
assertEquals( client.session.current_transaction, null, "Client was not released after rollback", ); },);
testClient("Transaction rollback validations", async function (generateClient) { const client = await generateClient();
const transaction = client.createTransaction( "transactionRollbackValidations", ); await transaction.begin();
await assertRejects( // @ts-ignore This is made to check the two properties aren't passed at once () => transaction.rollback({ savepoint: "unexistent", chain: true }), undefined, "The chain option can't be used alongside a savepoint on a rollback operation", );
await transaction.commit();});
testClient( "Transaction lock is released after unrecoverable error", async function (generateClient) { const client = await generateClient();
const name = "transactionLockIsReleasedOnUnrecoverableError"; const transaction = client.createTransaction(name);
await transaction.begin(); await assertRejects( () => transaction.queryArray`SELECT []`, undefined, `The transaction "${name}" has been aborted due to \`PostgresError:`, ); assertEquals(client.session.current_transaction, null);
await transaction.begin(); await assertRejects( () => transaction.queryObject`SELECT []`, undefined, `The transaction "${name}" has been aborted due to \`PostgresError:`, ); assertEquals(client.session.current_transaction, null); },);
testClient("Transaction savepoints", async function (generateClient) { const client = await generateClient();
const savepoint_name = "a1"; const transaction = client.createTransaction("x");
await transaction.begin(); await transaction.queryArray`CREATE TEMP TABLE X (Y INT)`; await transaction.queryArray`INSERT INTO X VALUES (1)`; const { rows: query_1 } = await transaction.queryObject<{ y: number }> `SELECT Y FROM X`; assertEquals(query_1, [{ y: 1 }]);
const savepoint = await transaction.savepoint(savepoint_name);
await transaction.queryArray`DELETE FROM X`; const { rowCount: query_2 } = await transaction.queryObject<{ y: number }> `SELECT Y FROM X`; assertEquals(query_2, 0);
await savepoint.update();
await transaction.queryArray`INSERT INTO X VALUES (2)`; const { rows: query_3 } = await transaction.queryObject<{ y: number }> `SELECT Y FROM X`; assertEquals(query_3, [{ y: 2 }]);
await transaction.rollback(savepoint); const { rowCount: query_4 } = await transaction.queryObject<{ y: number }> `SELECT Y FROM X`; assertEquals(query_4, 0);
assertEquals( savepoint.instances, 2, "An incorrect number of instances were created for a transaction savepoint", ); await savepoint.release(); assertEquals( savepoint.instances, 1, "The instance for the savepoint was not released", );
// This checks that the savepoint can be called by name as well await transaction.rollback(savepoint_name); const { rows: query_5 } = await transaction.queryObject<{ y: number }> `SELECT Y FROM X`; assertEquals(query_5, [{ y: 1 }]);
await transaction.commit();});
testClient( "Transaction savepoint validations", async function (generateClient) { const client = await generateClient();
const transaction = client.createTransaction("x"); await transaction.begin();
await assertRejects( () => transaction.savepoint("1"), undefined, "The savepoint name can't begin with a number", );
await assertRejects( () => transaction.savepoint( "this_savepoint_is_going_to_be_longer_than_sixty_three_characters", ), undefined, "The savepoint name can't be longer than 63 characters", );
await assertRejects( () => transaction.savepoint("+"), undefined, "The savepoint name can only contain alphanumeric characters", );
const savepoint = await transaction.savepoint("ABC1"); assertEquals(savepoint.name, "abc1");
assertEquals( savepoint, await transaction.savepoint("abc1"), "Creating a savepoint with the same name should return the original one", ); await savepoint.release();
await savepoint.release();
await assertRejects( () => savepoint.release(), undefined, "This savepoint has no instances to release", );
await assertRejects( () => transaction.rollback(savepoint), undefined, `There are no savepoints of "abc1" left to rollback to`, );
await assertRejects( () => transaction.rollback("UNEXISTENT"), undefined, `There is no "unexistent" savepoint registered in this transaction`, );
await transaction.commit(); },);
testClient( "Transaction operations throw if transaction has not been initialized", async function (generateClient) { const client = await generateClient();
const transaction_x = client.createTransaction("x");
const transaction_y = client.createTransaction("y");
await transaction_x.begin();
await assertRejects( () => transaction_y.begin(), undefined, `This client already has an ongoing transaction "x"`, );
await transaction_x.commit(); await transaction_y.begin(); await assertRejects( () => transaction_y.begin(), undefined, "This transaction is already open", );
await transaction_y.commit(); await assertRejects( () => transaction_y.commit(), undefined, `This transaction has not been started yet, make sure to use the "begin" method to do so`, );
await assertRejects( () => transaction_y.commit(), undefined, `This transaction has not been started yet, make sure to use the "begin" method to do so`, );
await assertRejects( () => transaction_y.queryArray`SELECT 1`, undefined, `This transaction has not been started yet, make sure to use the "begin" method to do so`, );
await assertRejects( () => transaction_y.queryObject`SELECT 1`, undefined, `This transaction has not been started yet, make sure to use the "begin" method to do so`, );
await assertRejects( () => transaction_y.rollback(), undefined, `This transaction has not been started yet, make sure to use the "begin" method to do so`, );
await assertRejects( () => transaction_y.savepoint("SOME"), undefined, `This transaction has not been started yet, make sure to use the "begin" method to do so`, ); },);