Some notes for deta newbies

Here are some notes on Bogdan Popa’s excellent deta package that go somewhat beyond its official documentation, touching on a a few points that you may bump up against as you start to use deta for making real-world, Racket-powered web sites. There’s nothing particularly deep here, but I hope these notes help spare you some thinking and puzzling that I needed to do when I was just getting started.

Inserting Racket values into deta queries

I screwed up badly when getting started building queries with deta. For some reason, even the simplest queries were failing for me. How can that be?, I thought. There’s no way I was doing anything fancy. I’m running PostgreSQL 13, which is fairly new, so perhaps deta is behind Postgres. Yes, I actually thought that, as unlikely as it may be. I kept getting errors like this (these are log messages emitted by Postgres):

2021-09-21 06:04:27.569 CEST [51834] ERROR:  column "param" does not exist at character 78
2021-09-21 06:04:27.569 CEST [51834] STATEMENT:  SELECT i.id, i.user_id, i.url, i.created_at FROM items AS i WHERE i.user_id = PARAM

The deta code I was running comes from this function:

(define (items-for-user-query uid)
  (~> (from item #:as i)
      (where (= i.user_id uid))))

Looking at the SQL log message, the difficulty is that the PARAM there should be either an integer, or perhaps a parameter, which should look like $1 (a number prefixed by a dollar). Why is my obviously correct code not working? I confirmed that PostgreSQL version 13 didn’t introduce any changes from versions 12 or 11. Perhaps deta hasn’t been tested on the latest version, I thought. I wasn’t following an important developer maxim: Blame your own code first.

Here’s the right way to do it:

(define (items-for-user-query uid)
  (~> (from item #:as u)
      (where (= u.user_id ,uid))))

Notice the comma there?

The first, incorrect function generates a unintended, malformed prepared query. Use the comma to interpolate Racket values! The comma is present in the deta examples, but I overlooked it. The real reason is that where and the other query-building tools used in deta are macros, not functions.

How to specify default values for a column

Like most web developers, when I make a new table in an SQL database, I almost always have a created_at column to store when the row was created. The default value for this column, specified in the CREATE TABLE statement, is CURRENT_TIMESTAMP. Specifying a default values helps me to keep the SQL more concise. However, if you want created_at to be available to you in your deta models, you need to specify it in your deta schema. Here’s an example schemas.rkt module that contains schemas for a couple of tables:

#lang racket/base

(require deta
         racket/string
         racket/contract
         gregor)

(define-schema user
  ([id id/f #:primary-key #:auto-increment]
   [password string/f #:contract non-empty-string?]
   [email_address string/f #:contract non-empty-string?]
   [(created_at (now/utc)) datetime/f]))

Notice the created_at column. When creating a model(say, a user using make-user), you don’t need to specify #:created_at; the default will be (now/utc). If you want, you still can specify a created-at timestamp, but you don’t have to.

This part of the post has been updated based on feedback from Bogdan. In an earlier, I advocated explicitly passing a value for #:created_at in every function call. Now I know how to specify defaults.

Using deta with north

The deta docs talk about changing a database’s schema in Racket code. There’s a sidenote saying that, in more real-world setups, one would probably use north to do things like add tables and columns to your database. This is good advice.

So how would that look like?

In short, you would simply not write deta code (specifically, create-table! and drop-table!) that does these kinds of operations to your database. Instead, you’d write migrations in SQL, and your Racket application code would just assume that your database has the schema you intend. This means that the deployment of your application becomes a bit more complicated. Instead of having, say, a Racket program that does everything, you’d now need to have two parts to your web application:

  1. the DB schema part, written in SQL (north, to be quite precise) that does any needed database schema operations
  2. the main Racket web part, handling requests and generates responses.

The way you might express this separation is with a simple shell script:

#!/bin/sh
set -e # don't start server if migration fails
raco north migrate
racket server.rkt

Running this shell script migrates your database (and dies if that fails), and starts the server (in server.rkt) afterward.