4 ms·

The one annoying thing about SQLite is that there is no easy way to change the table structure. Adding/Removing/Renaming columns is super complicated and afaik

The one annoying thing about SQLite is that there is no easy way to change the table structure. Adding/Removing/Renaming columns is super complicated and afaik there is no good command line tool that does it for you. That is the primary reason why I do not consider it for new projects. It's just to slow to iterate on.

masklinn 7y ago
> Adding/Removing/Renaming columns is super complicated and afaik there is no good command line tool that does it for you. sqlite supports ADD COLUMN and RENAME COLUMN DDLs. Dropping columns is not supported, nor is adding some of the more complex column, that does require going through full table rewriting.
mysterydip 7y ago
Would making a new db with the new structure and essentially importing the old data be a reasonably speedy activity?
- superturkey650 7y ago
  It is if your table is just a datastore with no foreign key links or indexes. See another reply in this thread: https://news.ycombinator.com/item?id=20841814 https://news.ycombinator.com/item?id=20841814
Dowwie 7y ago
That's a hell of a reason not to use sqlite. Staging data in a temporary table while a table is dropped, recreated, and then data is reinserted is not much of an inconvenience.
- FreeHugs 7y ago
  It is. It is a bunch of complex commands. Just look at the proposed solutions on Stackoverflow: https://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite https://stackoverflow.com/questions/8442147/how-to-delete-or...
  - Dowwie 7y ago
    So I'll keep my opinion about this out of this response. To those who are interested, the approved answer on that SO thread recommends: 1. create new table as the one you are trying to change 2. copy all data, 3. drop old table, 4. rename the new one. It's up to others to decide whether that is complex enough to warrant avoiding sqlite
    - Someone 7y ago
      That gets more cumbersome if the table has indexes (you will have to create them on the new table), and even more cumbersome if foreign keys point to it (you will have to drop them before step 3 and recreate them after step 4) “Copy all data” also can be difficult if the table has data that the database created that must stay the same because you use it elsewhere. That shouldn’t be a problem with SQLite, as it doesn’t allow rowid as foreign key, but if you use it as a foreign key outside the database, or use the hash of a full row to detect changes, it may still bite you. It also may mean being offline for a significant amount of time, but that also often is (effectively) the case for databases that support deleting columns
      - Dowwie 7y ago
        good points
    - bfrog 7y ago
      Which adds up to dozens to hundreds of lines of code which needs to be maintained in each project you use sqlite with, vs the one liner of sql that would be required if using a sql that supported it.
      - mbreese 7y ago
        It's only required if you need the ability to change the schema of a SQLite table at runtime. I'd wager that's not a very common use-case for SQLite.

masklinn 7y ago

> Adding/Removing/Renaming columns is super complicated and afaik there is no good command line tool that does it for you. sqlite supports ADD COLUMN and RENAME COLUMN DDLs. Dropping columns is not supported, nor is adding some of the more complex column, that does require going through full table rewriting.

mysterydip 7y ago

Would making a new db with the new structure and essentially importing the old data be a reasonably speedy activity?

superturkey650 7y ago
It is if your table is just a datastore with no foreign key links or indexes. See another reply in this thread: https://news.ycombinator.com/item?id=20841814 https://news.ycombinator.com/item?id=20841814

Dowwie 7y ago

That's a hell of a reason not to use sqlite. Staging data in a temporary table while a table is dropped, recreated, and then data is reinserted is not much of an inconvenience.

FreeHugs 7y ago
It is. It is a bunch of complex commands. Just look at the proposed solutions on Stackoverflow: https://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite https://stackoverflow.com/questions/8442147/how-to-delete-or...
- Dowwie 7y ago
  So I'll keep my opinion about this out of this response. To those who are interested, the approved answer on that SO thread recommends: 1. create new table as the one you are trying to change 2. copy all data, 3. drop old table, 4. rename the new one. It's up to others to decide whether that is complex enough to warrant avoiding sqlite
  - Someone 7y ago
    That gets more cumbersome if the table has indexes (you will have to create them on the new table), and even more cumbersome if foreign keys point to it (you will have to drop them before step 3 and recreate them after step 4) “Copy all data” also can be difficult if the table has data that the database created that must stay the same because you use it elsewhere. That shouldn’t be a problem with SQLite, as it doesn’t allow rowid as foreign key, but if you use it as a foreign key outside the database, or use the hash of a full row to detect changes, it may still bite you. It also may mean being offline for a significant amount of time, but that also often is (effectively) the case for databases that support deleting columns
    - Dowwie 7y ago
      good points
  - bfrog 7y ago
    Which adds up to dozens to hundreds of lines of code which needs to be maintained in each project you use sqlite with, vs the one liner of sql that would be required if using a sql that supported it.
    - mbreese 7y ago
      It's only required if you need the ability to change the schema of a SQLite table at runtime. I'd wager that's not a very common use-case for SQLite.