search

kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.72k stars 271 forks source link

SQLite adapter is not locking the db during migrations #729

Open joanrieu opened 1 year ago

joanrieu commented 1 year ago

Hi, once again thanks for this great library.

I've run into a bad surprise when performing migrations concurrently, as the SQLite adapter does not actually perform any locking during migrations: sqlite-adapter.ts

It assumes incorrectly that connection-level exclusivity is enough to guarantee that migrations can run safely but that is not true: when migrations are run by multiple callers (with each their own db connection), you end up having multiple migrators stepping on each other's toes.

(As a side-note, I do not see why the adapter reports supportsTransactionalDdl = false either.)

koskimas commented 1 year ago

I'm not aware of any way to lock the db reliably. SQLite doesn't even have row locks.

As for transactional DDL, SQLite only supports it partially.

joanrieu commented 1 year ago

About the transactional DDL, I'm by no means an expert, so I would love to read more about that partial support you're talking about, as it directly impacts this topic. All I could find online seemed to indicate full support.

SQLite can perform exclusive full-DB locking for transactions, so assuming transactional DDL is supported this is enough. If working without transactions, the locking_mode pragma can help achieve the same.