DEV Community

fumi
fumi

Posted on

bisql (Clojure Data Access Library) released v0.4.0: Support Malli Validation

#clojure #sql #database #rdb

Bisql

I'm building Bisql, a data access library for 2-way SQL in Clojure.

https://github.com/hatappo/bisql

Since it's “2-way,” I called it bisql with the bi- prefix.

It is pronounced like bicycle.

A common weakness of SQL-template libraries, not just 2-way SQL libraries, is that writing all SQL as templates can become tedious.

To address that, many SQL-template libraries support a query builder.

You can write some queries as templates and others with a builder.

But I think that approach is fundamentally inconsistent.

If every query function is maintained as SQL or SQL templates, the cost of reviewing and understanding the data access layer drops significantly. Every database access has a concrete representation as an actual SQL file.

Once a query builder gets mixed in, that consistency is gone.

And in practice, what starts as “we’ll only use the builder for simple queries” often drifts into using it for complex queries as well, until the generated SQL is no longer obviously what you intended.

Bisql takes a different approach:

every database access should be written as SQL.

That said, hand-writing even simple CRUD operations for every table is tedious. So Bisql takes another approach there: it generates a large and comprehensive set of typical CRUD queries automatically.

It connects to a real database, inspects the schema, considers indexes, and generates SQL templates for many index-friendly query patterns.

Then the defquery macro converts all of those .sql template files into Clojure functions at once.

So you keep the consistency of SQL-first development without the repetitive CRUD work.

Malli Support

In this release, generated SQL templates can now include :malli/in and :malli/out declaration metadata.

These hold schemas for:

  • the parameters passed to a query function
  • the response data returned from the query

Bisql SQL templates can already carry arbitrary metadata that becomes metadata on the generated query functions. Malli support builds on that.

If a query function has :malli/in and :malli/out metadata, Bisql can automatically run Malli validation during query execution. This behavior is configurable.

Bisql also generates a base Malli schema file for each table as schema.clj.

The :malli/in and :malli/out metadata refer to those generated schemas.

Example of a generated query 

/*:name crud.get-by-id */
/*:cardinality :one */
/*:malli/in [:map {:closed true} [:id int?]] */
/*:malli/out [:maybe sql.postgresql.public.users.schema/row] */
SELECT *
FROM users
WHERE id = /*$id*/1
Enter fullscreen mode Exit fullscreen mode

Example of a generated schema 

(ns sql.postgresql.public.users.schema
  (:refer-clojure :exclude [update])
  (:require [bisql.schema :as bisql.schema]))

#_{:clojure-lsp/ignore [:clojure-lsp/unused-public-var]}
(def insert
  [:map
   {:closed true}
   [:id [:or int? bisql.schema/malli-default-sentinel]]
   [:email string?]
   [:display-name string?]
   [:status [:or string? bisql.schema/malli-default-sentinel]]
   [:created-at [:or [:fn bisql.schema/offset-date-time?] bisql.schema/malli-default-sentinel]]])

#_{:clojure-lsp/ignore [:clojure-lsp/unused-public-var]}
(def update
  (bisql.schema/malli-map-all-entries-optional insert))

#_{:clojure-lsp/ignore [:clojure-lsp/unused-public-var]}
(def row
  (bisql.schema/malli-map-all-entries-strip-default-sentinel insert))
Enter fullscreen mode Exit fullscreen mode

In other words, typical CRUD queries and their schemas can now be generated automatically, and validation can run transparently with very little manual work.

A Small Expression Language for if

This release also adds a small expression language for if conditions inside SQL templates.

That makes conditional rendering more expressive without leaving SQL templates or introducing a separate query builder layer.

Links

Top comments (0)