It’s pretty common to need to add a new field or two in a SQL database. Doing an update is pretty simple – just follow the steps below.
First, this is our example current database:
CREATE TABLE ListItem (
itemId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
listId INTEGER NOT NULL,
itemText TEXT NOT NULL,
FOREIGN KEY (listId) REFERENCES ListTitle(listId) ON DELETE CASCADE
);
Let’s say we want to add fields for a note and an attachment. The first step is to add the fields you want to the code above, like so:
CREATE TABLE ListItem (
itemId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
listId INTEGER NOT NULL,
itemText TEXT NOT NULL,
note TEXT NOT NULL DEFAULT '',
attachment TEXT,
FOREIGN KEY (listId) REFERENCES ListTitle(listId) ON DELETE CASCADE
);
If you try to run this or access the new variables, it won’t work. You need to make a migration file and add the new fields to this file.
Navigate to your SQLDelight ‘database’ folder (or wherever you have your .sq files). Right click, and then press New > File

In the dialog that shows up, type ‘2.sqm.’ For all subsequent updates, you’ll simply increment the value used as your filename. (I’ve left all of my update files visible to demonstrate how this works, as you’ll very likely need to do many more updates in the future. For your first update, you should name the file ‘2.sqm’)

In your new migration file, add the lines you added to your table declaration, with the addition of ‘ALTER TABLE’ before each of them:
ALTER TABLE ListItem ADD COLUMN note TEXT NOT NULL DEFAULT '';
ALTER TABLE ListItem ADD COLUMN attachment TEXT;
If you try to access these variables in your code, it still won’t work. You need to do a Build > Clean Project first, and then you should click Sync Project with Gradle Files.

After running ‘Clean Project,’ your project will be full of errors, but don’t worry – you need to run a build first. You can either press ‘Rebuild Project’ or simply run the project as normal. After that, all the errors should disappear.
You should now be able to access the new fields you added in your code.
Leave a Reply