bogdanp / deta Goto Github PK

The following program provokes an error:
; prepare: no such column: GITHUB
I suspect that (somewhere) there an "deta field name to sql fieldname" conversion missing.

#lang racket
(require deta db threading)

(define db
  (sqlite3-connect #:database "bug.db"
                   #:mode     'create))

(define-schema github-user
  ([id         id/f        #:primary-key #:auto-increment]
   [user-id    integer/f]            ; racket-stories user-id   
   [github-id  integer/f   #:unique] ; github user id
   [login      string/f    #:unique] ; github login (username)
   [real-name  string/f]             
   [email      string/f]              
   [avatar-url string/f]
   [blog-url   string/f]))


(create-table! db 'github-user)

(lookup db (~> (from github-user #:as gu)
               (where (= github-id 123))))

the full error:

; prepare: no such column: GITHUB
;   error code: 1
; Context:
;  /Applications/Racket v7.4/collects/db/private/sqlite3/connection.rkt:466:0 handle-status*
;  /Applications/Racket v7.4/collects/db/private/sqlite3/connection.rkt:422:8
;  /Applications/Racket v7.4/collects/db/private/sqlite3/connection.rkt:225:4 prepare1* method in connection%
;  /Users/soegaard/Library/Racket/7.4/pkgs/deta-lib/private/query.rkt:23:2
;  /Applications/Racket v7.4/collects/db/private/generic/functions.rkt:90:0 compose-statement
;  /Applications/Racket v7.4/share/pkgs/db-lib/db/private/generic/functions2.rkt:64:0
;  /Users/soegaard/Library/Racket/7.4/pkgs/deta-lib/query.rkt:254:0 unpack219
;  /Users/soegaard/Library/Racket/7.4/pkgs/deta-lib/query.rkt:267:0 lookup
;  "/Users/soegaard/tmp/bug.rkt":1:1 [running body]
; [Due to errors, REPL is just module language, requires, and stub definitions]

It looks like deta doesn't properly escape column names. For example:

(define-schema name
  ([user string/f]))
(create-table! conn 'name)

fails with:

query-exec: syntax error at or near "user"
  SQLSTATE: 42601
  context...:
   /usr/share/racket/collects/db/private/generic/interfaces.rkt:170:0: raise-sql-error
   /usr/share/racket/pkgs/db-lib/db/private/postgresql/connection.rkt:560:10
   /usr/share/racket/pkgs/db-lib/db/private/postgresql/connection.rkt:108:6
   /usr/share/racket/pkgs/db-lib/db/private/postgresql/connection.rkt:367:15
   /usr/share/racket/collects/db/private/generic/common.rkt:308:13
   /usr/share/racket/collects/db/private/generic/common.rkt:215:18
   /usr/share/racket/collects/db/private/generic/common.rkt:180:8
   /usr/share/racket/collects/db/private/generic/common.rkt:209:4: call-with-lock* method in locking%
   /usr/share/racket/pkgs/db-lib/db/private/postgresql/connection.rkt:364:4: query method in connection-base%
   /usr/share/racket/collects/db/private/generic/functions.rkt:148:0: query-exec
   /usr/share/racket/collects/racket/contract/private/arrow-higher-order.rkt:375:33

The Postgres query log shows that it's trying to run:

STATEMENT:  CREATE TABLE IF NOT EXISTS names(
        user TEXT NOT NULL)

It looks like the issue is in quote/standard: this function only quotes column names if they're non-alphanumeric-ASCII, but it should quote them for reserved words, too. Or it could simply quote them always, as I don't think there's any downside from doing that.

Ended up needing to do crazy convenient things like ordering by values inside jsonb fields :)

Something like SELECT * FROM products ORDER BY attributes->'year' or SELECT * FROM products WHERE attributes->'year' = '2021'.

Not sure how to make those look nice in s-expressions... perhaps analogous to array-concat and other array/f functions? Something like json-ref, etc, perhaps...

P.S. Sorry for editing, hit return while typing...

Using the example for union from the docs:

#lang racket/base
(require racket/base deta threading db)

(define cache-conn (sqlite3-connect #:database "test.db" #:mode 'create))

(define q
  (~> (select _ 1)
      (union (select _ 2))
      (union (select _ 3))))
(displayln q)
(lookup cache-conn q)

Produces:

#<query: SELECT 1 UNION (SELECT 2 UNION (SELECT 3))>
. . prepare: near "(": syntax error
  error code: 1

Running the generated query directly in a SQLite client produces the same error. Removing all parentheses allows the query to run fine.

Maybe the parentheses are something PostgreSQL tolerates for readability but SQLite prohibits? See https://stackoverflow.com/a/4653180

Following the tutorial, I stumbled upon the following problem.

[email protected]> (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))
prepare: near "'1955-01-01'": syntax error
  error code: 1

The problem seems to be that the SQL statement built

[email protected]> (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>

It seems incompatible with SQLite version 3.31.1.

[email protected]> (query-list conn "select sqlite_version()")
'("3.31.1")

If we change the SQL to

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

then it works on version 3.31.1. That's the issue. Thanks for writing the library!

If I use subquery in join closure, I will get "join: contract violation expected: (or/c schema? string? symbol?)"
So I think it should add new contract query?

Thanks

What's the current story for doing a migration -- e.g. adding a column to an existing table? I didn't see functions for this. Is that a "use raw SQL" kind of thing?

when you use unquote inside a query it generates a query with the following form:
( query statement args ... )
but at least when you run it with query exec using racket 8.0 it fails with this message:

prepare: near "AS": syntax error
  error code: 1
  context...:
   /usr/share/racket/collects/db/private/sqlite3/connection.rkt:466:0: handle-status*
   /usr/share/racket/collects/db/private/sqlite3/connection.rkt:422:8
   /usr/share/racket/collects/db/private/sqlite3/connection.rkt:225:4: prepare1* method in connection%
   /usr/share/racket/collects/db/private/generic/common.rkt:163:24
   /home/jujo/.racket/8.0/pkgs/deta-lib/private/query.rkt:28:2
   /usr/share/racket/collects/racket/contract/private/arrow-higher-order.rkt:375:33
   /usr/share/racket/collects/db/private/generic/functions.rkt:90:0: compose-statement
   /usr/share/racket/collects/db/private/generic/functions.rkt:148:0: query-exec

this happens even in your example file, the workaround is nothing fancy (pattern matching and using apply), but I don't think this is the expected behaviour. could it be something in my racket setup or do you find the same results using racket 8.0?

I'm not sure if this is really a problem, mere an inconvenience:

test.rkt:

#lang racket/base

(require deta
         racket/list)

$ racket test.rkt
test.rkt:4:9: module: identifier already required
  at: group-by
  in: racket/list
  also provided by: deta
  location...:
   test.rkt:4:9
  context...:
   do-raise-syntax-error
   check-not-defined
   for-loop
   [repeats 1 more time]
   perform-require!
   for-loop
   finish
   [repeats 2 more times]
   pass-1-and-2-loop
   module-begin-k
   expand-module
   expand-capturing-lifts
   temp98_0
   temp71_0
   compile
   temp65_0
   ...

I've been doing (require (only-in racket/list ...) to work around this. I guess sacrificing group-by in deta is an option, so maybe we can add a note to the docs for newbie like me? :)

As a side note, it only happens when requiring racket/list explicitly, but not in #lang racket, where bindings from racket/list are provided automatically (I'm not sure why).

deta-lib looks great. Only downside is I have some databases that are MySQL. I could convert them to PostgreSQL, or I could help add MySQL support to deta-lib, if that isn't already part of your plan. If I were to do so, any pointers? I've looked over the source code, so I have a rough idea of how I'd proceed, but if you have any pointers, I'm all ears.

I know the docs say there is no association support. I get that.

But to clarify: Does that mean one simply doesn't use Deta for cases where models have associations?

Or if you do, would it be possible to see an example of that? I'm curious what the intended idioms/patterns are when using Deta in these contexts.

Let's say I have two tables named users and roles, they are related by roles_users in the many-to-many relationships.

And then now, I need to check out all the roles of each one user. The code may be like this:

(define-schema user-with-role
  #:virtual
  ([id id/f #:primary-key #:auto-increment]
   [name string/f]
   [role_names (array/f string/f)]))

(lookup conn (~> (from "users" #:as u)
                 (join "roles_users"
                       #:as ru
                       #:on (= ru.user_id u.id))
                 (join "roles"
                       #:as r
                       #:on (= ru.role_id r.id))
                 (select u.id u.name (as (array_agg r.name) role_names))
                 (group-by u.id)
                 (project-onto user-with-role-schema)))

The result was expected to be like (user-with-role #<meta> 22 "Yanying" '#(role1 role2 role3)), but instead of generating the defined schema format data, it raised a in-vector: contract violation excepted: vector given: pg-array ...... [,bt for context] error. This seems it didn't convert the pg-array type correctly.

Am I missing something and do we have a way to achieve this so far?

The field-definition arg includes the line:

(id default) field-type

There isn't really documentation for this (except perhaps a note about virtual entities -- ?). I was hoping I could say something like (id "gen_random_uuid()") uuid/f or even (id uuid-string), but those don't seem to work.

After playing around for a bit, I think the way it works is:

• There's no way to specify a Postgres DEFAULT value/expression
• You can optionally specify a literal constant for the default (for deta only, of course)
• It doesn't have to match the ID type (e.g., you can use #f to signify "not set yet")
• If you specify a field default, you don't need to specify a value when calling make-<entity> (but you can, in which case that value takes precedence)
• To get a default for your field (through deta only), use #:pre-persist-hook (but be sure to have it check for the default-default value, because you don't want to change the ID of an already-persisted entity)

Is this roughly correct? I'm not sure the "right" way to do this. I kind of hope I'm missing something and it's simpler, or could be made simpler. Thanks!

The code is:

(define (books-before year)
  (~> (from book #:as b)
      (where (< b.published-on ,(sql-date year 1 1)))
      (order-by ([b.published-on #:desc]))))
(in-entities conn (books-before 1950))

The error is:

bind-prepared-statement: cannot convert given value to SQL type
  given: (sql-date 1950 1 1)
  type: parameter
  dialect: SQLite
  context...:
   /Applications/Racket v8.1/collects/db/private/generic/interfaces.rkt:209:0: error*
   /Applications/Racket v8.1/collects/db/private/sqlite3/dbsystem.rkt:35:0: check-param
   /Applications/Racket v8.1/collects/db/private/generic/prepared.rkt:90:0: apply-type-handlers
   /Applications/Racket v8.1/collects/db/private/generic/prepared.rkt:70:4: bind method in prepared-statement%
   /Applications/Racket v8.1/collects/racket/contract/private/arrow-higher-order.rkt:375:33
   /Applications/Racket v8.1/collects/db/private/generic/functions.rkt:90:0: compose-statement
   /Applications/Racket v8.1/share/pkgs/db-lib/db/private/generic/functions2.rkt:64:0: in-query-helper
   /Applications/Racket v8.1/collects/racket/contract/private/arrow-val-first.rkt:555:3
   /Users/coq/Library/Racket/8.1/pkgs/deta-lib/query.rkt:291:0: in-entities
   body of "/Users/coq/Documents/programs/racket/deta/first-deta-p.rkt"

Hi, I'm looking for a way to define a foreign key constraint in my schemas, is there a way to do it or is out of the scope of this project?

I found out today that when using project-onto with a “virtual” schema, the columns must be selected in precisely the same order as they are specified in the virtual schema’s define-schema definition. The query attempts to assign the values in this order without reference to the column names; if the field types do not match, you’ll see errors; if they do match, then no error is given but the “getter” functions on the returned structs will give you the wrong values.

I don’t know if this is the expected behavior that everyone except me knew about already, or if it is a subtlety that would just be worth including in the documentation, or if it is actually a bug.

When I try to use a virtual connection or a connection pool (as described in the db package: https://docs.racket-lang.org/db/using-db.html), I get errors.

I could very well be doing something wrong, but before I launched into an investigation, I wanted to ask if Deta does theoretically support these db features. Do you use them in your production environment(s) with Deta?

For the record, with virtual-connection, I get errors like: prepare: cannot prepare statement with virtual connection and

send: no such method
  method name: async-message-evt
  class name: proxy-connection%

There isn’t much info on the docs on the precise behavior of #:wrapper-specified functions. The tutorial has one examples of it in use, but there is actually no description of its behavior or capabilities under the docs define-schema where one would expect to find it.

For example, at first I thought I could use it to automatically convert a value to the required type, such as for dates:

#lang racket/base

(require deta db gregor)

(define-schema article
  ([id id/f #:primary-key #:auto-increment]
   [date date/f #:wrapper iso8601->date]))

(define cache-conn (sqlite3-connect #:database "db.sqlite" #:mode 'create))

(create-table! cache-conn 'article)

(define a (make-article #:date "2020-01-04"))

(insert-one! cache-conn a)

But this results in an error:

make-article: contract violation
  expected: date-provider?
  given: "2020-01-04"
  in: an and/c case of
      the #:date argument of
      (->*
       (#:date (and/c date-provider? any/c))
       (#:id (and/c natural? any/c))
       article?)
  contract from: (function make-article)
  blaming: /Users/joel/Documents/code/sandbox/deta/deta-1.rkt
   (assuming the contract is correct)
  at: /Users/joel/Documents/code/sandbox/deta/deta-1.rkt

I’m not saying this should be allowed; rather, that it would be good for the docs for define-schema to explain exactly what #:wrapper functions can and can’t do. If you will accept a pull request for this I will be happy to work on it.

i think this is as small an example as i can make

(define-schema recipe
  ([id id/f #:primary-key #:auto-increment]
   [Favorite? boolean/f  #:name "Favorite"]))

(favs (val)
  (sequence->list (in-entities conn
                               (~> (from recipe #:as b)
                                   (where (= ,val b.Favorite))
                                   )))
(favs true) ;throws errors/contract violations

Hi Bogdan.

I wonder if this logic ought to be reconsidered. What happens when you insert an entity into SQLite? You simply query for the last inserted row-id and update the entity struct that was past in to insert-one! and then simply return this entity to the user. I imagine there's an implicit assumption that what you insert is what you have in the database. Unfortunately, this isn't true when you have triggers that may e.g. default some values in the inserted row. I learnt this the hard way.

Here's an example:

create table if not exists test (
  id        integer primary key autoincrement,
  ts        timestamp default (strftime('%Y-%m-%dT%H:%M:%S', CURRENT_TIMESTAMP)),
  gt        timestamp,
  gf        text
);

create trigger if not exists test_default_ts_compute_gt after insert on test
BEGIN
  update test set ts = strftime('%Y-%m-%dT%H:%M:%S', ifnull(ts, CURRENT_TIMESTAMP)) where id = new.id;
  update test set gt = strftime('%Y-%m-%dT%H:%M:%S', ifnull(gt, datetime(ts, new.gf))) where id = new.id;
END;

This will render the entity returned by insert-one! stale.

What I think would be a less dangerous and more intuitive approach is to do the moral equivalent of:

BEGIN TRANSACTION;
      insert into test (ts, gt, gf) values (NULL, NULL, '24 hours');
      SELECT * FROM test where id = (SELECT last_insert_rowid());
END TRANSACTION;

This probably applies to other DBs not just SQLite.

Thank you

I guess I find another bug:

> (~> (from "users" #:as u)
      (where (not (in u.id (list 1 2 3)))))

(query "SELECT * FROM users AS u WHERE NOT (u.id IN (1, 2, 3))")
>
>

it was expected to return "SELECT * FROM users AS u WHERE u.id NOT IN (1, 2, 3)"

A more practical example:

(~> (from "users" #:as u)
      (where (= u.name "Yanying"))
      (where (not (in u.id (list 11 22 33)))))
(query "SELECT * FROM users AS u WHERE (u.name = 'Yanying') AND (NOT (u.id IN (11, 22, 33)))")

I'm using a secondary table to store tags/labels attached to the primary table, and I want to be able to query based on these tags such that if it happens that multiple tags match a single entry, that entry is returned only once. Here's a simplified example:

#lang racket/base

(require db
         deta
         threading)

(define conn
  (postgresql-connect #:database "testing"
                      #:user "testing"
                      #:password "<random string>"))

(query-exec conn "CREATE TEMPORARY TABLE deta_items (id INT PRIMARY KEY, sku VARCHAR(250))")
(query-exec conn (string-append
                  "CREATE TEMPORARY TABLE deta_tags ("
                  "  item INT REFERENCES deta_items,"
                  "  tag VARCHAR(250),"
                  "  PRIMARY KEY(item, tag))"))
(query-exec conn "INSERT INTO deta_items VALUES (1, 'hello')")
(query-exec conn "INSERT INTO deta_tags VALUES (1, 'greeting'), (1, 'singleword')")

(define query-tags '("greeting" "singleword"))

(printf "deta: ~a~n"
        (query-rows conn (~> (from "deta_items" #:as di)
                             (join "deta_tags" #:as dt #:on (= di.id dt.item))
                             (where (= dt.tag (any ,query-tags)))
                             (select di.id di.sku))))

(printf "manual / distinct: ~a~n"
        (query-rows conn (string-append
                          "SELECT DISTINCT di.id, di.sku FROM deta_items AS di "
                          "JOIN deta_tags AS dt ON dt.item = di.id "
                          "WHERE dt.tag = ANY($1)")
                    query-tags))

Output:

deta: (#(1 hello) #(1 hello))
manual / distinct: (#(1 hello))

I would be happy to try implementing DISTINCT (maybe as a (distinct) form?) if you think it's okay to do so. Though I probably need a little hint at how to approach it.

I took some time to understand how to use these, and I'm not even certain about it.
Do we use it like the following ?

(require db deta)

(define vc ...) ; Virtual connection

(define-schema
  sch<>
    ([id id/f #:primary-key #:autoincrement]
     [name string/f]))

(create-all!)

(define A (make-sch<> #:name "TestA"))
(insert! vc A)

; So update! and update-one! needs a returned entity from autogenerated setters ?
(update-one! vc (set-sch<>-name A "TestModA"))

Storing a moment? with a timezone to a postgres database seems to lose the timezone info. Deta bug or user error?

Repro:

 (require deta gregor db threading)

 (define conn
   (postgresql-connect #:database "test"
                                   #:user     "test"
                                   #:password "test"))
 
 (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?]
                 [last-read-at datetime-tz/f]))

 (create-table! conn 'book)

 (define a-book
   (make-book #:title "To Kill a Mockingbird"
              #:author "Harper Lee"
              #:last-read-at (now/moment #:tz "America/Los_Angeles")))

 ;Works
 (displayln (~t (book-last-read-at a-book) "zzzz"))
 
 (define same-book
   (insert-one! conn a-book))

 ;Still Works
 (displayln (~t (book-last-read-at same-book) "zzzz"))

(define same-book-reloaded    
  (lookup conn 
          (~> (from book #:as b)
              (where (= b.id 1)))))

 ;Error
 (displayln (~t (book-last-read-at same-book-reloaded) "zzzz"))

Specifically the error is:

~t: contract violation
  expected: (and/c tzid-provider?)
  given: #<moment 2020-02-13T00:37:21.478157Z>
  argument position: 1st
  other arguments...:
   "zzzz"
  context...:
   (submod "/mc-data/tests/random.rkt" test): [running body]
   temp37_0
   for-loop
   run-module-instance!125
   /usr/share/racket/pkgs/compiler-lib/compiler/commands/test.rkt:179:16

The program below uses datetime/f. On mysql it works fine, but with PostgreSQL
i get the error:

; sql-timestamp: arity mismatch;
;  the expected number of arguments does not match the given number
;   expected: 8
;   given: 7
;   arguments...:
;    2019
;    9
;    17
;    15
;    32
;    26
;    322940918
; Context:
;  /Users/soegaard/Library/Racket/7.4/pkgs/deta-lib/query.rkt:61:0 insert-entity!

I think the problem is this line:

https://github.com/Bogdanp/deta/blob/master/deta-lib/type.rkt#L271

where sql-timestamp from db only gets 7 arguments.

#lang racket
(require db deta gregor)
(define db
    (postgresql-connect #:database "deta"
                        #:user     "deta"
                        #:password "deta"))

(define-schema example
  ([id              id/f        #:primary-key #:auto-increment]
   [created-at      datetime/f]))

(make-example #:created-at (now))
(create-table! db 'example)
(insert! db (make-example #:created-at (now)))

In the table of types: TIMESTMAPTZ.

I already read the doc, but can not find a way to write a query may be like this SQL:

SELECT "users" FROM "users" WHERE "users"."id" IN (1, 2, 3)

The most approximate way would be like this:

> (~> (from user #:as u)
    (where (IN u.id '(1 2 3))))
(query "SELECT u.id, u.name FROM users AS c WHERE IN(u.id, QUOTE(1(2, 3)))")

but apparently, this doesn't work.

Currently deta maps binary/f fields to the type BLOB. But in Postgresql, no such type actually exists, hence creating a table with a binary/f field gives me:

query-exec: type "blob" does not exist
  SQLSTATE: 42704

From the Postgresql wiki:

Caveat: many people come to this page looking for PostgreSQL's equivalent of the MySLQ (sic) "BLOB*" data types. The equivalent in Postgres is BYTEA.

I think the behaviour should be that binary/f should map to BYTEA for Postgresql.

When modelling my data (entities translated in multiple languages) I'm gravitating towards something like the following:

(struct/contract thing/i18n
  ([lang string?]
   [title string?]
   [description string?]))

(struct/contract thing
  ([uuid uuid?]
   [sku string?]
   [price exact?]
   [i18n (listof thing/i18n)]))  ;; or possibly a hash of lang code -> thing/i18n

i.e. I would like to be working with thing mostly as a single entity in that I would like to be able to retrieve thing from the database as one logical operation (and one joined query) as well delegate the job of inserting its related tables to itself (perhaps via a hook).

I was thinking that if deta could allow me to declare additional struct fields that are not mapped directly to database columns, I could use hooks to insert the relevant data in the related tables and to somehow map joined query results onto the additional fields.

I don't mean lazy behavior necessarily, but the ability to have a hybrid nonvirtual-virtual schema.

What do you think?

How would you go about writing Scribble docs for a deta schema? Use defstruct and add a caveat that it’s not actually a vanilla struct? Or just document the make- id function?

It would be neat if there was a defschema form for Scribble, but I can see why it might not be high on the list.

If I try to make a schema with a non-integer primary key, I get an error:

#lang racket/base

(require deta db)

(define-schema article
  ([page symbol/f #:primary-key]
   [date string/f]))

(define cache-conn (sqlite3-connect #:database "db.sqlite" #:mode 'create))

(create-table! cache-conn 'article)

(define a (make-article #:page 'my-article.html
                        #:date "2020-01-04"))

(insert-one! cache-conn a)

results in this error (on the insert-one! call):

set-article-page: contract violation
  expected: symbol?
  given: 1
  in: an and/c case of
      the 2nd argument of
      (->*
       (article? (and/c symbol? any/c))
       (boolean?)
       article?)
  contract from: (function set-article-page)
  blaming: /Users/joel/Documents/code/sandbox/deta/deta-1.rkt
   (assuming the contract is correct)
  at: /Users/joel/Documents/code/sandbox/deta/deta-1.rkt

Is there a way to get a list of fields and their types from a schema?

When I'm using deta to create some schemas, I found some fields might have data in int32? but deta can only build integer/f which is int4.

I think exposing define-type would be the best solution for future extensive. For example

(define-type bigint
  #:contract exact-integer?
  #:declaration "BIGINT")

Should I do it manually or is there a way to turno a entity into a hashmap or something?

define-type is provided from (submod deta/type define), which is not such common practice to provide bindings. Would it be better if the deta/type module (other than a submodule) provide the define-type macro? We can use except-out in deta/main.rkt to prevent define-type is provided from the main module.

Also, this define-type has the same with the one from typed-racket, through deta does not support typed-racket. But another name (define-field-type?) would help to make it unambiguous, IMHO.

I have a table with a few columns named similarly, and I want to pass a parameter to an expression to specify which column I want to use in the query. Here’s a naive example:

(define (list-articles type #:series [s #t] #:limit [lim -1] #:order [order 'desc])
  (define html-field (format "a.listing-~a-html" type))
  (~> (from article #:as a)
      (select (as ,html-field html)
              pub-field
              ser-field)
      (limit ,lim)
      (order-by ([published ,order]))
      (project-onto listing-schema)))

You can see why this won’t work; the value of the html column will always be the string contents of html-field, not the contents of the field it refers to:

> (lookup cache-conn (list-articles 'full #:series "test-series"))
(listing #<meta> "a.listing-full-html" #<date 2018-08-07> 'series/test-series.html)
> (list-articles 'full #:series "test-series")
(query "SELECT $1 AS html, a.published, a.series_page FROM articles AS a ORDER BY PUBLISHED DESC LIMIT $2" "a.listing-full-html" -1)

I have been able to do what I want by putting the entire query inside a macro:

(require (for-syntax racket/base syntax/parse racket/syntax))
(define-syntax (new-list-articles stx)
  (syntax-parse stx
    [(_ type
        (~optional (~seq #:series s) #:defaults ([s #'#t]))
        (~optional (~seq #:limit lim) #:defaults ([lim #'-1]))
        (~optional (~seq #:order order) #:defaults ([order #''desc])))
     (with-syntax ([html-field (format-id #'type "a.listing-~a-html" #'type)])
       #'(~> (from article #:as a)
             (select (as html-field html)
                     a.published
                     a.series-page)
             (limit ,lim)
             (order-by ([a.published ,order]))
             (project-onto listing-schema)))]))

Is this the best way to do this, or did I miss a simpler/better method?

I would like to use the uuid PostgreSQL type with deta - should I prepare a patch? :)

I tried making a query using (order-by), but the parameters #:asc and #:desc are syntax-based, so I can't have a function that takes an argument, say direction, and have the query changed depending on direction's value.

Something like:

(define (get-pirates db #:order-by [order? #f]
                        #:direction [dir? #f])
  (define order (and order?))
  (define direction (and dir?))
  (for/list ([p (in-entities db (~> (from pirate #:as p)
                                    (order-by ([p.order direction]))))])
    p))

By reading deta's code, I can see macros expand to usage of dynamic functions (like dyn:order-by), but they don't seem to be publicly accessible.

Any plans to provide dynamic equivalents of order-by and other syntaxes?

(I'm sorry if this is not the best place to ask questions; please point me to a more appropriate channel if so).

I'm wondering if it would be possible to somehow express "where" conditions as values that could be passed around. Let's say I have a function like:

(define (query-products-many lang #:extra-conditions [extra #f])
  ...)

And I would like for that function to be able to append additional conditions to its WHERE clause based on the extra argument. My intuition would be to be able to somehow pass a form like (> products.price 50) as a value to the function - is something like that possible?

Say, I have two schemas:

(define-schema first
  ([uuid uuid/f #:primary-key]
   [somefield string/f]))

(define-schema second
  ([uuid uuid/f #:primary-key]
   [foo (numeric/f 10 2)]))

I would like to write a function that would get the uuid of either. I could use (struct->vector) and (vector-ref) by index, but that seems hackish; we already have (schema-fields ...) in deta/private/schema, and we have (field-id ...) in deta/private/field. Maybe we could implement a function that would look up the field id in the schema and get its value from an entity by index?

Or is there a simple way I'm missing?

SQLite supports the RETURNING clause since version 3.35.0 (2021-03-12). I'd like to use this feature. Happy to get a patch going.

as doc from db-lib says:

A list may be provided for an array parameter, in which case it is automatically converted using list->pg-array. The type annotation can be dropped when the array type can be inferred from the left-hand side.

then we can write code like below:

>  (query-rows conn "SELECT * FROM users AS u WHERE u.email = ANY ($1)"
              (list->pg-array (list "[email protected]" "[email protected]")))
'()
>

But this cannot be converted correctly from deta:

>  (lookup conn
          (~> (from "users" #:as u)
              (where (= u.email (any ,(list "[email protected]" "[email protected]"))))))
; prepare: syntax error at or near "ANY"
;   SQLSTATE: 42601
; [,bt for context]
>
>
>  (~> (from "users" #:as u)
              (where (= u.email (any ,(list "[email protected]" "[email protected]")))))
(query
 "SELECT * FROM users AS u WHERE u.email = (ANY($1))"
 '("[email protected]" "[email protected]"))
>
>

Hi Bogdan,

In the documentation you mention the following: 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.

However, I am using deta because I really don't want to know how things are layed out in the DB. North seems to require me to know how tables are built. Therefore it seems that I would have to sort of break the abstraction created by deta to use north to manage my schemas.

I am slightly confused how to use them to cooperate with each other. Do you have an example that I can look at?

When create-table is uses like:

(create-table db 'foo)

then 'foo is looked up in the global registry to find the corresponding schema.
If there is no such scheme registered, this error is generated:

hash-ref: no value found for key

It would be better to have an error saying that foo is not a registered as a schema.