8 Database
| (require koyo/database) | package: koyo-lib |
This module provides a database component and functionality for working with database connections.
procedure
(make-database-factory connector [ #:log-statements? log-statements? #:max-connections max-connections #:max-idle-connections max-idle-connections]) → (-> database?) connector : (-> connection?) log-statements? : boolean? = #f max-connections : exact-positive-integer? = 16 max-idle-connections : exact-positive-integer? = 2
When the #:log-statements? argument is #t, statements are logged to the 'koyo:db-statements topic. The data part of the log message includes the thread the statement was executed from, the name of the procedure that executed the query and the statement itself.
Changed in version 0.8 of package koyo-lib: The component no longer forcefully shuts
down its associated custodian when the component is stopped. There
is now a lower bound on crypto-lib for version 1.6 to ensure
that shared libraries (eg. for libargon2) correctly get included in
distributions (using koyo dist or raco distribute).
Changed in version 0.20: Addded the #:log-statements?
argument.
procedure
(call-with-database-connection database proc) → any database : database? proc : (-> connection? any)
Nested calls to call-with-database-connection reuse the same connection.
procedure
(call-with-database-transaction database proc [ #:isolation isolation]) → any database : database? proc : (-> connection? any)
isolation :
(or/c #f 'serializable 'repeatable-read 'read-committed 'read-uncommitted) = #f
Nested calls to call-with-database-transaction reuse the same connection and, if the database supports it, create nested transactions.
syntax
(with-database-connection [id database] e ...+)
database : database?
syntax
(with-database-transaction [id database] maybe-isolation e ...+)
maybe-isolation =
| #:isolation isolation
database : database?
isolation :
(or/c #f 'serializable 'repeatable-read 'read-committed 'read-uncommitted)
For example, the following forms are equivalent:
(with-database-connection [c the-db] (query-value c "select 42"))
(call-with-database-connection the-db (lambda (c) (query-value c "select 42")))
value
procedure
conn : connection? stmt : statement? arg : any/c
procedure
conn : connection? stmt : statement? arg : any/c
syntax
(if-null test-expr fallback-expr)
Added in version 0.27 of package koyo-lib.
Added in version 0.27 of package koyo-lib.
8.1 Database URL
| (require koyo/database-url) | package: koyo-lib |
This module provides a function for parsing DATABASE_URL-style connection strings.
procedure
→
procedure? (or/c #f non-empty-string?) (or/c #f (integer-in 1 65535)) (or/c non-empty-string? 'memory 'temporary) (or/c #f string?) (or/c #f string?) s : string?
a function that can be used to create database connections
the database server host or #f if the scheme is sqlite
the database server port or #f if the scheme is sqlite
the database name or a filename if the scheme is sqlite
the database username or #f
the database password or #f
Examples:
> (parse-database-url "sqlite3:///:memory:") #<procedure:sqlite3-connect>
#f
#f
'memory
#f
#f
> (parse-database-url "sqlite3:///db.sqlite3") #<procedure:sqlite3-connect>
#f
#f
"db.sqlite3"
#f
#f
> (parse-database-url "sqlite3:////path/to/db.sqlite3") #<procedure:sqlite3-connect>
#f
#f
"/path/to/db.sqlite3"
#f
#f
> (parse-database-url "postgres:///example") #<procedure:postgresql-connect>
"127.0.0.1"
5432
"example"
#f
#f
> (parse-database-url "postgres://127.0.0.1:15432/example") #<procedure:postgresql-connect>
"127.0.0.1"
15432
"example"
#f
#f
> (parse-database-url "postgres://user:[email protected]:15432/example") #<procedure:postgresql-connect>
"127.0.0.1"
15432
"example"
"user"
"password"
8.2 Batch Inserts
| (require koyo/database/batch) | package: koyo-lib |
This module provides functionality for batching up database inserts. An insert batcher is an object that buffers rows in memory in order to insert them into the database using as few roundtrips as possible. Insert batchers are not thread safe.
procedure
(insert-batcher? v) → boolean?
v : any/c
Added in version 0.30 of package koyo-lib.
procedure
(make-insert-batcher table columns [ #:dialect dialect #:batch-size batch-size #:on-conflict on-conflict]) → insert-batcher? table : symbol? columns : (listof (list/c symbol? string?)) dialect : (or/c 'postgresql) = 'postgresql batch-size : exact-positive-integer? = 10000
on-conflict : (or/c 'error (list/c 'do-nothing (listof symbol?))) = 'error
Warning: The table name, column names and column types are interpolated into the insert queries. Avoid blindly passing in user input for these values as doing so could lead to SQL injection vulnerabilities.
Creates an insert batcher for the given table and columns. The columns must be a list of pairs of column names and column types, where the types represented as strings for the target dbsystem.
The #:dialect controls how data is inserted into the target database. For example, when the dialect is 'postgresql, the data is passed to the database using one pg-array per column.
The #:batch-size argument controls the maximum number of rows that are buffered in memory before they are flushed.
The #:on-conflict argument determines how conflicts are handled in the target dbsystem. The 'error behavior causes inserts to fail with a constraint violation on conflict. The 'do-nothing behavior ignores conflicts for rows that have a conflict for the given set of columns.
(define ib (make-insert-batcher #:on-conflict '(do-nothing (ticker)) 'tickers '([isin "TEXT"] [ticker "TEXT"] [added_at "TIMESTAMPTZ"]))) (with-database-connection [conn db] (for ([(isin ticker added-at) (in-sequence datasource)]) (ib-push! ib conn isin ticker added-at)) (ib-flush! ib conn))
Added in version 0.30 of package koyo-lib.
procedure
ib : insert-batcher? conn : connection? col-value : any/c
Added in version 0.30 of package koyo-lib.
procedure
ib : insert-batcher? conn : connection?
Added in version 0.30 of package koyo-lib.