1 Introduction
This section introduces this library’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 db)
First we create a connection. Replace user, db, and password below with the appropriate values for your configuration (see Base 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")
(rows-result
'(((name . "n") (typeid . 23)) ((name . "d") (typeid . 1043)))
'(#(0 "nothing") #(2 "company")))
When the query is known to return rows 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 exactly one row.
> (query-row pgc "select * from the_numbers where n = 0") '#(0 "nothing")
Similarly, use query-list for queries that produce rows 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 in a web servlet is more complicated 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 (require db) (define db-conn (postgresql-connect ....)) (define (serve req) .... db-conn ....)
The main problem with using one connection for all requests is that multiple threads accessing the same connection are not properly isolated. 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.
A secondary problem is performance. A connection can only perform a single query at a time, whereas most database systems are capable of concurrent query processing.
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 a request-specific (that is, thread-specific) “actual connection” by need and disconnects it when the request is handled (that is, when the thread terminates). Different request-handling threads using the same virtual connection are assigned different actual connection, so the threads are properly isolated.
"better-servlet.rkt"
#lang web-server (require db) (define db-conn (virtual-connection (lambda () (postgresql-connect ....)))) (define (serve req) .... db-conn ....)
This solution preserves the simplicity of the naive solution and fixes the isolation problem but 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 (require db) (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.