This is an ongoing work I wanted to share sometimes this year. It isn’t ready, it isn’t released, but if you are a motivated lisper I can add you to the GitHub repository and you can try the demo.

We all want more tools for easier and faster web development in CL I guess right? An automatic database admin dashboard is an important component for me, both for personal use and development purposes, but also for client-facing apps, at least at the beginning of a project.

What I started is based on the Mito ORM. You define your DB tables as usual, and then this package comes into play. Let’s call it mito-admin.

What’s more or less working so far is:

  • choose which tables to display (let’s say we have books, that can be in one shelf, and have many tags)
  • you get a welcome page. The tables are listed on the left side of the admin.
  • click on a table and see a list of records (with pagination, which module is already published as cosmo-pagination. See also lisp-pagination)
  • CRUD actions on records:
    • create
    • update
    • delete
  • with CSRF protection
  • with form validation
  • with automatic handling of relations and custom HTML widgets
    • a one-to-many gives you a select field
    • (there’s a lot to do here)
  • a search bar
  • built-in login, user auth and rights
  • all this being customizable with CLOS fields and methods
  • light and dark themes thanks to Bulma CSS
  • for SQLite, Postgres, MySQL.

Table of Contents

There’s a demo in the project. Here’s how it works.

Models

Here are 3 Mito models: a book, a shelf, tags.

This is regular Mito and regular classes definitions (note the :metaclass option).

We are excluding the print-object methods for brevity.

(in-package :mito-admin-demo)

(defparameter *db-name* "db.db"
  "SQLite DB name.")

(defvar *db* nil
  "DB connection object.")

(defclass shelf ()
  ((name
    :initarg :shelf
    :accessor name
    :initform ""
    :col-type (or :null (:varchar 128))))
  (:metaclass mito:dao-table-class)
  (:documentation "Shelf: where is the book located.")
  (:unique-keys name))

(defclass tag ()
  ((name
    :initarg :name
    :accessor name
    :initform nil
    :col-type (or :null (:varchar 128))))
  (:metaclass mito:dao-table-class)
  (:documentation "A book can have many tags (categories).")
  (:unique-keys name))

;; necessary intermediate table for m2m.
(defclass book-tags ()
  ((book :references book)
   (tag :col-type tag))
  (:metaclass mito:dao-table-class))


(defclass book ()
  ((title
    :accessor title
    :initarg :title
    :initform nil
    :type string
    :col-type (:varchar 128))

   (title-ascii
     :accessor title-ascii
     :initform nil
     :col-type (:varchar 128))
     :documentation "Same title, only ascii. Processed after the book creation. Used for search and URI slugs.")

   (shelf
    :accessor shelf
    :initform nil
    :col-type (or :null shelf)
    :documentation "A card has only one shelf.")

   (tags
    ;; This column is even optional, we can do everything with the intermediate table
    ;; and a method that collects the tag for a book.
    ;; In fact, this slot won't be populated by Mito, we'll see "slot unbound".
    :accessor tags
    :initform nil
    :col-type (or :null book-tags)
    :documentation "A book can have many tags, aka categories.")

   (cover-url
    :accessor cover-url
    :initarg :cover-url
    :initform nil
    :type (or string null)
    :col-type (or (:varchar 1024) :null))

   (review
    :accessor review
    :initarg :review
    :initform nil
    :type (or string null)
    :col-type (or :text :null)
    :documentation "Let's write reviews about our favourite books."))

  (:metaclass mito:dao-table-class)
  (:documentation   "Book class, simplified. After modification of the DB schema, run (migrate-all)."))

To create the database, you’ll need a couple more Mito invocations. See its README or the Cookbook.

Select tables to show

We need to select the tables we’ll make available in the admin.

Override the mito-admin::tables method:

(defmethod mito-admin::tables ()
  '(
    book
    shelf
    tag
    ))

I deleted some inline comments, but there’s something to keep in mind. We need a list of all classes for Mito, so that it runs the migrations, and another one of a subset of classes for the admin. There’s only one list for now but that’s easy to fix.

Start the admin

Call mito-admin:connect, and bootstrap users and their base roles:

    ;; users and roles:
    (mito-admin-auth/v1::ensure-users-and-roles)

    ;; base roles.
    (mito-admin-auth/v1::bootstrap-base-roles)

Configure the admin

The first thing to do is to register an app name:

(mito-admin::register-app :cosmo-admin-demo)

which is here our lisp’s package. Working in a Lisp image allows to work on multiple (web) apps at the same time (at least with Hunchentoot). So, in a web library you’ll need a layer of indirection if you want to support this use case. I don’t for now, but registering the app’s *package* is necessary internally to resolve table names to fully-qualified symbols. As a user, you shouldn’t need to know all that, but as a tester you might.

You can override the render-slot method to change the default representation of a record’s slot. Here we turn a book’s cover-url from a string to an HTML anchor. This can be automated and abstracted. Another TODO.

