Skip to content

Commit 8f6f4e3

Browse files
shayan-shojaeishayan-shojaei
authored
create beginPrepared function (porsager#628)
* create beginPrepared function * change implementation to new method * add prepare method type to TransactionSql * add documentations and test * fix test * enable prepared transactions in the bootstrap script * enable prepared transactions in the github actions setup file * fix github actions * fix github actions yml file
1 parent e546ac0 commit 8f6f4e3

File tree

12 files changed

+117
-4
lines changed

12 files changed

+117
-4
lines changed

‎.github/workflows/test.yml

+1
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@ jobs:
3535
sudo apt-get -y install "postgresql-${{ matrix.postgres }}"
3636
sudo cp ./tests/pg_hba.conf /etc/postgresql/${{ matrix.postgres }}/main/pg_hba.conf
3737
sudo sed -i 's/.*wal_level.*/wal_level = logical/' /etc/postgresql/${{ matrix.postgres }}/main/postgresql.conf
38+
sudo sed -i 's/.*max_prepared_transactions.*/max_prepared_transactions = 100/' /etc/postgresql/${{ matrix.postgres }}/main/postgresql.conf
3839
sudo sed -i 's/.*ssl = .*/ssl = on/' /etc/postgresql/${{ matrix.postgres }}/main/postgresql.conf
3940
openssl req -new -x509 -nodes -days 365 -text -subj "/CN=localhost" -extensions v3_req -config <(cat /etc/ssl/openssl.cnf <(printf "\n[v3_req]\nbasicConstraints=critical,CA:TRUE\nkeyUsage=nonRepudiation,digitalSignature,keyEncipherment\nsubjectAltName=DNS:localhost")) -keyout server.key -out server.crt
4041
sudo cp server.key /etc/postgresql/${{ matrix.postgres }}/main/server.key

‎README.md

+20
Original file line numberDiff line numberDiff line change
@@ -637,6 +637,26 @@ sql.begin('read write', async sql => {
637637
})
638638
```
639639

640+
641+
#### PREPARE `await sql.prepare([name]) -> fn()`
642+
643+
Indicates that the transactions should be prepared using the `PREPARED TRANASCTION [NAME]` statement
644+
instead of being committed.
645+
646+
```js
647+
sql.begin('read write', async sql => {
648+
const [user] = await sql`
649+
insert into users (
650+
name
651+
) values (
652+
'Murray'
653+
)
654+
`
655+
656+
await sql.prepare('tx1')
657+
})
658+
```
659+
640660
Do note that you can often achieve the same result using [`WITH` queries (Common Table Expressions)](https://www.postgresql.org/docs/current/queries-with.html) instead of using transactions.
641661

642662
## Data Transformation

‎cjs/src/index.js

+11-1
Original file line numberDiff line numberDiff line change
@@ -235,6 +235,7 @@ function Postgres(a, b) {
235235
const queries = Queue()
236236
let savepoints = 0
237237
, connection
238+
let transactionId = null
238239

239240
try {
240241
await sql.unsafe('begin ' + options.replace(/[^a-z ]/ig, ''), [], { onexecute }).execute()
@@ -246,6 +247,7 @@ function Postgres(a, b) {
246247
async function scope(c, fn, name) {
247248
const sql = Sql(handler)
248249
sql.savepoint = savepoint
250+
sql.prepare = prepare
249251
let uncaughtError
250252
, result
251253

@@ -266,7 +268,11 @@ function Postgres(a, b) {
266268
throw e instanceof PostgresError && e.code === '25P02' && uncaughtError || e
267269
}
268270

269-
!name && await sql`commit`
271+
if (transactionId) {
272+
!name && await sql.unsafe(`prepare transaction '${transactionId}'`)
273+
}else{
274+
!name && await sql`commit`
275+
}
270276
return result
271277

272278
function savepoint(name, fn) {
@@ -285,6 +291,9 @@ function Postgres(a, b) {
285291
}
286292
}
287293

294+
async function prepare(name) {
295+
transactionId = name
296+
}
288297
function onexecute(c) {
289298
connection = c
290299
move(c, reserved)
@@ -294,6 +303,7 @@ function Postgres(a, b) {
294303
}
295304
}
296305

306+
297307
function move(c, queue) {
298308
c.queue.remove(c)
299309
queue.push(c)

‎cjs/tests/index.js

+13
Original file line numberDiff line numberDiff line change
@@ -238,6 +238,19 @@ t('Savepoint returns Result', async() => {
238238
return [1, result[0].x]
239239
})
240240

241+
t('Prepared transaction', async() => {
242+
await sql`create table test (a int)`
243+
244+
await sql.begin(async sql => {
245+
await sql`insert into test values(1)`
246+
await sql.prepare('tx1')
247+
})
248+
249+
await sql.unsafe("commit prepared 'tx1'")
250+
251+
return ['1', (await sql`select count(1) from test`)[0].count, await sql`drop table test`]
252+
})
253+
241254
t('Transaction requests are executed implicitly', async() => {
242255
const sql = postgres({ debug: true, idle_timeout: 1, fetch_types: false })
243256
return [

‎deno/README.md

+20
Original file line numberDiff line numberDiff line change
@@ -633,6 +633,26 @@ sql.begin('read write', async sql => {
633633
})
634634
```
635635

636+
637+
#### PREPARE `await sql.prepare([name]) -> fn()`
638+
639+
Indicates that the transactions should be prepared using the `PREPARED TRANASCTION [NAME]` statement
640+
instead of being committed.
641+
642+
```js
643+
sql.begin('read write', async sql => {
644+
const [user] = await sql`
645+
insert into users (
646+
name
647+
) values (
648+
'Murray'
649+
)
650+
`
651+
652+
await sql.prepare('tx1')
653+
})
654+
```
655+
636656
Do note that you can often achieve the same result using [`WITH` queries (Common Table Expressions)](https://www.postgresql.org/docs/current/queries-with.html) instead of using transactions.
637657

638658
## Data Transformation

‎deno/src/index.js

+11-1
Original file line numberDiff line numberDiff line change
@@ -236,6 +236,7 @@ function Postgres(a, b) {
236236
const queries = Queue()
237237
let savepoints = 0
238238
, connection
239+
let transactionId = null
239240

240241
try {
241242
await sql.unsafe('begin ' + options.replace(/[^a-z ]/ig, ''), [], { onexecute }).execute()
@@ -247,6 +248,7 @@ function Postgres(a, b) {
247248
async function scope(c, fn, name) {
248249
const sql = Sql(handler)
249250
sql.savepoint = savepoint
251+
sql.prepare = prepare
250252
let uncaughtError
251253
, result
252254

@@ -267,7 +269,11 @@ function Postgres(a, b) {
267269
throw e instanceof PostgresError && e.code === '25P02' && uncaughtError || e
268270
}
269271

270-
!name && await sql`commit`
272+
if (transactionId) {
273+
!name && await sql.unsafe(`prepare transaction '${transactionId}'`)
274+
}else{
275+
!name && await sql`commit`
276+
}
271277
return result
272278

273279
function savepoint(name, fn) {
@@ -286,6 +292,9 @@ function Postgres(a, b) {
286292
}
287293
}
288294

295+
async function prepare(name) {
296+
transactionId = name
297+
}
289298
function onexecute(c) {
290299
connection = c
291300
move(c, reserved)
@@ -295,6 +304,7 @@ function Postgres(a, b) {
295304
}
296305
}
297306

307+
298308
function move(c, queue) {
299309
c.queue.remove(c)
300310
queue.push(c)

‎deno/tests/index.js

+13
Original file line numberDiff line numberDiff line change
@@ -240,6 +240,19 @@ t('Savepoint returns Result', async() => {
240240
return [1, result[0].x]
241241
})
242242

243+
t('Prepared transaction', async() => {
244+
await sql`create table test (a int)`
245+
246+
await sql.begin(async sql => {
247+
await sql`insert into test values(1)`
248+
await sql.prepare('tx1')
249+
})
250+
251+
await sql.unsafe("commit prepared 'tx1'")
252+
253+
return ['1', (await sql`select count(1) from test`)[0].count, await sql`drop table test`]
254+
})
255+
243256
t('Transaction requests are executed implicitly', async() => {
244257
const sql = postgres({ debug: true, idle_timeout: 1, fetch_types: false })
245258
return [

‎deno/types/index.d.ts

+2
Original file line numberDiff line numberDiff line change
@@ -698,6 +698,8 @@ declare namespace postgres {
698698
interface TransactionSql<TTypes extends Record<string, unknown> = {}> extends Sql<TTypes> {
699699
savepoint<T>(cb: (sql: TransactionSql<TTypes>) => T | Promise<T>): Promise<UnwrapPromiseArray<T>>;
700700
savepoint<T>(name: string, cb: (sql: TransactionSql<TTypes>) => T | Promise<T>): Promise<UnwrapPromiseArray<T>>;
701+
702+
prepare<T>(name: string): Promise<UnwrapPromiseArray<T>>;
701703
}
702704
}
703705

‎src/index.js

+11-1
Original file line numberDiff line numberDiff line change
@@ -235,6 +235,7 @@ function Postgres(a, b) {
235235
const queries = Queue()
236236
let savepoints = 0
237237
, connection
238+
let transactionId = null
238239

239240
try {
240241
await sql.unsafe('begin ' + options.replace(/[^a-z ]/ig, ''), [], { onexecute }).execute()
@@ -246,6 +247,7 @@ function Postgres(a, b) {
246247
async function scope(c, fn, name) {
247248
const sql = Sql(handler)
248249
sql.savepoint = savepoint
250+
sql.prepare = prepare
249251
let uncaughtError
250252
, result
251253

@@ -266,7 +268,11 @@ function Postgres(a, b) {
266268
throw e instanceof PostgresError && e.code === '25P02' && uncaughtError || e
267269
}
268270

269-
!name && await sql`commit`
271+
if (transactionId) {
272+
!name && await sql.unsafe(`prepare transaction '${transactionId}'`)
273+
}else{
274+
!name && await sql`commit`
275+
}
270276
return result
271277

272278
function savepoint(name, fn) {
@@ -285,6 +291,9 @@ function Postgres(a, b) {
285291
}
286292
}
287293

294+
async function prepare(name) {
295+
transactionId = name
296+
}
288297
function onexecute(c) {
289298
connection = c
290299
move(c, reserved)
@@ -294,6 +303,7 @@ function Postgres(a, b) {
294303
}
295304
}
296305

306+
297307
function move(c, queue) {
298308
c.queue.remove(c)
299309
queue.push(c)

‎tests/bootstrap.js

-1
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,6 @@ exec('createdb', ['postgres_js_test'])
1414
exec('psql', ['-c', 'grant all on database postgres_js_test to postgres_js_test'])
1515
exec('psql', ['-c', 'alter database postgres_js_test owner to postgres_js_test'])
1616

17-
1817
export function exec(cmd, args) {
1918
const { stderr } = spawnSync(cmd, args, { stdio: 'pipe', encoding: 'utf8' })
2019
if (stderr && !stderr.includes('already exists') && !stderr.includes('does not exist'))

‎tests/index.js

+13
Original file line numberDiff line numberDiff line change
@@ -238,6 +238,19 @@ t('Savepoint returns Result', async() => {
238238
return [1, result[0].x]
239239
})
240240

241+
t('Prepared transaction', async() => {
242+
await sql`create table test (a int)`
243+
244+
await sql.begin(async sql => {
245+
await sql`insert into test values(1)`
246+
await sql.prepare('tx1')
247+
})
248+
249+
await sql.unsafe("commit prepared 'tx1'")
250+
251+
return ['1', (await sql`select count(1) from test`)[0].count, await sql`drop table test`]
252+
})
253+
241254
t('Transaction requests are executed implicitly', async() => {
242255
const sql = postgres({ debug: true, idle_timeout: 1, fetch_types: false })
243256
return [

‎types/index.d.ts

+2
Original file line numberDiff line numberDiff line change
@@ -696,6 +696,8 @@ declare namespace postgres {
696696
interface TransactionSql<TTypes extends Record<string, unknown> = {}> extends Sql<TTypes> {
697697
savepoint<T>(cb: (sql: TransactionSql<TTypes>) => T | Promise<T>): Promise<UnwrapPromiseArray<T>>;
698698
savepoint<T>(name: string, cb: (sql: TransactionSql<TTypes>) => T | Promise<T>): Promise<UnwrapPromiseArray<T>>;
699+
700+
prepare<T>(name: string): Promise<UnwrapPromiseArray<T>>;
699701
}
700702
}
701703

0 commit comments

Comments
 (0)