prisma: Support setting a timeout for SQLite

I see this issue was tagged as resolved but I’m not seeing any documentation around the solution?

@pimeys thank you for taking the time to respond.

SQLite is not really meant for multi-user use cases. It’s much better if you just have one writer to the database.

Agreed. But let me preface our future discussion by saying I’m not just using SQLite for the fun of it. I’ve used it in the past and have studied it’s capabilities and limitations.

Every connection locks the whole thing while reading or writing, so you only want one of them to that database.

That is not entirely correct, SQLite can handle multiple readers quite well.

That error comes directly from the database. We really can’t do anything to it, if SQLite decides to error out while holding multiple connections into it.

I think you might be mistaken here. SQLite directly supports a “busy timeout”. See docs

Also the sqlite3 npm package supports the timeout as well. See docs

I can only assume that prisma could do something similar.

If you need more connections, I’d consider trying out PostgreSQL.

I work with small companies, usually developing in-house software to be used by at most 5-10 people. SQLite is more than capable of keeping up. And in “production” prisma is the only process that will be communicating with the DB in the vast majority of all cases.

However, if I need to do any maintenance of any kind. I can cause the DB to start throwing BUSY errors. It’s not a problem for the DB to just wait a few seconds. Hence, why I am trying to set a timeout.

The program having to wait is very acceptable in this case, but the busy errors are troublesome.

Is there any other way I can set the timeout?

Here is my workaround, I am getting it in seed operations

function pause(ms) {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

...
const USERS = [
  {
    email: "dave.brown@abc.com",
  },
...

...
  const createUsers = [] as Array<any>;

  for (const USER of USERS) {
    await pause(100);

    const create = db.user.create({ data: { email: USER.email, password: password } });

    createUsers.push(create);
  }

  const users = await Promise.all(createUsers);
...

I also thought it worth sharing that I’ve found this issue as well through a different path. I tried out Blitz on the weekend and spun up a demo project with SQLite and it was mostly a really good experience.

However the first thing I did was set up a database seed file per their guide here

And of course, I had a few dozen things to create to I wrapped them in an await Promise.all(...) statement, which would have tried to run multiple concurrent operations through the same Prisma Client (this is all in a single thread running on my dev machine)

And as soon as you get to about three things happening in parallel (specifically, create statements) the database throws with Operation timed out (SQLite database is busy)

I mention this mainly because it’s a weird/hard block to hit in the first experience, given I was following the documented “happy path” for both Prisma and Blitz.

I was able to work around the problem by rewriting my code to create entries in the database sequentially (using a for ... loop and async await db.thing.create() call in each iteration) but ideally either Prisma Client or the SQLite settings would sort this out for me.

cc/ @flybayer because it impacts Blitz getting started experience

@pimeys personally, I tend to prefer busy_timeout=N. I like features that are so obvious they don’t need documentation.

(Attempting to summarize the thread)

Problem: While a SQLite transaction is open, Prisma fails immediately with a “database is busy error”. Another Node.js client, better-sqlite, will wait until a timeout is reached before failing. If the transaction lock is released, better-sqlite will process it’s query.

Suggested Solution: Prisma should follow this behavior. Wait to process the query, or fail once the timeout has been reached.

• Repro: https://github.com/prisma/prisma/issues/2955#issuecomment-713973362
• Check out @internalfx’s comment which has a video demonstrating the problem.

For the record, this is still reproducible in 2.21.2

@cprieto You seem irritated, I’m not trying to annoy you, I’m trying to solve a problem.

And indeed! The problem does seem to be solved now! I did my testing with v3.15.2.

Cheers! 🥳

Worth noting for other users, I was experiencing failed transaction that resulted in me coming to this thread looking for answers but upgrading to Prisma 3.8.1 seems to have resolved it. I think I was hammering the SQLite DB a little too much for older versions of Prisma to reliably handle, its now working fine. Keep up the great work Prisma Team!

@janpio @timleslie This is easily reproducible. I know this is a long thread but the info is there. @pimeys and I have done a lot of digging.

We have a repo to reproduce, and I’ve put together a video as well. https://github.com/prisma/prisma/issues/2955#issuecomment-682125894

This comment explains what is actually happening. https://github.com/prisma/prisma/issues/2955#issuecomment-682168823

Thanks for the help @pimeys however there still seems to be no difference.

I used the following parameters…

file:../db.sqlite?connection_limit=1&connect_timeout=30

When that had no effect I also tried 30000 just in case it was milliseconds.

I opened the DB in another program and started a transaction (locking the DB) to test, prisma still throws an error immediately when attempting a write.

PrismaClientUnknownRequestError: Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: ConnectionError(Timeout("SQLite database is busy")

Any ideas?

Don’t mean to mining old issues, but I think I’m having this same issue with sqlite and timeouts. I posted i slack, and can share a reproducable repo where seeding always fails if needed. https://prisma.slack.com/archives/CA491RJH0/p1636902018478600

@janpio Thanks for the info. I’ll try to break down a minimal reproduction case and include all the various system versions so that we can try to get to the bottom of this.

Another symptom worth noting is that in my current setup the behaviour is non-deterministic. I’m doing a small handful (4 or 5) concurrent writes, and sometimes they all make it through and sometimes they don’t (perhaps 90% failure rate at a rough guess). I’ll see if I can get to a 100% failure rate to help with reproduction.

Basically Prisma works fine with EXCLUSIVE locking. But fails immediately for NORMAL locking.

ok…now this is getting weird…

I’ve updated my repo, with two test cases. There are some new clues.

https://github.com/internalfx/prisma-sqlite-timeout-demo

I’ve also posted a video showing the problem…

https://github.com/internalfx/prisma-sqlite-timeout-demo/raw/master/prisma-bug.mkv

HA! It is a bug! We have some weirdo param filtering here, and it never gets the timeout parameter.

For some reason my build has a default timeout of five seconds, and I was testing with that value, so I didn’t notice it never worked. When I used a different value I still got a timeout error after five seconds. I’ll fix this now…

Hey @internalfx, SQLite is a bit interesting in databases due to it staying in the same memory as the application you’re running. We do not have socket communication with the system; it’s more of a Mutex<Database>.

I think our defaults for SQLite are wrong. What I would do is the following:

• Set connection_limit=1. Every single query will lock the database from everybody else, so having more connections will not make things faster.
• Set connect_timeout=N where N is seconds. This will give timeouts for the waiting connections when the database is busy too long.

Socket timeout does nothing for SQLite due to us not using a socket with it. If one connection starts to be a bottleneck, I’d consider databases with more advanced locking mechanisms, such as PostgreSQL or MySQL.