4 ms·

I want to hear "What's not great using SQLite compare to PostgreSQL" instead .

Archelaos 3y ago
A short use-case comparison with client/server database engines in general can be found on the SQLite Web-site itself: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
frou_dh 3y ago
Poverty in terms of datatypes available to use.
- mjevans 3y ago
  Offhand, Date types are really strings in SQLite. Personally I'm not yet convinced that the normal SQL representation for what a 'date' object is matches real world use cases that well, or at least doesn't cover all of them. As a programmer, what I find I want is a 'moment' which retains the input specification. Possibly in a sanitized binary format that's not the literal text, but also isn't a single numeric value either. E.G. Timezone Specifier / City (not just one per TZ!) + Human timespec (5 PM Tuesday the Whatever day of Month in Year) AND a pair of evaluation rules version and 'effective UTC||TAI time' for comparisons. The evaluation version thing needn't be a full version it might even just be a couple bits at the top or bottom of a long range 64 or 128 etc time number of some units. Something that can be used to determine if a value still needs to be updated with the latest rules set rather than using the cached representation.
  - chrismorgan 3y ago
    > Offhand, Date types are really strings in SQLite. It’s more that there’s no such thing as a date type, but that date and time functions can work with text or numbers, in a few different interpretations. Documentation: https://www.sqlite.org/lang_datefunc.html https://www.sqlite.org/lang_datefunc.html.
    - chasil 3y ago
      If you load the ODBC driver version of SQLite, there is an option to transparently convert the ODBC date time expressions into Oracle style dates. It's an option for those who want easier handling. "When the DSN Option "JDConv" (Julian Day conversion) is enabled the SQLite 3 driver translates floating point column data interpreted as Julian Day to/from SQL_DATE, SQL_TIME, and SQL_TIMESTAMP data types (supported since May 2013)." http://www.ch-werner.de/sqliteodbc/html/index.html http://www.ch-werner.de/sqliteodbc/html/index.html
  - chrisweekly 3y ago
    Good point / ideas about SQL date objects... but you wrote about date in response to a comment about datatypes. Is your point that datatypes more generally may be less useful than parent implied?
quickthrower2 3y ago
No kitchen sink?
citrin_ru 3y ago
SQLite is not a good choice if you want to access a DB over the network from different hosts or even from many processes on the same host (later may work good in some cases but not all). Large write (insert/update) volume is also not the best load for SQLite. And of course where replication is needed I would prefer PostgreSQL or MySQL with mature replication support to things like SQLite+litestream.
- biorach 3y ago
  > SQLite is not a good choice if you want to access a DB over the network from different hosts or even from many processes on the same host That's a bit like saying that a family car is not suited for heavy goods transport.
  - GolDDranks 3y ago
    Indeed, but that was an answer to the question asked.
