Is there any examples of migrating a db? I created...

# touchlab-tools

[Andrew O Hart]

Is there any examples of migrating a db? I created a new .sq file that has a create table and it crashes when i run on top of our prod build because its saying the table isnt created

[russhwolf]

I don't think we have a public migration sample anywhere. If all you've done is add a new .sq file, you probably also need a .sqm file with the changes. See https://cashapp.github.io/sqldelight/jvm_sqlite/migrations/

[russhwolf]

There might also be a migration sample in the sqldelight repository but I haven't looked at their. samples in a while

[Andrew O Hart]

Thanks Russell.
My new .sq file is LastModificationDate that contains only queries, insert, get, remove, no CREATE TABLE

In 1.sqm I added CREATE TABLE LastModificationDate(lastModificationDate TEXT NOT NULL);

This seems to work. However, in LastModificationDate.sq, everywhere I reference LastModificationDate table in the queries, its red and says
"No table found with name LastModificationDate"

The migration seems to work fine but not sure why its red

[kpgalligan]

You need to have a create table statement for all of your tables. The migration only gets run if somebody has the old db. New installs just run create statements. Also, sqldelignt only generates it's db access code for create statements.

[kpgalligan]

Basically, migration is ignored in all cases except where a user has an old db version

[Andrew O Hart]

@kpgalligan cheers for the reply.
When you say create table statement for all tables, do you mean literally all existing tables or only new ones?
I tried putting all my create table statements from my old .sq files into the .scm file and it seemed to crash when migration happened, i think it said something about the table already existing.

If it is only for the new table, then that seems to work fine. I added just create table to .scm for the new table i added, and it seems like migration happens fine as i had stuff in the old db which was still there along with the new table

[kpgalligan]

In .sq files, if you have 5 tables that should be in your db, you need 5 create statements. One for each table. If somebody installed your app, brand new, right now, and you needed those 5 tables, they all need create statements in .sq files. For a brand new database, migration is never run. I think that’s the critical part you need to visualize. If you had 2 migrations from when you first started developing your app, your db version would be “3”. When a new user installs your app, they don’t go through versions 1 and 2. The new db has the create statements run, and the version is just set to 3.

[kpgalligan]

Basically, imagine you said “forget the old users” and decided to delete your migrations. What would the .sq files look like? Whatever that is, that is exactly what they should look like with migrations.

[kpgalligan]

The migration’s job is, say, a user had version 2 installed. The migration needs to get them to version 3, because sqldelight won’t run the create statements on an existing db.

[kpgalligan]

Having said all that, I’ve never tried this, but there is a flag to use migrations instead: deriveSchemaFromMigrations

[kpgalligan]

But, again, I haven’t used it and it doesn’t seem like the most common approach.

[Andrew O Hart]

Hey Kevin, thanks for the detailed reply.
That sounds about right to how I am imagining it.
Say I have .sq files A.sq, B.sq and c.sq, each have their on CREATE TABLE.
In initial DB, they all exist and have stored stuff.
In new version of app I add d.sq which will have its own CREATE TABLE.
Then the .sqm file should only contain the CREATE TABLE for d.sq right, as that is the only new addition, the others haven't been modified in anyway.
Any new users will get all of the .sq files (a-d), and any migrating users just get the CREATE TABLE call for d.sq performed

[kpgalligan]

Yes, that sounds correct