Allow modifying columns with ALTER TABLE

[psarna]

Suggested in https://github.com/libsql/libsql/discussions/41 and our Discord channel.

We should consider allowing to just alter existing columns. While it's possible in SQLite: https://stackoverflow.com/a/31140916/11876830, it's non-idiomatic and feels hacky. Implementing a full-fledged ALTER TABLE statement could make libSQL more dev friendly.

[Abdur-rahmaanJ]

Another description:

The SQLite database presents a challenge to migration tools in that it has almost no support for the ALTER statement which relational schema migrations rely upon. The rationale for this stems from philosophical and architectural concerns within SQLite, and they are unlikely to be changed.

Migration tools are instead expected to produce copies of SQLite tables that correspond to the new structure, transfer the data from the existing table to the new one, then drop the old table. For our purposes here we’ll call this “move and copy” workflow, and in order to accommodate it in a way that is reasonably predictable, while also remaining compatible with other databases, Alembic provides the batch operations context.

From https://alembic.sqlalchemy.org/en/latest/batch.html

[MarinPostma]

I'm interested in looking into that 👀

[penberg]

The following already work just fine in SQLite:

CREATE TABLE users (email TEXT);

ALTER TABLE users ADD COLUMN name TEXT;

ALTER TABLE users ADD COLUMN admin TEXT WITH false;

ALTER TABLE users DROP COLUMN admin;

ALTER TABLE users RENAME COLUMN name TO full_name;

The following are not (using PostgreSQL syntax here):

sqlite> ALTER TABLE users ALTER COLUMN email SET NOT NULL;
Parse error: near "ALTER": syntax error
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
^--- error here

sqlite> ALTER TABLE users ALTER COLUMN admin TYPE bool;
Parse error: near "ALTER": syntax error
ALTER TABLE users ALTER COLUMN admin TYPE bool;
^--- error here

sqlite> ALTER TABLE users ALTER COLUMN email DROP NOT NULL;
Parse error: near "ALTER": syntax error
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;
^--- error here

sqlite> ALTER TABLE users ADD PRIMARY KEY (email);
Parse error: near "PRIMARY": syntax error
ALTER TABLE users ADD PRIMARY KEY (email);
^--- error here

[penberg]

Altering column type can be done with

sqlite> ALTER TABLE users ADD COLUMN admin_new BOOL;
sqlite> UPDATE users SET admin_new = CAST(admin AS BOOL);
sqlite> ALTER TABLE users DROP COLUMN admin;
sqlite> ALTER TABLE users RENAME COLUMN admin_new TO admin;

We might be able to make

ALTER TABLE users ALTER COLUMN admin TYPE bool;

be syntactic sugar over that sequence of statements.

[penberg]

We can likely use the same trick for ALTER TABLE DROP NOT NULL:

sqlite> CREATE TABLE users (email TEXT NOT NULL);
sqlite> ALTER TABLE users ADD COLUMN email_new TEXT;
sqlite> UPDATE users SET email_new = email;
sqlite> ALTER TABLE users DROP COLUMN email;
sqlite> ALTER TABLE users RENAME COLUMN email_new TO email;

The SET NOT NULL is bit more tricky because you need a default value, but also perhaps doable:

sqlite> CREATE TABLE users (email TEXT, name TEXT);
sqlite> INSERT INTO users VALUES (NULL, 'Pekka Enberg');
sqlite> ALTER TABLE users ADD COLUMN email_new TEXT DEFAULT 'foo@bar' NOT NULL;
sqlite> UPDATE users SET email_new = email WHERE email IS NOT NULL;
sqlite> ALTER TABLE users DROP COLUMN email;
sqlite> ALTER TABLE users RENAME COLUMN email_new TO email;

[psarna]

Yeah, sounds doable. We need to mind all kinds of ugly corner cases, like:

• DROP COLUMN won't work on a primary key column
• the column might have an index on it, or a foreign constraint
• the new temporary column name must be unique and not subject to races
• and so on, and so forth

[penberg]

Foreign keys are also a pain according to Andrew Sherman from the Drizzle team. Basically, what you'd want is the ability to add and remove foreign keys dynamically:

penberg@vonneumann tmp % sqlite3
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users (id INT, name TEXT);
sqlite> CREATE TABLE emails (user_id INT, email TEXT);
sqlite> ALTER TABLE emails ADD CONSTRAINT fk_emails_users FOREIGN KEY (user_id) REFERENCES users (id);
Parse error: near "CONSTRAINT": syntax error
  ALTER TABLE emails ADD CONSTRAINT fk_emails_users FOREIGN KEY (user_id) REFERE
                         ^--- error here
sqlite> ALTER TABLE emails DROP CONSTRAINT fk_emails_users;
Parse error: near "CONSTRAINT": syntax error
  ALTER TABLE emails DROP CONSTRAINT fk_emails_users;
                          ^--- error here

Right now you can only do that at table creation time.

[glommer]

Another common complaint is that SQLite only allows one column to be altered at a time.