8 ms·

The lack of proper “alter table” support in SQLite

90 points by paroneayea 13y ago

deleted 13y ago
[deleted]
revelation 13y ago
ALTER is in itself an odd command, useful only in development, never in production, and bringin with it deep architectural implications and an endless list of problems in basically all SQL systems today. In SQLite, an embedded database, it is certainly out of place. Drop, rebuild, and move on with life.
- genwin 13y ago
  The ALTER doesn't have to be in the embedded code. It can be a separate utility.
  - PommeDeTerre 13y ago
    The migration libraries mentioned in the article basically are the "separate utility" that you describe. Yet apparently the developers of these libraries are unable to implement this functionality properly on their own, for whatever reason. So they want the SQLite developers to do so instead.
- deleted 13y ago
  [deleted]
- gaadd33 13y ago
  Why is ALTER never useful in production?
  - mateo411 13y ago
    I wouldn't say that ALTER TABLE is never useful in production, but you should try to avoid it, especially if you have large tables. Many implementations of ALTER TABLE, lock the table, which means that no process can write to the table, until the ALTER TABLE command completes. If the ALTER TABLE statement takes minutes, or a few hours to complete, then you will have degraded performance or downtime.
    - gaadd33 13y ago
      Oh yeah that's very true, many SQL engines seem to lock tables whenever a DDL statement is run. I do like that Postgresql tends not to, it is a huge change from the hoops you have to jump through to add columns in a production MySQL scenario.
- spatz2013 13y ago
  tell that Drupal when they go from one version to the next.
- kalininalex 13y ago
  "ALTER is in itself an odd command, useful only in development, never in production" How would you then ever modify an existing database?
  - kanzure 13y ago
    > How would you then ever modify an existing database? You would use techniques like: https://github.com/soundcloud/large-hadron-migrator https://github.com/soundcloud/large-hadron-migrator """ That is until your tables grow to millions of records. At this point, the locking nature of ALTER TABLE may take your site down for an hour or more while critical tables are migrated. In order to avoid this, developers begin to design around the problem by introducing join tables or moving the data into another layer. Development gets less and less agile as tables grow and grow. To make the problem worse, adding or changing indices to optimize data access becomes just as difficult. Side effects may include black holes and universe implosion. There are few things that can be done at the server or engine level. It is possible to change default values in an ALTER TABLE without locking the table. The InnoDB Plugin provides facilities for online index creation, which is great if you are using this engine, but only solves half the problem. At SoundCloud we started having migration pains quite a while ago, and after looking around for third party solutions, we decided to create our own. We called it Large Hadron Migrator, and it is a gem for online ActiveRecord and DataMapper migrations. The basic idea is to perform the migration online while the system is live, without locking the table. In contrast to OAK and the facebook tool, we only use a copy table and triggers. Lhm requires a monotonically increasing numeric Primary Key on the table, due to how the Chunker works. """
    - kalininalex 13y ago
      OK, I guess the issue is with the "never in production". One size rarely fits all. I can have a lookup table with three rows in my database, for which Large Hadron Migrator is hardly an appropriate solution. ALTER will work just fine. For large tables - yes, a more complex migration solution is in order.
    - yxhuvud 13y ago
      .. or you use a real database engine that support schema changes in background transactions, like postgres. Also, it is good to remember that not all production databases will grow large. Many stay small with a limited number of rows, but will still need to be updated as the product is updated.
rogerbinns 13y ago
I am very familiar with SQLite internals. The answer is already in there. SQLite stores each row as each column value encoded sequentially corresponding to the declared order of the columns. Changing column order or deletions/inserts require a rewrite of every row. The one special case that is allowed is adding a column on the end of the schema providing it has a default value. A SQLite provided ALTER TABLE implementation would do exactly what was stated - start a transaction, rename the existing table to a temporary name, create a new one with the desired schema, and copy data across mangling as appropriate before deleting the old table and finishing the transaction. For plain tables this is no big deal, but for more complicated ones there are a lot of issues such as foreign key references, constraints, indices. The majority of the code would be dealing with all these conditions and interactions. It also wouldn't be any more efficient than code the developer writes - ie there are no shortcuts available to the SQLite developers that aren't available to developers using SQLite. The net effect would be a larger library (they limit to a 250kb library size so something else would need to be chopped), some complex documentation and a heck of a lot of testing code. For something that is relatively rarely needed (see requests on the mailing lists). The chance of something like this ending up in the SQLite core is extremely slim, so you could never depend on it being there anyway. SQLite does have several things to help. There is a user_version pragma you can use to keep track of the schema version and use for upgrading. You can temporarily disable foreign key and constraint enforcing. There are numerous pragmas to get table metadata. The table definitions are stored as SQL strings in a sqlite master table, and a pragma allows you to make that writeable.
- codeka 13y ago
  I agree with you. This kind of functionality is much better implemented in a third-party tool like sqlalchemy-migrate or alembic that OP mentions in the article. I much rather a smaller sqlite library than one that is burdened with functionality you just don't use 99% of the time. The benefit of making an "official" migration library for SQLite is probably outweighed by the opportunity cost of having it developed, tested and kept up-to-date.
  - paroneayea 13y ago
