Transactions for SQLite

[sant123]

Hello, I haven’t found a discussion neither an issue that talks about the support for transactions. Is this something Node.js will/would support in the future?

This is how better-sqlite3 uses it: https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#transactionfunction---function

Thanks 🙏🏼

Originally posted by @sant123 in https://github.com/nodejs/node/discussions/57396

[targos]

We can certainly discuss the addition of features like that one to make the API more ergonomic, but you can already do transactions using SQL statements: https://sqlite.org/lang_transaction.html

There are some examples in https://github.com/nodejs/node/blob/a0139e06a0754058ffd891f779be55584665f8a8/test/parallel/test-sqlite-transactions.js

@nodejs/sqlite

[sant123]

Thanks @targos, I’m going to give it a try 🙏🏼

[sant123]

This is my code and perfectly worked:

import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync(":memory:");

db.exec(`CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  birthdate TEXT NOT NULL
) STRICT`);

const insertPeople = db.prepare(`
  INSERT INTO people
    (name, birthdate)
  VALUES
    (:name, :birthdate)
  `);

insertPeople.run({ name: "Flash", birthdate: "1956-07-16" });

db.exec("BEGIN TRANSACTION");
insertPeople.run({ name: "Bruno", birthdate: "1962-12-11" });
db.exec("ROLLBACK");

insertPeople.run({ name: "Bruce", birthdate: "1963-06-17" });

const people = db.prepare("SELECT * FROM people").all();

console.log(people);

[
  [Object: null prototype] {
    id: 1,
    name: 'Flash',
    birthdate: '1956-07-16'
  },
  [Object: null prototype] {
    id: 2,
    name: 'Bruce',
    birthdate: '1963-06-17'
  }
]

Thanks a lot @targos for pointing this out.

[geeksilva97]

I like this idea. I plan to start working on it next week if nobody beats me.

[cjihrig]

I'm not opposed to this, but please keep in mind that one day we may have an asynchronous API and we would want a similar API there too.

[geeksilva97]

I'm not opposed to this, but please keep in mind that one day we may have an asynchronous API and we would want a similar API there too.

Is it better to wait for this async api?

[cjihrig]

Personally, I would wait. Transactions are already possible with the current API. It would be a shame to implement something that doesn't translate to an async API. These transaction helper functions seem to exist in modules with a synchronous API like better-sqlite3. I haven't seen a similar helper for async APIs like sqlite3, and that makes me cautious that there could be a technical reason for it.

[geeksilva97]

Got it. Thanks for letting me know.

It looks like better-sqlite3 has a synced version: https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#transactionfunction---function. It has other capabilities when having nested transactions.

Honestly, I don't have a strong opinion about implementing it now, I'm just pursuing the module stability.

[cjihrig]

Honestly, I don't have a strong opinion about implementing it now, I'm just pursuing the module stability.

I don't think this impacts that. The functionality is already available, and adding a helper function in the future would just be a standard semver minor change.

[geeksilva97]

I don't think this impacts that. The functionality is already available, and adding a helper function in the future would just be a standard semver minor change.

Yeah, that's fine. Let's wait.