deta:   Functional Database Mapping
1 Principles
1.1 Non-goals
2 Compared to ...
2.1 Racquel
2.2 sql
3 Tutorial
4 Reference
4.1 Query
4.1.1 DDL
create-table!
drop-table!
4.1.2 Entity CRUD
insert!
insert-one!
in-entities
lookup
update!
update-one!
delete!
delete-one!
4.1.3 Query Expressions
+
-
*
/
%
<<
>>
=
>
<
>=
<=
<>
!=
array
array-concat
array-contains?
array-overlap?
array-ref
array-slice
as
between
bitwise-and
bitwise-not
bitwise-or
bitwise-xor
cast
date
extract
fragment
ilike
in
interval
is
is-distinct
json
json-check-path
json-concat
json-contains-all?
json-contains-any?
json-contains-path?
json-contains?
json-ref
json-ref-text
json-ref-text/  path
json-ref/  path
json-remove
json-remove/  path
json-subset?
json-superset?
jsonb
like
position
similar-to
string-concat
subquery
time
timestamp
4.1.4 Query Combinators
query?
delete
from
group-by
join
limit
offset
order-by
project-onto
project-virtual-fields
returning
select
select-for-schema
union
update
where
or-where
4.2 Schema
entity?
make-entity
entity-schema
entity->hash
schema?
define-schema
schema-out
4.3 Type
4.3.1 Support Matrix
4.3.2 Types
type?
id/  f
integer/  f
real/  f
numeric/  f
string/  f
binary/  f
symbol/  f
boolean/  f
date/  f
time/  f
datetime/  f
datetime-tz/  f
uuid/  f
array/  f
json/  f
jsonb/  f
any/  f
4.4 Reflection
current-schema-registry
schema-registry-allow-conflicts?
schema-registry-lookup
schema-virtual?
schema-fields
schema-table
field?
field-id
field-name
field-type
type?
type-declaration
4.5 Changelog
4.5.1 HEAD
4.5.2 v0.9.0 – 2021-07-25
4.5.3 v0.8.0 – 2021-03-06
4.5.4 v0.7.0 – 2021-02-04
4.5.5 v0.6.1 – 2021-01-11
4.5.6 v0.6.0 – 2020-12-20
4.5.7 v0.5.0 – 2020-11-15
4.5.8 v0.4.0 – 2020-10-10
4.5.9 v0.3.4 – 2020-03-20
4.5.10 v0.3.3 – 2020-02-01
4.5.11 v0.3.2 – 2020-01-16
4.5.12 v0.3.1 – 2020-01-12
4.5.13 v0.3.0 – 2019-12-29
4.5.14 v0.2.8 – 2019-12-04
4.5.15 v0.2.7 – 2019-11-26
4.5.16 v0.2.6 – 2019-11-13
4.5.17 v0.2.5 – 2019-10-06
4.5.18 v0.2.4 – 2019-09-27
4.5.19 v0.2.3 – 2019-09-17
4.5.20 v0.2.2 – 2019-09-14
4.5.21 v0.2.1 – 2019-09-13
4.5.22 v0.2.0 – 2019-07-20
4.5.23 v0.1.0 – 2019-07-19
8.0

deta: Functional Database Mapping

@#(struct:element #f (Bogdan Popa < #(struct:element #(struct:style #f (#(struct:target-url mailto:bogdan@defn.io))) (bogdan@defn.io)) >))

 (require deta) package: deta-lib

This library automatically maps database tables to Racket structs and lets you perform CRUD operations and arbitrary queries on them. Sort of like an ORM.

The API is currently stable, but small things may change before 1.0. Watch the GitHub repository if you want to stay on top of potential changes.

1 Principles

The main principle behind this library is "explicitness without tedium." By that I mean that it should be clear to someone who is reading code that uses this library how that 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

If you’re down with that, then, by all means, carry on and read the tutorial!

2 Compared to ...

2.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 backed by mutable objects and with support for associations via lazy loading. Deta’s approach is the opposite of this by focusing on working with immutable structs, avoiding associations and any sort of lazy behavior altogether.

2.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 to compose them 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.

3 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 schema.

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.

4 Reference

4.1 Query

4.1.1 DDL

procedure

(create-table! conn schema)  void?

  conn : connection?
  schema : (or/c schema? symbol?)
Creates the table represented by schema if it does not exist. If schema is a symbol, then it is looked up in the global registry.

