One thing missing from this:
There are massive performance gains to be had by using transactions. In other RDBMSs transactions are about atomicity/consistency. But in SQLite, transactions are about batching inserts for awesome speed gains. Use them!
There are massive performance gains to be had by using transactions. In other RDBMSs transactions are about atomicity/consistency. But in SQLite, transactions are about batching inserts for awesome speed gains. Use them!
This example from the better-sqlite3 docs makes more sense now:
const insert = db.prepare('INSERT INTO cats (name, age) VALUES (@name, @age)');
const insertMany = db.transaction((cats) => { for (const cat of cats) insert.run(cat); });
insertMany([ { name: 'Joey', age: 2 }, { name: 'Sally', age: 4 }, { name: 'Junior', age: 1 }, ]);
SQLite is internally handling the locking for you under most providers[0]. Any sort of external attempts at the same will just make things go slower. The only thing that could really beat the internal SQLite mutex is to put a very high performance MPSC queue in front of your single SQLite connection and take resulting micro batches of logical requests into transactions as noted above, or some more consolidated form of representation.
i would've imagined that most RDBMS would have a pool of connections that get reused, rather than creating a new connection every time.
A bunch of inserts in a single transaction is generally fast and unproblematic. Though if you have weird clustered indexes and a lot of concurrency you might run the risk of frequent deadlocks.
What I have done so far was to collect safe data, that is not sensitive info like passwords and such, and save them in an array (in my case I used PHP language), and at a specific number of elements you know it's enough to commit in your database, you do so only once via transaction; in my case was 25K rows and worked flawlessly without users realizing anything at all.
I have even done the same thing with 100K rows and didn't even break a sweat!