4 ms·

The biggest thorn I found working with sqlite was the lack of ability to modify columns with ALTER TABLE which was a real pain. Doesn't look like this is fixed

The biggest thorn I found working with sqlite was the lack of ability to modify columns with ALTER TABLE which was a real pain. Doesn't look like this is fixed in sqlite4 though...

kamac 9y ago
Same here. Had to switch to dockerized mariadb for local tests, because migrations wouldn't work.
- jey 9y ago
  Wouldn't you want your tests to be run against the same DB family (and version) as production anyway?
  - kamac 9y ago
    I suppose it was a long time coming. SQLite was very convenient for me while the product was in a very early development stage, with everything changing rapidly.
  - mst 9y ago
    Depending on the situation, it can be well worth it to have your test suite run against SQLite while doing active development to be able to iterate faster and then run it again against the target database before pushing the branch. Where possible I much prefer to spin up a version of my target database in a tempdir but "faster test cycles" is sometimes worth accepting the trade-offs.
- contingencies 9y ago
  Why not work around the expectation and simply migrate offline? (eg. dump DB, hack dumpfile/stream, load new CSV?) While you may lose instantaneous constraint validation, it would almost certainly be faster and allow you to work with known and well tested tools. Conforms to the Unix design philosophy: "Store data in flat text files." / "Write programs to handle text streams, because that is a universal interface." http://github.com/globalcitizen/taoup http://github.com/globalcitizen/taoup Since you were nominally optimizing for migration, a zoom-out perspective may be to note that upgrading SQLite3 versions vs. upgrading major RDBMS versions is trivial/fast, relatively rarely required, also cohabitation of multiple versions works a lot easier, any kind of CI/CD process is going to be orders of magnitude faster and use much less CPU/memory/disk space, which means smaller build artifacts and thus faster transfer/download.
gigatexal 9y ago
lack of window functions kill it's usefulness for me.
- contingencies 9y ago
  Official SQLite response http://sqlite.1065341.n5.nabble.com/Window-functions-td77470.html http://sqlite.1065341.n5.nabble.com/Window-functions-td77470...
  - wongarsu 9y ago
    So in essence it's a lot of work for a small functionality gain? There are some things that are inconvenient to do without window functions, but my primary use for them is simple optimization. Most of the cases where I've used window function in postgres, the query would be trivial to rewrite to use a subquery instead. Window functions always led to a much faster query that scaled much better over large query sets.
    - gigatexal 9y ago
      Exactly why I miss them so much.
  - gigatexal 9y ago
    Reading through the responses just screams: not worth the effort. I know it’s an open source volunteer product and even with a commercial arm the lack of window functions (aren’t they part of the SQL standard?) is laughable.
coleifer 9y ago
There's an easy workaround: rename the table, create new table, insert into/select from.
- mst 9y ago
  The migration tools I work with have this already automated and (having been involved in the design of said automation) it's really not that difficult to add. Other DBMSes often have to do complete table rewrites for certain types of ALTER anyway - so while, yes, it's faintly annoying to have to do that in userspace rather than having the db engine do it for you, it's not nearly as big a disadvantage as it first appears.
carry_bit 9y ago
You can add columns at least.

The biggest thorn I found working with sqlite was the lack of ability to modify columns with ALTER TABLE which was a real pain. Doesn't look like this is fixed in sqlite4 though...

kamac 9y ago

Same here. Had to switch to dockerized mariadb for local tests, because migrations wouldn't work.

jey 9y ago
Wouldn't you want your tests to be run against the same DB family (and version) as production anyway?
- kamac 9y ago
  I suppose it was a long time coming. SQLite was very convenient for me while the product was in a very early development stage, with everything changing rapidly.
- mst 9y ago
  Depending on the situation, it can be well worth it to have your test suite run against SQLite while doing active development to be able to iterate faster and then run it again against the target database before pushing the branch. Where possible I much prefer to spin up a version of my target database in a tempdir but "faster test cycles" is sometimes worth accepting the trade-offs.
contingencies 9y ago
Why not work around the expectation and simply migrate offline? (eg. dump DB, hack dumpfile/stream, load new CSV?) While you may lose instantaneous constraint validation, it would almost certainly be faster and allow you to work with known and well tested tools. Conforms to the Unix design philosophy: "Store data in flat text files." / "Write programs to handle text streams, because that is a universal interface." http://github.com/globalcitizen/taoup http://github.com/globalcitizen/taoup Since you were nominally optimizing for migration, a zoom-out perspective may be to note that upgrading SQLite3 versions vs. upgrading major RDBMS versions is trivial/fast, relatively rarely required, also cohabitation of multiple versions works a lot easier, any kind of CI/CD process is going to be orders of magnitude faster and use much less CPU/memory/disk space, which means smaller build artifacts and thus faster transfer/download.

gigatexal 9y ago

lack of window functions kill it's usefulness for me.

contingencies 9y ago
Official SQLite response http://sqlite.1065341.n5.nabble.com/Window-functions-td77470.html http://sqlite.1065341.n5.nabble.com/Window-functions-td77470...
- wongarsu 9y ago
  So in essence it's a lot of work for a small functionality gain? There are some things that are inconvenient to do without window functions, but my primary use for them is simple optimization. Most of the cases where I've used window function in postgres, the query would be trivial to rewrite to use a subquery instead. Window functions always led to a much faster query that scaled much better over large query sets.
  - gigatexal 9y ago
    Exactly why I miss them so much.
- gigatexal 9y ago
  Reading through the responses just screams: not worth the effort. I know it’s an open source volunteer product and even with a commercial arm the lack of window functions (aren’t they part of the SQL standard?) is laughable.

coleifer 9y ago

There's an easy workaround: rename the table, create new table, insert into/select from.

mst 9y ago
The migration tools I work with have this already automated and (having been involved in the design of said automation) it's really not that difficult to add. Other DBMSes often have to do complete table rewrites for certain types of ALTER anyway - so while, yes, it's faintly annoying to have to do that in userspace rather than having the db engine do it for you, it's not nearly as big a disadvantage as it first appears.

carry_bit 9y ago

You can add columns at least.