Object-relational mapping in Racket with Racquel

Without getting into the weeds, object-relational mapping is a technique for making a correspondence between classes and tables in a relational database. Instances of (some) classes map to rows in a table in the database. You can always write custom code for working with the database. But that gets unwieldy pretty fast, and there’s a lot of repetitive code.

Here’s how to do that in Racket using the Racquel package.

Racquel to the rescue

The package to work with is Scott Brown’s excellent Racquel.

Racquel is not installed in Racket by default, so it needs to be installed. With raco it’s as simple as

$ raco pkg install racquel

Racquel comes with several useful features:

  1. a way of defining classes in your Racket programs so that they correspond to tables in your database
  2. a way of automatically defining classes from a database (essentially by scanning your DB and defining appropriate classes)
  3. a domain-specific language (DSL) for SQL called RQL
  4. utilities for mapping data objects to JSON and generating data objects from JSON

We’ll use (1), (3), and (4) in this tutorial.

The task, should you choose to accept it

Imagine that we’re charged with writing a little HTTP service that allows products to be viewed and created.

A product consists of five pieces of information:

Products also have an ID (an integer), which is not to be submitted when creating a new product.

Format When submitting a new product to the server, one will use JSON. Viewing a previously created product will also result in JSON.

The data ought to be stored in a relational database. There will be two tqables: one for products, another for the categories of a product.

Starting the implementation

The code is not entirely trivial. We need to connect to a relational database, validate JSON inputs, and work with a new concept—data classes—where one might be tempted into doing the work by hand. Before diving in to Racquel, let’s take care of the database and a JSON Schema that we will use to ensure that an incoming JSON data piece makes sense.

JSON Schema

Here’s a sensible JSON Schema that is used to ensure that, when creating a new product, our data makes sense. Note that, consistent with our requirements above, we do not require that an ID is submitted when creating a product. (The database will be responsible for assigning an ID to the newly created product; it’s not the submitter’s job to know that ID in advance.)

{
    "type": "object",
    "properties": {
	"name": {
	    "type": "string",
	    "minLength": 1
	},
	"sku": {
	    "type": "string",
	    "minLength": 1
	},
	"categories": {
	    "type": "array",
	    "items": {
		"type": "string",
		"minLength": 1,
		"pattern": "^[^,]+$"
	    },
	    "uniqueItems": true,
	    "minItems": 1
	},
	"price": {
	    "type": "number",
	    "exclusiveMinimum": 0
	},
	"currency": {
	    "type": "string",
	    "enum": [
		"EUR",
		"USD",
		"GBP",
		"JPY",
		"INR"
	    ]
	},
	"image": {
	    "type": "string",
	    "format": "uri"
	}
    },
    "required": [
	"name",
	"sku",
	"categories",
	"price",
	"currency",
	"image"
    ],
    "additionalProperties": false
}

SQL schema

And here’s the SQL: we have two tables, products and product_categories. Here’s the schema (more precisely, the CREATE TABLE statement) that would work.

CREATE TABLE products (
    product_id integer NOT NULL,
    sku text NOT NULL,
    name text NOT NULL,
    image text NOT NULL,
    price numeric NOT NULL,
    currency text NOT NULL
);

We’re using Postgres here, though we’re by no means forced to do so. MySQL or SQLite would also be fine.

The schema for the product_categories table looks like this:

CREATE TABLE product_categories (
    product_category_id integer NOT NULL,
    name text NOT NULL,
    product_id integer NOT NULL
);

In product_categories, the product_id column refers to the product_id of an existing product.

Enter Racquel

And, finally, our Racket code.

Racquel allows one to define what so-called data classes, which are Racket classes that are intended to correspond to tables in your database.

Perhaps it’s best if we just dive in to an example. Here’s how one can define a data class for the products table:

