On this page:
1.1 Basic Features
1.2 Databases and Web Servlets

1 Introduction

This section introduces this package’s basic features and discusses how to build a database-backed web servlet.

1.1 Basic Features

The following annotated program demonstrates how to connect to a database and perform simple queries. Some of the SQL syntax used below is PostgreSQL-specific, such as the syntax of query parameters ($1 rather than ?).

> (require (planet ryanc/db:1:5))

First we create a connection. Replace user, db, and password below with the appropriate values for your configuration (see Creating connections for other connection examples):

> (define pgc
    (postgresql-connect #:user user
                        #:database db
                        #:password password))
> pgc

(object:connection% ...)

Use query-exec method to execute a SQL statement for effect.

> (query-exec pgc
   "create temporary table the_numbers (n integer, d varchar(20))")
> (query-exec pgc
    "insert into the_numbers values (0, 'nothing')")
> (query-exec pgc
    "insert into the_numbers values (1, 'the loneliest number')")
> (query-exec pgc
    "insert into the_numbers values (2, 'company')")

The query function is a more general way to execute a statement. It returns a structure encapsulating information about the statement’s execution. (But some of that information varies from system to system and is subject to change.)

> (query pgc "insert into the_numbers values (3, 'a crowd')")

(simple-result '((command insert 0 1)))

> (query pgc "select n, d from the_numbers where n % 2 = 0")

(recordset '(((name . "n") (typeid . 23)) ((name . "d") (typeid . 1043))) '(#(0 "nothing") #(2 "company")))

When the query is known to return a recordset and when the field descriptions are not needed, it is more convenient to use the query-rows function.

> (query-rows pgc "select n, d from the_numbers where n % 2 = 0")

'(#(0 "nothing") #(2 "company"))

Use query-row for queries that are known to return a recordset of exactly one row.

> (query-row pgc "select * from the_numbers where n = 0")

'#(0 "nothing")

Similarly, use query-list for queries that produce a recordset of exactly one column.

> (query-list pgc "select d from the_numbers order by n")

'("nothing" "the loneliest number" "company" "a crowd")

When a query is known to return a single value (one row and one column), use query-value.

> (query-value pgc "select count(*) from the_numbers")

4

> (query-value pgc "select d from the_numbers where n = 5")

query-value: query returned zero rows: "select d from

the_numbers where n = 5"

When a query may return zero or one rows, as the last example, use query-maybe-row or query-maybe-value instead.

> (query-maybe-value pgc "select d from the_numbers where n = 5")

#f

The in-query function produces a sequence that can be used with Racket’s iteration forms:

> (for ([(n d) (in-query pgc "select * from the_numbers where n < 4")])
    (printf "~a is ~a\n" n d))

0: nothing

1: the loneliest number

2: company

3: a crowd

> (for/fold ([sum 0]) ([n (in-query pgc "select n from the_numbers")])
    (+ sum n))

6

Errors in queries generally do not cause the connection to disconnect.

> (begin (with-handlers [(exn:fail?
                          (lambda (e) (printf "~a~n" (exn-message e))))]
           (query-value pgc "select NoSuchField from NoSuchTable"))
         (query-value pgc "select 'okay to proceed!'"))

query-value: relation "nosuchtable" does not exist (SQLSTATE 42P01)

"okay to proceed!"

Queries may contain parameters. The easiest way to execute a parameterized query is to provide the parameters “inline” after the SQL statement in the query function call.

> (query-value pgc
   "select d from the_numbers where n = $1" 2)

"company"

> (query-list pgc
   "select n from the_numbers where n > $1 and n < $2" 0 3)

'(1 2)

Alternatively, a parameterized query may be prepared in advance and executed later. Prepared statements can be executed multiple times with different parameter values.

> (define get-less-than-pst
    (prepare pgc "select n from the_numbers where n < $1"))
> (query-list pgc get-less-than-pst 1)

'(0)

> (query-list pgc (bind-prepared-statement get-less-than-pst 2))

'(0 1)

When a connection’s work is done, it should be disconnected.

> (disconnect pgc)

1.2 Databases and Web Servlets

Using database connections is more complicated in a web servlet than in a standalone program. A single servlet is potentially used to serve many requests at once, each in a separate request handling thread. Furthermore, the use of send/suspend, send/suspend/dispatch, etc, means that there are many places where a servlet may start and stop executing to service a request.

Why not use a single connection to handle all of a servlet’s requests? That is, create the connection with the servlet instance and never disconnect it. Such a servlet would look something like the following:

"bad-servlet.rkt"

#lang web-server
(define db-conn (postgresql-connect ....))
(define (serve req)
  .... db-conn ....)

The main problem with using one connection for all requests is that while all connection functions are thread-safe, two threads accessing a connection concurrently may still interfere. For example, if two threads both attempt to start a new transaction, the second one will fail, because the first thread has already put the connection into an “in transaction” state. And if one thread is accessing the connection within a transaction and another thread issues a query, the second thread may see invalid data or even disrupt the work of the first thread (see isolation).

The proper way to use database connections in a servlet is to create a connection for each request and disconnect it when the request is handled. But since a request thread may start and stop executing in many places (due to send/suspend, etc), inserting the code to connect and disconnect at the proper places can be challenging and messy.

A better solution is to use a virtual connection, which creates request-specific (that is, thread-specific) “actual connections” by need and disconnects them when the request is handled (that is, when the thread terminates):

"better-servlet.rkt"

#lang web-server
(define db-conn
  (virtual-connection
   (lambda () (postgresql-connect ....))))
(define (serve req)
  .... db-conn ....)

This solution preserves the simplicity of the naive solution but fixes the isolation problem, at the cost of creating many short-lived database connections. That cost can be eliminated by using a connection pool:

"best-servlet.rkt"

#lang web-server
(define db-conn
  (virtual-connection
   (connection-pool
    (lambda () (postgresql-connect ....)))))
(define (serve req)
  .... db-conn ....)

By using a virtual connection backed by a connection pool, a servlet can achieve simplicity, isolation, and performance.