Ningle Tutorial 11: Posting Tweets & Advanced Database Queries

Contents

• Part 1 (Hello World)
• Part 2 (Basic Templates)
• Part 3 (Introduction to middleware and Static File management)
• Part 4 (Forms)
• Part 5 (Environmental Variables)
• Part 6 (Database Connections)
• Part 7 (Envy Configuation Switching)
• Part 8 (Mounting Middleware)
• Part 9 (Authentication System)
• Part 10 (Email)
• Part 11 (Posting Tweets & Advanced Database Queries)

Introduction

Welcome back! I hope you are well, this tutorial will be have us writing code to integrate the concept of “posts” into our tutorial app, up until now we had a list of posts displayed as an example of how the page might look, well, this changes now. In the course of this tutorial we will be adding new models and forms (like we did in Part 9 (Authentication System)), we will be exploring a new concept in Ningle that allows us to define and use our own requirements, we will also be using some advanced SXQL to perform somewhat more complicated collection of data than we have previously used, and finally, we can add an honest to goodness json library for returning some responses as something other than html.

With any luck that all sounds exciting! We can broadly split our work this month into three sections, which should make the task easier.

DB Schema and forms

Here we will be defining our new models, but unlike before, not every model will be getting a form, some models will be used behind the scenes and users wont directly interact with. This is one of those areas where data has to be very carefully thought of, the example here is likes, in social media platforms, each post has some sort of interaction (likes, reactions, thumbsup, etc), and it looks like this is a property of a post, and indeed it might make sense to assume that a post “has” likes, but this isn’t actually true, what we will have is a likes model that relates to both a post and a user. The user is just presented with visual information that makes it look like likes are something a post has.

src/models.lisp

Our models file will include more than just model definitions, we have some methods and functions we need to write to access or alter our data, we will have two models our posts and likes, we will use likes to link a post to a user (from our ningle-auth package).

Let’s start by defining our package and models, we will look at the other methods and functions we are exporting a little further down.

