DOCSEARCHbyAlgolia
Submit Docs

Mito ORM

repository·master·Indexed 43 days ago

https://github.com/fukamachi/mito

A Common Lisp Object Relational Mapper (ORM) supporting MySQL, PostgreSQL, and SQLite3. Designed as a successor to Integral, it provides an ActiveRecord-like experience with automatic timestamp management, built-in migration support, and high-level DAO operations. Currently in ALPHA status, it supports SBCL and Clozure CL.

Tokens
3.3K
Snippets
11
Records
15
Agent score
6%

What's inside Mito

  1. Overview of Mito ORM

    master

    Mito is an Object Relational Mapper (ORM) designed as a successor to Integral. It provides high-level abstractions for interacting with relational databases, including automatic management of common fields and schema versioning.

    Key features include:

    • Database Support: MySQL, PostgreSQL, and SQLite3.
    • Automatic Fields: Automatically adds id (serial or UUID primary key), created_at, and updated_at fields, similar to Ruby's ActiveRecord.
    • Schema Management: Supports migrations and DB schema versioning.
    • Runtime Support: Works with SBCL and Clozure CL.

  4. Define relationships and accessors

    master

    Mito supports defining relationships between tables using :references or by specifying a class as a :col-type.

    One-to-One / Foreign Key Reference:

    • Use :references (class-name slot-name) to define a foreign key column.
    • Alternatively, set the :col-type to the target class name. This allows you to work with objects directly (e.g., (mito:create-dao 'tweet :user *user-object*)).

    One-to-Many / Many-to-Many (Custom Accessors): Since Mito doesn't automatically generate collection accessors, use the define-accessor macro. This creates a cached accessor that executes the query only on the first call and caches the result to prevent repeated database hits.

    ;; Defining a relationship via class type
(mito:deftable tweet ()
  ((status :col-type :text)
   (user :col-type user))) ; 'user' is the class name

;; Defining a one-to-many accessor (user -> tweets)
(define-accessor user-tweets (user user)
  (mito:select-dao 'tweet
    (sxql:where (:= :user_id (mito:object-id user)))))

;; Usage: (user-tweets *my-user-instance) ; Returns cached list of tweets

  5. Connect to a database with Mito

    master

    Mito uses connect-toplevel to establish a connection to an RDBMS and sets the global *connection* variable. It accepts arguments similar to dbi:connect, such as driver-type, database name, username, and password.

    To use a connection lexically (without setting a global variable), bind mito:*connection* within a let block. For multi-threaded applications, dbi:connect-cached is recommended to reuse connections.

    To connect to an in-memory sqlite3 database, use the special path `#P":memory:".

    ;; Global connection
(mito:connect-toplevel :mysql :database-name "myapp" :username "fukamachi" :password "c0mon-1isp")

;; Lexical connection
(let ((mito:*connection* (dbi:connect :sqlite3 :database-name #P"/tmp/myapp.db")))
  (unwind-protect (progn ...)
    (dbi:disconnect mito:*connection*)))

;; In-memory SQLite
(mito:connect-toplevel :sqlite3 :database-name #P":memory:")

  6. Manage database migrations

    master

    Mito provides tools for schema evolution and migrations.

    In-code Migrations:

    • mito:migration-expressions 'table-name: Returns the sxql expressions required to bring the current database schema in line with the current deftable definition.
    • mito:migrate-table 'table-name: Executes the necessary migration expressions.
    • mito:ensure-table-exists 'table-name: Creates the table if it doesn't exist.
    • Auto-migration: If mito:*auto-migration-mode* is set to t, migrations run automatically after model changes.

    CLI Schema Versioning: Use the mito command-line tool for managing migration files:

    • mito generate-migrations: Creates new migration files.
    • mito migrate: Applies pending migrations.
    • mito migration-status: Checks the status of migrations.

    CLI Flags:

    • -t, --type: Driver type (mysql, postgres, sqlite3).
    • -d, --database: Database name.
    • -u, --username: RDBMS username.
    • -p, --password: RDBMS password.
    • --dry-run: List SQL expressions without executing them.
    # Example CLI usage
mito --database postgres --username fukamachi --password c0mmon-l1sp migrate

  7. Install Mito via Quicklisp or Roswell

    master

    You can install Mito using Quicklisp or Roswell. If you are building a binary, ensure you include the appropriate database driver in your dependencies (e.g., :dbd-sqlite3, :dbd-mysql, or :dbd-postgres).

    (ql:quickload :mito)

    Or with Roswell:

    ros install mito

  8. Run Mito tests using Docker Compose

    master

    You can run the Mito test suite using the provided docker-compose.yml. The tests service builds a container from docker/Dockerfile and depends on both postgres and mysql services.

    To customize the Lisp implementation used during the build, you can pass the LISP build argument (defaults to sbcl).

    Environment Variables for Tests:

    • POSTGRES_HOST: mito-test-postgres
    • POSTGRES_USER: mito
    • POSTGRES_PASS: mito
    • MYSQL_HOST: mito-test-mysql
    • MYSQL_USER: root
    • MYSQL_PASS: mito
    # Example: Building tests with a specific Lisp implementation
docker compose build --build-arg LISP=sbcl tests

# Run the tests
docker compose up tests

  10. Define table classes with deftable

    master

    The mito:deftable macro is a thin wrapper around defclass used to define table classes with the mito:dao-table-class metaclass. It reduces boilerplate by automatically adding default accessors (prefixed with the class name) and handling column definitions.

    Key Options:

    • :conc-name: Sets the prefix for accessors. If set to NIL, no default accessors are defined.
    • :primary-key: Defines the primary key.
    • :unique-keys: Defines unique constraints.
    • :table-name: Specifies the actual database table name.
    • :auto-pk: Specifies the auto-increment/UUID mixin (e.g., :serial, :uuid). Set to nil to disable automatic id generation.
    • :record-timestamps: Enables automatic created_at and updated_at columns. Set to nil to disable.
    ;; Basic definition with default accessors (e.g., user-name)
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (or (:varchar 128) :null))))

;; Custom accessor prefix
(mito:deftable user ()
  ((name :col-type (:varchar 64)))
  (:conc-name my-))

;; Accessing value: (my-name (make-instance 'user :name "fukamachi"))

  11. Perform CRUD operations

    master

    Mito provides high-level functions for standard database operations on DAO instances:

    • mito:create-dao: Creates and inserts a new record.
    • mito:insert-dao: Inserts an existing instance.
    • mito:find-dao: Retrieves a single record by its primary key.
    • mito:retrieve-dao: Retrieves all records from a table.
    • mito:save-dao: Updates an existing record in the database.
    • mito:delete-dao: Deletes a specific record.
    • mito:delete-by-values: Deletes records matching specific criteria.
    • mito:count-dao: Returns the total count of records in a table.
    ;; Create and Insert
(defvar me (make-instance 'user :name "Eitaro" :email "e@example.com"))
(mito:insert-dao me)

;; Retrieve by ID
(mito:find-dao 'user :id 1)

;; Update
(setf (slot-value me 'name) "new_name")
(mito:save-dao me)

;; Delete
(mito:delete-dao me)

  12. Execute custom queries with select-dao and retrieve-by-sql

    master

    Mito allows for complex queries using sxql syntax. There are three main levels of abstraction:

    1. mito:select-dao (Highest level): Returns a list of DAO instances. This is the most common API for application logic.
    2. mito:select-by-sql: Returns a list of DAO instances by mapping results through make-dao-instance.
    3. mito:retrieve-by-sql (Lowest level): Returns a list of plists (property lists) representing the raw database rows.

    Eager Loading with includes: To avoid the N+1 query problem, use includes to load related objects in a single WHERE IN query instead of multiple individual queries.

    ;; High-level selection with eager loading
(mito:select-dao 'tweet
  (includes 'user)
  (where (:like :status "%Japan%")))

;; Low-level raw data retrieval
(mito:retrieve-by-sql
  (select (:user.*) (from :users)))