I think there's nothing else in the world of programming that's given me more headaches than the lack of proper alter table support in SQLite. I'm not alone, because almost every developer I've worked with has had similar pains and complaints. How many hours of developer time have been wasted on the lack of proper SQLite alter table support? It must be in the thousands of hours. Surely this is fixable, and I'm willing to put my money where my mouth is: if someone is willing to develop SQLite alter support, I'm pre-pledging $200.00 towards fixing the problem. And I bet others would be willing to donate towards such a solution as well.

First of all, yes, there's a venting of frustration above, but it is not a venting of frustration in lack of appreciation of SQLite. SQLite is wonderful software; you could say that it was one of the biggest reasons (maybe the biggest, but of several) for my own project MediaGoblin switching from MongoDB to SQL (with SQLAlchemy, which is also wonderful software). Yes, we want people to be able to run medium to large installations of MediaGoblin (and there's PostgreSQL for that), but we also want people to be able to run smallish installations for themselves or their friends and family as well. SQLite is great for this, and it's also great for making developing super simple.

But one of the original reasons for going with Mongo was remembering how frustrating migration failures in SQL could be. When deciding to switch to SQL, I realized that we'd be moving back into this pain territory (we did do migrations with Mongo; anyone who suggests you don't need migrations with a document store database doesn't know what they're talking about and is aiming a shotgun squarely at their foot... even so, migrations were easier with Mongo). But of ALTER TABLE commands, SQLite only supports RENAME TABLE and ADD COLUMN. I had remembered also how because of this sqlite could require annoying workarounds to make migrations happen. I didn't realize though that at times doing migrations would become nearly impossible.

Since sqlite lacks most ALTER TABLE commands, most migration frameworks like South and sqlalchemy-migrate do crazy workarounds for the missing commands that usually involve renaming the table, creating an entirely new table renamed with the new schema in place, copying all the data back, and killing the old table.

If that sounds like a mess, that's because it is. In fact, the sqlalchemy-migrate project homepage suggests that for new projects to use a successor called Alembic founded by the same core author as sqlalchemy-migrate (edit: I've been corrected on this on HackerNews: "Alembic is not founded by the same core author as sqlalchemy-migrate. Alembic is founded by Mike Bayer who is the core author of SQLAlchemy itself."). But we didn't use Alembic because at the time there wasn't much support for sqlite (I think bit of support has been added since then, though I don't know how much) and we knew we really wanted it. In fact, on the Alembic homepage, this is listed as a goal:

Don't break our necks over SQLite's inability to ALTER things. SQLite has almost no support for table or column alteration, and this is likely intentional. Alembic's design is kept simple by not contorting its core API around these limitations, understanding that SQLite is simply not intended to support schema changes. While Alembic's architecture can support SQLite's workarounds, and we will support these features provided someone takes the initiative to implement and test, until the SQLite developers decide to provide a fully working version of ALTER, it's still vastly preferable to use Alembic, or any migrations tool, with databases that are designed to work under the assumption of in-place schema migrations taking place.

And indeed, there's a lot of neck-breaking involved in trying to use migrations with SQLite...

At one point I tried dropping a boolean field but discovered this was impossible because SQLAlchemy doesn't have a good sense of the constraints on an sqlite table, so sqlalchemy-migrate tries to reproduce the table without the boolean field, but since the boolean check is implemented as a constraint, the new table still has a constraint on a non-existent field and sqlalchemy-migrate doesn't notice. When the statement to create the new table is executed, sqlite explodes wondering what this boolean check is doing on this field that doesn't exist. More recently, one of our Summer of Code students tried writing some migrations, discovered that one broke in sqlite and another that didn't break deleted the unique constraint. We have no idea how to move forward on some of these issues, and that's a frustrating situation to be in.

Given all the above pain, why doesn't SQLite implement ALTER TABLE? I actually don't really know the details, but one of our contributors knows a bit about the sqlite structure and tells me that he thinks it might be because the data format makes some actions like appending rows fairly easy, but other actions like deleting a field would mean rewriting the entire table line by line.

But to that I think: migration frameworks are already rewriting tables entirely! So as far as I can tell, in the worst case scenario, sqlite implementing these other alter table methods means that it will be doing the same thing that migration frameworks have to do already, but in an official way, with a better sense of the structure of the existing tables, and probably even a bit faster than some other program likely operating through a different language doing the same. Sure, this may not be ideal, but it would be much better than the present situation. The documentation could even say this: "be aware that due to the nature of the sqlite file structure, this is a very slow operation that requires rewriting your entire table." But at least it would be a operation that rewrites the table natively, and would not explode in such strange and unpredictable ways!

I would be interested in helping myself, but I don't know SQLite's codebase, database structures is a domain I don't presently know, and I do not have time to learn it. But I'm more than happy to donate money (and I'm running off a crowdfunding campaign salary... I don't normally donate this amount of money to things, but surely fixing the most frustrating recurring bug in my programming career is worth putting $200 down). And I bet I'm not alone. If someone experienced with developing sqlite was willing to make an upstream-aimed contribution to kill this pain point, I bet it'd be a very fundable project.

Update: This post has gotten a fair amount of discussion on HackerNews, which is good! I'm surprised though at the amount of people who are taking the stance of "that feature doesn't exist, so why would you want that feature?" I thought this reply gave a good response to that.

Another update: One comment on HackerNews suggests that SQLite needs to stay around 250kb to stay "light". But on my Debian install, the sqlite binary is 680kb. Now granted, Debian probably has everything optional compiled in. But there's your answer if you're afraid about the binary getting too large: make it a compile-time option!