Mito: An ORM for Common Lisp

In this post, we are going to take a look at Mito, the ORM library for Common Lisp and walk through its major features to create, select, update and delete records in a database with a sample application.

Before diving into the topic, let's get some basics right about ORM and Mito.

ORM

Object-relational mapping (ORM) is a programming technique in which a metadata descriptor is used to connect object code to a relational database. Object code is written in object-oriented programming (OOP) languages such as Java or C#. ORM converts data between type systems that are unable to coexist within relational databases and OOP languages.

In addition to the data access technique, ORM's benefits also include:

• Simplified development because it automates object-to-table and table-to-object conversion, resulting in lower development and maintenance costs
• Less code compared to embedded SQL and handwritten stored procedures
• Transparent object caching in the application tier, improving system performance
• An optimized solution making an application faster and easier to maintain

Mito

Mito is an object relational mapper for Common Lisp created by Eitaro Fukamachi. It supports MySQL, PostgreSQL and SQLite3. It automatically adds id (serial/uuid primary key), created_at and updated_at by default like Ruby's ActiveRecord. It also provides support for Migrations and DB schema versioning.

The Database & Schema

For this post, we are going to use a database schema from the SQL Exercises called the warehouse.

The schema for the warehouse database looks like below. It contains two tables Warehouses and Boxes. The Boxes table contains a Foreign key for the Warehouse.

Creating the demo app

We are going to walk through the examples by building an online Warehouse management system using Caveman

