deta: Functional Database Mapping
(require deta) | package: deta-lib |
This library automatically maps database tables to Racket structs and lets you perform CRUD operations on them as well as arbitrary queries. Sort of like an ORM, but without associations and all the bad bits.
The API is currently fairly stable, but it may change before 1.0. Watch the GitHub repository if you want to stay on top of potential changes.
1 Principles
The main principle backing this library is "explicitness without tedium." By that I mean that it should be crystal clear to someone who is reading code that uses this library how the code maps to database operations, while making the common cases of mapping data between database tables and Racket structs straightforward and simple.
1.1 Non-goals
Support for every SQL dialect. Despite the fact that both SQLite and PostgreSQL are currently supported by this library, the focus is on PostgreSQL and SQLite just serves as a check to ensure that nothing is too PostgreSQL specific.
Being a general purpose SQL DSL. For some queries you may have to resort to raw SQL or the sql library. deta is very much an "80% solution."
Being externally-extensible. The SQL AST as well as all of the dialect code is considered private and any new dialects (such as MySQL) will have to be added to the library itself.
If you’re down with that, then, by all means, carry on and read the tutorial!
2 Tutorial
deta builds upon the db library. You will use deta to generate your mappings and create queries, but the db library will be doing the actual work of talking to the database and handling transactions.
Let’s start by creating a database connection in the usual way:
> (require db) >
> (define conn (postgresql-connect #:database "deta" #:user "deta" #:password "deta"))
Next, let’s define a schema for books:
> (require deta) >
> (define-schema book ([id id/f #:primary-key #:auto-increment] [title string/f #:contract non-empty-string? #:wrapper string-titlecase] [author string/f #:contract non-empty-string?] [published-on date/f]))
The above generates a struct named book with fields for the table’s id, title, author and published-on columns, an associated "smart constructor" called make-book and functional setter and updater functions for each field.
> (require gregor) >
> (define a-book (make-book #:title "To Kill a Mockingbird" #:author "Harper Lee" #:published-on (date 1960 7 11))) > > (book-id a-book) #<sql-null>
> (book-title a-book) "To Kill A Mockingbird"
> (book-title (update-book-title a-book (lambda (t) (string-append t "?")))) "To Kill A Mockingbird?"
> ; schema entities are immutable so the above did not change a-book > (book-title a-book) "To Kill A Mockingbird"
We can use the schema to issue DDL commands to the database and create the table:
> (create-table! conn 'book)
While the DDL functionality is convenient for the purposes of this tutorial, in real world projects you should probably use something like north to manage your database table schemas.
Now that we have a table, we can insert the book that we created into the database:
> (define saved-book (insert-one! conn a-book)) > > (book-id saved-book) 1
Let’s insert a few more books:
> (void (insert! conn (make-book #:title "1984" #:author "George Orwell" #:published-on (date 1949 6 8)) (make-book #:title "The Lord of the Rings" #:author "J.R.R. Tolkien" #:published-on (date 1954 7 29)) (make-book #:title "The Catcher in the Rye" #:author "J.D. Salinger" #:published-on (date 1949 7 16))))
And now let’s query for all of the books published before 1955:
> (require threading) >
> (for/list ([b (in-entities conn (~> (from book #:as b) (where (< b.published-on (date "1955-01-01"))) (order-by ([b.published-on #:desc]))))]) (book-title b)) '("The Lord Of The Rings" "The Catcher In The Rye" "1984")
Sweet! Here’s the query we just ran:
> (displayln (~> (from book #:as b) (where (< b.published-on (date "1955-01-01"))) (order-by ([b.published-on #:desc])))) #<query: SELECT b.id, b.title, b.author, b.published_on FROM books AS b WHERE b.published_on < (DATE '1955-01-01') ORDER BY b.published_on DESC>
What about dynamic parameters, you may ask? Let’s wrap the above query in a function:
> (define (books-before year) (~> (from book #:as b) (where (< b.published-on ,(sql-date year 1 1))) (order-by ([b.published-on #:desc])))) >
> (for/list ([b (in-entities conn (books-before 1950))]) (book-title b)) '("The Catcher In The Rye" "1984")
>
> (for/list ([b (in-entities conn (books-before 1955))]) (book-title b)) '("The Lord Of The Rings" "The Catcher In The Rye" "1984")
Any time the query combinators encounter an unquote, that value gets replaced with a placeholder node in the query AST and, when the query is eventually executed, the value is bound to its prepared statement. This makes it safe and easy to parameterize your queries without having to worry about SQL injection attacks.
Oftentimes, you’ll want to query data from the DB that doesn’t match your schema. For example, let’s say we want to grab the number of books published by year from our database. To do that, we can declare a "virtual" schema – one whose entities can’t be persisted – and project our queries onto that schema.
> (define-schema book-stats #:virtual ([year date/f] [books integer/f])) >
> (define books-published-by-year (~> (from book #:as b) (select (as (cast (date_trunc "year" b.published-on) date) year) (count b.title)) (group-by year) (order-by ([year])) (project-onto book-stats-schema))) >
> (for ([s (in-entities conn books-published-by-year)]) (displayln (format "year: ~a books: ~a" (book-stats-year s) (book-stats-books s))))
year: #<date 1949-01-01> books: 2
year: #<date 1954-01-01> books: 1
year: #<date 1960-01-01> books: 1
If we hadn’t wrapped our query with project-onto, then the data would’ve been returned as values which we could destructure inside the for loop, exactly like in-query from db.
It’s time to wind things down so let’s delete all the books published before 1950:
> (query-exec conn (delete (books-before 1950)))
Re-run the last query to make sure it worked:
> (for ([s (in-entities conn books-published-by-year)]) (displayln (format "year: ~a books: ~a" (book-stats-year s) (book-stats-books s))))
year: #<date 1954-01-01> books: 1
year: #<date 1960-01-01> books: 1
That’s all there is to it. You now know the basics of deta. Thanks for following along! If you want to learn more be sure to check out the reference documentation below.
3 Compared to *
3.1 Racquel
Racquel takes a more classic approach to database mapping by being a "real" ORM. It is based on the class system, with entities (data objects, as it calls them) being backed by mutable objects and having support for associations via lazy loading. deta’s approach is nearly the opposite of this by focusing on working with immutable structs, avoiding associations altogether and any sort of lazy behaviour.
3.2 sql
The sql library is great at statically generating SQL queries. The problem is that the generated queries are not composable at runtime. You have to write macros upon macros to handle composition and I’ve found that that gets tedious quickly.
On top of giving you composable queries – as you can hopefully see from the tutorial –, deta also automatically maps CRUD operations to structs, which is out of scope for sql.
4 TODOs
The following features are planned:
VALUES expressions
Column constraints for DDL
The following query forms are not currently supported:
UPDATE ... FROM ...
SELECT DISTINCT ...
SELECT ... HAVING ...
SELECT ... WINDOW ...
SELECT ... {INTERSECT | EXCEPT} ...
SELECT ... FOR UPDATE ...
5 Reference
5.1 Query
(require deta/query) | package: deta-lib |
5.1.1 DDL
procedure
(create-table! conn schema) → void?
conn : connection? schema : (or/c schema? symbol?)
procedure
(drop-table! conn schema) → void?
conn : connection? schema : (or/c schema? symbol?)
5.1.2 Entity CRUD
procedure
conn : connection? e : entity?
Raises a user error if any of the entities are based on virtual schemas.
procedure
(insert-one! conn e) → (or/c false/c entity?)
conn : connection? e : entity?
Equivalent to:
(match (insert! conn e) [(list e) e] [_ #f])
procedure
(in-entities conn q [#:batch-size batch-size]) → sequence?
conn : connection? q : query? batch-size : (or/c exact-positive-integer? +inf.0) = +inf.0
#:batch-size controls how many rows to fetch from the databaase at a time. It is analogous to in-query’s #:fetch argument.
procedure
conn : connection? q : query?
If there are no results then #f is returned.
procedure
conn : connection? e : entity?
Raises a user error if any of the entities don’t have a primary key field.
procedure
(update-one! conn e) → (or/c false/c entity?)
conn : connection? e : entity?
Equivalent to:
(match (update! conn e) [(list e) e] [_ #f])
procedure
conn : connection? e : entity?
Raises a user error if any of the entities don’t have a primary key field.
procedure
(delete-one! conn e) → (or/c false/c entity?)
conn : connection? e : entity?
Equivalent to:
(match (delete! conn e) [(list e) e] [_ #f])
5.1.3 Query Combinators
q-expr = (array q-expr ...) | (as q-expr id) | (and q-expr ...+) | (case [q-expr q-expr] ...+) |
(case [q-expr q-expr] ...+ [else q-expr]) | (fragment expr) | (or q-expr ...+) | (list q-expr ...) | '(q-expr ...) | ,expr | ident | boolean | string | number | app app = (q-expr q-expr ...) ident = symbol
The grammar for SQL expressions.
Tuples are created using the (list 1 2 3) or '(1 2 3) syntax:
> (select _ (in 1 '(1 2 3 4))) (query "SELECT 1 IN (1, 2, 3, 4)")
> (select _ (in 1 (list 1 2 3 4))) (query "SELECT 1 IN (1, 2, 3, 4)")
Arrays are created using the (array 1 2 3) syntax:
> (select _ (array-concat (array 1 2) (array 3 4))) (query "SELECT ARRAY[1, 2] || ARRAY[3, 4]")
Various operators (like in) have built-in support and generate queries predictably. Operator names are always lower-case so (in a b) is valid, while (IN a b) is not. If you find an operator that you need doesn’t produce the query you expect, then open an issue on GitHub and I’ll fix it as soon as I can.
Within an SQL expression, the following identifiers are treated specially by the base (i.e. PostgreSQL) dialect. These are inherited by other dialects, but using them may result in invalid queries.
Identifier |
| |||||||
array-concat |
|
| ||||||
array-contains? |
|
| ||||||
array-overlap? |
|
| ||||||
array-ref |
|
| ||||||
array-slice |
|
| ||||||
bitwise-not |
|
| ||||||
bitwise-and |
|
| ||||||
bitwise-or |
|
| ||||||
bitwise-xor |
|
|
An error is raised if q is anything other than a SELECT query.
> (delete (from "users" #:as u)) (query "DELETE FROM users AS u")
syntax
(from table-name #:as alias)
(from schema-id #:as alias) (from (subquery query) #:as alias)
table-name : non-empty-string?
query : query?
> (define-schema user ([id id/f #:primary-key #:auto-increment] [username string/f])) > > (from "users" #:as u) (query "SELECT * FROM users AS u")
> > (from user #:as u) (query "SELECT u.id, u.username FROM users AS u")
>
> (~> (from (subquery (from user #:as u)) #:as out) (select (count out.*)))
(query
"SELECT COUNT(out.*) FROM (SELECT u.id, u.username FROM users AS u) AS out")
syntax
(group-by query q-expr ...+)
> (~> (from "books" #:as b) (select b.year-published (count *)) (group-by b.year-published))
(query
"SELECT b.year_published, COUNT(*) FROM books AS b GROUP BY b.year_published")
syntax
(join query maybe-type table-name #:as alias #:on q-expr)
(join query maybe-type schema-id #:as alias #:on q-expr) (join query maybe-type (subquery query) #:as alias #:on q-expr)
maybe-type =
| #:inner | #:left | #:right | #:full | #:cross
table-name : non-empty-string?
query : query?
> (~> (from "posts" #:as p) (join "post_images" #:as pi #:on (= p.id pi.post-id)) (join #:left "comments" #:as c #:on (= p.id c.post-id)) (select p.* c.*))
(query
"SELECT p.*, c.* FROM posts AS p JOIN post_images AS pi ON p.id = pi.post_id LEFT JOIN comments AS c ON p.id = c.post_id")
The first form raises a syntax error if n is not an exact positive integer or 0.
> (~> (from "users" #:as u) (offset 20) (limit 10)) (query "SELECT * FROM users AS u LIMIT 10 OFFSET 20")
The first form raises a syntax error if n is not an exact positive integer or 0.
> (~> (from "users" #:as u) (offset 10)) (query "SELECT * FROM users AS u OFFSET 10")
syntax
(order-by query ([column maybe-direction] ...+))
maybe-direction =
| #:asc | #:desc | ,e
> (~> (from "users" #:as u) (order-by ([u.last-login #:desc] [u.username]))) (query "SELECT * FROM users AS u ORDER BY u.last_login DESC, u.username")
> (define direction 'desc)
> (~> (from "users" #:as u) (order-by ([u.last-login ,direction]))) (query "SELECT * FROM users AS u ORDER BY u.last_login DESC")
procedure
(project-onto q s) → query?
q : query? s : schema?
> (define-schema book-stats #:virtual ([year-published integer/f] [books integer/f])) >
> (~> (from "books" #:as b) (select b.year-published (count *)) (group-by b.year-published) (order-by ([b.year-published #:desc])) (project-onto book-stats-schema))
(query
"SELECT b.year_published, COUNT(*) FROM books AS b GROUP BY b.year_published ORDER BY b.year_published DESC")
syntax
(returning query q-expr ...+)
> (~> (delete (from "users" #:as u)) (where (not u.active?)) (returning u.id)) (query "DELETE FROM users AS u WHERE NOT u.is_active RETURNING u.id")
The first form (with the _) generates a fresh query.
> (select _ 1 2) (query "SELECT 1, 2")
> (select (from "users" #:as u) u.username) (query "SELECT u.username FROM users AS u")
syntax
(union query-1 query-2)
> (~> (select _ 1) (union (select _ 2)) (union (select _ 3))) (query "SELECT 1 UNION (SELECT 2 UNION (SELECT 3))")
syntax
(update query assignment ...+)
assignment = [column q-expr]
An error is raised if q is anything other than a SELECT query.
> (~> (from "users" #:as u) (update [active? #t])) (query "UPDATE users AS u SET is_active = TRUE")
syntax
(where query q-expr)
> (~> (from "users" #:as u) (where u.active?) (where (> u.last-login (- (now) (interval "2 weeks")))))
(query
"SELECT * FROM users AS u WHERE u.is_active AND (u.last_login > ((NOW()) - (INTERVAL '2 weeks')))")
syntax
(or-where query q-expr)
> (~> (delete (from "users" #:as u)) (where (not u.active?)) (or-where (< u.last-login (- (now) (interval "1 year")))))
(query
"DELETE FROM users AS u WHERE (NOT u.is_active) OR (u.last_login < ((NOW()) - (INTERVAL '1 year')))")
5.2 Schema
(require deta/schema) | package: deta-lib |
syntax
(define-schema id maybe-table maybe-virtual (field-definition ...+) maybe-pre-persist-hook maybe-pre-delete-hook)
maybe-table =
| #:table table-name maybe-virtual =
| #:virtual maybe-pre-persist-hook =
| #:pre-persist-hook pre-persist-hook maybe-pre-delete-hook =
| #:pre-delete-hook pre-delete-hook field-definition =
[id field-type maybe-name maybe-primary-key maybe-auto-increment maybe-unique maybe-nullable maybe-contract maybe-wrapper] |
[(id default) field-type maybe-name maybe-primary-key maybe-auto-increment maybe-unique maybe-nullable maybe-contract maybe-wrapper] maybe-name =
| #:name field-name maybe-primary-key =
| #:primary-key maybe-auto-increment =
| #:auto-increment maybe-unique =
| #:unique maybe-nullable =
| #:nullable maybe-contract =
| #:contract e maybe-wrapper =
| #:wrapper e
table-name : non-empty-string?
field-name : non-empty-string?
field-type : type?
pre-persist-hook : (-> entity? entity?)
pre-delete-hook : (-> entity? entity?)
For every defined field there will be an associated functional setter and updater named set-id-field and update-id-field, respectively.
If a table-name is provided, then that is used as the name for the table. Otherwise, an "s" is appended to the schema id to pluralize it. Currently, there are no other pluralization rules.
If #:virtual is provided, then the resulting schema’s entities will not be able to be persisted, nor will the schema be registered in the global registry.
The pre-persist-hook is run before an entity is either insert!ed or update!d.
The pre-delete-hook is run before an entity is delete!d.
Hooks do not run for arbitrary queries.
A syntax error is raised if you declare a field as both a primary key and nullable. Additionally, a syntax error is raised if a schema has multiple primary keys.
Every type has an associated contract so the #:contract option for fields is only necessary if you want to further restrict the values that a field can contain.
When converting field names to SQL, dashes are replaced with underscores and field names that end in question marks drop their question mark and are prefixed with "is_". admin? becomes is_admin.
Custom field names can be specified by providing a #:name in the field definition. Note, however, that the library does not currently translate between field names and custom column names within arbitrary queries.
Example:
> (define-schema book ([id id/f #:primary-key #:auto-increment] [title string/f #:unique #:contract non-empty-string? #:wrapper string-titlecase] [author string/f #:contract non-empty-string?]))
syntax
(schema-out schema)
> (module sub racket/base (require deta) (provide (schema-out album)) (define-schema album #:virtual ([id id/f #:primary-key #:auto-increment] [title string/f] [band string/f]))) > > (require 'sub)
> (define an-album (make-album #:title "Led Zeppelin" #:band "Led Zeppelin")) > > (album? an-album) #t
> (album-title an-album) "Led Zeppelin"
> (album-title (update-album-title an-album string-upcase)) "LED ZEPPELIN"
5.3 Type
(require deta/type) | package: deta-lib |
These are all the field types currently supported by deta. Note that not all database backends support all of these types.
5.3.1 Support Matrix
Here are all the types and how they map to the different backends.
Field Type |
| Racket Type |
| PostgreSQL Type |
| SQLite Type |
|
| INTEGER / SERIAL |
| INTEGER | ||
|
| INTEGER |
| INTEGER | ||
|
| REAL |
| REAL | ||
|
| NUMERIC |
| UNSUPPORTED | ||
|
| TEXT |
| TEXT | ||
|
| BLOB |
| BLOB | ||
|
| TEXT |
| TEXT | ||
|
| BOOLEAN |
| INTEGER | ||
|
| DATE |
| TEXT | ||
|
| TIME |
| TEXT | ||
|
| TIMESTAMP |
| TEXT | ||
|
| TIMESTMAPTZ |
| TEXT | ||
|
| ARRAY |
| UNSUPPORTED | ||
|
| JSON |
| UNSUPPORTED | ||
|
| JSONB |
| UNSUPPORTED |
5.3.2 Types
procedure
v : any/c
value
value
value
procedure
precision : exact-integer? scale : exact-integer?
value
value
value
value
value
value
value
value
procedure
t : type?
value
value
5.4 Changelog
5.4.1 v0.2.8 – 2019-12-04
An issue where ANY expressions were wrapped in excessive parens.
5.4.2 v0.2.7 – 2019-11-26
Array value retrieval and insertion.
5.4.3 v0.2.6 – 2019-11-13
Support for quoted tuples in q-exprs.
5.4.4 v0.2.5 – 2019-10-06
order-by now supports dynamic directions
(fragment e) syntax to q-expr
5.4.5 v0.2.4 – 2019-09-27
5.4.6 v0.2.3 – 2019-09-17
datetime/f values now serialize correctly
5.4.7 v0.2.2 – 2019-09-14
sql-null values are now handled correrctly
5.4.8 v0.2.1 – 2019-09-13
create-table! now raises an appropriate exception when its id argument is invalid
5.4.9 v0.2.0 – 2019-07-20
Support for subquery
Dropped #:with keyword from join
5.4.10 v0.1.0 – 2019-07-19
Support for joins