i'm having trouble figuring out whats going on with my migrations for a jvm project using sqldelight

# squarelibraries

[Josh Feinberg]

i'm having trouble figuring out whats going on with my migrations for a jvm project using sqldelight

[Josh Feinberg]

i have a table defined in my Main.sq file

CREATE TABLE holding (
    accountId TEXT NOT NULL,
    securityId TEXT NOT NULL,
    costBasis REAL NOT NULL
)

i then created a migration 1.sqm file that has

ALTER TABLE holding ADD COLUMN timestamp INTEGER AS Instant NOT NULL DEFAULT 0;

however i get a compile error

[Josh Feinberg]

if i have deriveSchemaFromMigrations set to true, i get an error in the .sq that "Table already defined with name holding"

[Josh Feinberg]

if i have it set to false then i get an error in the .sqm that "No table found with name holding"

[hfhbd]

You should either move all table definitions in you sq files or use deriveSchemaFromMigrations and declare all tables in sqm files only, eg 0.sqm for initial schema.

[Rafs]

I'm getting a similar error as well. I'm just trying to use the sample table but I get the no such table error when I try to execute the selectAll query.

[dhia chemingui]

I have same problem Too who can help us Or who find a solution

[dhia chemingui]

@Josh Feinberg do u fix the problem

[Josh Feinberg]

@hfhbd all table definitions are in my sq files but i'm trying to build a migration to alter that table. i'm also adding the column timestamp to the sq file but i still get those errors. will try to move all table definitions to 0.sqm but not sure if thats a great fix

[dhia chemingui]

@hfhbd why I should add all table definitions in the file migrations . I will do that in each migration ??

[Rafs]

I fixed it by adding the table definitions to 1.sqm and adding the other queries into my .sq file

[Josh Feinberg]

if you add the table definitions to your migration file doesn't it try to recreate them when you run the migration?

[Rafs]

IF NOT EXISTS will skip table creation if it exists

[dhia chemingui]

yes me too but I dont wanna add this in each migration and its rejected in code review

[Rafs]

You don't have to add it in every migration. Only the first migration 1.sqm

[dhia chemingui]

emm okay will see thnx

[dhia chemingui]

I have a question it is logic to add the a column in the new migration and add this colum to the creation query hhh .

[hfhbd]

The schema files (sqm) are evaluated before the query files because it is needed to evaluate the queries. Otherwise the queries are not resolved correctly.
You don't need to add IF NOT EXISTS, you should just conditionally run the schema.create function (or use pragma version).

[Josh Feinberg]

the sample app doesn't seem to do this to handle migrations (https://github.com/cashapp/sqldelight/tree/master/sample/common/src/commonMain/sqldelight). it does look like the sample app hasn't been updated to 2.0.0 though so was this a breaking change? the docs don't seem to say anything about this changing (https://cashapp.github.io/sqldelight/2.0.0/jvm_sqlite/migrations/)

[hfhbd]

Well the migration of the sample does not do any useful things, so yeah, we should update it.

[Josh Feinberg]

yeah, looks like the docs then should be updated as well. the table hockeyPlayer in the docs in defined in Player.sq then the alter function is defined in 1.sqm which is exactly what i'm trying to do so it seems like you are saying that is wrong?