bobbyi_settv 13y ago
> a successor called Alembic founded by the same core author as sqlalchemy-migrate 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.
- paroneayea 13y ago
  Thanks for that correction. I updated the post.
k_bx 13y ago
> but we also want people to be able to run smallish installations for themselves or their friends and family as well So, what's the problem in running PostgreSQL or MySQL in small installations? They really need small amount of space and memory to be installed and operated.
- xmodem 13y ago
  There's still time and friction involved in getting an instance up or adding a database to an existing instance.
  - k_bx 13y ago
    It should all be done via apt-get's after-install hooks of your software. More on that, you can do things like supporting any of mysql or postgresql if they're already installed.
- VLM 13y ago
  On a extremely big picture 50000 foot level, there's the windows model where you put everything into one piece of software and you run that one piece of software on one server (real or virtual). Its philosophically anti-windows to have more than one piece of software installed per OS. So you want a "something" and a database server, well now you need two installs / licenses / hardware / images / admins / whatever not just one. And in windows philosophy you never run user stuff on a server anyway so right there you may have issues with a "user" app and a "server" db system requiring two systems. Its hardly all peaches and unicorns on the unix side either. Seriously, you're going to install 17 completely separate, different versions of "magic embedded mysql" or whatever, and have no way to do joins across DB servers or system wide DB backup system? Crazy talk. Why not have every programmer write their own homemade strcmp() complete with different buffer overflow bugs on each version... So the whole concept of a "private" database violates quite a few unix philosophy principles. Handling multiple versions of DBs across different distros is a puzzler for the devs rather than just including one version and calling it good. The whole mess from the 100K standpoint is DBs and DB apps are too new, too much technical churn, too much incompatibility. For example, why are you installing a new DB and replacing all your API code if all you want is a different engine perhaps (or perhaps not) better suited to your workload? Its conceptually like switching your whole infrastructure to a new word processor in 1985.
tome 13y ago
> I'm pre-pledging $200.00 towards fixing the problem Nice idea, but wouldn't he have to find about one hundred other pledgers to make this financially viable?
- ctoth 13y ago
  I believe that's the entire point of this blog post. I'm rather puzzled to see the negative attention it has received. Is this a huge pain point? No, but it is a constant niggling annoyance when using SQLite to power things like desktop apps. If not built in to the core, I believe that SQLite supports compile-time extensions, would someone more knowledgeable comment? I wonder if the OP would be satisfied with one of those? I personally would be fine building my own SQLite to get the functionality and it looks like the author would as well. In short, I'm kind of confused as to why everybody seems so down on this idea. If an extension could be developed to do this, and if this extension would meet the OP's needs, I'm not sure why we're all going on about how it would ruin the sanctity and tininess of core SQLite. I'll put my $200 pledge here.
  - paroneayea 13y ago
    Yeah, I'm also surprised to see people so upset about the idea of the feature mostly for the argument that it doesn't presently exist. As for the compile-time-extension, my main concern with that as a free software project is that we try to play nice with distro packaging and requiring users to compile their own sqlite would be setting them up for a bit of pain. But I suspect it doesn't matter: if the feature was coded with the aim of pushing it upstream, I'm suspecting there's a good chance upstream would accept it if of good quality. Then again, I'm not upstream. :)
    - rogerbinns 13y ago
      Code is unlikely to be accepted upstream because it very rarely is (assuming you mean by the SQLite team). They are very picky about code contributions and almost always implement themselves. One part is legal - there has to be very clear lineage/authoring to the code, patent clearances etc. The second part is that the actual code to implement any particular functionality or even a bug fix is relatively trivial. There is far more effort and far more lines of code for testing: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html - and implementing code to enable the rigourous testing usually results in a different structure. The third part would be conflicting with the existing goals: http://www.sqlite.org/whentouse.html http://www.sqlite.org/whentouse.html OTOH if the intention isn't to be part of the core then upstream doesn't matter and the code can be distributed in any way you deem fit.
