sqlite migration tries to re-create table

[nustiueudinastea]

Hi,

I am getting the following error when running a sqlite migration:

migrate: 20241129130848.sql: table accounts already exists

I am running the migration as follows:

sqddl migrate -verbose -db 'sqlite://test.db' -dir ./migrations

This is the second migration file under the ./migrations directory. The first one applied just fine.

Any idea what might be happening here? I understand that sqlite has limited support for altering schemas but I assume that sqddl does the re-creation of the table by itself.

[bokwoon95]

um, sqddl does recreate the table but only through generate, not migrate. Were the contents of 20241129130848.sql generated by the generate command?

[nustiueudinastea]

Yes, 20241129130848.sql was generated by the generate command. What I noticed is that when I pass -dialect sqlite -prefix test to sqddl, it doesn't do numbered migration files when doing any changes to the models and rerunning generates. It always rewrite the original file (test.sql), and when applying using migrate, it doesn't attempt to make changes based on the current db schema. It always applies all the changes as if it's a new db. For mysql, I always get numbered migration files and sqddl knows how to apply any changes that might not be reflected in the db yet. Is the same pattern not supported for sqlite?

[bokwoon95]

What I noticed is that when I pass -dialect sqlite -prefix test to sqddl, it doesn't do numbered migration files

Oh yeah, the prefix is there to group a batch of files that were generated by sqddl generate together. But all sqlite migrations generated by generate will only ever generate at most one file, so there's no numbering after the prefix and the prefix is essentially the entire filename.

Yes, 20241129130848.sql was generated by the generate command.

Could you show me your before and after models as well as the two migration files that were generated? Thanks.