5 ms·

SQLite: I enjoy using reliable and correct databases even at the cost of scalability

chipotle_coyote 6y ago
I admit I was kind of thinking that, even though I appreciated the humor. :) I imagine an awful lot of web sites out there would do just fine with SQLite as their back end.
- phyzome 6y ago
  It's great for small, read-only websites that aren't quite simple enough to just be static HTML sites. Also for desktop app config/data DBs.
  - risyachka 6y ago
    Can you elaborate? I've seen benchmarks and from their website what I understood is that it can handle really massive reads and writes, tens (maybe hundreds) thousands of ops per second, but personally never tested to this extent.
    - stickupkid 6y ago
      It really can (LXD cloud setup from personal experience), the problem is that if you don't serialise your writes then yeah, fun times to be had. There are compromises for all databases. People just like telling others their opinion as fact, and how wrong everybody is apart from themselves of course.
    - kuschku 6y ago
      We're using it in Quassel, and as soon as you go over ~3-4 parallel write/read threads, it starts locking up completely, sometimes taking 30 seconds for simple queries that should really take milliseconds. The big issue is that sqlite does full db locking for any operation, so during any write you can't just easily read at all. This can be fixed with WAL mode, but WAL mode is broken in uts early versions, and new versions of sqlite aren't in all disteos yet, despite being out for almost a decade. And even WAL mode gets abysmal performance.
      - yread 6y ago
        Which version 'fixes' WAL in your opinion?
      - phyzome 6y ago
        As long as you're only doing reads, though, you can forego the locking and it's pretty nice! Definitely would not use in a read/write situation for a web API for various reasons, though. :-)
erik_seaberg 6y ago
SQLite has always intentionally failed to report this error: sqlite> create table foo (n int); sqlite> insert into foo (n) values ('dave'); sqlite> select count(*) from foo where n = 'dave'; 1
- phyzome 6y ago
  That is documented as a feature. https://www.sqlite.org/different.html https://www.sqlite.org/different.html under "Manifest typing". SQLite does not use column typing except in integer primary keys.
  - erik_seaberg 6y ago
    SQLite accepts a type constraint without enforcing it. That's a bug just like MySQL sometimes accepting a foreign key constraint without enforcing it.
    - camgunz 6y ago
      It's not a type constraint. It's a hint to SQLite to try and coerce values when it can. Here's what that link parent posted says: > As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python. It's intended behavior that's compatible with the SQL spec.
      - OOPMan 6y ago
        That doesn't change the fact that it's not a good idea.
        kirstenbirgit 6y ago

I admit I was kind of thinking that, even though I appreciated the humor. :) I imagine an awful lot of web sites out there would do just fine with SQLite as their back end.

phyzome 6y ago
It's great for small, read-only websites that aren't quite simple enough to just be static HTML sites. Also for desktop app config/data DBs.
- risyachka 6y ago
  Can you elaborate? I've seen benchmarks and from their website what I understood is that it can handle really massive reads and writes, tens (maybe hundreds) thousands of ops per second, but personally never tested to this extent.
  - stickupkid 6y ago
    It really can (LXD cloud setup from personal experience), the problem is that if you don't serialise your writes then yeah, fun times to be had. There are compromises for all databases. People just like telling others their opinion as fact, and how wrong everybody is apart from themselves of course.
  - kuschku 6y ago
    We're using it in Quassel, and as soon as you go over ~3-4 parallel write/read threads, it starts locking up completely, sometimes taking 30 seconds for simple queries that should really take milliseconds. The big issue is that sqlite does full db locking for any operation, so during any write you can't just easily read at all. This can be fixed with WAL mode, but WAL mode is broken in uts early versions, and new versions of sqlite aren't in all disteos yet, despite being out for almost a decade. And even WAL mode gets abysmal performance.
    - yread 6y ago
      Which version 'fixes' WAL in your opinion?
    - phyzome 6y ago
      As long as you're only doing reads, though, you can forego the locking and it's pretty nice! Definitely would not use in a read/write situation for a web API for various reasons, though. :-)

erik_seaberg 6y ago

SQLite has always intentionally failed to report this error: sqlite> create table foo (n int); sqlite> insert into foo (n) values ('dave'); sqlite> select count(*) from foo where n = 'dave'; 1

phyzome 6y ago
That is documented as a feature. https://www.sqlite.org/different.html https://www.sqlite.org/different.html under "Manifest typing". SQLite does not use column typing except in integer primary keys.
- erik_seaberg 6y ago
  SQLite accepts a type constraint without enforcing it. That's a bug just like MySQL sometimes accepting a foreign key constraint without enforcing it.
  - camgunz 6y ago
    It's not a type constraint. It's a hint to SQLite to try and coerce values when it can. Here's what that link parent posted says: > As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python. It's intended behavior that's compatible with the SQL spec.
    - OOPMan 6y ago
      That doesn't change the fact that it's not a good idea.
      - kirstenbirgit 6y ago