I am trying to write a SqlDelight migration that adds a column to a table. I want to set a default v...

# squarelibraries

[Kirill Zhukov]

I am trying to write a SqlDelight migration that adds a column to a table. I want to set a default value during migration but not on the fresh tables. I’ve tried this so far:

-- someTable.sq 0.sq
CREATE TABLE someTable(
	id TEXT NOT NULL PRIMARY KEY
)

-- someTable.sq 1.sq
CREATE TABLE someTable(
	id TEXT NOT NULL PRIMARY KEY,
	value INT NOT NULL
)

-- 1.sqm
ALTER TABLE someTable ADD COLUMN value INT NOT NULL DEFAULT 42;

This doesn’t pass SqlDelight tests because someTable.sq schema does not define default value, which I don’t want…Is it possible to make ADD COLUMN work here without forcing original schema to define default value, or is my best bet here to do manual table re-creation (create new table with new schema, move values there, delete old table, rename new table)?

[Benoit Quenaudon]

How about creating a tmp table, and doing something like
INSERT INTO new_table FROM (SELECT *, 42 FROM OLD_TABLE)

[Kirill Zhukov]

Yup, that’s exactly my second option that I ended up going with