drogonframework / drogonframework/drogon

Add ability to execute SQL queries on all connections in the connection pool

Open
#2,458 1 comment 0 reactions 0 assignees View on GitHub
enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.