Executing advanced ALTER TABLE operations in SQLite

39
[favorite_button]
Executing advanced ALTER TABLE operations in SQLite
Advertisements

SQLite’s ALTER TABLE has some significant limitations: it can’t drop columns, it can’t alter NOT NULL status, it can’t change column types. Since I spend a lot of time with SQLite these days I’ve written some code to fix this—both from Python and as a command-line utility.

To SQLite’s credit, not only are these limitations well explained in the documentation but the explanation is accompanied by a detailed description of the recommended workaround. The short version looks something like this:

Start a transaction
Create a new temporary table with the exact shape you would like
Copy all of your old data across using INSERT INTO temp_table SELECT FROM old_table
Drop the old table
Rename the temp table to the old table
Commit the transaction

My sqlite-utils tool and Python library aims to make working with SQLite as convenient as possible. So I set out to build a utility method for performing this kind of large scale table transformation. I’ve called it table.transform(…).

Advertisements

Here are some simple examples of what it can do, lifted from the documentation:

# Convert the ‘age’ column to an integer, and ‘weight’ to a float
table.transform(types={“age”: int, “weight”: float})

# Rename the ‘age’ column to ‘initial_age’:
table.transform(rename={“age”: “initial_age”})

# Drop the ‘age’ column:
table.transform(drop={“age”})

Advertisements

# Make `user_id` the new primary key
table.transform(pk=”user_id”)

# Make the ‘age’ and ‘weight’ columns NOT NULL
table.transform(not_null={“age”, “weight”})

# Make age allow NULL and switch weight to being NOT NULL:
table.transform(not_null={“age”: False, “weight”: True})

# Set default age to 1:
table.transform(defaults={“age”: 1})

Advertisements

# Now remove the default from that column:
table.transform(defaults={“age”: None})
Each time the table.transform(…) method runs it will create a brand new table, copy the data across and then drop the old table. You can combine multiple operations together in a single call, avoiding copying the table multiple times.

The table.transform_sql(…) method returns the SQL that would be executed instead of executing it directly, useful if you want to handle even more complex requirements.

The “sqlite-utils transform” command-line tool
Almost every feature in sqlite-utils is available in both the Python library and as a command-line utility, and .transform() is no exception. The sqlite-utils transform command can be used to apply complex table transformations directly from the command-line.

Here’s an example, starting with the fixtures.db database that powers Datasette’s unit tests:

Advertisements

$ wget https://static.simonwillison.net/static/2020/fixtures.db
$ sqlite3 fixtures.db ‘.schema facetable’
CREATE TABLE facetable (
pk integer primary key,
created text,
planet_int integer,
on_earth integer,
state text,
city_id integer,
neighborhood text,
tags text,
complex_array text,
distinct_some_null,
FOREIGN KEY (“city_id”) REFERENCES [facet_cities](id)
);
$ sqlite-utils transform fixtures.db facetable
–type on_earth text
–drop complex_array
–drop state
–rename tags the_tags
$ sqlite3 fixtures.db ‘.schema facetable’
CREATE TABLE IF NOT EXISTS “facetable” (
[pk] INTEGER PRIMARY KEY,
[created] TEXT,
[planet_int] INTEGER,
[on_earth] TEXT,
[city_id] INTEGER REFERENCES [facet_cities]([id]),
[neighborhood] TEXT,
[the_tags] TEXT,
[distinct_some_null] TEXT
);

You can use the –sql option to see the SQL that would be executed without actually running it:

$ wget https://latest.datasette.io/fixtures.db
$ sqlite-utils transform fixtures.db facetable
–type on_earth text
–drop complex_array
–drop state
–rename tags the_tags
–sql
CREATE TABLE [facetable_new_442f07e26eef] (
[pk] INTEGER PRIMARY KEY,
[created] TEXT,
[planet_int] INTEGER,
[on_earth] TEXT,
[city_id] INTEGER REFERENCES [facet_cities]([id]),
[neighborhood] TEXT,
[the_tags] TEXT,
[distinct_some_null] TEXT
);
INSERT INTO [facetable_new_442f07e26eef] ([pk], [created], [planet_int], [on_earth], [city_id], [neighborhood], [the_tags], [distinct_some_null])
SELECT [pk], [created], [planet_int], [on_earth], [city_id], [neighborhood], [tags], [distinct_some_null] FROM [facetable];
DROP TABLE [facetable];
ALTER TABLE [facetable_new_442f07e26eef] RENAME TO [facetable];

Plenty more tricks
sqlite-utils has plenty more tricks Up its sleeve. I suggest spending some time browsing the Python library reference and the sqlite-utils CLI documentation, or taking a look through through the release notes.

Advertisements

Read More
Share this on knowasiak.com to discuss with people on this topicSign Up on Knowasiak.com now if you’re not registered yet.

Advertisements
Knowasiak
WRITEN BY

Knowasiak

Hey! look, i give tutorials to all my users and i help them!
Get Connected!
One of the Biggest Social Platform for Entrepreneurs, College Students and all. Come and join our community. Expand your network and get to know new people!

Discussion(s)

No comments yet
Knowasiak We would like to show you notifications so you don't miss chats & status updates.
Dismiss
Allow Notifications