5 ms·

By choosing SQLite. No server process and a single file per DB which I can put wherever I like.

mort96 3y ago
I like SQLite. But I really wish its default behavior wasn't to simply allow garbage data into the database. If I have an int column, don't let me accidentally store a string.
- eesmith 3y ago
  https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html "In a CREATE TABLE statement, if the "STRICT" table-option keyword is added to the end, after the closing ")", then strict typing rules apply to that table. ... The STRICT keyword at the end of a CREATE TABLE statement is only recognized by SQLite version 3.37.0 (2021-11-27) and later. sqlite> create table x (a int); sqlite> insert into x values ('hello'); sqlite> select * from x; hello sqlite> drop table x; sqlite> create table x (a int) strict; sqlite> insert into x values ('hello'); Runtime error: cannot store TEXT value in INT column x.a (19)
  - mort96 3y ago
    Yeah, I know about that, and I'm doing that on all my tables these days. It's just sad that the default behaviour is to allow garbage data, and that if you ever forget to put 'strict' on your tables you'll have a perfectly functional application with no sign that anything is wrong until you suddenly find corrupt data in your database.
- euroderf 3y ago
  create table strict
lisasays 3y ago
SQLite is great for its scope - but not in the same class as a full-fledged RDBMS.
- MrThoughtful 3y ago
  Can you give a specific example of what you are missing when using SQLite?
  - endisneigh 3y ago
    Multi writer, access through the internet without 3rd party software, etc.
  - mort96 3y ago
    At a certain scale, you'll want replication or replication, which SQLite doesn't really do AFAIK. At a scale below that, you'll probably want to be able to have multiple web servers talking to one database server, which SQLite doesn't really do either. I also think SQLite's performance during heavy write workloads is worse than PostgreSQL's? Basically, AFAIK, SQLite becomes problematic once you need more than one computer to handle requests.
    - justinclift 3y ago
      Just to point out, there are now SQLite replication and various "distributed database" projects which seem to work fairly well. They're probably not as battle tested as the PostgreSQL ones, but they are around, have users, and are actively developed. The ones I remember off the top of my head: * https://litestream.io https://litestream.io * https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite <-- more of a "distributed database using RAFT" type of thing * https://github.com/canonical/dqlite https://github.com/canonical/dqlite
      - otoolep 3y ago
        rqlite[1] creator here, happy to answer any questions. [1] https://www.rqlite.io https://www.rqlite.io
      - mort96 3y ago
        Yeah, there are SQLite forks or products built on SQLite which have these sorts of features, but SQLite doesn't. They remains reasons why someone may want to use another database (such as PostgreSQL, or an SQLite fork, or a database which uses SQLite as a storage engine) instead of SQLite. Honestly though, if I need these sorts of distribution features, I would probably prefer the database to have them built in. I don't really see the point in using SQLite at that scale.
jjav 3y ago
> By choosing SQLite. It's a good reminder to give some thought to whether one actually needs MySQL|Postgres. If not, SQLite is the way to go. Most of my code that uses a DB is using SQLite. But obviously, if you actually need MySQL|Postgres then SQLite is not an option.

I like SQLite. But I really wish its default behavior wasn't to simply allow garbage data into the database. If I have an int column, don't let me accidentally store a string.

eesmith 3y ago
https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html "In a CREATE TABLE statement, if the "STRICT" table-option keyword is added to the end, after the closing ")", then strict typing rules apply to that table. ... The STRICT keyword at the end of a CREATE TABLE statement is only recognized by SQLite version 3.37.0 (2021-11-27) and later. sqlite> create table x (a int); sqlite> insert into x values ('hello'); sqlite> select * from x; hello sqlite> drop table x; sqlite> create table x (a int) strict; sqlite> insert into x values ('hello'); Runtime error: cannot store TEXT value in INT column x.a (19)
- mort96 3y ago
  Yeah, I know about that, and I'm doing that on all my tables these days. It's just sad that the default behaviour is to allow garbage data, and that if you ever forget to put 'strict' on your tables you'll have a perfectly functional application with no sign that anything is wrong until you suddenly find corrupt data in your database.
euroderf 3y ago
create table strict

lisasays 3y ago

SQLite is great for its scope - but not in the same class as a full-fledged RDBMS.

MrThoughtful 3y ago
Can you give a specific example of what you are missing when using SQLite?
- endisneigh 3y ago
  Multi writer, access through the internet without 3rd party software, etc.
- mort96 3y ago
  At a certain scale, you'll want replication or replication, which SQLite doesn't really do AFAIK. At a scale below that, you'll probably want to be able to have multiple web servers talking to one database server, which SQLite doesn't really do either. I also think SQLite's performance during heavy write workloads is worse than PostgreSQL's? Basically, AFAIK, SQLite becomes problematic once you need more than one computer to handle requests.
  - justinclift 3y ago
    Just to point out, there are now SQLite replication and various "distributed database" projects which seem to work fairly well. They're probably not as battle tested as the PostgreSQL ones, but they are around, have users, and are actively developed. The ones I remember off the top of my head: * https://litestream.io https://litestream.io * https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite <-- more of a "distributed database using RAFT" type of thing * https://github.com/canonical/dqlite https://github.com/canonical/dqlite
    - otoolep 3y ago
      rqlite[1] creator here, happy to answer any questions. [1] https://www.rqlite.io https://www.rqlite.io
    - mort96 3y ago
      Yeah, there are SQLite forks or products built on SQLite which have these sorts of features, but SQLite doesn't. They remains reasons why someone may want to use another database (such as PostgreSQL, or an SQLite fork, or a database which uses SQLite as a storage engine) instead of SQLite. Honestly though, if I need these sorts of distribution features, I would probably prefer the database to have them built in. I don't really see the point in using SQLite at that scale.

jjav 3y ago

> By choosing SQLite. It's a good reminder to give some thought to whether one actually needs MySQL|Postgres. If not, SQLite is the way to go. Most of my code that uses a DB is using SQLite. But obviously, if you actually need MySQL|Postgres then SQLite is not an option.