Migration Question and Docs

[daninus14]

Hi,

I was about to write a lot of code for migrations just to realize it seems to all be there already, just not documented.

Question is: once the files are generated how should they be executed?

I can write the docs, I just need a bit of guidance.

This is what I have so far (needs to be edited). Does it look like a good enough approach? If so I can fix the formatting and add it to the docs.

;; Tutorial

;; to initialize the migrations table in the database execute
MITO-MIGRATIONS> (mito:current-migration-version)
;; CREATE TABLE IF NOT EXISTS "schema_migrations" (
;;     "version" BIGINT PRIMARY KEY,
;;     "applied_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
;;     "dirty" BOOLEAN NOT NULL DEFAULT false
;; ) ()  (3318ms) | MITO.MIGRATION.VERSIONS::INITIALIZE-MIGRATIONS-TABLE
;; SELECT "version" FROM "schema_migrations" ORDER BY "version" DESC LIMIT 1 () [0 rows] (652ms) | MITO.MIGRATION.VERSIONS:CURRENT-MIGRATION-VERSION
NIL
MITO-MIGRATIONS> (mito:current-migration-version)
;; SELECT "version" FROM "schema_migrations" ORDER BY "version" DESC LIMIT 1 () [0 rows] (347ms) | MITO.MIGRATION.VERSIONS:CURRENT-MIGRATION-VERSION
NIL

;; this created a table in the database called "schema_migrations"

;; to see all the migration expressions that can be generated
MITO-MIGRATIONS> (mito:all-migration-expressions)
(#<SXQL-STATEMENT: CREATE TABLE migration (
                                           id BIGSERIAL NOT NULL PRIMARY KEY,
                                           migration_filename VARCHAR(120),
                                           success_p BOOLEAN,
                                           date_applied TIMESTAMPTZ,
                                           created_at TIMESTAMPTZ,
                                           updated_at TIMESTAMPTZ
                                           )>
                   #<SXQL-STATEMENT: CREATE UNIQUE INDEX unique_migration_migration_filename ON migration (migration_filename)>)
(#<SXQL-STATEMENT: DROP TABLE migration>)

;; note that these above are multiple returned. They are the "up" statements
;; which are the migrations to be done and the "down" statements which are
;; to undo the migrations
;; if you want to deal with the down statements you can use multiple-value-bind or multiple-value-list

;; to generate the migration files do
MITO-MIGRATIONS> (mito:generate-migrations #P"./")
;; SELECT "version" FROM "schema_migrations" ORDER BY "version" DESC LIMIT 1 () [0 rows] (259ms) | MITO.MIGRATION.VERSIONS:CURRENT-MIGRATION-VERSION
CREATE TABLE "migration" (
                          "id" BIGSERIAL NOT NULL PRIMARY KEY,
                          "migration_filename" VARCHAR(120),
                          "success_p" BOOLEAN,
                          "date_applied" TIMESTAMPTZ,
                          "created_at" TIMESTAMPTZ,
                          "updated_at" TIMESTAMPTZ
                          );
CREATE UNIQUE INDEX "unique_migration_migration_filename" ON "migration" ("migration_filename");
Successfully generated: ./migrations/20250820222637.up.sql

#P"./migrations/20250820222637.up.sql"
#P"./migrations/20250820222637.down.sql"

;; the argument passed to the function is a directory path where the
;; files related to migration will be created.
;; .
;; ├── migrations
;; │   ├── 20250820223115.down.sql
;; │   └── 20250820223115.up.sql
;; └── schema.sql

;; 2 directories, 3 files

;; as you can see a schema.sql file was generated with the *post* migration schema
;; and up and down migration files were generated.
;; the migration files are named based on time.
;; The filename format can be changed with this variable
MITO-MIGRATIONS> mito:*migration-version-format*
:TIME

Question is: once the files are generated: how should they be executed?

I tried using

(mito:migrate #P"./")

But this gave me an error saying:

DB Error: relation "mytable" already exists (Code: 42P07)
   [Condition of type DBI.ERROR:DBI-PROGRAMMING-ERROR]

This seems to be trying to execute the schema.sql file instead of the migration files.

If instead I run:

MITO-MIGRATIONS> (mito:migrate #P"./migrations/")
;; SELECT "version" FROM "schema_migrations" ORDER BY "version" DESC LIMIT 1 () [0 rows] (300ms) | MITO.MIGRATION.VERSIONS:CURRENT-MIGRATION-VERSION
Nothing to migrate.
NIL

So the migration file is not actually executed.

So how do we execute the migration files with mito?

[daninus14]

I will change the example of the table name so that it's not confusing. I was in the process of creating a migration table when I realized it was already implemented, that's why it's the example.

[daninus14]

Just changed it and added a properly formatted PR here: #176

[daninus14]

for a preview of how the readme looks see https://github.com/daninus14/mito/tree/migration-readme?tab=readme-ov-file#managed-migrations

[fukamachi]

Looks pretty good!
I know migration is tricky and not easy to explain at all, but I think it's good enough for the first version.

This is not a request to change anything, but I want to leave a comment.

I'm always asked by people that "generate-migrations" doesn't generate as people intended, but it's not what it's supposed to do. generate-migrations is only for simple cases like adding, deleting tables, columns or indices. People misunderstood it's perfect, but it's like a template, and people can edit the result. Actually, you don't need to use it. It's completely fine to just place an SQL file and call mito:migrate.

[vindarel]

Thanks! TIL.

my 2c: say again that during development, it is possible to not use migrations files, but to rely on ensure-table-exists and migrate-table (and mito:*auto-migration-mode*).

[daninus14]

Hi,

I've tried using mito:migrate multiple times, sometimes providing the directory where the generated schema.sql is, or providing the actual file to be used for the migration, but the DB is not changing.

If I use mito:migrate without :force t I get an error saying that the table cannot be crated because this line executes:

mito/src/migration/versions.lisp

Line 368 in 5d315c8

(execute-sql stmt)))))

with the schema.sql file. Using force makes it run without error. Is that the expected behavior?

Then, the generated migration files are not really executing. I keep checking the DB and the columns which are supposed to be added with the migration files are not added, even though the messages say it worked.

I get these outputs:

CL-USER> (let*
    ((my-app-dir (asdf:system-source-directory "my-app"))
     (db-path (uiop:merge-pathnames* my-app-dir #P"/db"))
     (db-dir (uiop:ensure-directory-pathname db-path)))
  (mito:migrate db-dir :dry-run t :force t))
;; SELECT "version" FROM "schema_migrations" ORDER BY "version" DESC LIMIT 1 () [0 rows] (206ms) | MITO.MIGRATION.VERSIONS:CURRENT-MIGRATION-VERSION
;; INSERT INTO "schema_migrations" ("version") VALUES ($1) (20251215020651) [1 row] (1361ms) | MITO.MIGRATION.VERSIONS:MIGRATE
No problems were found while migration.
0 (0 bits, #x0, #o0, #b0)
CL-USER> (let*
    ((my-app-dir (asdf:system-source-directory "my-app"))
     (db-path (uiop:merge-pathnames* my-app-dir #P"/db"))
     (db-dir (uiop:ensure-directory-pathname db-path)))
  (mito:migrate db-dir :force t))
;; SELECT "version" FROM "schema_migrations" ORDER BY "version" DESC LIMIT 1 () [0 rows] (195ms) | MITO.MIGRATION.VERSIONS:CURRENT-MIGRATION-VERSION
;; INSERT INTO "schema_migrations" ("version") VALUES ($1) (20251215020651) [1 row] (189ms) | MITO.MIGRATION.VERSIONS:MIGRATE
Successfully updated to the version 20251215020651.
NIL
CL-USER> (let*
    ((my-app-dir (asdf:system-source-directory "my-app"))
     (db-path (uiop:merge-pathnames* my-app-dir #P"/db"))
     (db-dir (uiop:ensure-directory-pathname db-path)))
  (mito:migrate db-dir))
;; SELECT "version" FROM "schema_migrations" ORDER BY "version" DESC LIMIT 1 () [1 row] (1175ms) | MITO.MIGRATION.VERSIONS:CURRENT-MIGRATION-VERSION
;; SELECT "version" FROM "schema_migrations" WHERE ("applied_at" IS NOT NULL) ORDER BY "version" () [1 row] (158ms) | MITO.MIGRATION.VERSIONS::%MIGRATION-STATUS
Version 20251215020651 is up to date.
NIL
CL-USER> (let*
    ((my-app-dir (asdf:system-source-directory "my-app"))
     (db-path (uiop:merge-pathnames* my-app-dir #P"/db"))
     (db-dir (uiop:ensure-directory-pathname db-path))
     (migrations-dir (uiop:ensure-directory-pathname (uiop:merge-pathnames* db-dir #P"migrations"))))
  migrations-dir)
#P"/home/my-app/db/migrations/"
CL-USER> (mito:migrate #P"/home/my-app/db/migrations/20251215020651.up.sql")
;; SELECT "version" FROM "schema_migrations" ORDER BY "version" DESC LIMIT 1 () [1 row] (204ms) | MITO.MIGRATION.VERSIONS:CURRENT-MIGRATION-VERSION
;; SELECT "version" FROM "schema_migrations" WHERE ("applied_at" IS NOT NULL) ORDER BY "version" () [1 row] (181ms) | MITO.MIGRATION.VERSIONS::%MIGRATION-STATUS
Version 20251215020651 is up to date.
NIL
CL-USER> 

Any ideas? Am I doing something wrong?

Thanks!

[daninus14]

UPDATE

I finally managed to get this working.

The problems I had were that this does not work well with an existing database which already mostly conforms to the generated schema.

If there is no existing migration, mito:migrate will attempt to apply the schema.sql and not the generated *.up.sql migration file. I don't know why that is. the workaround is to do this:

(unless (mito.migration.versions:current-migration-version))
  (mito.migration.versions:update-migration-version 1))
(mito.migration.versions:generate-migrations db-dir)

That generates the migration files.

To apply the migrations, do the following:

(unless (mito.migration.versions:current-migration-version))
  (mito.migration.versions:update-migration-version 1))

(mito.migration.versions:migrate db-dir)

Note I just added the code to update the migration version in both places in case there is no current migration. This bypasses mito trying to apply the schema instead of the latest up migration file.

I have not actually tried using the code on a db with no tables, but presumably that should work since the application of the schema should cause no issues. I don't know if after applying the schema mito will update the migration version to the latest up file or not.

@fukamachi This was my workaround because of an existing db. Should I add this as part of the docs? Is this how the library is intended to work or is this a bug?