ExpiredLink 13y ago
So he gets a free, lightweight, immensely practical tool. His answer: complaint.
- PommeDeTerre 13y ago
  If you're using a given piece of software, and you run into problems, it's perfectly acceptable to complain. In fact, it's one of the most helpful things you can do. Even if it doesn't help resolve the problem directly, it at least helps make the problem more widely known. This can be very useful information for people investigating the use of such software. It doesn't matter how much, if any, you may have paid for the software. If it doesn't work in a particular case, let it be known.
mrbaxter 13y ago
SQLite is a replacement for fopen(), not a database.
ams6110 13y ago
SQLLite is "lite" If you want alter table support, use a SQL database that provides it. There are many.
stevoski 13y ago
The H2 open source java database implements "alter table" exactly as per the description of sql alchemy: create a new table with the new structure, copy all the data across, delete the original table, rename the new table to the origina name. This could perhaps be acceptable for sqlite.
strictfp 13y ago
SQlite might be meant to be light, but surely they could implement rudimentary alter table support (perhaps through the create new and rename strategy mentioned here)? The alternative is that each client have to code their own version. I can understand if they say no to features which are rarely used, but if something would be used by the majority of the users, I cannot understand that they would say no for 'lightness' sake.
stevenwei 13y ago
It's probably relevant to note that both Android and iOS ship with SQLite as the de-facto database storage engine, so there's very much a use case for having reliable in-place migrations in SQLite on modern software. I could certainly see the value of having more robust ALTER TABLE support in SQLite itself, and I don't find the argument that such functionality will go unused very convincing. In real world scenarios almost everyone is going to have to migrate at some point, and the easier this is to accomplish, the better.

90 points by paroneayea 13y ago

deleted 13y ago

[deleted]

ALTER is in itself an odd command, useful only in development, never in production, and bringin with it deep architectural implications and an endless list of problems in basically all SQL systems today. In SQLite, an embedded database, it is certainly out of place. Drop, rebuild, and move on with life.

genwin 13y ago
The ALTER doesn't have to be in the embedded code. It can be a separate utility.
- PommeDeTerre 13y ago
  The migration libraries mentioned in the article basically are the "separate utility" that you describe. Yet apparently the developers of these libraries are unable to implement this functionality properly on their own, for whatever reason. So they want the SQLite developers to do so instead.
deleted 13y ago
[deleted]
gaadd33 13y ago
Why is ALTER never useful in production?
- mateo411 13y ago
  I wouldn't say that ALTER TABLE is never useful in production, but you should try to avoid it, especially if you have large tables. Many implementations of ALTER TABLE, lock the table, which means that no process can write to the table, until the ALTER TABLE command completes. If the ALTER TABLE statement takes minutes, or a few hours to complete, then you will have degraded performance or downtime.
  - gaadd33 13y ago
    Oh yeah that's very true, many SQL engines seem to lock tables whenever a DDL statement is run. I do like that Postgresql tends not to, it is a huge change from the hoops you have to jump through to add columns in a production MySQL scenario.
