6 ms·

SQLite is not easy to use with migrations, because it doesn't support many ALTER TABLE options [1]: you need to create a new table instead of modifying a column

• llimllib 3y ago
  simonw's sqlite-utils can be helpful for executing table changes: https://sqlite-utils.datasette.io/en/stable/python-api.html#transforming-a-table https://sqlite-utils.datasette.io/en/stable/python-api.html#... https://sqlite-utils.datasette.io/en/stable/cli-reference.html#transform https://sqlite-utils.datasette.io/en/stable/cli-reference.ht... > Also it doesn't allow you to use multiple application servers. Not in the same way you use postgres etc, but you can do it with sharding or with LiteFS, but you do have to consider carefully how you scale your app. I'm not _really_ disagreeing with you, but I think you're painting with a bit too broad of a brush.
  • modo_ 3y ago
    Thanks! Came to this thread hoping for something like these. They look useful!
• yawaramin 3y ago
  > you need to create a new table instead of modifying a column If modifying the column's type, yes. But you don't if you're just renaming, adding, or dropping a column. > column types are not checked and you can easily insert a string into numeric column. Can be fixed with CHECK constraints in the table definitions. > Also it doesn't allow you to use multiple application servers. This is mentioned in OP. > So it can be used only with small, simple sites. It can be used in a large variety of applications, but it's more appropriate for some than for others.
• simonw 3y ago
  I have a pretty robust, well-tested solution for advanced SQLite ALTER TABLE here: https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
• Timon3 3y ago
  > Also, column types are not checked and you can easily insert a string into numeric column. This is only true if you don't use strict tables (implemented end of 2021): https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html If you define a table as strict, the data is coerced, and if not successful an error is thrown.
  • mickeyp 3y ago
    > If you define a table as strict, the data is coerced, and if not successful an error is thrown. Coercing the data is hardly better. Now you have data integrity issues and you have no errors!
    • Sammi 3y ago
      SQLite attempts to coerce the data into the appropriate type using the usual affinity rules, as PostgreSQL, MySQL, SQL Server, and Oracle all do. If the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
  • IshKebab 3y ago
    > the data is coerced Seriously? And there I was thinking they actually fixed this mistake... they just made another schoolboy error.
  • Mawr 3y ago
    To clarify the bit about coercion: "SQLite attempts to coerce the data into the appropriate type using the usual affinity rules, as PostgreSQL, MySQL, SQL Server, and Oracle all do."
• euroderf 3y ago
  > foreign keys are ignored by default This can be fixed in the connection string. > column types are not checked Now available, the STRICT keyword.
  • graemep 3y ago
    > This can be fixed in the connection string. Not a solid guarantee. More prone to bugs, errors, etc. What is someone changes something using the comnandline client and forgets to issue the pragma command? I would feel a lot happier using SQLite if this was a per DB setting rather than a per connection one.
    • angra_mainyu 3y ago
      > Not a solid guarantee How so? Are you saying that sqlite would ignore the connection string?
      • progmetaldev 3y ago
        An older version that doesn't support the feature will probably just ignore the addition to the connection string.
      • progmetaldev 3y ago
        https://sqlite.org/foreignkeys.html#fk_enable https://sqlite.org/foreignkeys.html#fk_enable Judging by the documentation, if you issue a PRAGMA foreign_keys; and no row is returned containing a 0 or 1, then you are using an unsupported version of SQLite, or the library was compiled with foreign key support disabled. I am struggling to find any documentation that states if anything will occur if enabling foreign keys in the connection string, when the version does not support foreign keys.
      • graemep 3y ago
        I mean it is too easy to get wrong through human error or buggy application code.
  • rrdharan 3y ago
• Cthulhu_ 3y ago
  There's a workaround for the alter table flaw, but it's a bit involved and takes longer - create a new table with the updated fields, move all data, remove old table, rename the new table. I'm sure there's a historical or valid reason for it, it's just annoying.
• Svip 3y ago
  > numeric column Worth mentioning that numeric in Sqlite is still just a float.[0] A table of two rows where column a is 0.1 and 0.2, respectively, sum(a) will not yield 0.3. [0] https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
  • LispSporks22 3y ago
    Huh what do folks use for money in Sqlite?
    • Yiin 3y ago
      same thing you should use in any data store for money, integers
      • kruador 3y ago
        In Microsoft SQL Server you should use MONEY or DECIMAL. In Oracle you should use NUMBER. In Postgres you should use MONEY or NUMERIC. In MySQL you should use NUMERIC or DECIMAL. In IBM DB2 you should use DECIMAL. These are all backed by integer data storage and arithmetic, but the database handles scaling the values for you, to whatever number of decimal places you have configured. SQL Server and Postgres MONEY type will additionally format values with a currency symbol, when converted to a character string. In SQLite you're out of luck - if you want accounting values you'll have to store them as integers and scale the values yourself. Source: I work on a mobile app with offline storage of pricing and weighed quantities, using SQLite in the app, and SQL Server on the back end.
        • mercxry 3y ago
          You should still not use MONEY in Postgres, even their own website says so [1]. I still wouldn't use floats/numerics/decimals to store currency either in any db generally, as said by others you're going to end up with inaccurate numbers [2]. Therefore using integers is in fact very good for this use-case, especially if you are in accounting or book-keeping! Source: I work for a Fintech company that processes millions of payments. [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... [2] https://www.youtube.com/watch?v=PZRI1IfStY0 https://www.youtube.com/watch?v=PZRI1IfStY0
• sorenbs 3y ago
  At Prisma, the "twelve easy steps" for performing a table change in SQLite documented on https://sqlite.org/lang_altertable.html https://sqlite.org/lang_altertable.html is a bit of a running joke. SQLite is super versatile, but very different from other more traditional RDMBS.
  • rmbyrro 3y ago
    A bit of unfair comment. These steps apply only to ALTER statements that are NOT supported natively by SQLite. The natively supported statements are as easy to perform as in MySQL or Postgres, for example. > "The only schema altering commands directly supported by SQLite are the 'rename table', 'rename column', 'add column', 'drop column' commands shown above. However, applications can make other arbitrary changes to the format of a table using a simple sequence of operations.
    • yencabulator 3y ago
      The whole point is how few ALTER TABLE statements are actually supported by SQLite.
      • rmbyrro 3y ago
        The original comments talks nothing about that, actually. If that was the purpose, why not say that instead?