Hacker News Reader

Comment by codedokode | Hacker News Reader

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, for example. Also, foreign keys are ignored by default and you need to explicitly enable them after connecting.

Also, column types are not checked and you can easily insert a string into numeric column.

Also it doesn't allow you to use multiple application servers.

So it can be used only with small, simple sites.

[1] https://www.sqlite.org/lang_altertable.html

View on HN

> 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

If you define a table as strict, the data is coerced, and if not successful an error is thrown.

> 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!

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

> the data is coerced

Seriously? And there I was thinking they actually fixed this mistake... they just made another schoolboy error.

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."

I have a pretty robust, well-tested solution for advanced SQLite ALTER TABLE here: https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

> 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

Huh what do folks use for money in Sqlite?

same thing you should use in any data store for money, integers

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.

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... [2] https://www.youtube.com/watch?v=PZRI1IfStY0

Using a fixed point representation doesn't solve your SUM() problem.

If you add 0.1 to 1000000 with an integer representation scaled by 10^6 then you are still boned.

Binary floats may actually produce a better result here.

What you really want is a decimal floating point type with a suitable amount of significant digit precision.

int64?

You've checked this? Perhaps you're correct in a general sense, but in this specific case, neither "number" nor "numeric" datatypes show this issue.

    > sqlite3 --interactive ./floats.db
    SQLite version 3.37.2 2022-01-06 13:25:41
    Enter ".help" for usage hints.
    sqlite> create table floats(colA number);
    sqlite> insert into floats(colA) values (0.1);
    sqlite> insert into floats(colA) values (0.2);
    sqlite> select sum(colA) from floats;
    0.3
    sqlite> create table floats2(colA numeric);
    sqlite> insert into floats2(colA) values (0.1);
    sqlite> insert into floats2(colA) values (0.2);
    sqlite> select sum(colA) from floats2;
    0.3
    sqlite>

At Prisma, the "twelve easy steps" for performing a table change in SQLite documented on 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.

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.

The whole point is how few ALTER TABLE statements are actually supported by SQLite.

The original comments talks nothing about that, actually. If that was the purpose, why not say that instead?

simonw's sqlite-utils can be helpful for executing table changes:

https://sqlite-utils.datasette.io/en/stable/python-api.html#...

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.

Thanks! Came to this thread hoping for something like these. They look useful!

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.

> foreign keys are ignored by default

This can be fixed in the connection string.

> column types are not checked

Now available, the STRICT keyword.

Coercion isn’t necessarily what you want. See discussion here (when STRICT was introduced):

https://news.ycombinator.com/item?id=28259104

It’s also only two years old which is forever in the web world and brand new by Databases ops/maturity standards. There are likely still warts waiting to be discovered (there always are, but the discovery rate tapers over decades).

> 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.

> Not a solid guarantee

How so? Are you saying that sqlite would ignore the connection string?

An older version that doesn't support the feature will probably just ignore the addition to the connection string.

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.

I mean it is too easy to get wrong through human error or buggy application code.

> 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.

Reply on news.ycombinator.com