drogonframework / drogonframework/drogon
Add ability to execute SQL queries on all connections in the connection pool
- Dominant language
- C++
- Stars
- 14.3k
- Forks
- 1.4k
- Avg merge
- 1d 12h
- Merged PRs (30d)
- 15
Description
SQLite3 is capable of being used in production web apps. However, it's default settings are overly conservative and need to be changed.
Here's the problem: many of the settings that need to be changed for optimal robustness and performance are per-connection (e.g., PRAGMA foerign_keys = ON). But, Drogon's database API currently has no way to execute the needed PRAGMA queries on all connections in the connection pool.
Simply executing the PRAGMA queries one by one won't work, because each query may be executed by different connections in the pool (unless you have only a single connection). I've managed to workaround this by reserving connections using a set of transactions, and then queuing up the pragma queries (some of which cannot be run inside a transaction). However, it's a bit ugly, and might break if the connection pool algorithm changes.
Ideally, we'd have a way to either:
- Send the same query to all connections in the pool, or
- Have a set of initialization queries that are always run whenever a connection in a pool is opened
NOTE: AFAIK, this problem is unique to sqlite3. Mysql and postgres settings are managed via config files.
Contributor guide
Assessment
This issue has not been assessed yet.