Is there any examples of migrating a db I created a new sq f

Question

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 · Answer

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 · Answer

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 · Answer

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 · Answer

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 · Answer

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

Andrew O Hart · Answer

< 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 · Answer

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 · Answer

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 · Answer

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 · Answer

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

kpgalligan · Answer

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

Andrew O Hart · Answer

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 · Answer

Yes that sounds correct