Convenient, thread-safe database connections for your Racket web site

They say that no man is an island, and that’s true for many web sites, too.

By which I mean: many web sites connect to a relational database (Postgres, MySQL, etc.) to serve their content. Often it’s not a single process (be it PHP, Python, Racket, Ruby, Perl, you name it) that generates the entirety of every response. Rather, data is often pulled from one (or more than one) source—often, a relational database—which then gets processed by PHP, Python, etc. etc. into an HTTP response.

If you want to build a web site with Racket, you may very well need to connect to a relational database such as Postgres or MySQL. Working with databases is likely to add a layer (or two) of complexity to your web application, but let’s start at the beginning: the connection to the database. We’ll see how Racket makes this convenient and robust.

The package you need: db

The package in question is db. It’s built in to Racket; there’s no need to install it à la raco pkg install db. Just do (require db) in your relevant Racket modules and you’re halfway there.

In the code snippets that follow, I’ll use Postgres, but the same ideas apply to MySQL, SQLite, and ODBC. The db package supports these databases essentially to the same extent as it supports Postgres.

Understandable, but less-than-ideal approaches the database problem

Given a request, you need to fetch some data from a database. It’s natural to want to open a connection, get what you need, and then shut down the connection. You might write code like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

(define (connect-to-db!) ;; fill in values to configure and initialize ;; a connnection to the database (postgresql-connect ...)
)

(define handle-homepage (req) (let ([db (connect-to-db!)]) ;; execute some queries against the DB )
)

The difficulty with this approach is that you’d need to include the same (connect-to-db!) bit in every function that handles responses. That’s a bit involved.

Another approach would be to define a toplevel variable for the database connection, like so:

1
2
3
4
5

(define the-db (connect-to-db!))

(define handle-homepage (req) ;; execute some queries against the DB
)

That solves the problem of having to repeat the same DB connection code snippet for every request handler. But a more troubling problem arises: the one and only DB connection will now be shared among the threads that are spawned when responding to requests. If you are dealing with a site that responds to many requests more or less simultaneously, this issue may bite you.

There must be a better way!

Racket offers a smart way to handle the DB connection problem with the concept of virtual connection. A virtual connection is a connection to the DB that is opened as needed. And it’s thread-safe. One sets up the connection and can move on to other parts of your site safe in the knowledge that the difficult bits are being taken care of for you.

That means that if you need to do some work but don’t need the database, then no connection will be opened. Depending on how your server performs, this might make a big difference. For instance, if you don’t need a DB connection to serve your homepage, then you’re losing precious time by firing up a connection, do nothing with it, then kill it.

Here’s how you can get started with virtual connections:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

#lang racket/base

(require db)

(define (getenv-or-die var) (let ([val (getenv var)]) (if (string? val) val (error (format "Mandatory environment variable \"~a\" is unset." var)))))

(define db-source (let* ([host (getenv-or-die "DB_HOST")] [port (getenv-or-die "DB_PORT")] [user (getenv-or-die "DB_USER")] [pass (getenv-or-die "DB_PASSWORD")] [database (getenv-or-die "DB_DATABASE")] [port/number (string->number port 10 'number-or-false)]) (unless (integer? port/number) (error "Port cannot be understood as an integer:" port)) (unless (> port/number 0) (error "Port should be positive:" port/number)) (postgresql-data-source #:user user #:port port/number #:server host #:password pass #:database database)))

(define (connect!) (dsn-connect db-source))

(define the-db (virtual-connection (connection-pool connect!)))

(provide the-db)

This code defines a function, connect!, that connects to the DB. It looks up the login credentials it needs from the environment. I’ve set things up so that the database’s hostname, port number, username, password, and database are needed, and they must, moreover, be defined as environment variables. (That’s much better than putting the credentials in your code base!) If we any piece of the DB connection puzzle is missing from the environment, well, game over (that’s what getenv-or-die is doing).

A nice feature of this approach is that the environment gets inspected for DB credentials happens when the server starts.

The especially cool bit comes here:

1
2
3
(define the-db
  (virtual-connection
    (connection-pool connect!)))

With this setup, I run queries against the database by using the-db. Here’s a code snippet in a Racket web application that deals with products. The snippet shows how to check whether a product exists in the database:

1
2
3
4
5
6
7
8

(require db)

(define (product-known? product-id) (let ([maybe (query-maybe-value the-db "select 1 from \"catalog\" where \"product_id\" = $1 limit 1" product-id)]) (not (eq? #f maybe))))

Here, query-maybe-value is a function from the db package that returns either the specified value of the query or, if nothing is returned, #f.

(query-maybe-value is but one of many useful functions available in the db module. You’ll probably want to dive in to see what else you can do.)

Here, I’m connecting to the database using the-db, the virtual connection we defined earlier. Because of how we set up the-db, a connection will be requested from the connection pool. What’s nice here is that there’s no fuss with setting up the connection (that is, making a call to connect! before doing anything with the database).

The fact that a connection won’t be made until truly needed is, by itself, quite useful. Even better, this is all thread-safe: if you’re simultaneously serving multiple requests, each with their own connection to the database, Racket takes care that there’s no sharing of connections. A fresh connection is made, as needed.

(Depending on how you’ve set up your connection pool, requests might even have to wait their turn. This can be the case if you set up the pool to handle, say, at most 5 connections, but you’re currently experiencing a spike of traffic where you have well more than 5 visitors, more or less continuously.)