Kemal Database

Kemal Database Integration

This skill provides expert guidance on integrating SQLite databases with Kemal using the db and sqlite3 shards, strictly following patterns from kemal-by-example.

Core Mandates

• Dependencies: Always require "db" and require "sqlite3" in the main application file. Add them to shard.yml.

• Connection: Centralize the database connection in a config/database.cr file. Use environment variables for the URL:

  module MyProject
    module Database
      extend self
      DATABASE_URL = ENV["DATABASE_URL"]? || "sqlite3:./db/database.db"
  
      @@mutex = Mutex.new
      @@connection : DB::Database? = nil
  
      def connection : DB::Database
        @@connection || @@mutex.synchronize { @@connection ||= DB.open(DATABASE_URL) }
      end
    end
  end
  

• Schema Setup: Use a Schema.setup method to run CREATE TABLE IF NOT EXISTS statements:

  module MyProject
    module Schema
      extend self
  
      def setup
        Database.connection.exec <<-SQL
          CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            created_at TEXT NOT NULL
          )
        SQL
      end
    end
  end
  

• Model Mapping: Use include DB::Serializable in models for automatic mapping from SQL results:

  class Item
    include DB::Serializable
    property id : Int64?
    property name : String
    property created_at : String
  end
  

• Data Retrieval: Use query_all and query_one? with explicit column lists and the as: Class argument:

  • Database.connection.query_all("SELECT id, name, created_at FROM items", as: Item)
  • Database.connection.query_one?("SELECT id, name, created_at FROM items WHERE id = ?", id, as: Item)

Patterns from Source Code

Database Module Pattern (All Projects)

# config/database.cr
module Blog
  module Database
    extend self

    DATABASE_URL = ENV["DATABASE_URL"]? || "sqlite3:./db/blog.db"

    @@mutex = Mutex.new
    @@connection : DB::Database? = nil

    def connection : DB::Database
      @@connection || @@mutex.synchronize { @@connection ||= DB.open(DATABASE_URL) }
    end
  end
end

Schema Setup with Multiple Tables

# config/schema.cr
module Ecommerce
  module Schema
    extend self

    def setup
      Database.connection.exec <<-SQL
        CREATE TABLE IF NOT EXISTS users (
          id INTEGER PRIMARY KEY,
          name TEXT NOT NULL,
          email TEXT NOT NULL UNIQUE,
          password_hash TEXT NOT NULL,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL
        )
      SQL

      Database.connection.exec <<-SQL
        CREATE TABLE IF NOT EXISTS products (
          id INTEGER PRIMARY KEY,
          name TEXT NOT NULL,
          description TEXT NOT NULL,
          price_cents INTEGER NOT NULL,
          inventory_count INTEGER NOT NULL DEFAULT 0,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL
        )
      SQL
    end
  end
end

Model with DB::Serializable

# models/post.cr
class Post
  include DB::Serializable

  getter id : Int64?
  getter title : String
  getter body : String
  getter created_at : String
  getter updated_at : String

  def initialize(
    @title : String,
    @body : String,
    @id : Int64? = nil,
    @created_at : String = Time.utc.to_s,
    @updated_at : String = Time.utc.to_s,
  )
  end

  def self.all : Array(Post)
    Blog::Database.connection.query_all(
      "SELECT id, title, body, created_at, updated_at FROM posts ORDER BY created_at DESC",
      as: Post
    )
  end

  def self.find(id : Int64) : Post?
    Blog::Database.connection.query_one?(
      "SELECT id, title, body, created_at, updated_at FROM posts WHERE id = ?",
      id,
      as: Post
    )
  end

  def self.create(title : String, body : String) : Post
    now = Time.utc.to_s
    # `Database.connection` is a pooled `DB::Database`: a separate
    # `SELECT last_insert_rowid()` may run on a different connection and return
    # the wrong id. Read the id from the `DB::ExecResult` of the INSERT itself.
    result = Blog::Database.connection.exec(
      "INSERT INTO posts (title, body, created_at, updated_at) VALUES (?, ?, ?, ?)",
      title,
      body,
      now,
      now
    )
    id = result.last_insert_id
    find(id) || raise "Failed to load post ##{id} after creation"
  end

  def update(title : String, body : String)
    Blog::Database.connection.exec(
      "UPDATE posts SET title = ?, body = ?, updated_at = ? WHERE id = ?",
      title,
      body,
      Time.utc.to_s,
      @id
    )
  end

  def delete
    Blog::Database.connection.exec(
      "DELETE FROM posts WHERE id = ?",
      @id
    )
  end
end

Initialization in Main File

# blog.cr
require "kemal"
require "db"
require "sqlite3"

require "./config/database"
require "./config/schema"
require "./models/post"
require "./routes/home"
require "./routes/posts"

Blog::Schema.setup
Kemal.run

Best Practices

• Initialization: Call Schema.setup before Kemal.run in the main application file.
• Timestamps: Always use Time.utc.to_s for storing created_at and updated_at as TEXT in SQLite.
• ID Handling: Use Int64? for IDs to accommodate auto-incrementing fields during creation.
• Transactions: Use Database.connection.transaction do |tx| ... end for atomic operations.
• SQL Execution: Use Database.connection.exec("SQL", args) for insert, update, and delete operations.
• Explicit Column Lists: List all columns explicitly in SELECT statements rather than using SELECT *.

When to Use

• When setting up a new database or modifying an existing schema.
• When implementing model methods that interact with the database.
• When configuring database connections and initialization logic.