Thanks for that helpful reply! Interesting to see that what I wrote out isn't far from what reality would be. I appreciate your informed input here.
But still, as I described in the post, all these bits of tooling that other developers write seem to break all the time anyway. If you look at the quote from the Alembic developer, you can read the level of frustration and spent time that's been put into this. If this is how it's going to work, why not just do it in one location, as I said? All this tooling that's trying to add workarounds seems to be constantly breaking. I think that sqlite could do a better job implementing that logic itself. At the moment, with MediaGoblin, we're trapped and our developers and users are fighting these problems on a very regular basis.
I believe that the constant reimplementation of this in migration frameworks is indication enough that it really is desired and would be useful. Again, I encourage: if there's an experienced developer of sqlite, try testing my theory by running a crowdfunding campaign to add the feature. I bet you'll have proof enough through donations that this is something people want.
I don't see what the actual difficulty is. Being able to do arbitrary schema migrations is considerably harder than making it work for your own data. In my own setup code I look at the schema version and know how to go from version 3 to 4 to 5. It is usually a handful of lines of code for each.
I've reread the content again, and something unstated appears to be the use of an ORM (there are several good posts about why they are evil). ORMs already place several layers of indirection and abstraction between code and the database. It is hardly surprising that the contortions make it harder for them to do things. However I couldn't find an explanation as to why they think the copy and adjust approach would be so hard. Perhaps they are unaware of SQLite functionality like getting the table as a schema or a column by column basis.
I've been on the sqlite-users mailing list for a decade, and use the frequency of requests there to gauge the mood. Alter table is very infrequently mentioned.
It should be noted that just having some code that implements alter table is insufficient. If it doesn't become a standard part of the SQLite core then you can't depend on it being there. And even if it did, you would have to wait years before it is present on the SQLite versions used by stable/enterprise distributions.
You'll also need to establish what you want to happen - eg if a column affinity changes what happens to existing data? What happens to constraints and foreign keys? What should it do if a transaction is already active?
Let me jump in as somebody very familiar with two of the tools mentioned in the OP--sqlachemy and alembic.
> posts about why they are evil
There are legitimate flame wars to be had about ORMs being "evil", but they all have to be had in the context of using them to solve a particular thing. It is true that ORMs do not solve all problems for all people, but it is also true that they solve some problems for some people. There is no point in hashing it out without knowing what OP or me or you or some particular person is doing.
> being able to do arbitrary schema migrations is considerably harder
Being able to do arbitrary schema migrations is not a feature anybody is seriously suggesting, either for SQLite, sqlalchemy, alembic, or any other tool. What is being suggested is merely that SQLite support ALTER TABLE, a very particular, well-defined feature that has been standardized since at least 1992. In the intervening two decades I am not aware of any serious proposals to the effect that the standardized behavior is flawed, although there are various vendor-specific extensions. If you would like to raise an objection to the standard, I would be greatly intrigued.
> I couldn't find an explanation as to why they think the copy and adjust approach would be so hard
All snark aside, four out of five SQL vendors support alter table. They would be doing this logic solely for SQLite. Why would they do that? The path of least resistance is just to tell people to migrate to MySQL. That's what people who need migration support end up doing.
> alter table is very infrequently mentioned
You seem to anticipate that users will discover that SQLite does not support alter table correctly, and that following this discovery they will continue to use SQLite. My experience is at variance with your anticipation.
Specifically, I don't post to SQLite-users, first and foremost, because I am not a SQLite user, because it does not support alter table. The sampling bias would actually preclude you from hearing about any really critical feature requests.
> you would have to wait years before it is present on SQLite versions used by stable/enterprise distributions
Literally an argument against doing anything to SQLite ever. Is this a common objection to feature requests on the mailing list?
> You'll also need to establish what you want to happen
OP needs to establish nothing, this has literally been decided a full eight years before the first line of SQLite was ever written.
I know you're thinking "SQLite is fine, go use MySQL if you need this alter table nonsense". And that's precisely what people who do a lot of migrations end up doing. I've migrated every SQLite codebase I've committed to this year to either MySQL or Postgres or NoSQL. Every last one.
Neither I nor the SQLite team think that SQLite is the solution to everyone's database use. As they say it is a good alternative to fopen: http://www.sqlite.org/whentouse.html - it is not a project goal to be a complete SQL implementation or to be a drop in replacement for MySQL/Postgres/Oracle etc
SQLite's manifest typing is a complication for ALTER TABLE that doesn't affect other databases.
On the ORM side, my point was that layers of abstraction and indirection will make things harder. Is your statement that they would have to have SQLite specific code the only reason why it isn't done, or is there something else considered hard?
While it is true your voice isn't heard, enough people do post to sqlite-users to get some idea of what the various issues are and a rough idea of their popularity. For example a far more common issue is that SQLite does not work well if you use a networked filesystem (you will eventually get corruption).
Timeliness is a practical issue. ALTER TABLE can't be used until it is available. The way the SQLite developers consider best practise is you embed the SQLite library statically inside your app and you write specifically to SQLite: http://www.sqlite.org/amalgamation.html
There are no legal or technical reasons preventing anyone to cause this to be implemented separately from the SQLite core. Wanting it to be part of the core is very different.
You seem to have an underlying assumption of zero sum games or SQLite being in some competition with all other database engines. It isn't. It doesn't lose by developers using MySQL or MongoDB. Heck I use MongoDB far more than I use SQLite. Developers who need comprehensive ALTER TABLE support and find the copy table with modifications approach too onerous are far outside of SQLite's sweet spot. That is ok.
But still, as I described in the post, all these bits of tooling that other developers write seem to break all the time anyway. If you look at the quote from the Alembic developer, you can read the level of frustration and spent time that's been put into this. If this is how it's going to work, why not just do it in one location, as I said? All this tooling that's trying to add workarounds seems to be constantly breaking. I think that sqlite could do a better job implementing that logic itself. At the moment, with MediaGoblin, we're trapped and our developers and users are fighting these problems on a very regular basis.
I believe that the constant reimplementation of this in migration frameworks is indication enough that it really is desired and would be useful. Again, I encourage: if there's an experienced developer of sqlite, try testing my theory by running a crowdfunding campaign to add the feature. I bet you'll have proof enough through donations that this is something people want.