I wrote a simple migration for SQLDelgight. It pas...

# squarelibraries

[Colton Idle]

I wrote a simple migration for SQLDelgight. It passes the migration tests from sqldelight. cool.
but I noticed something (I think) is that since I added a new column ALTER TABLE myDbo ADD COLUMN userId INTEGER NOT NULL; wouldn't that backfire on me if I try to exist tables that have been migrated because I'm expecting a value but there's no default. should the test have caught that?

[Benoit Quenaudon]

exist tables?

Tests usually run on the current schema. Whatever migration ran, your tests are executed after that. Unless you have specific migration versioned tests.

[Colton Idle]

table exists yes. and i also ran the generateMainDbSchema command which generated a new db file.

it just got me thinking. maybe i should be writing my own tests instead to test migrations instead of relying on sqldelight?

[Benoit Quenaudon]

What's there to be tested that SQLD doesn't test?

Unless you wrote some kotlin or specific queries to be ran between migrations, I don't think there's anything to do

[Colton Idle]

Wrote this up. maybe it does a better job at why i was surprised with the behavior. esp as someone that didn't know i should have a default value

https://github.com/cashapp/sqldelight/issues/5258

[ursus]

you can add not null column in migrations? I'd expect that to throw unless a default is provided

[youssef hachicha]

you cant assign Not null while trying to add a column to your existing table

[Colton Idle]

hm. seems like i was able to. and the docs also say it was fine.

[Nicolas Picon]

It will only work if the table is empty. If the original table is not empty, the not null constraint will fail as the column won't have a value.

[Colton Idle]

really!? What if you did a NOT NULL DEFAULT 0;
i should likely do a bunch more testing on this... hm

[Nicolas Picon]

If you provide a default value the existing records will use this value automatically so it will work.

You will also be able to INSERT without specifying the column and it will use the default value. If you want the column value to be always specified in INSERT/UPDATE you should drop the DEFAULT in a separate statement, after the ALTER TABLE