spatz2013 13y ago
tell that Drupal when they go from one version to the next.
kalininalex 13y ago
"ALTER is in itself an odd command, useful only in development, never in production" How would you then ever modify an existing database?
- kanzure 13y ago
  > How would you then ever modify an existing database? You would use techniques like: https://github.com/soundcloud/large-hadron-migrator https://github.com/soundcloud/large-hadron-migrator """ That is until your tables grow to millions of records. At this point, the locking nature of ALTER TABLE may take your site down for an hour or more while critical tables are migrated. In order to avoid this, developers begin to design around the problem by introducing join tables or moving the data into another layer. Development gets less and less agile as tables grow and grow. To make the problem worse, adding or changing indices to optimize data access becomes just as difficult. Side effects may include black holes and universe implosion. There are few things that can be done at the server or engine level. It is possible to change default values in an ALTER TABLE without locking the table. The InnoDB Plugin provides facilities for online index creation, which is great if you are using this engine, but only solves half the problem. At SoundCloud we started having migration pains quite a while ago, and after looking around for third party solutions, we decided to create our own. We called it Large Hadron Migrator, and it is a gem for online ActiveRecord and DataMapper migrations. The basic idea is to perform the migration online while the system is live, without locking the table. In contrast to OAK and the facebook tool, we only use a copy table and triggers. Lhm requires a monotonically increasing numeric Primary Key on the table, due to how the Chunker works. """
  - kalininalex 13y ago
    OK, I guess the issue is with the "never in production". One size rarely fits all. I can have a lookup table with three rows in my database, for which Large Hadron Migrator is hardly an appropriate solution. ALTER will work just fine. For large tables - yes, a more complex migration solution is in order.
  - yxhuvud 13y ago
    .. or you use a real database engine that support schema changes in background transactions, like postgres. Also, it is good to remember that not all production databases will grow large. Many stay small with a limited number of rows, but will still need to be updated as the product is updated.

rogerbinns 13y ago

I am very familiar with SQLite internals. The answer is already in there. SQLite stores each row as each column value encoded sequentially corresponding to the declared order of the columns. Changing column order or deletions/inserts require a rewrite of every row. The one special case that is allowed is adding a column on the end of the schema providing it has a default value. A SQLite provided ALTER TABLE implementation would do exactly what was stated - start a transaction, rename the existing table to a temporary name, create a new one with the desired schema, and copy data across mangling as appropriate before deleting the old table and finishing the transaction. For plain tables this is no big deal, but for more complicated ones there are a lot of issues such as foreign key references, constraints, indices. The majority of the code would be dealing with all these conditions and interactions. It also wouldn't be any more efficient than code the developer writes - ie there are no shortcuts available to the SQLite developers that aren't available to developers using SQLite. The net effect would be a larger library (they limit to a 250kb library size so something else would need to be chopped), some complex documentation and a heck of a lot of testing code. For something that is relatively rarely needed (see requests on the mailing lists). The chance of something like this ending up in the SQLite core is extremely slim, so you could never depend on it being there anyway. SQLite does have several things to help. There is a user_version pragma you can use to keep track of the schema version and use for upgrading. You can temporarily disable foreign key and constraint enforcing. There are numerous pragmas to get table metadata. The table definitions are stored as SQL strings in a sqlite master table, and a pragma allows you to make that writeable.

codeka 13y ago
I agree with you. This kind of functionality is much better implemented in a third-party tool like sqlalchemy-migrate or alembic that OP mentions in the article. I much rather a smaller sqlite library than one that is burdened with functionality you just don't use 99% of the time. The benefit of making an "official" migration library for SQLite is probably outweighed by the opportunity cost of having it developed, tested and kept up-to-date.
- paroneayea 13y ago

bobbyi_settv 13y ago

> a successor called Alembic founded by the same core author as sqlalchemy-migrate 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.

paroneayea 13y ago
Thanks for that correction. I updated the post.

k_bx 13y ago

> but we also want people to be able to run smallish installations for themselves or their friends and family as well So, what's the problem in running PostgreSQL or MySQL in small installations? They really need small amount of space and memory to be installed and operated.

xmodem 13y ago
There's still time and friction involved in getting an instance up or adding a database to an existing instance.
- k_bx 13y ago
  It should all be done via apt-get's after-install hooks of your software. More on that, you can do things like supporting any of mysql or postgresql if they're already installed.
VLM 13y ago
On a extremely big picture 50000 foot level, there's the windows model where you put everything into one piece of software and you run that one piece of software on one server (real or virtual). Its philosophically anti-windows to have more than one piece of software installed per OS. So you want a "something" and a database server, well now you need two installs / licenses / hardware / images / admins / whatever not just one. And in windows philosophy you never run user stuff on a server anyway so right there you may have issues with a "user" app and a "server" db system requiring two systems. Its hardly all peaches and unicorns on the unix side either. Seriously, you're going to install 17 completely separate, different versions of "magic embedded mysql" or whatever, and have no way to do joins across DB servers or system wide DB backup system? Crazy talk. Why not have every programmer write their own homemade strcmp() complete with different buffer overflow bugs on each version... So the whole concept of a "private" database violates quite a few unix philosophy principles. Handling multiple versions of DBs across different distros is a puzzler for the devs rather than just including one version and calling it good. The whole mess from the 100K standpoint is DBs and DB apps are too new, too much technical churn, too much incompatibility. For example, why are you installing a new DB and replacing all your API code if all you want is a different engine perhaps (or perhaps not) better suited to your workload? Its conceptually like switching your whole infrastructure to a new word processor in 1985.

tome 13y ago

> I'm pre-pledging $200.00 towards fixing the problem Nice idea, but wouldn't he have to find about one hundred other pledgers to make this financially viable?

ctoth 13y ago
I believe that's the entire point of this blog post. I'm rather puzzled to see the negative attention it has received. Is this a huge pain point? No, but it is a constant niggling annoyance when using SQLite to power things like desktop apps. If not built in to the core, I believe that SQLite supports compile-time extensions, would someone more knowledgeable comment? I wonder if the OP would be satisfied with one of those? I personally would be fine building my own SQLite to get the functionality and it looks like the author would as well. In short, I'm kind of confused as to why everybody seems so down on this idea. If an extension could be developed to do this, and if this extension would meet the OP's needs, I'm not sure why we're all going on about how it would ruin the sanctity and tininess of core SQLite. I'll put my $200 pledge here.
- paroneayea 13y ago
  Yeah, I'm also surprised to see people so upset about the idea of the feature mostly for the argument that it doesn't presently exist. As for the compile-time-extension, my main concern with that as a free software project is that we try to play nice with distro packaging and requiring users to compile their own sqlite would be setting them up for a bit of pain. But I suspect it doesn't matter: if the feature was coded with the aim of pushing it upstream, I'm suspecting there's a good chance upstream would accept it if of good quality. Then again, I'm not upstream. :)
  - rogerbinns 13y ago
    Code is unlikely to be accepted upstream because it very rarely is (assuming you mean by the SQLite team). They are very picky about code contributions and almost always implement themselves. One part is legal - there has to be very clear lineage/authoring to the code, patent clearances etc. The second part is that the actual code to implement any particular functionality or even a bug fix is relatively trivial. There is far more effort and far more lines of code for testing: http://www.sqlite.org/testing.html http://www.sqlite.org/testing.html - and implementing code to enable the rigourous testing usually results in a different structure. The third part would be conflicting with the existing goals: http://www.sqlite.org/whentouse.html http://www.sqlite.org/whentouse.html OTOH if the intention isn't to be part of the core then upstream doesn't matter and the code can be distributed in any way you deem fit.

ExpiredLink 13y ago

So he gets a free, lightweight, immensely practical tool. His answer: complaint.

PommeDeTerre 13y ago
If you're using a given piece of software, and you run into problems, it's perfectly acceptable to complain. In fact, it's one of the most helpful things you can do. Even if it doesn't help resolve the problem directly, it at least helps make the problem more widely known. This can be very useful information for people investigating the use of such software. It doesn't matter how much, if any, you may have paid for the software. If it doesn't work in a particular case, let it be known.

mrbaxter 13y ago

SQLite is a replacement for fopen(), not a database.

ams6110 13y ago

SQLLite is "lite" If you want alter table support, use a SQL database that provides it. There are many.

stevoski 13y ago

The H2 open source java database implements "alter table" exactly as per the description of sql alchemy: create a new table with the new structure, copy all the data across, delete the original table, rename the new table to the origina name. This could perhaps be acceptable for sqlite.

strictfp 13y ago

SQlite might be meant to be light, but surely they could implement rudimentary alter table support (perhaps through the create new and rename strategy mentioned here)? The alternative is that each client have to code their own version. I can understand if they say no to features which are rarely used, but if something would be used by the majority of the users, I cannot understand that they would say no for 'lightness' sake.

stevenwei 13y ago

It's probably relevant to note that both Android and iOS ship with SQLite as the de-facto database storage engine, so there's very much a use case for having reliable in-place migrations in SQLite on modern software. I could certainly see the value of having more robust ALTER TABLE support in SQLite itself, and I don't find the argument that such functionality will go unused very convincing. In real world scenarios almost everyone is going to have to migrate at some point, and the easier this is to accomplish, the better.