-
-
Notifications
You must be signed in to change notification settings - Fork 383
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
How to use transactions #49
Comments
thank you bud for all the help :D |
I have a question about Transactions. (I hope this is the right place to ask) Scenario : Suppose my sqlArray contains 100 insert statements. the 41st row contains an error in the data. Now, we can have 3 cases :
The current method of using P.S : I think it can be achieved by using try-catch , begin , commit , and db.prepare / db.exec combinations. But, is it possible via Thanks |
How we experience any issue at the moment is if there is an issue at the moment with an insert the whole dB locks up.. users have to close the application and re open.. haven’t found a way around it yet. |
@Souravsaraf Currently no. In order to achieve the effect you want, you'll need to manage the transaction yourself with |
@Custardcs that sounds like a bug, can you open an issue describing it? |
May I suggest adding begin/commit/rollback helper methods to the Database object? const sqlite = require('better-sqlite3');
var db = new sqlite('foo.sqlite');
db.begin();
try {
// doing stuff
db.commit();
} finally {
if(db.inTransaction) db.rollback();
} |
I ended up writing a tiny 'use strict';
const Database = require('better-sqlite3');
Database.prototype.begin = function() { this.prepare('BEGIN' ).run(); }
Database.prototype.commit = function() { this.prepare('COMMIT' ).run(); }
Database.prototype.rollback = function() { this.prepare('ROLLBACK').run(); }
module.exports = Database; |
@dimitry-ishenko I don't want to add such methods to the library because there are many different kinds of "begin" and "commit" statements (such as As for your wrapper, you can improve performance by caching the prepared statements. 'use strict';
const Database = require('better-sqlite3');
const begin = this.prepare('BEGIN');
const commit = this.prepare('COMMIT');
const rollback = this.prepare('ROLLBACK');
Database.prototype.begin = function() { return begin.run(); }
Database.prototype.commit = function() { return commit.run(); }
Database.prototype.rollback = function() { return rollback.run(); }
module.exports = Database; |
@JoshuaWise thank you for your suggestion and thanks for being performance oriented 😄 But wait... I don't have access to const begin = this.prepare('BEGIN');
const commit = this.prepare('COMMIT');
const rollback = this.prepare('ROLLBACK'); So this won't work... 😞 Do I even need to |
@dimitry-ishenko Sorry, I jumped the gun there. You'll need to do that with a constructed database object, like this: 'use strict';
const Database = require('better-sqlite3');
function makeDatabase(...args) {
const db = new Database(...args);
const begin = db.prepare('BEGIN');
const commit = db.prepare('COMMIT');
const rollback = db.prepare('ROLLBACK');
db.begin = () => begin.run();
db.commit = () => commit.run();
db.rollback = () => rollback.run();
return db;
}
module.exports = makeDatabase; |
First post in this thread really needs to be in wiki. I knew I had seen it, but then had a devil of a time finding it when I wanted it again. |
I wonder, @JoshuaWise is there any reason you wrote your code like this: try {
func(...args);
commit.run();
} finally {
if (db.inTransaction) rollback.run();
} instead of try {
func(...args);
commit.run();
} catch (error) {
if (db.inTransaction) rollback.run();
throw error
} |
@pke Nope, either way is fine. |
Transactions have been reworked in version 5.0.0, and they are now well-documented here. |
@JoshuaWise Shouldn't |
UPDATE
Transactions have been reworked in version 5.0.0, and they are now well-documented here.
ORIGINAL POST
In response to #48 (comment)
You can use
db.transaction()
like this:If you need to run javascript code in your transaction, you must manage the transaction manually. This is easier if you create a nice utility like this:
Then you can run arbitrary transactions like this:
The text was updated successfully, but these errors were encountered: