Databases and SQL in Racket | Schema Programming Part 22

Racket applications often need persistence. The standard db library provides a unified interface for connecting to PostgreSQL, MySQL, and SQLite.

Connecting to SQLite

SQLite is great for embedded local data.

#lang racket
(require db)

; Create an in-memory database for testing
(define pgc (sqlite3-connect #:database 'memory))

; Or a file
; (define pgc (sqlite3-connect #:database "my-data.db"))

Creating Tables

Use query-exec for statements that don't return rows.

(query-exec pgc
 "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")

(query-exec pgc
 "INSERT INTO users (name, age) VALUES ($1, $2)"
 "Alice" 30)

Querying Data

Use query-rows to get a list of vectors.

(define results
  (query-rows pgc "SELECT * FROM users WHERE age > $1" 20))

(for ([row results])
  (printf "User: ~a, Age: ~a\n" (vector-ref row 1) (vector-ref row 2)))

Prepared Statements

Notice the $1 placeholder? Racket automatically handles Prepared Statements, protecting you from SQL Injection attacks. Never concatenate strings to build queries!

Summary

The db library makes Racket a viable backend language. It supports connection pooling, transactions, and parameterized queries out of the box.