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
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 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
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.
Top comments (1)
Thanks Steven