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.