procedure

(drop-table! conn schema)  void?

  conn : connection?
  schema : (or/c schema? symbol?)
Drops the table represented by schema if it exists.

4.1.2 Entity CRUD

procedure

(insert! conn e ...)  (listof entity?)

  conn : connection?
  e : entity?
Attempts to insert any newly-created entities into the database, returning the ones that were persisted. Entities that have already been persisted are ignored.

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?
Attempts to insert e. If it doesn’t need to be persisted, then #f is returned.

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
Queries the database and, based on q, either returns a sequence of entities or a sequence of values.

#:batch-size controls how many rows to fetch from the databaase at a time. It is analogous to in-query’s #:fetch argument.

procedure

(lookup conn q)  any

  conn : connection?
  q : query?
Retrieves the first result for q.

If there are no results then #f is returned.

procedure

(update! conn e ...)  (listof entity?)

  conn : connection?
  e : entity?
Attempts to update any modified entities. Only updates the fields that have changed since the entities were retrieved from the database. Returns those entities that have been updated.

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?
Attempts to update e. If it doesn’t need to be updated, then #f is returned.

Equivalent to:

(match (update! conn e)
  [(list e) e]
  [_ #f])

procedure

(delete! conn e ...)  (listof entity?)

  conn : connection?
  e : entity?
Attempts to delete any previously-persisted entities. Returns those entities that have been deleted.

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?
Attempts to delete e. If it doesn’t need to be deleted, then #f is returned.

Equivalent to:

(match (delete! conn e)
  [(list e) e]
  [_ #f])
4.1.3 Query Expressions
  q-expr = (and q-expr ...+)
  | (array q-expr ...)
  | (as q-expr id)
  | (case [q-expr q-expr] ...+)
  | 
(case [q-expr q-expr] ...+
      [else q-expr])
  | (fragment expr)
  | (not q-expr)
  | (or q-expr ...+)
  | (list q-expr ...)
  | (subquery expr)
  | '(q-expr ...)
  | ,expr
  | ,@expr
  | ident
  | boolean
  | string
  | number
  | app
     
  app = (q-expr q-expr ...)
     
  ident = symbol

The grammar for query 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)")

Dynamic lists can be turned into tuples using the ,@(list 1 2 3) syntax:

> (let ([xs (list 1 2 3)])
    (select _ (in 1 ,@(map add1 xs))))

(query "SELECT 1 IN (2, 3, 4)")

Note that the splicing syntax splices scalar lists directly into the query so you must be careful when using it in conjunction with untrusted user input.

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 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 a query expression, the following identifiers are treated specially by the base (i.e. PostgreSQL) dialect. These are inherited by other dialects, but using them with those dialects may result in invalid queries.

operator

+

> (display (select _ (+ 1 2 3)))

#<query: SELECT 1 + 2 + 3>

operator

-

> (display (select _ (- 3 1 0)))

#<query: SELECT 3 - 1 - 0>

operator

*

> (display (select _ (* 2 2 3)))

#<query: SELECT 2 * 2 * 3>

operator

/

> (display (select _ (/ 1 2 3)))

#<query: SELECT 1 / 2 / 3>

operator

%

> (display (select _ (% 10 2)))

#<query: SELECT 10 % 2>

operator

<<

> (display (select _ (<< 10 2)))

#<query: SELECT 10 << 2>

operator

>>

> (display (select _ (<< 10 2)))

#<query: SELECT 10 << 2>

operator

=

> (display (select _ (= 1 1)))

#<query: SELECT 1 = 1>

> (display (select _ (= "a" "a")))

#<query: SELECT 'a' = 'a'>

operator

>

> (display (select _ (> 2 1)))

#<query: SELECT 2 > 1>

operator

<

> (display (select _ (> 1 2)))

#<query: SELECT 1 > 2>

operator

>=

> (display (select _ (>= 2 1)))

#<query: SELECT 2 >= 1>

operator

<=

> (display (select _ (>= 1 2)))

#<query: SELECT 1 >= 2>

operator

<>

> (display (select _ (<> 1 2)))

#<query: SELECT 1 <> 2>

operator

!=

> (display (select _ (!= 1 2)))

#<query: SELECT 1 != 2>

operator

array

> (display (select _ (array 1 2 3)))

#<query: SELECT ARRAY[1, 2, 3]>

operator

array-concat

> (display (select _ (array-concat (array 1 2) (array 3))))

#<query: SELECT ARRAY[1, 2] || ARRAY[3]>

operator

array-contains?

> (display (select _ (array-contains? (array 1 2) (array 1))))

#<query: SELECT ARRAY[1, 2] @> ARRAY[1]>

operator

array-overlap?

> (display (select _ (array-overlap? (array 1) (array 1 2))))

#<query: SELECT ARRAY[1] && ARRAY[1, 2]>

operator

array-ref

> (display (select _ (array-ref (array 1 2 3) 1)))

#<query: SELECT (ARRAY[1, 2, 3])[1]>

operator

array-slice

> (display (select _ (array-slice (array 1 2 3 4 5) 1 3)))

#<query: SELECT (ARRAY[1, 2, 3, 4, 5])[1:3]>

operator

as

> (display (select _ (as 1 x)))

#<query: SELECT 1 AS x>

operator

between

> (display (select _ (between (date "2021-04-09")
                              (date "2021-04-01")
                              (date "2021-05-01"))))

#<query: SELECT (DATE '2021-04-09') BETWEEN (DATE '2021-04-01') AND (DATE '2021-05-01')>

operator

bitwise-and

> (display (select _ (bitwise-and 1 2)))

#<query: SELECT 1 & 2>

operator

bitwise-not

> (display (select _ (bitwise-not 0)))

#<query: SELECT ~ 0>

operator

bitwise-or

> (display (select _ (bitwise-or 0 1)))

#<query: SELECT 0 | 1>

operator

bitwise-xor

> (display (select _ (bitwise-xor 1 1)))

#<query: SELECT 1 # 1>

operator

cast

> (display (select _ (cast 1 float)))

#<query: SELECT CAST(1 AS FLOAT)>

operator

date

> (display (select _ (date "2021-04-09")))

#<query: SELECT DATE '2021-04-09'>

operator

extract

> (display (select _ (extract hour (timestamp "2021-04-09 18:25:00"))))

#<query: SELECT EXTRACT(HOUR FROM (TIMESTAMP '2021-04-09 18:25:00'))>

operator

fragment

operator

ilike

> (display (select _ (ilike "A" "%a%")))

#<query: SELECT 'A' ILIKE '%a%'>

operator

in

> (display (select _ (in 5 '(1 2 3 4 5))))

#<query: SELECT 5 IN (1, 2, 3, 4, 5)>

operator

interval

> (display (select _ (interval "5 minutes")))

#<query: SELECT INTERVAL '5 minutes'>

operator

is

> (display (select _ (is null null)))

#<query: SELECT NULL IS NULL>

operator

is-distinct

> (display (select _ (is-distinct 1 null)))

#<query: SELECT 1 IS DISTINCT NULL>

operator

json

> (display (select _ (json "{}")))

#<query: SELECT JSON '{}'>

operator

json-check-path

> (display (select _ (json-check-path (json "{}") "$.a[*] > 2")))

#<query: SELECT (JSON '{}') @@ '$.a[*] > 2'>

operator

json-concat

> (display (select _ (json-concat (json "{}")
                                  (json "{\"a\": 42}"))))

#<query: SELECT (JSON '{}') || (JSON '{"a": 42}')>

operator

json-contains-all?

> (display (select _ (json-contains-all? (json "{\"a\": 1}") (array "a" "b"))))

#<query: SELECT (JSON '{"a": 1}') ?& ARRAY['a', 'b']>

operator

json-contains-any?

> (display (select _ (json-contains-any? (json "{\"a\": 1}") (array "a" "b"))))

#<query: SELECT (JSON '{"a": 1}') ?| ARRAY['a', 'b']>

operator

json-contains-path?

> (display (select _ (json-contains-path? (json "{\"a\": {\"b\": 42}}") "$.a.b")))

#<query: SELECT (JSON '{"a": {"b": 42}}') @? '$.a.b'>

operator

json-contains?

> (display (select _ (json-contains? (json "{\"a\": 42}") "a")))

#<query: SELECT (JSON '{"a": 42}') ? 'a'>

operator

json-ref

> (display (select _ (json-ref (json "{\"a\": {\"b\": 42}}") "a" "b")))

#<query: SELECT (JSON '{"a": {"b": 42}}') -> 'a' -> 'b'>

operator

json-ref-text

> (display (select _ (json-ref-text (json "{\"a\": \"hello\"}") "a")))

#<query: SELECT (JSON '{"a": "hello"}') ->> 'a'>

operator

json-ref-text/path

> (display (select _ (json-ref-text/path (json "{\"a\": \"hello\"}") (array "a"))))

#<query: SELECT (JSON '{"a": "hello"}') #>> ARRAY['a']>

operator

json-ref/path

> (display (select _ (json-ref/path (json "{\"a\": \"hello\"}") (array "a"))))

#<query: SELECT (JSON '{"a": "hello"}') #> ARRAY['a']>

operator

json-remove

> (display (select _ (json-remove (json "{\"a\": \"hello\"}") "a")))

#<query: SELECT (JSON '{"a": "hello"}') - 'a'>

operator

json-remove/path

> (display (select _ (json-remove/path (json "{\"a\": \"hello\"}") (array "a"))))

#<query: SELECT (JSON '{"a": "hello"}') #- ARRAY['a']>

operator

json-subset?

> (display (select _ (json-subset? (jsonb "{}") (jsonb "{\"a\": 1}"))))

#<query: SELECT (JSONB '{}') <@ (JSONB '{"a": 1}')>

operator

json-superset?

> (display (select _ (json-superset? (jsonb "{\"a\": 1}") (jsonb "{}"))))

#<query: SELECT (JSONB '{"a": 1}') @> (JSONB '{}')>

operator

jsonb

> (display (select _ (jsonb "{}")))

#<query: SELECT JSONB '{}'>

operator

like

> (display (select _ (like "a" "%a%")))

#<query: SELECT 'a' LIKE '%a%'>

operator

position

> (display (select _ (position "om" "Thomas")))

#<query: SELECT POSITION('om' IN 'Thomas')>

operator

similar-to

> (display (select _ (similar-to "a" "abc")))

#<query: SELECT 'a' SIMILAR TO 'abc'>

operator

string-concat

> (display (select _ (string-concat "a" "bc" "def")))

#<query: SELECT 'a' || 'bc' || 'def'>

operator

subquery

> (display (select _ (as (subquery (select _ 1)) x)))

#<query: SELECT (SELECT 1) AS x>

operator

time

> (display (select _ (time "18:45:00")))

#<query: SELECT TIME '18:45:00'>

operator

timestamp

> (display (select _ (timestamp "2021-04-09 18:45:00")))

#<query: SELECT TIMESTAMP '2021-04-09 18:45:00'>

4.1.4 Query Combinators

procedure

(query? q)  boolean?

  q : any/c
Returns #t when q is a query.

procedure

(delete q)  query?

  q : query?
Converts q into a DELETE query, preserving its FROM and WHERE clauses.

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)
(from ,table-name-expr  #:as alias)
 
  table-name : non-empty-string?
  query : query?
Creates a new SELECT query? from a schema or a table name.

> (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")

>
> (let ([tbl "users"])
    (from ,tbl #:as u))

(query "SELECT * 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 ...+)

The racket/list module also provides a group-by function. Importing both racket/list and deta can result in a name collision, which you can resolve using only-in, rename-in, except-in or prefix-in to selectively require the needed functions and/or rename them locally.

Adds a GROUP BY clause to query. If query already has one, then the new columns are appended to the existing clause.

> (~> (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 maybe-lateral table-name #:as alias #:on q-expr)

(join query maybe-type maybe-lateral schema-id #:as alias #:on q-expr)
(join query maybe-type maybe-lateral (subquery query) #:as alias #:on q-expr)
(join query maybe-type maybe-lateral ,table-name-expr  #:as alias #:on q-expr)
 
maybe-type = 
  | #:inner
  | #:left
  | #:right
  | #:full
  | #:cross
     
maybe-lateral = 
  | #:lateral
 
  table-name : non-empty-string?
  query : query?
Adds a JOIN to query. If a join type is not provided, then the join defaults to an INNER join.

> (~> (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")

syntax

(limit query n)

(limit query ,e)
Adds or replaces a LIMIT n clause to query.

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")

syntax

(offset query n)

(offset query ,e)
Adds or replaces an OFFSET n clause to query.

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 maybe-nulls-direction
  | #:desc maybe-nulls-direction
  | ,direction-expr  maybe-nulls-direction
     
maybe-nulls-direction = 
  | #:nulls-first
  | #:nulls-last
  | ,nulls-direction-expr
 
  direction-expr : (or/c 'asc 'desc)
  nulls-direction-expr : (or/c 'nulls-first 'nulls-last)
Adds an ORDER BY clause to query. If query already has one, then the new columns are appended to the existing clause.

> (~> (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")

> (~> (from "artworks" #:as a)
      (order-by ([a.year #:desc #:nulls-last])))

(query "SELECT * FROM artworks AS a ORDER BY a.year DESC NULLS LAST")

procedure

(project-onto q s)  query?

  q : query?
  s : schema?
Changes the target schema for q to s. Projecting a query does not change the fields that that query selects so, most of the time, you will have to explicitly select the fields you want when using project-onto.

> (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")

Omitting the select does not produce the expected query in this case since the books table has different fields from a book-stats value:

>
> (~> (from "books" #:as b)
      (group-by b.year-published)
      (order-by ([b.year-published #:desc]))
      (project-onto book-stats-schema))

(query

 "SELECT * FROM books AS b GROUP BY b.year_published ORDER BY b.year_published DESC")

procedure

(project-virtual-fields q)  query?

  q : query?
Modifies q such that any virtual fields belonging to its projected schema will be populated by in-entities and lookup.

syntax

(returning query q-expr ...+)

Adds a RETURNING clause to query. If query already has one, then the new columns are appended to the existing clause.

> (~> (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")

syntax

(select _ maybe-distinct q-expr ...+)

(select query maybe-distinct q-expr ...+)
 
maybe-distinct = 
  | #:distinct
 
  query : query?
Refines the set of selected values in query. This operation removes the schema, if any, from the input query so you’ll have to use project-onto to project the results onto an entity, otherwise the resulting query will return a sequence of values.

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

(select-for-schema query schema
                     #:from tbl-alias-id)
(select-for-schema query schema
                     #:from tbl-alias-id
                     #:customizing ([field-id q-expr] ...))
 
schema = schema-id
  | ,schema-expr
 
  query : query?
  schema-expr : schema?
Adds a select clause to query for every field in schema. Expressions for individual fields may be customized via the #:customizing option.

> (define-schema example
   ([a string/f]
    [b integer/f]
    [c integer/f]))
>
> (~> (from example #:as e)
      (select-for-schema example #:from e))

(query "SELECT e.a, e.b, e.c FROM examples AS e")

>
> (~> (from example #:as e)
      (join "some_table" #:as t #:on (= t.a e.a))
      (select-for-schema
       example
       #:from e
       #:customizing
       ([c (* t.c 2)])))

(query

 "SELECT e.a, e.b, t.c * 2 FROM examples AS e JOIN some_table AS t ON t.a = e.a")

Use this operator in conjunction with project-virtual-fields to project joined or otherwise-constructed virtual fields onto a schema.

syntax

(union query-1 query-2)

Produces a query that is the union of query-1 and 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]
Converts query into an UPDATE query, preserving its FROM clause, making it the target table for the update, and its WHERE clause.

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)

Wraps the WHERE clause in query to the result of AND-ing it with 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)

Wraps the WHERE clause in query to the result of OR-ing it with 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')))")

4.2 Schema

procedure

(entity? e)  boolean?

  e : any/c
Returns #t when e is an instance of a schema struct (i.e. an entity).

procedure

(make-entity conn-or-dialect schema cols)  entity?

  conn-or-dialect : (or/c connection? symbol?)
  schema : schema?
  cols : (listof any/c)
Instantiates an entity from schema using the given set of cols and either the dialect represented by conn-or-dialect or its inferred dialect if it is a connection?. The cols must be provided in the same order as schema’s fields. Virtual fields must be omitted.

Two entites are equal? when they are instances of the same schema and all their fields are equal?.

procedure

(entity-schema e)  schema?

  e : entity?
Retrieves e’s schema.

procedure

(entity->hash e [f])  (hash/c symbol? any/c)

  e : entity?
  f : (-> symbol? any/c any/c) = (λ (k v) v)
Returns an immutable hash? where the entries are the ids of every field in e and their associated values.

The f argument can be used to convert individual fields’ values before they are added to the hash.

> (require gregor)
> (define-schema book
    #:virtual
    ([title string/f]
     [author string/f]
     [published-at datetime-tz/f]))
> (define b
   (make-book
    #:title "Lord of the Rings"
    #:author "J. R. R. Tolkien"
    #:published-at (iso8601->moment "1954-07-29T00:00:00Z")))
> (entity->hash b)

'#hasheq((author . "J. R. R. Tolkien")

         (published-at . #<moment 1954-07-29T00:00:00Z>)

         (title . "Lord of the Rings"))

> (entity->hash b (λ (_ v)
                      (cond
                        [(moment? v)
                         (moment->iso8601 v)]
                        [else
                         v])))

'#hasheq((author . "J. R. R. Tolkien")

         (published-at . "1954-07-29T00:00:00Z")

         (title . "Lord of the Rings"))

procedure

(schema? s)  boolean?

  s : any/c
Returns #t when s is a schema.

syntax

(define-schema id
  maybe-table
  maybe-virtual
  (field-definition ...+)
  maybe-pre-persist-hook
  maybe-pre-delete-hook
  maybe-struct-option ...)
 
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-virtual
 maybe-contract
 maybe-wrapper]
     
maybe-table = 
  | #:table table-name
     
maybe-virtual = 
  | #:virtual
     
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
     
maybe-pre-persist-hook = 
  | #:pre-persist-hook pre-persist-hook
     
maybe-pre-delete-hook = 
  | #:pre-delete-hook pre-delete-hook
     
maybe-struct-option = 
  | struct-option
 
  table-name : non-empty-string?
  field-name : non-empty-string?
  field-type : type?
  pre-persist-hook : (-> entity? entity?)
  pre-delete-hook : (-> entity? entity?)
Defines a schema named id. The schema will have an associated struct with the same name and a smart constructor called make-id. The struct’s "dumb" constructor is hidden so that invalid entities cannot be created.

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 on a schema definition, then the resulting schema’s entities will not be able to be persisted, nor will the schema be registered in the global registry.

Alternatively, individual fields may be set as #:virtual. Such fields must have a default value defined, and will be ignored when performing database operations. in-entities will produce entities with virtual fields set to their default values. Virtual fields may be used for data not directly mapped to database columns (see also the any/f field type.)

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.

All provided struct-options are passed directly to the underlying struct definition.

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.

The #:wrapper option allows you to supply a function for use in normalizing values. The function will be automatically called when using a schema’s make- or setter functions, and its return value is the value that will be stored in the database. A wrapper function cannot be used to coerce values to the field’s type; both the value passed to it and the value it returns must satisfy the field’s contract.

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_, so that, for example, 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)

Exports all bindings related to 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"

4.3 Type

These are all the field types currently supported by deta. Note that not all database backends support all of these types.

4.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

id/f

  

exact-nonnegative-integer?

  

INTEGER / SERIAL

  

INTEGER

integer/f

  

exact-integer?

  

INTEGER

  

INTEGER

real/f

  

real?

  

REAL

  

REAL

numeric/f

  

(or/c rational? +nan.0)

  

NUMERIC

  

UNSUPPORTED

string/f

  

string?

  

TEXT

  

TEXT

binary/f

  

bytes?

  

BYTEA

  

BLOB

symbol/f

  

symbol?

  

TEXT

  

TEXT

boolean/f

  

boolean?

  

BOOLEAN

  

INTEGER

date/f

  

date-provider?

  

DATE

  

TEXT

time/f

  

time-provider?

  

TIME

  

TEXT

datetime/f

  

datetime-provider?

  

TIMESTAMP

  

TEXT

datetime-tz/f

  

moment-provider?

  

TIMESTMAPTZ

  

TEXT

uuid/f

  

uuid?

  

UUID

  

UNSUPPORTED

array/f

  

vector?

  

ARRAY

  

UNSUPPORTED

json/f

  

jsexpr?

  

JSON

  

UNSUPPORTED

jsonb/f

  

jsexpr?

  

JSONB

  

UNSUPPORTED

any/f

  

any/c

  

N/A

  

N/A

4.3.2 Types

procedure

(type? v)  boolean?

  v : any/c

value

id/f : type?

value

integer/f : type?

value

real/f : type?

procedure

(numeric/f precision scale)  type?

  precision : exact-integer?
  scale : exact-integer?

value

string/f : type?

value

binary/f : type?

value

symbol/f : type?

value

boolean/f : type?

value

date/f : type?

value

time/f : type?

value

datetime/f : type?

value

datetime-tz/f : type?

value

uuid/f : type?

procedure

(array/f t)  type?

  t : type?

value

json/f : type?

value

jsonb/f : type?

The various types that deta supports.

value

any/f : type?

A special type used to indicate that no type restriction is applied to a field. May only be used on virtual fields.

4.4 Reflection

 (require deta/reflect) package: deta-lib

The bindings provided by this module let you access schema and field metadata at runtime. Before 1.0 is released, I might make breaking changes to this module. It’s unlikely, but it could happen so keep that in mind!

parameter

(current-schema-registry)  (hash/c symbol? schema?)

(current-schema-registry registry)  void?
  registry : (hash/c symbol? schema?)
 = (make-hasheq)
Holds the current schema registry hash.

parameter

(schema-registry-allow-conflicts?)  boolean?

(schema-registry-allow-conflicts? allow?)  void?
  allow? : boolean?
 = #f
When this parameter is #f (the default), registering multiple schemas with the same id fails with a user error. It may be useful to set this to #t when reloading code with dynamic-rerequire.

procedure

(schema-registry-lookup id)  schema?

  id : symbol?
Returns the schema whose id is id from the registry. If a schema with the requested id is not registered, an exn:fail:user? is raised.

procedure

(schema-virtual? s)  boolean?

  s : schema?
Returns #t when s is a virtual schema.

procedure

(schema-fields s)  (listof field?)

  s : schema?
Returns a list of every field defined in s.

procedure

(schema-table s)  string?

  s : schema?
Returns the name of the table s maps to.

procedure

(field? v)  boolean?

  v : any/c
Returns #t when v is a deta field.

procedure

(field-id f)  symbol?

  f : field?
Returns the Racket name of f.

procedure

(field-name f)  string?

  f : field?
Returns the name of the database column f is associated with.

procedure

(field-type f)  type?

  f : field?
Returns the deta type of f.

procedure

(type? v)  boolean?

  v : any/c
Returns #t when v is a deta type.

procedure

(type-declaration t dialect)  string?

  t : type?
  dialect : (or/c 'postgresql 'sqlite3)
Returns the DDL type declaration for the type t under the dialect SQL dialect.

4.5 Changelog

4.5.1 HEAD
4.5.2 v0.9.0 – 2021-07-25

Added:

4.5.3 v0.8.0 – 2021-03-06

Added:
Changed:
Fixed:

4.5.4 v0.7.0 – 2021-02-04

Changed:
  • A syntax error is now reported if two or more fields in a schema are marked as #:primary-keys.

4.5.5 v0.6.1 – 2021-01-11

Fixed:

4.5.6 v0.6.0 – 2020-12-20

Added:

4.5.7 v0.5.0 – 2020-11-15

Added:

4.5.8 v0.4.0 – 2020-10-10

Added:
  • from and join now support runtime table names.

4.5.9 v0.3.4 – 2020-03-20

Fixed:
  • virtual-connections can now be used with deta.

  • The SQLite3 dialect now correctly emits date-time related function calls.

4.5.10 v0.3.3 – 2020-02-01

Fixed:
  • #t and #f are now translated to 1 and 0, respectively, when provided as query parameters under SQLite.

4.5.11 v0.3.2 – 2020-01-16

Added:

4.5.12 v0.3.1 – 2020-01-12

Fixed:
  • union now produces correct syntax for SQLite.

4.5.13 v0.3.0 – 2019-12-29

Added:

4.5.14 v0.2.8 – 2019-12-04

Fixed:
  • An issue where ANY expressions were wrapped in excessive parens.

4.5.15 v0.2.7 – 2019-11-26

Fixed:
  • Array value retrieval and insertion.

4.5.16 v0.2.6 – 2019-11-13

Added:
  • Support for quoted tuples in q-exprs.

4.5.17 v0.2.5 – 2019-10-06

Added:

4.5.18 v0.2.4 – 2019-09-27

Added:

4.5.19 v0.2.3 – 2019-09-17

Fixed:

4.5.20 v0.2.2 – 2019-09-14

Fixed:
  • sql-null values are now handled correrctly

4.5.21 v0.2.1 – 2019-09-13

Fixed:
  • create-table! now raises an appropriate exception when its id argument is invalid

4.5.22 v0.2.0 – 2019-07-20

Added:

Changed:
  • Dropped #:with keyword from join

4.5.23 v0.1.0 – 2019-07-19

Added:

Changed:

Fixed:
  • Loosened the return contract on lookup to any