(defpackage ningle-tutorial-project/models
  (:use :cl :mito :sxql)
  (:import-from :ningle-auth/models #:user)
  (:export #:post
           #:id
           #:content
           #:likes
           #:user
           #:liked-post-p
           #:logged-in-posts
           #:not-logged-in-posts
           #:toggle-like))

(in-package ningle-tutorial-project/models)

(deftable post ()
  ((user    :col-type ningle-auth/models:user :initarg :user    :accessor user)
   (content :col-type (:varchar 140)          :initarg :content :accessor content)))

(deftable likes ()
  ((user :col-type ningle-auth/models:user :initarg :user :reader user)
   (post :col-type post                    :initarg :post :reader post))
  (:unique-keys (user post)))

Our post has a user and some content, we don’t have comments or reposts or anything (this is a tutorial after all!), what we want to ensure with the likes model though, is that there’s a unique constraint between user and post, this ensures that a user can like a specific post only once. Otherwise our like count would be unreliable.

In our exports list you will see we export the id, user, content, likes, post etc, but there’s more!

Recall that Common Lisp is a lisp-2 and as such we can have function/method names as the same as objects, and because of this, we will defined some methods with the name “likes” which are different from our class called “likes”.

(defgeneric likes (post)
  (:documentation "Returns the number of likes a post has"))

(defmethod likes ((post post))
  (mito:count-dao 'likes :post post))

Here we define a method that will accept a post and return the total number of likes it has, which will give us our likes count when we render the main page.

The next method we are going to write is a way to toggle the user like of a post, if they don’t like it, clicking it will like the post, if they do already like the post, clicking the like button will undo the like.

(defmethod toggle-like ((ningle-auth/models:user user) (post post))
  (let ((liked-post (liked-post-p user post)))
    (if liked-post
        (mito:delete-dao liked-post)
        (mito:create-dao 'likes :post post :user user))
    (not liked-post)))

(defgeneric liked-post-p (user post)
  (:documentation "Returns true if a user likes a given post"))

(defmethod liked-post-p ((ningle-auth/models:user user) (post post))
  (mito:find-dao 'likes :user user :post post))

The toggle-like tries to be as simple as possible, by calling the liked-post-p method to query if a user likes a post, and if the post is liked, the record of the like is deleted, if not it is created. The final thing the function does is return the not of liked-post-p, so if the post was liked at first, it will return nil, if the post wasn’t liked, it’ll return t. This will become important later, but if your function can be written in a way that can return helpful information, I suggest doing so, you may not always, or ever use the data it returns, but it’s there if you need to, it forms a usable interface.

Now to SQL!

If you are unfamiliar with SQL this part might look complicated, but in terms of SQL, it isn’t, SQL is a language used for a very specific purpose; querying and manipulating data! If you have not used SQL before/much, I highly encourage you to do so, it’s nearly 50 years old, it’s a very well tested and proven technology. It’s not going anywhere (despite what you may read online NoSQL isn’t going to replace it), and will be great for your career.

Mito is a pretty thin wrapper around SQL, unlike something like Django, Rails, or Larvel (comprehensive web frameworks), Mito doesn’t have a complex DSL for abstracting the SQL details away, instead it has the user use an SQL generator SXQL, so, for things beyond the simplest of things, we’re gonna have to get into SQL, which is fine.

We have two things we want to do:

1. Retrieve 50 posts ordered in descending order, with an extra column for the like count.
2. Retrieve 50 posts ordered in descending order, with two extra columns, one for the like count, and a second indicating if the logged in user liked the post.

Let’s start with the first case, a user has loaded the website, but they are not logged in. The best place to start is with the SQL query we want to run:

SELECT post.*, COUNT(likes.id) AS like_count
    FROM post
    LEFT JOIN likes ON (post.id = likes.post_id)
    GROUP BY post.id
    ORDER BY post.created_at DESC
    LIMIT 50;

This will give us a structure like this:

id	user_id	content	created_at	updated_at	like_count
1	4	“hi”	2025-09-13 19:43:16.718416Z	2025-09-13 19:43:16.718416Z	5

This query works by using joins, we want to get each post record and its like count, so we must join post and likes on the intersection of post.id and likes.post.id. This will allow us to iterate over the combined results and use them in our templates later.

We also use the GROUP BY clause to ensure that there is only one result per post, and that each like for a given post is summed together, so we have one post with many likes, rather than many copies of the same post each with one like.

We use the retrieve-by-sql function from mito which allows us to run SQL explicitly, but as previously mentioned we will use SXQL to more easily generate the SQL we might want within Common Lisp.

We will also use the yield function (from SXQL) to actually convert the Common Lisp representation into a string SQL can use, within that we will begin with select (also from SXQL).

(defun not-logged-in-posts ()
    (mito:retrieve-by-sql
        (sxql:yield
            (sxql:select
                (:post.* (:as (:count :likes.id) :like_count))
                (sxql:from :post)
                (sxql:left-join :likes :on (:= :post.id :likes.post_id))
                (sxql:group-by :post.id)
                (sxql:order-by (:desc :post.created_at))
                (sxql:limit 50)))))

You should be able to see that our original SQL is represented quite similarly in the SXQL, here’s a table to clearly show the minor differences.

SQL	SXQL
SELECT post.*, COUNT(likes.id) AS like_count FROM post LEFT JOIN likes ON (post.id = likes.post_id) GROUP BY post.id ORDER BY post.created_at DESC LIMIT 50;	(sxql:select (:post.* (:as (:count :likes.id) :like_count)) (sxql:from :post) (sxql:left-join :likes :on (:= :post.id :likes.post_id)) (sxql:group-by :post.id) (sxql:order-by (:desc :post.created_at)) (sxql:limit 50))

The next query we need to construct is that of the logged in user, which includes a column denoting likes for any specific post, this will be our second function logged-in-posts. As before, let’s start with what the SQL will be:

SELECT post.*, COUNT(likes.id) AS like_count, COUNT(user_likes.id) AS liked_by_user
FROM post
LEFT JOIN likes ON (post.id = likes.post_id)
LEFT JOIN likes AS user_likes ON ((post.id = user_likes.post_id) AND (user_likes.user_id = ?))
GROUP BY post.id
ORDER BY post.created_at DESC
LIMIT 50;

Please note that we have a ? where the user id would go, we do not wish to be subject to SQL injection attacks, so mito allows us to bind values, but we will keep the ? as it’s what we will use in the SXQL too.

Which will generate the following table structure.

id	user_id	content	created_at	updated_at	like_count	liked_by_user
1	4	“hi”	2025-09-13 19:43:16.718416Z	2025-09-13 19:43:16.718416Z	5	1

The extra column is only a small change on the first query, by adding a new call to COUNT in the SELECT line, we prepare the column, and we get the data from the second LEFT JOIN which will join (using a new alias; user_likes) where the post id is the same as the user likes post id and where the user likes user id is the same as the logged in user, this will either return a record or null. When we call count on the record returned, it becomes 1 or 0, effectively a boolean check.

We can see the differences between the SQL and the SXQL here.

SQL

SELECT post.*, COUNT(likes.id) AS like_count, COUNT(user_likes.id) AS liked_by_user
FROM post
LEFT JOIN likes ON (post.id = likes.post_id)
LEFT JOIN likes AS user_likes ON ((post.id = user_likes.post_id) AND (user_likes.user_id = ?))
GROUP BY post.id
ORDER BY post.created_at DESC
LIMIT 50;

SXQL

(sxql:select (:post.* (:as (:count :likes.id) :like_count) (:as (:count :user_likes.id) :liked_by_user))
(sxql:from :post)
(sxql:left-join :likes :on (:= :post.id :likes.post_id))
(sxql:left-join (:as :likes :user_likes) :on (:and (:= :post.id :user_likes.post_id) (:= :user_likes.user_id :?)))
(sxql:group-by :post.id)
(sxql:order-by (:desc :post.created_at))
(sxql:limit 50)))

So this SXQL will be used in our function like so:

(defmethod logged-in-posts ((user user))
  (let ((uid (slot-value user 'mito.dao.mixin::id)))
    (mito:retrieve-by-sql
        (sxql:yield
            (sxql:select
                (:post.* (:as (:count :likes.id) :like_count) (:as (:count :user_likes.id) :liked_by_user))
                (sxql:from :post)
                (sxql:left-join :likes :on (:= :post.id :likes.post_id))
                (sxql:left-join (:as :likes :user_likes)
                                :on (:and (:= :post.id :user_likes.post_id)
                                          (:= :user_likes.user_id :?)))
                (sxql:group-by :post.id)
                (sxql:order-by (:desc :post.created_at))
                (sxql:limit 50)))
            :binds (list uid))))

As mentioned before, you can see the :binds which will insert the user id into the SXQL query for safety.

So with these two complex functions in place now, we have everything we need, for clarity the complete listing of the models.lisp file is as follows:

(defpackage ningle-tutorial-project/models
  (:use :cl :mito :sxql)
  (:import-from :ningle-auth/models #:user)
  (:export #:post
           #:id
           #:content
           #:likes
           #:user
           #:liked-post-p
           #:logged-in-posts
           #:not-logged-in-posts
           #:toggle-like))

(in-package ningle-tutorial-project/models)

(deftable post ()
  ((user    :col-type ningle-auth/models:user :initarg :user    :accessor user)
   (content :col-type (:varchar 140)          :initarg :content :accessor content)))

(deftable likes ()
  ((user :col-type ningle-auth/models:user :initarg :user :reader user)
   (post :col-type post                    :initarg :post :reader post))
  (:unique-keys (user post)))

(defgeneric likes (post)
  (:documentation "Returns the number of likes a post has"))

(defmethod likes ((post post))
  (mito:count-dao 'likes :post post))

(defgeneric toggle-like (user post)
  (:documentation "Toggles the like of a user to a given post"))

(defmethod toggle-like ((ningle-auth/models:user user) (post post))
  (let ((liked-post (liked-post-p user post)))
    (if liked-post
        (mito:delete-dao liked-post)
        (mito:create-dao 'likes :post post :user user))
    (not liked-post)))

(defgeneric liked-post-p (user post)
  (:documentation "Returns true if a user likes a given post"))

(defmethod liked-post-p ((ningle-auth/models:user user) (post post))
  (mito:find-dao 'likes :user user :post post))

(defgeneric logged-in-posts (user)
  (:documentation "Gets the posts for a logged in user"))

(defmethod logged-in-posts ((user user))
  (let ((uid (slot-value user 'mito.dao.mixin::id)))
    (mito:retrieve-by-sql
        (sxql:yield
            (sxql:select
                (:post.*
                (:as (:count :likes.id) :like_count)
                (:as (:count :user_likes.id) :liked_by_user))
                (sxql:from :post)
                (sxql:left-join :likes :on (:= :post.id :likes.post_id))
                (sxql:left-join (:as :likes :user_likes)
                                :on (:and (:= :post.id :user_likes.post_id)
                                          (:= :user_likes.user_id :?)))
                (sxql:group-by :post.id)
                (sxql:order-by (:desc :post.created_at))
                (sxql:limit 50)))
            :binds (list uid))))

(defun not-logged-in-posts ()
    (mito:retrieve-by-sql
        (sxql:yield
        (sxql:select
            (:post.* (:as (:count :likes.id) :like_count))
            (sxql:from :post)
            (sxql:left-join :likes :on (:= :post.id :likes.post_id))
            (sxql:group-by :post.id)
            (sxql:order-by (:desc :post.created_at))
            (sxql:limit 50)))))

src/forms.lisp

Our forms are much simpler, we only have one form, the post. While we do have the likes model, our users will not be directly using that, and thus we don’t need to render a form for this.

(defpackage ningle-tutorial-project/forms
  (:use :cl :cl-forms)
  (:export #:post
           #:content
           #:submit))

(in-package ningle-tutorial-project/forms)

(defparameter *post-validator* (list (clavier:not-blank)
                                     (clavier:is-a-string)
                                     (clavier:len :max 140)))

(defform post (:id "post" :csrf-protection t :csrf-field-name "csrftoken" :action "/post")
  ((content  :string   :value "" :constraints *post-validator*)
   (submit   :submit   :label "Post")))

Like we used in a previous tutorial, we use the clavier validation library to ensure that our users post things that fit within the constraints of our system, we also want to make sure we are using CSRF tokens for security.

We will style this form using CSS later.

src/migrations.lisp

Now, our main project now contains its own migrations, we perhaps should have written the code to perform migrations in another file and reserved this for specific migrations, but we can work with things the way they are.

We are going to start by adding a function to the top of our migrations.lisp file.

(defun migrate ()
  "Explicitly apply migrations when called."
  (format t "Applying migrations...~%")
  (mito:ensure-table-exists 'ningle-tutorial-project/models:post)
  (mito:ensure-table-exists 'ningle-tutorial-project/models:likes)
  (mito:migrate-table 'ningle-tutorial-project/models:post)
  (mito:migrate-table 'ningle-tutorial-project/models:likes)
  (format t "Migrations complete.~%"))

These will be the project specific migrations, however we still need a way to trigger them, and since we wrote a way to apply specific apps only, we need a way to exclude these if we do not wish to run these migrations.

The next thing we need to do is to extend the migrate-apps function we previously wrote. We will add a parameter to the function:

(defun migrate-apps (&optional (apps nil) &key skip-root)

And within the macro call:

(with-db-connection
    ...)

We add:

(unless skip-root
    (format t "Running root project migrations...~%")
    (migrate))

There is also a small correction we need to make, this line.

(error "Migrate function not found in package ~A." migrations-pkg-name)

Needs to be corrected to:

(error (format nil "Migrate function not found in package ~A." migrations-pkg-name))

Full listing:

(defpackage ningle-tutorial-project/migrations
  (:use :cl :ningle-tutorial-project/contrib)
  (:export #:migrate-apps))

(in-package :ningle-tutorial-project/migrations)

(defun migrate ()
  "Explicitly apply migrations when called."
  (format t "Applying migrations...~%")
  (mito:ensure-table-exists 'ningle-tutorial-project/models:post)
  (mito:ensure-table-exists 'ningle-tutorial-project/models:likes)
  (mito:migrate-table 'ningle-tutorial-project/models:post)
  (mito:migrate-table 'ningle-tutorial-project/models:likes)
  (format t "Migrations complete.~%"))

(defun migrate-apps (&optional (apps nil) &key skip-root)
  "Run migrate function for each app in APPS list. If APPS is nil, migrate all apps listed in *config* :installed-apps."

  (let ((apps (or apps (getf (envy:config :ningle-tutorial-project/config) :installed-apps))))
    (unless apps
      (error "No apps specified and no :installed-apps found in config."))

    (with-db-connection
        (unless skip-root
          (format t "Running root project migrations...~%")
          (migrate))

        (dolist (app apps)
            (let* ((migrations-pkg-name (string-upcase (format nil "~A/MIGRATIONS" (string-upcase (symbol-name app)))))
                   (migrations-pkg (find-package migrations-pkg-name)))
                (unless migrations-pkg
                    (error "Migrations package ~A not found." migrations-pkg-name))

                ;; Set app-specific config before calling migrate
                (let ((migrate-fn (find-symbol "MIGRATE" migrations-pkg))) ;; Name known to project
                    (unless (and migrate-fn (fboundp migrate-fn))
                        (error (format nil "Migrate function not found in package ~A." migrations-pkg-name)))
                    (funcall migrate-fn)))))))

ningle-tutorial-project.asd

With these files added, we need to remember to add them to our project.asd file.

:components ((:module "src"
                :components
                ((:file "contrib")
                 (:file "middleware")
                 (:file "config")
                 (:file "models")  ; add this line
                 (:file "forms")   ; add this line
                 (:file "migrations")
                 (:file "main"))))

Controller Logic

src/main.lisp

We will now look at the controller logic to handle posting, well, posts. We will introduce a feature of Ningle we have not yet looked into that can help us create smaller, more specialised, logical units of work, requirements. Ningle has the ability to define conditions that can be passed as keyword arguments to a controller, if the condition is true, the controller is triggered. In our controllers previously we have had if checks for if a user is logged in, or if a request is a GET or a POST, these requirements allow us to write smaller functions to help us focus on one specific type of request (even if on the same route). I find this helps me, personally, if I can reduce the number of things I have to be remembering when I am working on a function.

Before we do, however, we will allow our main code to use the forms we defined in the previous section.

(defpackage ningle-tutorial-project
  (:use :cl :sxql :ningle-tutorial-project/forms) ; Add the :ningle-tutorial-project/forms bit!
  (:export #:start
           #:stop))

(in-package ningle-tutorial-project)

Now with that in place we can begin in earnest! We already use these requirements already with our :method '(:GET :POST) that we used previously, but we can define our own! We will define a requirement that there is a logged in user. In our src/main.lisp file, before the routes we previously defined, we will add this:

(setf (ningle:requirement *app* :logged-in-p)
      (lambda (value)
        (and (cu-sith:logged-in-p) value)))

Since this will be used as a keyword argument, the lambda function will always define a parameter, this will be the value found to the key word argument later when this is used in a route definition. We will use this requirement in a few places here, starting with our “/” route.

Previously we just had a dummy response that returned what we thought the posts might look like, but now we have the capability to store and retrieve posts from a database we can change this now.

We have different database queries too, a query to run when a user is not logged in, and a query to run when they are, this this helps split our controllers into a logged in view, and a not logged in view.

A quick word on controller definitions, if you have multiple controllers, you must define the most specific ones first! So we will start by defining a view that matches on “/” and when logged-in-p is t, because if we try to match on “/” first, then it matches every controller for that route, ignoring any other specific requirements of it, so we must define our logged in view first!

(setf (ningle:route *app* "/" :logged-in-p t)
      (lambda (params)
        (let* ((user (gethash :user ningle:*session*))
               (form (cl-forms:find-form 'post))
               (posts (ningle-tutorial-project/models:logged-in-posts user)))
          (djula:render-template* "main/index.html" nil :title "Home" :user user :posts posts :form form))))

In this controller we ensure that there is a user that is logged in using :logged-in-p t, and another change this controller handles if a user is logged in, is permitting them to post! So this controller grabs the logged in user, the form for posting content and the first 50 posts (which is what logged-in-posts does) and renders them in the template.

Then we can define a more general “/” controller after it.

(setf (ningle:route *app* "/")
      (lambda (params)
        (let ((posts (ningle-tutorial-project/models:not-logged-in-posts)))
          (djula:render-template* "main/index.html" nil :title "Home" :user (gethash :user ningle:*session*) :posts posts))))

This is simpler, by not needing a user or post form, we can forgo these and simply get a list of posts with not-logged-in-posts. Although, now I think about it, I could have written a helper method that takes a user object and runs these functions depending on if the user is nil or not, you live and learn!

Please note that these two controllers will replace the previous “/” controller we had.

With these in place we need a controller to toggle the liked status of a post.

(setf (ningle:route *app* "/post/:id/likes" :method :POST :logged-in-p t)
     (lambda (params)
       (let* ((user (gethash :user ningle:*session*))
              (post (mito:find-dao 'ningle-tutorial-project/models:post :id (parse-integer (ingle:get-param :id params))))
              (res (make-hash-table :test 'equal)))
           (setf (gethash :post res) (ingle:get-param :id params))
           (setf (gethash :likes res) (ningle-tutorial-project/models:likes post))
           (setf (gethash :liked res) (ningle-tutorial-project/models:toggle-like user post))
           (com.inuoe.jzon:stringify res))))

Here, this controller is permitted to POST only and requires that a user is logged in, we obviously don’t want users that aren’t logged in to be able to like posts. So we grab the user, the post that is to be liked and we create a hash-table for creating our response because here, we actually use the jzon package to return a valid json response. This controller sets the :post, :likes, and :liked fields and stringifies the hash-table so it can be read as json. We need to grab the post id from the url, but we have seen this before.

Our next controller simply directs the user to a specific post.

(setf (ningle:route *app* "/post/:id")
      (lambda (params)
        (handler-case
          (let ((post (mito:find-dao 'ningle-tutorial-project/models:post :id (parse-integer (ingle:get-param :id params)))))
              (djula:render-template* "main/post.html" nil :title "Post" :post post))

          (parse-error (err)
                (setf (lack.response:response-status ningle:*response*) 404)
                (djula:render-template* "error.html" nil :title "Error" :error err)))))

We set up a handler-case to attempt to load a specific post and render the template, if that fails, we set a 404 response code and render the error page.

Moving on now to actually posting some content! Once again this controller should only be permitted to serve POST requests and require that a user is logged in. As we have seen previously in this series we need to grab the user object and the form that was submitted. From there we do the error handling handler-case by handling the loading of the form, we handle the values of valid, or errors and enter the content of a post into the database if there’s no errors, if there are, a 403 is set and the error is rendered.

(setf (ningle:route *app* "/post" :method :POST :logged-in-p t)
      (lambda (params)
        (let ((user (gethash :user ningle:*session*))
              (form (cl-forms:find-form 'post)))
          (handler-case
            (progn
              (cl-forms:handle-request form) ; Can throw an error if CSRF fails

              (multiple-value-bind (valid errors)
                  (cl-forms:validate-form form)

                (when errors
                  (format t "Errors: ~A~%" errors))

                (when valid
                  (cl-forms:with-form-field-values (content) form
                    (mito:create-dao 'ningle-tutorial-project/models:post :content content :user user)
                    (ingle:redirect "/")))))

            (simple-error (err)
              (setf (lack.response:response-status ningle:*response*) 403)
              (djula:render-template* "error.html" nil :title "Error" :error err))))))

Finally we now look to replace the “/profile” controllers, we have already explored the new concepts but this serves as a simple, clear example, and it helps we need to work on this further anyway!

(setf (ningle:route *app* "/profile" :logged-in-p t)
      (lambda (params)
        (let ((user (gethash :user ningle:*session*)))
            (djula:render-template* "main/profile.html" nil :title "Profile" :user user))))

(setf (ningle:route *app* "/profile")
      (lambda (params)
          (setf (lack.response:response-status ningle:*response*) 403)
          (djula:render-template* "error.html" nil :title "Error" :error "Unauthorized")))

Full listing:

(defpackage ningle-tutorial-project
  (:use :cl :sxql :ningle-tutorial-project/forms)
  (:export #:start
           #:stop))

(in-package ningle-tutorial-project)

(defvar *app* (make-instance 'ningle:app))

;; requirements
(setf (ningle:requirement *app* :logged-in-p)
      (lambda (value)
        (and (cu-sith:logged-in-p) value)))

;; routes
(setf (ningle:route *app* "/" :logged-in-p t)
      (lambda (params)
        (let* ((user (gethash :user ningle:*session*))
               (form (cl-forms:find-form 'post))
               (posts (ningle-tutorial-project/models:logged-in-posts user)))
          (djula:render-template* "main/index.html" nil :title "Home" :user user :posts posts :form form))))

(setf (ningle:route *app* "/")
      (lambda (params)
        (let ((posts (ningle-tutorial-project/models:not-logged-in-posts)))
          (djula:render-template* "main/index.html" nil :title "Home" :user (gethash :user ningle:*session*) :posts posts))))

(setf (ningle:route *app* "/post/:id/likes" :method :POST :logged-in-p t)
     (lambda (params)
       (let* ((user (gethash :user ningle:*session*))
              (post (mito:find-dao 'ningle-tutorial-project/models:post :id (parse-integer (ingle:get-param :id params))))
              (res (make-hash-table :test 'equal)))
           (setf (gethash :post res) (ingle:get-param :id params))
           (setf (gethash :likes res) (ningle-tutorial-project/models:likes post))
           (setf (gethash :liked res) (ningle-tutorial-project/models:toggle-like user post))
           (com.inuoe.jzon:stringify res))))

(setf (ningle:route *app* "/post/:id")
      (lambda (params)
        (handler-case
          (let ((post (mito:find-dao 'ningle-tutorial-project/models:post :id (parse-integer (ingle:get-param :id params)))))
              (djula:render-template* "main/post.html" nil :title "Post" :post post))

          (parse-error (err)
                (setf (lack.response:response-status ningle:*response*) 404)
                (djula:render-template* "error.html" nil :title "Error" :error err)))))

(setf (ningle:route *app* "/post" :method :POST :logged-in-p t)
      (lambda (params)
        (let ((user (gethash :user ningle:*session*))
              (form (cl-forms:find-form 'post)))
          (handler-case
            (progn
              (cl-forms:handle-request form) ; Can throw an error if CSRF fails

              (multiple-value-bind (valid errors)
                  (cl-forms:validate-form form)

                (when errors
                  (format t "Errors: ~A~%" errors))

                (when valid
                  (cl-forms:with-form-field-values (content) form
                    (mito:create-dao 'ningle-tutorial-project/models:post :content content :user user)
                    (ingle:redirect "/")))))

            (simple-error (err)
              (setf (lack.response:response-status ningle:*response*) 403)
              (djula:render-template* "error.html" nil :title "Error" :error err))))))

(setf (ningle:route *app* "/profile" :logged-in-p t)
      (lambda (params)
        (let ((user (gethash :user ningle:*session*)))
            (djula:render-template* "main/profile.html" nil :title "Profile" :user user))))

(setf (ningle:route *app* "/profile")
      (lambda (params)
          (setf (lack.response:response-status ningle:*response*) 403)
          (djula:render-template* "error.html" nil :title "Error" :error "Unauthorized")))

(setf (ningle:route *app* "/people")
      (lambda (params)
        (let ((users (mito:retrieve-dao 'ningle-auth/models:user)))
          (djula:render-template* "main/people.html" nil :title "People" :users users :user (cu-sith:logged-in-p)))))

(setf (ningle:route *app* "/people/:person")
      (lambda (params)
        (let* ((username-or-email (ingle:get-param :person params))
               (person (first (mito:select-dao
                              'ningle-auth/models:user
                              (where (:or (:= :username username-or-email)
                                          (:= :email username-or-email)))))))
          (djula:render-template* "main/person.html" nil :title "Person" :person person :user (cu-sith:logged-in-p)))))

(defmethod ningle:not-found ((app ningle:<app>))
    (declare (ignore app))
    (setf (lack.response:response-status ningle:*response*) 404)
    (djula:render-template* "error.html" nil :title "Error" :error "Not Found"))

(defun start (&key (server :woo) (address "127.0.0.1") (port 8000))
    (djula:add-template-directory (asdf:system-relative-pathname :ningle-tutorial-project "src/templates/"))
    (djula:set-static-url "/public/")
    (clack:clackup
     (lack.builder:builder (envy-ningle:build-middleware :ningle-tutorial-project/config *app*))
     :server server
     :address address
     :port port))

(defun stop (instance)
    (clack:stop instance))

ningle-tutorial-project.asd

There’s one final thing to add before we look at the aesthetic changes we will be applying, we need to ensure we add the jzon package to our project dependencies.

:depends-on (:cl-dotenv
               :clack
               :djula
               :cl-forms
               :cl-forms.djula
               :cl-forms.ningle
               :envy
               :envy-ningle
               :ingle
               :com.inuoe.jzon ; <- Add this line
               :mito
               :mito-auth
               :ningle
               :ningle-auth)

HTML Changes

We make some changes to our html, sadly the biggest part of it is JavaScript, but nevermind!

src/template/base.html

In our base template we only make a couple of changes, in our <head></head> section, prior to loading our own css, we must include the bootstrap icons package.

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.css"> <! -- add this line! -->
<link rel="stylesheet" href="{% static "css/main.css" %}"/>

Next, right at the bottom, we include a way to add JS to templates, if we need to.

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script>
    {% block js %}
    {% endblock %}
</script>

Full listing:

<!doctype html>
<html lang="en">
    <head>
        {% if title %}
            <title>{{ title }} - Y</title>
        {% else %}
            <title>Welcome to Y</title>
        {% endif %}
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
        <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.css">
        <link rel="stylesheet" href="{% static "css/main.css" %}"/>
    </head>
    <body>
        <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
            <div class="container-fluid">
                <a class="navbar-brand" href="/">
                    <img src="{% static "images/logo.jpg" %}" alt="Logo" class="d-inline-block align-text-top logo">
                    Y
                </a>

                <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
                    <span class="navbar-toggler-icon"></span>
                </button>

                <div class="collapse navbar-collapse" id="navbarSupportedContent">
                    <ul class="navbar-nav me-auto">
                        <li class="nav-item {% ifequal title "Home" %}disabled{% endifequal %}">
                            <a class="nav-link" href="/">Home</a>
                        </li>
                        <li class="nav-item {% ifequal title "People" %}disabled{% endifequal %}">
                            <a class="nav-link" href="/people">People</a>
                        </li>
                    </ul>

                    <div class="d-flex ms-auto">
                        {% if user %}
                            <a href="/profile" class="btn btn-primary">{{ user.username }}</a>
                            &nbsp;|&nbsp;
                            <a href="/auth/logout" class="btn btn-secondary">Logout</a>
                        {% else %}
                            <a href="/auth/register" class="btn btn-primary">Register</a>
                            &nbsp;|&nbsp;
                            <a href="/auth/login" class="btn btn-success">Login</a>
                        {% endif %}
                    </div>
                </div>
            </div>
        </nav>

        <div class="container mt-4">
            {% block content %}
            {% endblock %}
        </div>

        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
        <script>
            {% block js %}
            {% endblock %}
        </script>
    </body>
</html>

src/template/main/index.html

Our index page will need to include some JavaScript, this will be with the intention of sending a request to the controller to increment/decrement the like count of a post. Again since this tutorial is about Common Lisp, I won’t really be explaining the JS.

In the first part of the container div, we will add our form to post content:

{% block content %}
<div class="container">
    <!-- Post form -->
    <div class="row mb-4">
        <div class="col">
            {% if form %}
                {% form form %}
            {% endif %}
        </div>
    </div>
    ...

This displays the full form, including labels we don’t necessarily need, so we hide this using the css that was written, but this will only show when a user is logged in and will post content for the logged in user.

Next we will be changing the structure of the contents of our posts for loop, nothing major, but since we have better CSS we might want to ensure our HTML matches it.

{% for post in posts %}
  <div class="card post mb-3" data-href="/post/{{ post.id }}">
      <div class="card-body">
      <h5 class="card-title mb-2">{{ post.content }}</h5>
      <p class="card-subtitle text-muted mb-0">@{{ post.user.username }}</p>
      </div>

      <div class="card-footer d-flex justify-content-between align-items-center">
        <button type="button"
                class="btn btn-sm btn-outline-primary like-button"
                data-post-id="{{ post.id }}"
                data-logged-in="{% if user.username != "" %}true{% else %}false{% endif %}"
                data-liked="{% if post.liked-by-user == 1 %}true{% else %}false{% endif %}"
                aria-label="Like post {{ post.id }}">
            {% if post.liked-by-user == 1 %}
              <i class="bi bi-hand-thumbs-up-fill text-primary" aria-hidden="true"></i>
            {% else %}
              <i class="bi bi-hand-thumbs-up text-muted" aria-hidden="true"></i>
            {% endif %}
            <span class="ms-1 like-count">{{ post.like-count }}</span>
        </button>

        <small class="text-muted">Posted on: {{ post.created-at }}</small>
      </div>
  </div>
{% endfor %}

Then in the case where we do not have any posts!

{% if not posts %}
    <div class="text-center">
        <p class="text-muted">No posts to display.</p>
    </div>
{% endif %}

Finally the dreaded JS!

{% block js %}
document.querySelectorAll(".like-button").forEach(btn => {
  btn.addEventListener("click", function (e) {
    e.stopPropagation();
    e.preventDefault();

    // Check login
    if (btn.dataset.loggedIn !== "true") {
      alert("You must be logged in to like posts.");
      return;
    }

    const postId = btn.dataset.postId;
    const countSpan = btn.querySelector(".like-count");
    const icon = btn.querySelector("i");
    const liked = btn.dataset.liked === "true";
    const previous = parseInt(countSpan.textContent, 10) || 0;
    const url = `/post/${postId}/likes`;

    // Optimistic UI toggle
    countSpan.textContent = liked ? previous - 1 : previous + 1;
    btn.dataset.liked = liked ? "false" : "true";

    // Toggle icon classes optimistically
    if (liked) {
      // Currently liked, so unlike it
      icon.className = "bi bi-hand-thumbs-up text-muted";
    } else {
      // Currently not liked, so like it
      icon.className = "bi bi-hand-thumbs-up-fill text-primary";
    }

    const csrfTokenMeta = document.querySelector('meta[name="csrf-token"]');
    const headers = { "Content-Type": "application/json" };
    if (csrfTokenMeta) headers["X-CSRF-Token"] = csrfTokenMeta.getAttribute("content");

    fetch(url, {
      method: "POST",
      headers: headers,
      body: JSON.stringify({ toggle: true })
    })
    .then(resp => {
      if (!resp.ok) {
        // Revert optimistic changes on error
        countSpan.textContent = previous;
        btn.dataset.liked = liked ? "true" : "false";
        if (liked) {
          icon.className = "bi bi-hand-thumbs-up-fill text-primary";
        } else {
          icon.className = "bi bi-hand-thumbs-up text-muted";
        }
        throw new Error("Network response was not ok");
      }
      return resp.json();
    })
    .then(data => {
      if (data && typeof data.likes !== "undefined") {
        countSpan.textContent = data.likes;
        btn.dataset.liked = data.liked ? "true" : "false";

        // Update icon based on server response
        if (data.liked) {
          icon.className = "bi bi-hand-thumbs-up-fill text-primary";
        } else {
          icon.className = "bi bi-hand-thumbs-up text-muted";
        }
      }
    })
    .catch(err => {
      console.error("Like failed:", err);
      // Revert optimistic changes on error
      countSpan.textContent = previous;
      btn.dataset.liked = liked ? "true" : "false";
      if (liked) {
        icon.className = "bi bi-hand-thumbs-up-fill text-primary";
      } else {
        icon.className = "bi bi-hand-thumbs-up text-muted";
      }
    });
  });
});

document.querySelectorAll(".card.post").forEach(card => {
  card.addEventListener("click", function () {
    const href = card.dataset.href;
    if (href) {
      window.location.href = href;
    }
  });
});
{% endblock %}

Full listing:

{% extends "base.html" %}

{% block content %}
<div class="container">
    <!-- Post form -->
    <div class="row mb-4">
        <div class="col">
            {% if form %}
                {% form form %}
            {% endif %}
        </div>
    </div>

    <!-- Posts Section -->
    <div class="row">
        <div class="col-12">
            {% for post in posts %}
            <div class="card post mb-3" data-href="/post/{{ post.id }}">
                <div class="card-body">
                  <h5 class="card-title mb-2">{{ post.content }}</h5>
                  <p class="card-subtitle text-muted mb-0">@{{ post.user.username }}</p>
                </div>

                <div class="card-footer d-flex justify-content-between align-items-center">
                <button type="button"
                        class="btn btn-sm btn-outline-primary like-button"
                        data-post-id="{{ post.id }}"
                        data-logged-in="{% if user.username != "" %}{% endraw %true{% endraw %true{% raw %}{% else %}false{% endif %}"
                        data-liked="{% if post.liked-by-user == 1 %}true{% else %}false{% endif %}"
                        aria-label="Like post {{ post.id }}">
                    {% if post.liked-by-user == 1 %}
                      <i class="bi bi-hand-thumbs-up-fill text-primary" aria-hidden="true"></i>
                    {% else %}
                      <i class="bi bi-hand-thumbs-up text-muted" aria-hidden="true"></i>
                    {% endif %}
                    <span class="ms-1 like-count">{{ post.like-count }}</span>
                </button>

                <small class="text-muted">Posted on: {{ post.created-at }}{% raw %}</small>
                </div>
            </div>
            {% raw %}{% endfor %}

            {% if not posts %}
                <div class="text-center">
                    <p class="text-muted">No posts to display.</p>
                </div>
            {% endif %}
        </div>
    </div>
</div>
{% endblock %}

{% block js %}
document.querySelectorAll(".like-button").forEach(btn => {
  btn.addEventListener("click", function (e) {
    e.stopPropagation();
    e.preventDefault();

    // Check login
    if (btn.dataset.loggedIn !== "true") {
      alert("You must be logged in to like posts.");
      return;
    }

    const postId = btn.dataset.postId;
    const countSpan = btn.querySelector(".like-count");
    const icon = btn.querySelector("i");
    const liked = btn.dataset.liked === "true";
    const previous = parseInt(countSpan.textContent, 10) || 0;
    const url = `/post/${postId}/likes`;

    // Optimistic UI toggle
    countSpan.textContent = liked ? previous - 1 : previous + 1;
    btn.dataset.liked = liked ? "false" : "true";

    // Toggle icon classes optimistically
    if (liked) {
      // Currently liked, so unlike it
      icon.className = "bi bi-hand-thumbs-up text-muted";
    } else {
      // Currently not liked, so like it
      icon.className = "bi bi-hand-thumbs-up-fill text-primary";
    }

    const csrfTokenMeta = document.querySelector('meta[name="csrf-token"]');
    const headers = { "Content-Type": "application/json" };
    if (csrfTokenMeta) headers["X-CSRF-Token"] = csrfTokenMeta.getAttribute("content");

    fetch(url, {
      method: "POST",
      headers: headers,
      body: JSON.stringify({ toggle: true })
    })
    .then(resp => {
      if (!resp.ok) {
        // Revert optimistic changes on error
        countSpan.textContent = previous;
        btn.dataset.liked = liked ? "true" : "false";
        if (liked) {
          icon.className = "bi bi-hand-thumbs-up-fill text-primary";
        } else {
          icon.className = "bi bi-hand-thumbs-up text-muted";
        }
        throw new Error("Network response was not ok");
      }
      return resp.json();
    })
    .then(data => {
      if (data && typeof data.likes !== "undefined") {
        countSpan.textContent = data.likes;
        btn.dataset.liked = data.liked ? "true" : "false";

        // Update icon based on server response
        if (data.liked) {
          icon.className = "bi bi-hand-thumbs-up-fill text-primary";
        } else {
          icon.className = "bi bi-hand-thumbs-up text-muted";
        }
      }
    })
    .catch(err => {
      console.error("Like failed:", err);
      // Revert optimistic changes on error
      countSpan.textContent = previous;
      btn.dataset.liked = liked ? "true" : "false";
      if (liked) {
        icon.className = "bi bi-hand-thumbs-up-fill text-primary";
      } else {
        icon.className = "bi bi-hand-thumbs-up text-muted";
      }
    });
  });
});

document.querySelectorAll(".card.post").forEach(card => {
  card.addEventListener("click", function () {
    const href = card.dataset.href;
    if (href) {
      window.location.href = href;
    }
  });
});
{% endblock %}

src/template/main/post.html

We will add a new post template, this isn’t actually for creating a post, as we saw above we integrated that form into the index page, but rather this is the template for showing an individual post. In the future we might introduce comments etc and this would make it easier to see all of that content in one page.

{% extends "base.html" %}

{% block content %}
<div class="container">
    <div class="row">
        <div class="col-12">
            <h2>{{ post.user.username }}</h2>
            <p>{{ post.content }}</p>
        </div>
    </div>
</div>
{% endblock %}

CSS Changes

I made a number of css changes (with the help of AI, cos I hate writing CSS!), and I wanted to include them here, but since the objective of this tutorial is Lisp not the nuances of selectors, I will just include the full listing without comments.

src/static/css/main.css

.logo {
    height: 30px;
    width: 30px;
}

.error-404 {
    height: 75vh;
}

form#signup input {
    display: block;  /* Ensure inputs take up the full width */
    width: 100% !important; /* Override any conflicting styles */
    max-width: 100%; /* Ensure no unnecessary constraints */
    box-sizing: border-box;
}

form#signup input[type="email"],
form#signup input[type="text"],
form#signup input[type="password"] {
    @extend .form-control; /* Apply Bootstrap's .form-control */
    display: block; /* Ensure they are block-level elements */
    width: 100%; /* Make the input full width */
    margin-bottom: 1rem; /* Spacing */
}

form#signup select {
    @extend .form-select;
    width: 100%;
}

form#signup input[type="submit"] {
    @extend .btn;
    @extend .btn-primary;
    width: 100%;
}

form#signup div {
    @extend .mb-3;
}

form#signup label {
    @extend .form-label;
    font-weight: bold;
    margin-bottom: 0.5rem;
}

form#login input {
    display: block;  /* Ensure inputs take up the full width */
    width: 100% !important; /* Override any conflicting styles */
    max-width: 100%; /* Ensure no unnecessary constraints */
    box-sizing: border-box;
}

form#login input[type="text"],
form#login input[type="password"] {
    @extend .form-control; /* Apply Bootstrap's .form-control */
    display: block; /* Ensure they are block-level elements */
    width: 100%; /* Make the input full width */
    margin-bottom: 1rem; /* Spacing */
}

form#login input[type="submit"] {
    @extend .btn;
    @extend .btn-primary;
    width: 100%;
}

form#login div {
    @extend .mb-3;
}

form#post div {
    @extend .mb-3;
}

form#post {
    display: flex !important;
    align-items: center !important;
    gap: 0.5rem;
    width: 100% !important;
}

/* Make the input wrapper expand */
form#post > div:first-of-type {
    flex: 1 1 auto !important;
    min-width: 0;  /* allow shrinking */
}

form#post label {
    display: none !important;
}

form#post input[type="text"] {
    flex: 1 1 0% !important;
    width: 100% !important;
    min-width: 0 !important;
    /* Bootstrap .form-control styles */
    display: block;
    padding: 0.375rem 0.75rem;
    font-size: 1rem;
    font-weight: 400;
    line-height: 1.5;
    color: #212529;
    background-color: #fff;
    background-clip: padding-box;
    border: 1px solid #ced4da;
    border-radius: 0.375rem;
    transition: border-color .15s ease-in-out, box-shadow .15s ease-in-out;
}

form#post input[type="submit"] {
    flex: 0 0 auto !important;
    /* Bootstrap .btn + .btn-primary styles */
    display: inline-block;
    font-weight: 400;
    color: #fff;
    text-align: center;
    vertical-align: middle;
    user-select: none;
    background-color: #0d6efd;
    border: 1px solid #0d6efd;
    padding: 0.375rem 0.75rem;
    font-size: 1rem;
    line-height: 1.5;
    border-radius: 0.375rem;
    transition: color .15s ease-in-out, background-color .15s ease-in-out,
                border-color .15s ease-in-out, box-shadow .15s ease-in-out;
    cursor: pointer;
}
form#post input[type="submit"]:hover {
    background-color: #0b5ed7;
    border-color: #0a58ca;
}

/* Post container styling */
.post {
    display: block;                /* Makes the whole card clickable */
    text-decoration: none;         /* Remove underline from link */
    color: inherit;                /* Use normal text color */
    background: #fff;              /* White card background */
    border: 1px solid #dee2e6;     /* Subtle border */
    border-radius: 0.5rem;         /* Rounded corners */
    padding: 1rem;                 /* Inner spacing */
    margin-bottom: 1rem;           /* Space between posts */
    transition: box-shadow 0.2s ease, transform 0.1s ease;
    cursor: pointer;
}

/* Hover/active effect */
.post:hover {
    box-shadow: 0 4px 12px rgba(0,0,0,0.08);
    transform: translateY(-2px);
    text-decoration: none;   /* still no underline on hover */
}

/* Post title/content */
.post-title {
    font-weight: 600;
    font-size: 1.1rem;
    margin-bottom: 0.25rem;
    color: #0d6efd;  /* bootstrap primary link color */
}

/* Post meta info */
.post-meta {
    font-size: 0.875rem;
    color: #6c757d;  /* muted gray */
    margin-top: 0.5rem;
}

Conclusion

Phew! That was another big one, but the good news is that most of the key pieces of building an application with Ningle and Mito are in place, next month we will look at tidying up our project. We are far from done with this tutorial series though, as we will still need to look at hosting our applications, testing, and developing good practices.

Thank you for following this tutorial series, I hope you are finding it as interesting/helpful to read as I am finding it interesting/helpful to write.

Learning Outcomes

Level	Learning Outcome
Remember	Define the purpose of post and likes models in Ningle. Recall the role of SXQL in generating SQL queries.
Understand	Explain how toggle-like manages user interactions with posts. Describe how requirements (e.g., :logged-in-p) simplify route definitions. Interpret SQL queries that use JOIN and GROUP BY to aggregate like counts. Summarize how SXQL represents SQL constructs such as LEFT JOIN, COUNT, and AS. Explain why COUNT(user_likes.id) can be used to represent a boolean “liked by user” column.
Apply	Use cl-forms to create a validated post submission form with CSRF protection. Implement not-logged-in-posts and logged-in-posts to retrieve posts with like counts.
Analyse	Compare the differences between raw SQL and SXQL representations for joins and counts. Distinguish between logged-in and non-logged-in query results.

Github

• The link for this tutorials code is available here.

Resources

Common Lisp HyperSpec

• defpackage
• in-package
• defvar
• defparameter
• defun
• defgeneric
• defmethod
• lambda
• setf
• let
• let*
• if
• when
• unless
• not
• and
• or
• eq
• equal
• format
• multiple-value-bind
• slot-value
• find-symbol
• string-upcase
• symbol-name
• first
• list
• error
• handler-case
• parse-integer
• make-hash-table
• gethash
• dolist
• ignore
• declare