Utilize Transactions in Data Operations

[gotdibbs]

SQLite has to open a file every time it does a single transaction thus anywhere we do any chaining of operations we should probably be doing them inside of one single transaction to increase performance. This should significantly improve performance of our migrations at the very least.

Bookshelf/Knex appears to provide this functionality already (see: http://bookshelfjs.org/#Bookshelf-Transaction).

[jgable]

+1. I was amazed how much this affected performance in browsers.

[ErisDS]

I looked into this before and I seem to remember @tgriesser saying Transactions were not available for schema-builder queries, only the standard select/insert/update/delete queries... which is kind of a bummer since migrations are almost always builder queries and are probably the ones which could benefit the most.

However, if you can find places to optimise the queries with transactions, please have a stab at them and submit PRs 👍

Also.. if you feel like figuring out transactions for the schema builder queries.. that would also be most excellent.

[willglynn]

MySQL does not support transactional DDL, but that's not the case across the board. PostgreSQL (#1333) lets you say things like:

BEGIN;
CREATE TABLE foos (...);
CREATE TABLE bars (...);
INSERT INTO foos SELECT ... FROM old_table GROUP BY ...;
INSERT INTO bars SELECT ... FROM old_table GROUP BY ...;
DROP TABLE old_table;
CREATE UNIQUE INDEX bars_foo_id_position_key ON bars (foo_id, position);
-- ERROR:  could not create unique index "bars_foo_id_position_key"
-- DETAIL:  Key (foo_id,position)=(1234,5) is duplicated.

ROLLBACK;
-- new tables don't exist, old_table still does; i.e. migration is entirely un-done

SQLite has at least some transactional DDL support, though I don't know how far it goes:

$ sqlite3 foo.db
SQLite version 3.7.12 2012-04-03 19:43:07
sqlite> .tables
sqlite> begin;
sqlite> create table foo (id integer);
sqlite> .tables
foo
sqlite> rollback;
sqlite> .tables
sqlite> 

[jberkus]

SQLite's transactional DDL supports multistatement DDL, if that's what you're asking.

[ErisDS]

This issue has never gotten traction, going to close it for now. If anyone has ideas for specific changes that would be great.