(define product%
  (data-class object%
    (table-name "products")
    (column (product-id #f "product_id"))
    (init-column (name "name")
                 (sku "sku")
                 (price "price")
                 (currency "currency")
                 (image "image"))
    (primary-key product-id
                 #:autoincrement "products_product_id_seq")
    (join [product-to-category
           product-category%
           #:cardinality 'one-to-many
           (where (= (product% product-id)
                     (product-category% product-category-id)))])
    (define/public (get-product-id)
      (get-column product-id this))

    (super-new)))

Let’s step through these bits one at a time:

object%

Our class is a subclass of the root class object%. That’s a sensible choice here, though if one were working with a more elaborate set of tables, one could define more classes and make your data classes extend them.

table-name

Here we are saying that the product% class corresponds to the products table.

column and init-column

With init-column we specify what pieces of information (columns) are needed to instantiate the product% class. In line with our specification, we always need price, currency, name, SKU, and an image URL.

The column class can be thought of as a list of columns that are present in the table, but not needed when instantiating the class. Here, we are listing the primary key of the table, which makes reflects the thinking that it is entirely the database’s job to issue IDs.

primary-key

Since we’re using Postgres, the value here for the primary key is a bit fussier than you might be used to, even if you have some experience with databases. We need to supply the sequence whose values increment. Double check that there are no typos in your column names! You may need to do a bit of sleuthing to discover the name of the autoincrement sequence in Postgres. (I use the Postico client, and this information is presented in the table structure view. For other clients, this information may or mat not be be as straightforwardly available.)

If you’re using MySQL or SQLite, you can simply supply the value #t for the autoincrement keyword.

join

The value here tells Racquel that we’ve got a one-to-many relationship here between products and product categories. In the end, this is just a fancy way of writing an SQL join. We are using a bit of RQL—Racket Query Language—here in the where bit.

define/public and super-new

Since Racquel data classes are just Racket classes, you can use the usual Racket class apparatus. We use get-column—which comes with Racquel—to define a straightforward accessor for the ID of a product.

super-new is similarly a part of ordinary Racket OOP and is not specific to Racquel.

Here’s the data class for product categories, which are supposed to correspond to entries in the product_categories table:

(define product-category%
  (data-class object%
    (table-name "product_categories")
    (column (product-category-id #f "product_category_id"))
    (init-column (product-id #f "product_id")
                 (name #f "name"))
    (primary-key product-category-id
                 #:autoincrement "product_categories_product_category_id_seq")
    (define/public (get-product-id)
      (get-column product-id this))
    (super-new)))

Creating data objects (instances of the data classes)

Our schema for the product_categories table is reflected in our definition of the data class. The idea behind product categories is that to create one, a product needs to exist beforehand. Every column except the primary key is listed under init-column

Similarly, when it comes to products, the idea is that, to create one, you need to supply all necessary pieces of information. In our simple schema, every column is non-NULL. That is reflected in our data class: notice that every column is listed in init-column except the primary key.

Complete implementation

You’re welcome to download the complete implementation here. The site is contained in products.rkt. The main functions to focus on are

Using the code

Here’s how you can use this little site.

Prerequisites

One big proviso before running the code: you must have a Postgres server running! Upon startup, products.rkt loads the file .env to enrich your environment variables. It is assumed that five environment variables are defined:

  • DB_HOST
  • DB_PORT
  • DB_USER
  • DB_PASSWORD
  • DB_DATABASE
  • The code uses Royall Spences’s nice dotenv module, which also needs to be installed.

Run the code in the command line using

racket products.rkt

or run products.rkt in DrRacket. The server will be running and is awaiting your input.

Here’s how you can submit input:

http POST localhost:6994/catalog < product.json

(Here’s I’m using httpie. You can of course use whatever HTTP client you prefer.)

To view products:

http GET localhost:6994/catalog/4
# need not be 4; could be the ID of any existing product

Takeaway

Object-relational mapping is a quick, yet fairly powerful way to get your relational database to play with your Racket code. Use Racquel.

This kind of mapping is not perfect. Some tasks that you’d like to carry out in the database correspond at best awkwardly, if at all, using Racquel. If you need to do some SQL ninja moves, you may well need to take matters in your own hand and use the DB connection directly. There’s nothing wrong with that, and such situations do not reflect poorly on Racquel.