(defmethod mito-admin::render-slot ((obj book) (slot (eql 'cover-url)))
  "A book cover URL must be a <a> tag with an href."
  (if (str:non-blank-string-p (mito-admin::slot-value? obj slot))
      (format nil "<a href=\"~a\"> ~a </a>" val val)
      ""))

Form handling

This is the most important, and unfinished, part of the admin.

We could maybe use cl-forms but I didn’t find that it maps well to this admin’s ABI. You might find it useful though, as it’s feature complete. It even has: client-side validation, sub-forms, Spinneret and Djula-based renderers, etc. Look at its demo.

We need to:

  • be able to include and exclude fields from the HTML forms
  • be able to choose a different HTML widget for a given field
  • validate forms
    • with custom validation logic
    • and nicely render errors
    • or create or update the records.

We currently need to create forms explicitely:

(mito-admin:define-forms '(shelf tag))

or also

;; We can define forms manually.
;; (we can override default slots, but we can also override them by redefining the accessor methods)
(defclass book-form (mito-admin::form)
  ())

A form class has several slots we may redefine and use later, such as a list of validators, or the fields to use for the search, fields to exclude, etc.

We can exclude fields:

(defmethod mito-admin::exclude-fields (book-form)
  "Return a list of field names (symbols) to exclude from the creation form."
  '(title-ascii
    ;; TODO: handle relations
    ;; we need to exclude shelf, or we'll get an error on mito:insert-dao if the field is NIL.
    ;; shelf
    shelf-id
    tags-id
    ))

As you see, there are more TODOs here. title-ascii is a private field that we don’t need to expose. That’s fine. shelf-id and all are Mito’s references to the other tables. We need to recognize them and exclude them.

To define an HTML widget, override mito-admin::field-input. Our demo uses a built-in template to render a select field from a list of options.

TODOs: there’s a lot to do here. Our admin app still requires too much configuration, we want it to be more automatic. Recognize the input types better, ship an async select2 input for many-to-many relations.

Form validation

To validate a record, override the validators method. It returns a hash-table that, for all the table’s fields, associates a list of validators.

(defmethod mito-admin::validators ((obj (eql 'book)))
  "To validate a book:

  - its title should not be equal to \"test\".

  Beware that we override the method MITO-ADMIN::VALIDATORS."
  (serapeum:dict 'title (clavier:~= "test"
                                    "this title is too common, please change it!")))

We use the clavier library for this. Here’s a short blog post that shows a quick usage and our :allow-blank passthrough and validate-all function (which were not accepted upstream btw (shit it was nearly a year ago)).

Our form validation mechanism shows a global error message, and a specific one under each input field.

You didn’t write a single HTML line for this o/

One-to-many relations

Those are correctly handled by Mito and it’s easy to have a custom widget in the admin.

Many-to-many relations

See here: https://github.com/fukamachi/mito/discussions/161

At present, Mito doesn’t do much for m2m relations, but we can simply write a short method that will select all related objects of a record.

Here’s for a book’s tags:

(defmethod tags (obj)
  ;; null case
  nil)

(defmethod tags ((obj book))
  (let ((book-tags
            (mito:select-dao 'book-tags
                (sxql:where (:= :book-id
                                (mito:object-id obj))))))
    (when book-tags
     (mapcar #'tag book-tags))))

How to use it:

(tags (mito:find-dao 'book))
;; => (#<TAG tag1> #<TAG tag2>)

(tags (mito:find-dao 'book :id 2))
;; NIL

Simple enough, but that’s another TODO: automate the creation of such methods.

Closing words

This project has too many moving parts to my taste but I’ll get there.

I hate when people don’t release their useful library because “the code is meh” and yes, I’m doing that to you.

However I extracted parts from OpenBookStore (WIP) (notably the login, users auth and rights which was contributed by gnuxie), I published the little pagination module, contributed to a couple libraries and wrote blog posts. It’s already that for you.

You are at the very least helping as duck-brainstorming, so thanks. If you’d like to try the demo and look at horrible code, it should be doable.

I wonder under which license I’ll publish that.

There might be other ways for an admin panel and please try and cook us a plug-and-play solution. The proprietary Airtable with the new cl-airtable library or the open-source NocoDB (they give you a spreadsheet-like web UI + an API), or the lightweight Pocketbase, Mathesar or Supabase for Postgres (would you give this to your non-tech-savvy clients?), or NodeJS-based admin panels, etc, etc, etc. But a pure Common Lisp one? We’ll talk more about it in less than ten years, fingers crossed.

You can support E. Fukamachi for his work on Mito, cl-dbi, SxQL and all his other useful libraries.

Appendix: TODOs

View:

- [X] list of tables
- [-] list of records for each table
  - [X] see records
  - [X] pagination
  - [X] add "create" button
  - [ ] choose fields to display in search result
  - [ ] order records by field
- [X] a specific record
  - [X] view related column
  - [X] view some fields, ignore some fields

Search:

- [X] lax search on given fields
- [ ] more criteria

Create:

- [-] create a record
  - [X] ignore some fields
  - [X] choose related column
    - [X] select input for a to-1 relationship
    - [-] have a usable input widget for many-to-many relationships
      - [X] define a many-to-many in the demo: a book can have many tags.
      - [ ] define a default input widget (probably select2)
      - Mito doesn't help much with many-to-manys though.
  - [-] form handling
    - [X] form validation
      - [X] basics
    - [X] CSRF protection
      - in the create and edit forms.
  - [ ] form handling (cont)
    - [ ] subforms (create a new shelf in the card form)
    - [ ] client-side validation

Update:

- [X] update an existing record
  - [X] with same mechansim as create

Demo:

- [X] demo project with a couple tables and fields
  - [X] decouple the POC from openbookstore. Shit that wasn't that
    easy. <2024-03-21>
- [ ] display the app name instead of mito-admin (in title, header, footer).

Login:

- [-] admin user and rights
  - [X] base mechanism: users, roles, DB migrations (imported from
    OpenBookStore, again. Contributions by gnuxie). <2024-07-31>
  - [X] templates
  - [ ] add access rights to all the admin dashboard routes
  - [ ] add user logout dropdown in app

Actions:

- [ ] in the list of records, have actions: export to CSV, etc.


More:

- reference documentation website and tutorial
- unit tests
- include static assets (Bulma…) to work offline

i18n

- translations. See cl-gettext in OpenBookStore.

style:

- [X] dark mode (thanks Bulma 1.0)