Migration fails with SQLite3 exception

[joshsharp]

Hello again! I'm unable to run a migration that I just generated, as it fails with an exception. I'm using SQLite.

Here's the relevant table:

class Follow < Marten::Model
    field :id, :big_int, primary_key: true, auto: true
    field :user, :many_to_one, to: Auth::User, related: :follows
    field :person, :many_to_one, to: Person, related: :follows
    field :created, :date_time, auto_now_add: true
    field :cast, :bool, default: true
    field :crew, :bool, default: true

    db_unique_constraint :user_person, field_names: [:user, :person]
end

Here's the migration, you can see I only added the uniqueness constraint:

# Generated by Marten 0.4.0 on 2024-02-23 15:54:46 +11:00

class Migration::Main::V202402231554461 < Marten::Migration
  depends_on :main, "202402231540031_add_birthday_to_main_person_table"

  def plan
    add_unique_constraint :main_follow, :user_person, [:user_id, :person_id]
  end
end

And finally here is the exception:

Running migrations:
  › Applying main_202402231554461_add_user_person_unique_constraint_to_main_follow_table...Unhandled exception: near ",": syntax error (SQLite3::Exception)
  from lib/sqlite3/src/sqlite3/statement.cr:81:5 in 'check'
  from lib/sqlite3/src/sqlite3/statement.cr:4:5 in 'initialize'
  from lib/sqlite3/src/sqlite3/statement.cr:2:3 in 'new'
  from lib/sqlite3/src/sqlite3/connection.cr:63:5 in 'build_prepared_statement'
  from lib/db/src/db/connection.cr:60:42 in 'fetch_or_build_prepared_statement'
  from lib/db/src/db/session_methods.cr:25:16 in 'build'
  from lib/db/src/db/query_methods.cr:275:7 in 'exec'
  from lib/marten/src/marten/db/management/schema_editor/sqlite.cr:260:15 in 'remake_table_with_added_unique_constraint'
  from lib/marten/src/marten/db/management/schema_editor/sqlite.cr:15:13 in 'add_unique_constraint'
  from lib/marten/src/marten/db/migration/operation/add_unique_constraint.cr:36:13 in 'mutate_db_forward'
  from lib/marten/src/marten/db/migration.cr:138:11 in 'apply_forward'
  from lib/marten/src/marten/db/management/migrations/runner.cr:220:25 in 'migrate_forward'
  from lib/marten/src/marten/db/management/migrations/runner.cr:99:15 in 'run_migrations'
  from lib/marten/src/marten/cli/manage/command/migrate.cr:57:15 in 'run'
  from lib/marten/src/marten/cli/manage/command/base.cr:441:13 in 'setup_and_run'
  from lib/marten/src/marten/cli/manage/command/base.cr:106:13 in 'handle!'
  from lib/marten/src/marten/cli/manage.cr:72:9 in 'run'
  from lib/marten/src/marten/cli.cr:24:7 in 'run'
  from lib/marten/src/marten/cli.cr:23:5 in 'run'
  from manage.cr:4:1 in '__crystal_main'
  from /usr/share/crystal/src/crystal/main.cr:129:5 in 'main_user_code'
  from /usr/share/crystal/src/crystal/main.cr:115:7 in 'main'
  from /usr/share/crystal/src/crystal/main.cr:141:3 in 'main'
  from /lib/x86_64-linux-gnu/libc.so.6 in '__libc_start_main'
  from tmp/manage in '_start'
  from ???

It's generating invalid SQL, but I'm unsure what exactly. Let me know if there are other details I can provide.

[ellmetha]

Thanks a lot for reporting this! Indeed there was a possible invalid SQL query generated for SQLite when running queries for recreating the migrated table. This was fixed in 05d00e4! The fix will be part of a patch version that should be released in the next few days.

[joshsharp]

Brilliant, thanks a heap :)