Mito: An ORM for Common Lisp
Tags: commonlisp, lisp, orm, database
Published: 2022-08-22 09:03:30

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](https://github.com/fukamachi/mito) is an object relational mapper for Common Lisp created by [Eitaro Fukamachi](https://github.com/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](https://en.wikibooks.org/wiki/SQL_Exercises/The_warehouse) 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o6vw1hrvosdiglv3gisk.png)

## Creating the demo app
We are going to walk through the examples by building an online Warehouse management system using [Caveman](https://github.com/fukamachi/caveman)

```lisp
(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`.

```lisp
(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.

```lisp
(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.

```lisp
(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`.


```lisp
(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.

```sql
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.

```lisp
(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

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

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

```lisp
(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:

```lisp
(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.

```lisp
(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:

```lisp
(mito:select-dao 'warehouses)
```
This will give all the records from the table. It will be something similar in SQL like:

```sql
(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](https://github.com/fukamachi/sxql).

```lisp
;; 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.

```lisp
;; 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.

```lisp
(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.

```lisp
(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. 

```lisp
;; 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.

```lisp
;; 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.

```lisp
;; 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:

```lisp
(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.

```lisp
;; 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

| SQL                 | Mito                           |
|---------------------|--------------------------------|
| create table        | deftable & ensure-table-exists |
| insert into table   | insert-dao                     |
| select *            | select-dao                     |
| select * from where | find-dao                       |
| update table        | save-dao                       |
| delete              | delete-dao                     |

Mito has much more capabilities than simple CRUD operations like Migrations and DB Schema versioning. Please refer to the Github [repository](https://github.com/fukamachi/mito) for more details in the README.

## Source Code
The source code for this demo application is hosted in [Github](https://github.com/rajasegar/cl-warehouse). 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:

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

{% youtube https://youtu.be/cIHC9uMQiR0 %}


## References
- [The Warehouse DB](https://en.wikibooks.org/wiki/SQL_Exercises/The_warehouse)
- [ORM](https://www.techopedia.com/definition/24200/object-relational-mapping--orm)
- [Mito](https://github.com/fukamachi/mito)
- [sxql](https://github.com/fukamachi/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.