Support for PRAGMAs via query params? #84
Comments
luislavena
added a commit
to luislavena/crystal-sqlite3
that referenced
this issue
Jan 15, 2023
Introduce the flexibility to adjust certain PRAGMAs of the SQLite3 connection without having to hardcode those in your codebase (and wait for compilation). This allows applications to use `DATABASE_URL` to dynamically fine tune their SQLite3 configuration. The change complements `#setup_connection` that offers, via code, the option to perform queries on setup of each connection. Only a few PRAGMAs necessary to allow more performant concurrent reads and reduce write locking. These pragmas are detected and combined in a single SQL string to reduce to 1 the number of calls to `sqlite3_exec` C function. There is no validation of supplied values as SQLite3 automatically ignores incorrect values for these pragmas. Closes crystal-lang#84 References: - https://www.sqlite.org/pragma.html
bcardiff
added a commit
that referenced
this issue
Feb 18, 2023
* Automatically set PRAGMAs using connection query params Introduce the flexibility to adjust certain PRAGMAs of the SQLite3 connection without having to hardcode those in your codebase (and wait for compilation). This allows applications to use `DATABASE_URL` to dynamically fine tune their SQLite3 configuration. The change complements `#setup_connection` that offers, via code, the option to perform queries on setup of each connection. Only a few PRAGMAs necessary to allow more performant concurrent reads and reduce write locking. These pragmas are detected and combined in a single SQL string to reduce to 1 the number of calls to `sqlite3_exec` C function. There is no validation of supplied values as SQLite3 automatically ignores incorrect values for these pragmas. Closes #84 References: - https://www.sqlite.org/pragma.html * Simplify PRAGMA mapping and detection No longer prefix PRAGMAS with `_`, so the mapping between the real SQLite3 pragmas and the usage in the URI is more direct. Use macros instead of case to detect pragmas from URI params and return those as NamedTuple. * Update README.md --------- Co-authored-by: Brian J. Cardiff <[email protected]>
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hello folks,
Been using crystal-sqlite3 a lot recently and found a bit cumbersome to use
DB#setup_connectionon every codebase. Things outside of DB pool size or retry timeout cannot be controlled or passed to the lower level driver.When looked at crystal-sqlite3 source noticed a TODO for enabling URI support for additional options:
https://github.com/crystal-lang/crystal-sqlite3/blob/master/src/sqlite3/connection.cr#L5-L6
But the recognized query parameters provided by that are quite limited.
Comparing this with other implementations of sqlite3 in other languages like Go (example mattn/go-sqlite3), saw out-of-the-box support for foreign keys, timeout and journal mode (see feature list).
Made an attempt to explore adding this, but encountered that couldn't use
#execdirectly from within the connectioninitialize, as this is being instantiated by the Pool andexectries to access the pool to pick a connection from it, and then 💣Saw comments in #48 about considering broader support for things like this.
The SQLite3 example of setting PRAGMAs is one, but other examples like TLS support for MySQL comes to my mind. Perhaps something for this could be added to the abstraction that allows doing these adjustments on a per-driver basis?
(perhaps should be posting this on crystal-db instead, but better ask somewhere).
Thank you in advance for your responses! 😊
❤️ ❤️ ❤️
The text was updated successfully, but these errors were encountered: