Anyone got "DB error near )" when running migrate-table?

[vindarel]

This isn't the first time, I think it started occurring since the QL 2026-01 upgrade, not sure…

I work on an existing app with an existing DB fine, I add a model field, I want to run the migrations and get:

DB Error: near ")": syntax error (Code: ERROR)

;; PRAGMA table_info("book") () [8 rows] (109ms) | MITO.DB.SQLITE3:COLUMN-DEFINITIONS
;; SELECT 1 FROM sqlite_master WHERE tbl_name = 'book' AND sql LIKE '%AUTOINCREMENT%' () [1 row] (58ms) | MITO.DB.SQLITE3:COLUMN-DEFINITIONS
;; PRAGMA table_info("book") () [8 rows] (58ms) | MITO.DB.SQLITE3::TABLE-PRIMARY-KEYS
;; PRAGMA index_list("book") () [0 rows] (11ms) | MITO.MIGRATION.TABLE::MIGRATION-EXPRESSIONS-AUX
;; ALTER TABLE "book" RENAME TO "book1006" () [0 rows] (930ms) | MITO.MIGRATION.TABLE:MIGRATE-TABLE
;; ALTER TABLE "book" RENAME TO "book1006" () [0 rows] (930ms) | MITO.MIGRATION.TABLE:MIGRATE-TABLE
;; CREATE TABLE "book" (
;;     "id" INTEGER PRIMARY KEY AUTOINCREMENT,
;;     "title" VARCHAR(128) NOT NULL,
;;     "shelf_id" INTEGER,
;;     "tags_id" INTEGER,
;;     "cover_url" VARCHAR(1024),
;;     "publication_date" TIMESTAMP,
;;     "review" TEXT,
;;     "created_at" TIMESTAMP,
;;     "updated_at" TIMESTAMP
;; ) () [0 rows] (156ms) | MITO.MIGRATION.TABLE:MIGRATE-TABLE
;; CREATE TABLE "book" (
;;     "id" INTEGER PRIMARY KEY AUTOINCREMENT,
;;     "title" VARCHAR(128) NOT NULL,
;;     "shelf_id" INTEGER,
;;     "tags_id" INTEGER,
;;     "cover_url" VARCHAR(1024),
;;     "publication_date" TIMESTAMP,
;;     "review" TEXT,
;;     "created_at" TIMESTAMP,
;;     "updated_at" TIMESTAMP
;; ) () [0 rows] (156ms) | MITO.MIGRATION.TABLE:MIGRATE-TABLE

My new field:

   (publication-date
    :accessor publication-date
    :initarg :publication-date
    :initform nil
    :type (or null number)
    :col-type (or :null :timestamp))

WDYT?

might be similar to #186

---

migrate-all:

(defun migrate-all ()
  "Migrate the tables after we changed the class definitions."
  ;; We'd rather use a "with-connexion" style to drop the connection afterwards.
  ;; Use mito:*auto-migration-mode* to nil to delete migration intermediate tables used with sqlite3.
  (unless mito:*connection*
    (error "Please connect to the DB."))
  (ensure-tables-exist)
  (mapcar #'mito:migrate-table (mito-admin::tables)))

On Quicklisp 2026-01.

[vindarel]

more SQL:

(mito:migration-expressions 'book)
(#<SXQL-STATEMENT: ALTER TABLE book RENAME TO book1008>
 #<SXQL-STATEMENT: CREATE TABLE book (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(128) NOT NULL,
    shelf_id INTEGER,
    tags_id INTEGER,
    cover_url VARCHAR(1024),
    publication_date TIMESTAMP,
    review TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
)>
 #<SXQL-STATEMENT: INSERT INTO book (cover_url, created_at, id, review, shelf_id, tags_id, title, updated_at, publication_date) SELECT cover_url, created_at, id, review, shelf_id, tags_id, title, updated_at, () FROM book1008>
 #<SXQL-STATEMENT: DROP TABLE book1008>)

in the last one, there's a dubious ():

INSERT INTO book (cover_url, created_at, id, review, shelf_id, tags_id, title, updated_at, publication_date) SELECT cover_url, created_at, id, review, shelf_id, tags_id, title, updated_at, () FROM book1008

--
Shouldn't it be a null instead of () ?

[fukamachi]

Confirmed the bug. Here's what's going on.

Root cause

The bug is in migration-expressions-between-for-sqlite3 in src/migration/table.lisp (lines 119-127). When you add a new column that has an :initform, the SQLite migration builds an INSERT INTO ... SELECT to copy rows from the old table into the new one, plugging in a default value for the new column.

Here's what happens with your column:

1. :initform nil creates an initfunction, so (c2mop:slot-definition-initfunction slot) is non-nil and the first cond branch fires
2. Calling the initfunction returns nil
3. dao-table-column-deflate and convert-for-driver-type both pass nil through unchanged
4. That nil gets stored as (cons "publication_date" nil), which is just ("publication_date")
5. (cdr ("publication_date")) pulls out nil as the SELECT field value
6. sxql renders Lisp nil as () in the SQL output

So you end up with:

INSERT INTO book (..., publication_date)
  SELECT ..., () FROM book303
--           ^^^ should be NULL

Reproduction

(ql:quickload :mito :silent t)

(defclass book ()
  ((title :col-type (:varchar 128)
          :initarg :title))
  (:metaclass mito:dao-table-class))

;; Create a temporary SQLite DB and table
(mito:connect-toplevel :sqlite3 :database-name "/tmp/repro-194.db")
(mapc #'mito:execute-sql (mito:table-definition 'book))

;; Add a nullable column with :initform nil
(defclass book ()
  ((title :col-type (:varchar 128)
          :initarg :title)
   (publication-date :col-type (or :null :timestamp)
                     :initarg :publication-date
                     :initform nil))
  (:metaclass mito:dao-table-class))

;; Shows the bad SQL with ()
(mito:migration-expressions 'book)

;; Fails with: DB Error: near ")": syntax error
(mito:migrate-table 'book)

(mito:disconnect-toplevel)
(delete-file "/tmp/repro-194.db")

Fix

In the first cond branch (lines 119-127), when the converted value is nil, don't include the column in new-columns-have-default. The column won't appear in the INSERT INTO ... SELECT, and SQLite fills nullable columns with NULL on its own.

;; current code
((c2mop:slot-definition-initfunction slot)
 (list
  (cons (car new-column)
        (convert-for-driver-type ...))))

;; fix
((c2mop:slot-definition-initfunction slot)
 (let ((value (convert-for-driver-type
               :sqlite3
               (table-column-type slot)
               (dao-table-column-deflate
                slot
                (funcall (c2mop:slot-definition-initfunction slot))))))
   (when value
     (list (cons (car new-column) value)))))

This only affects the SQLite3 migration path. The non-SQLite path (line 193) already gets this right since it only sets a default when (getf (cdr column) :not-null) is true.

[vindarel]

Thank you M. Fukamachi (and your verbose LLM friend if you have one). Sent a PR.

[vindarel]

The fix is merged. Thank you.