(ql:quickload 'caveman2)
(caveman2:make-project #P"~/quicklisp/local-projects/cl-warehouse")

Once you created your project, you can refer to the below code examples within the context of our newly created warehouse management application.

Connecting to the database

You can connect to a database using the connect-toplevel function in Mito to any database provider like MySQL, Postgres, etc.,

This is an example for connecting to a SQLite3 database in the local machine called warehouse.db.

(mito:connect-toplevel :sqlite3 :database-name (merge-pathnames #P"warehouse.db" *application-root*))

Defining tables

Mito provides a deftable function to define your table schema. Let's define our warehouses table using the same. It contains two columns, location and capacity.

(mito:deftable warehouses ()
  ((location :col-type (:varchar 50))
   (capacity :col-type (:integer))))

Creating references

Next create our boxes table using the same deftable function from Mito. The boxes table also contains just two fields, contents and value. Let's also add a foreign key to the table definition for the warehouses table, by adding a column named warehouse by specifying the :col-type as warehouses and :references as warehouses.

Now the two tables are linked by this foreign key.

(mito:deftable boxes ()
  ((contents :col-type (:varchar 10))
   (value :col-type (:integer))
   (warehouse :col-type warehouses :references warehouses)))

Ensuring tables are created

So are we have defined only the table schemas and the actual table has not yet been created. So before, performing any table operations, let's just ensure that the tables are created by Mito using the function ensure-table-exists.

(mito:ensure-table-exists 'warehouses)
(mito:ensure-table-exists 'boxes)

This will generate the following SQL and execute it to create the table in our database.

CREATE TABLE IF NOT EXISTS "warehouses" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "location" VARCHAR(50) NOT NULL,
    "capacity" INTEGER NOT NULL,
    "created_at" TIMESTAMP,
    "updated_at" TIMESTAMP
);

Seeding data

Now let's seed our database tables by inserting some sample data. Mito provides a function called insert-dao to create new rows in the tables.

First we have to create an object instance for the respective table, in our case it is the warehouses table.

(make-instance 'warehouses :location "Chicago" :capacity 3)

And then we have to use the insert-dao function to push this instance as a record into the table

(mito:insert-dao (make-instance 'warehouses :location "Chicago" :capacity 3))

So, the code for seeding the warehouses table will look something like this:

(mito:insert-dao (make-instance 'warehouses :location "Chicago" :capacity 3))
(mito:insert-dao (make-instance 'warehouses :location "Chicago" :capacity 4))
(mito:insert-dao (make-instance 'warehouses :location "New York" :capacity 7))
(mito:insert-dao (make-instance 'warehouses :location "Los Angeles" :capacity 2))
(mito:insert-dao (make-instance 'warehouses :location "San Francisco" :capacity 8))

Our seeding code for the boxes table will also be the same except for the additional foreign key value for the warehouses table will be like:

(make-instance 'boxes :contents "Rocks" :value 180 :warehouse-id 1)

The final code for seeding the sample data for the boxes table will look like this.

(mito:insert-dao (make-instance 'boxes :contents "Rocks" :value 180 :warehouse-id 1))
(mito:insert-dao (make-instance 'boxes :contents "Paper" :value 250 :warehouse-id 1))
(mito:insert-dao (make-instance 'boxes :contents "Scissors" :value 90 :warehouse-id 1))
(mito:insert-dao (make-instance 'boxes :contents "Rocks" :value 180 :warehouse-id 2))
(mito:insert-dao (make-instance 'boxes :contents "Paper" :value 250 :warehouse-id 2))
(mito:insert-dao (make-instance 'boxes :contents "Scissors" :value 90 :warehouse-id 2))
(mito:insert-dao (make-instance 'boxes :contents "Rocks" :value 180 :warehouse-id 3))
(mito:insert-dao (make-instance 'boxes :contents "Paper" :value 250 :warehouse-id 3))
(mito:insert-dao (make-instance 'boxes :contents "Scissors" :value 90 :warehouse-id 3))
(mito:insert-dao (make-instance 'boxes :contents "Rocks" :value 180 :warehouse-id 4))
(mito:insert-dao (make-instance 'boxes :contents "Paper" :value 250 :warehouse-id 5))

Now let's do some CRUD operations on our tables in the database. Let's start by reading the rows from the warehouses table.

Getting the list of rows

In our sample application, we have a page for displaying all the Warehouses. So to fetch them from the database we use the select-dao function from Mito like below:

(mito:select-dao 'warehouses)

This will give all the records from the table. It will be something similar in SQL like:

(select * from warehouses)

Also we want to sort the list of warehouse based on location and the sorting based on ascending or descending order. So we pass these arguments in the form of query parameters in the URL for the warehouses list page.

http://localhost:3000/warehouses?sort-by=location&direction=asc

And we handle the same in our route handlers by reading the query parameters and sorting the data accordingly using select-dao and sxql:order-by

You can build custom queries like this in Mito with sxql.

;; GET /warehouses
;; List of warehouses
(defroute "/warehouses" (&key _parsed)
  (let* ((direction (or (get-param "direction" _parsed) "asc"))
        (sort-by (or (get-param "sort-by" _parsed) "location"))
        (warehouses (mito:select-dao 'warehouses
                      (sxql:order-by (get-order-by direction sort-by))))
         (alerts *alerts*))
    ;; Clear all the alerts
    (setf *alerts* nil)
    (render #P"warehouses/index.html"
            (list
             :warehouses warehouses
             :active "/warehouses"
             :direction direction
             :sort-by sort-by
             :alerts alerts))))

Creating new warehouses

Now, let's focus on creating new records in the database. Let's create a new warehouse for our appliction in the new warehouse page.

In Mito, you can create new rows in the table using insert-dao function by first creating an instance of the warehouse object using make-instance with the data from the HTTP POST request.

;; POST /warehouses
;; Create a new warehouse
(defroute ("/warehouses" :method :POST) (&key _parsed)
  (print _parsed)
  (let ((new-warehouse (make-instance 'warehouses
                                      :location (get-param "location" _parsed)
                                      :capacity (get-param "capacity" _parsed))))
    (mito:insert-dao new-warehouse)
    (push (list :type "bg-primary" :title "New Warehouse created.") *alerts*)
    (redirect "/warehouses")))

Finding warehouses

In order to view the detailed information for a particular warehouse in the warehouse detail page, we need to find the relevant record using the warehouse id from the query parameter.

The URL structure will be something like:

http://localhost:3000/warehouses/1

In Mito, we can find a particular record based on the id using the find-dao function by supplying the id as the argument.

(mito:find-dao 'warehouses :id id)

Here we are also fetching the details about the list of boxes that are stored in the particular warehouse using the select-dao to display them along with the warehouse details.

(mito:select-dao 'boxes (sxql:where (:= :warehouse-id (parse-integer id)))

So the route handler for the warehouse detail page will be something like below.

;; GET /warehouses/:id
;; Warehouse detail page
(defroute "/warehouses/:id" (&key id)
  (let ((warehouse (mito:find-dao 'warehouses :id id))
    (boxes (mito:select-dao 'boxes (sxql:where (:= :warehouse-id (parse-integer id))))))
    (render #P"warehouses/show.html" (list :warehouse warehouse
                       :boxes boxes))))

Updating warehouses

Now, if we want to update a record via the Edit page by sending the new record details via a HTTP POST request, we can make use of the save-dao function available in Mito.

First we need to find the warehouse record, and then update the respective slot values in the object using the new values and save the record in the database. And that's exactly what we are doing here.

;; POST /warehouses/:id/update
;; Update warehouse
(defroute ("/warehouses/:id/update" :method :POST) (&key id _parsed)
  (let ((warehouse (mito:find-dao 'warehouses :id id)))
    (setf (slot-value warehouse 'location) (get-param "location" _parsed)
          (slot-value warehouse 'capacity) (get-param "capacity" _parsed))
    (mito:save-dao warehouse)
    (push (list :title "Warehouse info updated successfully.") *alerts*)
    (redirect "/warehouses")))

Deleting warehouses

Finally, if you want to delete any records in the table, you can use the delete-by-values function present in Mito, to delete a particular record based on the id.

Here we are sending an HTTP GET request to delete a warehouse record, the URL structure will be something like:

http://localhost:3000/warehouses/1/delete

The final route handler code for the delete route will be something like this. After deleting the record, we are redirecting the user to the Warehouses list page.

;; GET /warehouses/:id/delete
;; Delete warehouse
(defroute "/warehouses/:id/delete" (&key id)
  (mito:delete-by-values 'warehouses :id id)
  (push (list :type "bg-danger" :title "Warehouse deleted.") *alerts*)
  (redirect "/warehouses"))

Getting boxes along with warehouses

Finally, let's say you want to fetch the box details along with the warehouse information, we need to fetch data from both the tables at the same time.

This is normally achieved in SQL using JOIN queries. In Mito, we can use the :includes property and specify the linked table as the value to fetch the associated records.

So in our example, let's say we want to fetch the box and the warehouse details:

(mito:select-dao 'boxes (mito:includes 'warehouses)

This is the final code for the Box list page where we display the information about the boxes and the warehouses.

;; GET /boxes
;; Boxes list page
(defroute "/boxes" (&key _parsed)
  (let* ((direction (or (get-param "direction" _parsed) "asc"))
         (sort-by (or (get-param "sort-by" _parsed) "contents"))
         (boxes (mito:select-dao 'boxes (mito:includes 'warehouses)
                  (sxql:order-by (get-order-by direction sort-by))))
         (alerts *alerts*))
    (setf *alerts* nil)
  (render #P"boxes/index.html" (list
                :boxes boxes
                :active "/boxes"
        :direction direction
        :sort-by sort-by
        :alerts alerts))))

Now, let's summarize the SQL operations along with their Mito counterparts

Mito has much more capabilities than simple CRUD operations like Migrations and DB Schema versioning. Please refer to the Github repository for more details in the README.

Source Code

The source code for this demo application is hosted in Github. You can always refer back to the original code if you feel any gaps in the code snippets in this post.

Demo app

You can fire up the demo app to see it live in the browser by loading them in your favorite Lisp implementation:

(ql:quickload :cl-warehouse)
(cl-warehouse:start :port 3000)

References

• The Warehouse DB
• ORM
• Mito
• sxql

Hope you enjoyed the post about Mito, the ORM library for Common Lisp, please let me know the queries and feedback in the comments section.