Database

Database Interaction

For database connections, we use the hikari-cp/hikari-cp library, which provides a fast and efficient connection pool. We leverage com.github.seancorfield/next.jdbc for database interactions and com.github.seancorfield/honeysql for building SQL queries. HoneySQL offers a particularly elegant and Clojure-idiomatic way to construct SQL queries.

All database components are defined in the src/myproject/db.clj file.

The project provides two primary functions for database operations:

• exec! - Executes a SQL query and returns a vector containing all result rows.
• exec-one! - Executes a SQL query and returns a map with the first result only.

Both functions take the database component as their first argument and the SQL query as the second argument. They are pre-configured to return result maps with unqualified keys in lower kebab case for consistent data handling.

Migrations

For database schema migrations, we use the dev.weavejester/ragtime library, which provides a simple and effective approach to managing database schema changes in Clojure.

Migrations run automatically at system startup as part of the database component initialization process. All migration files are stored in the resources/migrations directory.

Migration files follow a numbered naming convention, such as 0001.up.sql. When you need to add a new migration, simply create a new file with the next sequential number. The system executes migration files in numerical order, ensuring a consistent and predictable database evolution.

SQLite

For improving SQLite performance in production there is a default migration that sets the PRAGMA journal_mode to WAL (Write-Ahead Logging) and PRAGMA synchronous to NORMAL. This configuration enhances performance by allowing concurrent reads and writes, while still maintaining a reasonable level of durability.

More info read at: https://til.simonwillison.net/sqlite/enabling-wal-mode

Production

In production the database will be created in the db directory in the root of your project. This directory is mounted as a volume in the Docker container, ensuring that the database files persist even if the container is stopped or removed.