chasil 3y ago
No foreign keys by default. Using them means that every connecting session must toggle it on. Forget one and it will be free to violate referential integrity. One writer. Any session that issues BEGIN TRANSACTION and then hangs halts all dml. WAL mode confusion. WAL cannot safely be used on network filesystems, and it breaks ACID on ATTACHed databases, among other problems. Date and time types don't really exist. There are functions to assemble your own, but it does require some thought. The ODBC driver for SQLite does have options to "emulate" this. Length specifications on a column are ignored. CHAR(2) will allow the insertion of a blob. I think that check constraints could be used to enforce this. Type affinity means that any data type can be inserted into columns declared as any other data type. Rigid type enforcement can be done, but it is not the default. Those are the major eye-openers.
- anitil 3y ago
  I believe there is a recent (optional) strict mode that will enforce types. I haven't used it so I don't know how full-featured it is. Your other points are of course correct
  - chrismorgan 3y ago
    Unfortunately, strict mode is incompatible with name-based type mapping: if you have a column that you want to be an RFC 3339 date-time (e.g. "2023-06-06T23:25:27+11:00"), for example, the only spelling permitted for the column type is “TEXT”, whereas without strict mode you can name it “DATETIME” or similar and popular libraries in various languages will automatically map that to the language’s datetime type (according to local conventions, not anything universally defined—e.g. https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html shows the built-in mappings the Rust sqlx library has decided to support). This destroys the ergonomics of strict mode for me, rendering strict mode quite undesirable, since it’s basically fighting with a more useful sort of strictness. (I wrote a little more about this, with examples, at https://news.ycombinator.com/item?id=33282830 https://news.ycombinator.com/item?id=33282830.)
- norir 3y ago
  These are all fair points though they mostly can be addressed at the application layer. One could, for example, use a wrapper that opens the sqlite library and immediately turns foreign keys on. One does this once per language in your project and never think about it again. Similarly, type constraints can be enforced at the application layer. I agree that it would be nice if some of these things were built in but at least sqlite is reliable when configured and used in particular ways.
jasode 3y ago
>I want to hear "What's not great using SQLite compare to PostgreSQL" instead . The context of this thread's article is about "edge computing" which means resource-constrained endpoints like IoT, mobile devices, cloud "workers" on CDN, etc. Those scenarios will inherently favor a lightweight database like SQLite over a full-blown heavyweight RDBMS like Postgresql/MySQL. RDBMS engines have extra code and cpu/RAM requirements for handling multi-user concurrency, locking, etc. You don't need all that machinery for edge computing. Another example of the above tradeoffs is smartphones like iPhone and Android. Their default persistence API framework in iOS/Android use embedded SQLite as the local backing database. It doesn't make sense for a battery-powered device to waste cpu and RAM on a multi-user Postgresql/MySQL engine when there's only a single user of a smartphone.

A short use-case comparison with client/server database engines in general can be found on the SQLite Web-site itself: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

frou_dh 3y ago

Poverty in terms of datatypes available to use.

mjevans 3y ago
Offhand, Date types are really strings in SQLite. Personally I'm not yet convinced that the normal SQL representation for what a 'date' object is matches real world use cases that well, or at least doesn't cover all of them. As a programmer, what I find I want is a 'moment' which retains the input specification. Possibly in a sanitized binary format that's not the literal text, but also isn't a single numeric value either. E.G. Timezone Specifier / City (not just one per TZ!) + Human timespec (5 PM Tuesday the Whatever day of Month in Year) AND a pair of evaluation rules version and 'effective UTC||TAI time' for comparisons. The evaluation version thing needn't be a full version it might even just be a couple bits at the top or bottom of a long range 64 or 128 etc time number of some units. Something that can be used to determine if a value still needs to be updated with the latest rules set rather than using the cached representation.
- chrismorgan 3y ago
  > Offhand, Date types are really strings in SQLite. It’s more that there’s no such thing as a date type, but that date and time functions can work with text or numbers, in a few different interpretations. Documentation: https://www.sqlite.org/lang_datefunc.html https://www.sqlite.org/lang_datefunc.html.
  - chasil 3y ago
    If you load the ODBC driver version of SQLite, there is an option to transparently convert the ODBC date time expressions into Oracle style dates. It's an option for those who want easier handling. "When the DSN Option "JDConv" (Julian Day conversion) is enabled the SQLite 3 driver translates floating point column data interpreted as Julian Day to/from SQL_DATE, SQL_TIME, and SQL_TIMESTAMP data types (supported since May 2013)." http://www.ch-werner.de/sqliteodbc/html/index.html http://www.ch-werner.de/sqliteodbc/html/index.html
- chrisweekly 3y ago
  Good point / ideas about SQL date objects... but you wrote about date in response to a comment about datatypes. Is your point that datatypes more generally may be less useful than parent implied?

quickthrower2 3y ago

No kitchen sink?

citrin_ru 3y ago

SQLite is not a good choice if you want to access a DB over the network from different hosts or even from many processes on the same host (later may work good in some cases but not all). Large write (insert/update) volume is also not the best load for SQLite. And of course where replication is needed I would prefer PostgreSQL or MySQL with mature replication support to things like SQLite+litestream.

biorach 3y ago
> SQLite is not a good choice if you want to access a DB over the network from different hosts or even from many processes on the same host That's a bit like saying that a family car is not suited for heavy goods transport.
- GolDDranks 3y ago
  Indeed, but that was an answer to the question asked.

chasil 3y ago

No foreign keys by default. Using them means that every connecting session must toggle it on. Forget one and it will be free to violate referential integrity. One writer. Any session that issues BEGIN TRANSACTION and then hangs halts all dml. WAL mode confusion. WAL cannot safely be used on network filesystems, and it breaks ACID on ATTACHed databases, among other problems. Date and time types don't really exist. There are functions to assemble your own, but it does require some thought. The ODBC driver for SQLite does have options to "emulate" this. Length specifications on a column are ignored. CHAR(2) will allow the insertion of a blob. I think that check constraints could be used to enforce this. Type affinity means that any data type can be inserted into columns declared as any other data type. Rigid type enforcement can be done, but it is not the default. Those are the major eye-openers.

anitil 3y ago
I believe there is a recent (optional) strict mode that will enforce types. I haven't used it so I don't know how full-featured it is. Your other points are of course correct
- chrismorgan 3y ago
  Unfortunately, strict mode is incompatible with name-based type mapping: if you have a column that you want to be an RFC 3339 date-time (e.g. "2023-06-06T23:25:27+11:00"), for example, the only spelling permitted for the column type is “TEXT”, whereas without strict mode you can name it “DATETIME” or similar and popular libraries in various languages will automatically map that to the language’s datetime type (according to local conventions, not anything universally defined—e.g. https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html shows the built-in mappings the Rust sqlx library has decided to support). This destroys the ergonomics of strict mode for me, rendering strict mode quite undesirable, since it’s basically fighting with a more useful sort of strictness. (I wrote a little more about this, with examples, at https://news.ycombinator.com/item?id=33282830 https://news.ycombinator.com/item?id=33282830.)
norir 3y ago
These are all fair points though they mostly can be addressed at the application layer. One could, for example, use a wrapper that opens the sqlite library and immediately turns foreign keys on. One does this once per language in your project and never think about it again. Similarly, type constraints can be enforced at the application layer. I agree that it would be nice if some of these things were built in but at least sqlite is reliable when configured and used in particular ways.

jasode 3y ago

>I want to hear "What's not great using SQLite compare to PostgreSQL" instead . The context of this thread's article is about "edge computing" which means resource-constrained endpoints like IoT, mobile devices, cloud "workers" on CDN, etc. Those scenarios will inherently favor a lightweight database like SQLite over a full-blown heavyweight RDBMS like Postgresql/MySQL. RDBMS engines have extra code and cpu/RAM requirements for handling multi-user concurrency, locking, etc. You don't need all that machinery for edge computing. Another example of the above tradeoffs is smartphones like iPhone and Android. Their default persistence API framework in iOS/Android use embedded SQLite as the local backing database. It doesn't make sense for a battery-powered device to waste cpu and RAM on a multi-user Postgresql/MySQL engine when there's only a single user of a smartphone.