6 ms·

Glad to see SQLite on then front page -- it's one of the silent workhorses of the modern programming/database world. It is the most widely deployed database[0]

Glad to see SQLite on then front page -- it's one of the silent workhorses of the modern programming/database world. It is the most widely deployed database[0]. If you haven't given it a look/aren't interested in it since it seems to be a "toy" database (often the "test" or "local" db for frameworks like rails or django), you owe it to yourself to see what it can really do. Easy to use full text search[1][2], CTEs[3], JSON support via extension[4] (also the extension system is worth looking at[5]) and much much more. There are certainly things that SQLite does not do, and that's well documented too[6]. If all this doesn't convince you to use SQLite, it's also one of the most well documented large C codebases that is fantastic to learn from. I'd go as far as to say that many modern startups could get pretty far with SQLite + aggressive caching before even bringing in a big database like Postgres (though with recent deployment/ops advancements it's easier than ever to run postgres). [0]: https://sqlite.org/mostdeployed.html https://sqlite.org/mostdeployed.html [1]: https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html [2]: https://sqlite.org/fts3.html https://sqlite.org/fts3.html [3]: https://sqlite.org/lang_with.html https://sqlite.org/lang_with.html [4]: https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html [5]: https://www.sqlite.org/loadext.html https://www.sqlite.org/loadext.html [6]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

dmarlow 7y ago
Apparently clustering too https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- minxomat 7y ago
  Hardly, that is more of a hack. There is however a distributed, clustered version of sqlite: Bloomberg's Comdb2. It has the same scaling factors as e.g. percona, can be Geo-replicated (multi-roomed) and has many advanced timing and financial data types. Plus SQL functions can be written in Lua. Oh, and it has super granular serialization guarantees, up to and including full linearizibility. By using Sqlite as the SQL frontend, it retains features other NewSQL engines struggle with (recursive CTE, deep optimization, compatibility with existing queries etc)
  - e12e 7y ago
    Wow, thats: https://github.com/bloomberg/comdb2 https://github.com/bloomberg/comdb2 Looks interesting. Any experience using it in real projects?
    - minxomat 7y ago
      Yeah it works well. I also sometimes push code into the db for faster execution (sorry on mobile, can't detail now). Haven't tested out Geo replication though, but it's pretty straightforward. Clock drift maximum and all that.
- hardwaresofton 7y ago
  Also there is dqlite[0]. I really do think that SQLite can go even farther than people think, but the exploration makes less and less sense the better and easier to run options like postgres become. That said, I'm glad projects like dqlite and rqlite exist. There are also previous threads discussing them but here's one[1]. [0]: https://github.com/CanonicalLtd/dqlite https://github.com/CanonicalLtd/dqlite [1]: https://news.ycombinator.com/item?id=20836331 https://news.ycombinator.com/item?id=20836331
  - speedplane 7y ago
    > I really do think that SQLite can go even farther than people think I know different databases support different features (e.g., atomic transactions, ACID compliance, etc.), but it seems like the big differentiator is size, namely: 1. Can your database live within a single computer's RAM? 2. Can your database live within a single computer? 3. Can your database live across a cluster of computers in a single data center? 4. Can your database live across a cluster of computers spread across the planet? Once you pick your category from the above, there are still choices to be made, but the "best" options narrow down significantly.
    - hardwaresofton 7y ago
      I'd argue that feature set (transactions, ACID, etc) are a bigger differentiator because they drive/change application development -- this can have drastic effects on application features and complexity whether immediately or down the road. I agree that the possible second biggest (if not the first) differentiator is size though -- despite the amount of choices out there, once you start narrowing down answering those questions, many options fall away very quickly. I'd also add on that the only real way to scale a database is to shard. There are a bunch of tricks you can do to improve throughput and those are always welcome, and sometimes I'll see articles that proclaim "we just changed to a system with 256GB of RAM to fix our scaling problem!" -- but these improvements don't get past the basic premise that once your data does not fit on one computer/harddrive you need to start figuring out a way for multiple to coordinate. You need to shard, and you need to organize your data in such a way that it can shard, then you need to start worrying about data access patterns. There's a lot of space before something like this is necessary (you really do have to have a lot of data for it to not fit on one machine these days), but I think people don't see this chain coming fast/often enough.
dmarlow 7y ago
I only wish I could do some basic things without having to create temporary tables and copy data around.
- gigatexal 7y ago
  Yeah that’s likely the one thing that keeps it from being perfect.
- hardwaresofton 7y ago
  I'm not sure exactly what the use case is but another awesome tool that's recently been making the rounds is datasette[0], it provides an easy to use data exploration point for SQLite databases. There's also a PyCon 2019 talk[1]. [0]: https://datasette.readthedocs.io/en/stable https://datasette.readthedocs.io/en/stable [1]: https://www.youtube.com/watch?v=pTr1uLQTJNE https://www.youtube.com/watch?v=pTr1uLQTJNE
kragen 7y ago
I only learned this week that it supports recursive CTEs (although apparently you can't refer to them in subqueries yet?) and only yesterday that you can do this: >>> import sqlite3 >>> con = sqlite3.connect(':memory:') >>> cur = con.cursor() >>> q = '''select x, sum(a * power(x, e)) ... from (select -2 as x union select -1 union select 0 union select 1 union select 2) xt, ... (select 1 as a, 2 as e union select 4, 1 union select 2, 0) et ... group by x''' >>> cur.execute(q).fetchall() Traceback (most recent call last): File "<stdin>", line 4, in <module> sqlite3.OperationalError: no such function: power >>> con.create_function('power', 2, lambda base, power: base**power) >>> cur.execute(q).fetchall() [(-2, -2), (-1, -1), (0, 2), (1, 7), (2, 14)] That is freaking cool. Not evaluating polynomials in SQL, I mean, but defining a user-defined function for SQL in one line of Python.
- emj 7y ago
  That is cool; Being able to call python code from SQL is very cool. Is it very slow?
  - kyberias 7y ago
    Being able to call python code from SQL is super scary!
    - sbierwagen 7y ago
      What attacks do you believe it enables?
  - oefrha 7y ago
    It simply uses the sqlite3_create_function API, so it’s as fast/slow as your Python function. You can even use Cython if pure Python isn’t fast enough. I use UDFs quite a bit and the performance is fine without extra tweaks most of the time, but for something like pow I’d use the math extension instead which would be way more performant. Btw, user-defined scalar functions aren’t remotely as sexy as user-defined window functions.
steve_adams_86 7y ago
Checkfront, a booking management system, got by on sqlite up until maybe 2 years ago - it was the solution for 5 or more years! These days it runs on postgres but it went very far without it.
Scarbutt 7y ago
But why?(for a server) Postgres can run in a 256MB VPS(if one can find one these days) and has more features that you might need later. Installing is as easy as 'apt-get install postgresql', create user, create database, done.
- xyzzy_plugh 7y ago
  Furthermore, postgres has great replication, and a read-replica is trivial to set up. sqlite is amazing (for any embedded database, absolutely) but postgres can't be beat as a go to for server side.
  - cyphar 7y ago
    To paraphrase the SQLite site: "SQLite isn't competing with [PostgreSQL]; it's competing with fopen(3)." The primary purpose is to replace custom (and fragile) single-file databases with something that is far more resilient and provides uniformity in implementation. The fact that it is a suprisingly good SQL database (to the point where folks are actively arguing that much more fully-fledged databases are better in some aspects) is incidental.
- hardwaresofton 7y ago
  SQLite is even easier to start with -- I'm not trying to say Postgres is not the right choice, but it is certainly a more complex choice. Postgres is a fantastic database, but most of the time getting started with and using SQLite will be easier. For example -- you'd never have to secure firewall rules to a SQLite instance. Bad defaults (unlikely but possible) or some bad post-install configuration and you've got an exposed server. I want to emphasize that my point about SQLite being viable for startups was with the assumption of a low-write high read workload -- most of the time queries won't even hit the DB but instead be served by some caching layer. It's also really easy to scale read only versions of your applications horizontally if all you've got to do is ship/swap out DB files (again at this point it might make more sense to look into a bigger DB and just scaling vertically).
- throwaway_bad 7y ago
  I have a small blog (using Ghost) that uses sqlite as the main database and it's incredibly easy to maintain. It's just a single file. For example if you want to back up, you just copy the file or add it to git. If you want to do a diff you just do sqldiff on two files. It is as easy as working with json or csv files, except with atomic writes and better querying. From their site: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html >SQLite does not compete with client/server databases. SQLite competes with fopen().
saagarjha 7y ago
> If you haven't given it a look/aren't interested in it since it seems to be a "toy" database As you mentioned, it ships in every major browser, most OSes, and a number of extremely popular instances of consumer software. If that's still a "toy" I don't know what isn't…
erik_seaberg 7y ago
sqlite can be handy but it's not a substitute for testing against a staging instance of another database platform. It ignores a lot of mistakes in column types and size limits and sometimes even foreign keys. If anything, I would want tests to be more strict than prod.
- hardwaresofton 7y ago
  Your point is correct, but I'm afraid you might not have understood what I meant -- I meant that people should be considering using SQLite in production. Agreed that testing against copies of production or staging instances that are as close as possible to production is important, but what I'm suggesting is that you could ride SQLite all the way into production if you wanted to. I'd love to hear more about the ignoring column types/size limit/other issues that you've run into though, have you written about them anywhere or is there an SO post? I haven't run into anything super egregious though I've had some problems with FKs before.
  - ris 7y ago
    Do a GROUP BY, and then try referencing a term which hasn't been either aggregated or mentioned in the GROUP BY clause. The result should be "abort! this query can't make sense!". The result (last I tried) is instead "sure, here's some arbitrary value from this group".
- marmaduke 7y ago
  It seeks to replace fopen() not postgres.
- justinclift 7y ago
  Foreign keys are supported with SQLite version 3.6.19 onward (~late 2009), though not enabled by default. https://sqlite.org/foreignkeys.html https://sqlite.org/foreignkeys.html If you can add "PRAGMA foreign_keys = ON;" to your testing script SQL calls - somewhere soon after connecting to SQLite - that enables it.
siscia 7y ago
Indeed, I believe as well that SQLite is underused in a lot of situations. Which is mostly the reason why I created RediSQL[1], to allow a more widespread use of it also in "modern"/"cloud native" application where you want to just connect to a database via the network, ideally through common API like the Redis one. It has the great benefit, that if you create your database in memory is crazy fast, and it retains the persistency properties of Redis, which are good enough in most cases. [1] https://RediSQL.com https://RediSQL.com
- jeff_redradish 7y ago
  Just FYI, on Chrome a 'Subscribe to the mail list' pop-up appears, and cannot be dismissed by clicking the X. Hitting Escape works.
  - siscia 7y ago
    Thanks so much! Sorry for the annoyance!
    - philips 7y ago
      Safari on iPadOS as well.
ngrilly 7y ago
> I'd go as far as to say that many modern startups could get pretty far with SQLite + aggressive caching before even bringing in a big database like Postgres Modern startups need their services to be available, and this is difficult to achieve with SQLite because you can't run long database migrations without blocking writes during the migration. Personally, I'd use SQLite more often if there was a good solution to this (and also DROP COLUMN).
- hardwaresofton 7y ago
  My statement was definitely a reach, but I think there is a bunch of space in the life of an early startup for lack of highly available services. I've found startups to prioritize time-to-market (or time to PoC) over technical purity -- there's usually lots of unplanned outages early on that often have nothing to do with the database or are a result of bad application code/misunderstood requirements as well, so I'm not fully buying the availability emphasis, but a few things that I think could counteract it: - I'm not sure I agree that the delay of the blocking write from a migration is so long as to cause significant (if any) damage to the business. - If SQLite makes development and deployment easier (I think it does), this becomes a tradeoff between a deployment downtime/partial operation window likely on the order of milliseconds and hours of time spent setting up/managing the alternatives. With really robust systems like Postgres out there this is a pretty weak argument on my part, but there is at least something there. - As I've also mentioned elsewhere, a high read-to-write workload would be less affected by this problem. If your startup produces aggregated business analytics dashboards for manufacturing plant stakeholders for example, your data set could be completely static. - WAL mode[0] improves SQLite's resiliency to concurrent readers/writers. - In the extreme case, you could treat SQLite as a hybrid row & JSON document store, and manage your own schemas (I do not endorse this approach), but you'd still be saving some time because you can do stuff like make use of SQLite's FTS search features, and you'd have access to other relational features that you might not get with other document stores. All this said, SQLite is not for lots of concurrent writers, and they so themselves[1], but I think it can absolutely be done. More to the point, I think that SQLite can help you get something out and easily deployable, and is not hard to move off of later. [0]: https://sqlite.org/wal.html https://sqlite.org/wal.html [1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
beatgammit 7y ago
We used SQLite for a while until we were limited by IO on our crappy SD cards for our embedded product and wanted a database server with more tuning options. Before that point, it was a very capable database, she I wouldn't hesitate to use it for pretty much any product that doesn't need high performance, cross thread access (basically any crud app) or the ability to run in a cluster/dedicated server (e.g. high storage requirements). It's fast, simple, and easy. Perhaps my favorite feature is that backups are just a copy (in fact, when I'm debugging an app, I often copy the database into a temp directory and go to town). I totally agree that SQLite is a great first option for startups. If your database will fit on your filesystem (gigabytes, not terabytes), it'll probably do really well. It's really easy to switch to something like Postgres later, and it's really nice to not have to worry about the complexities of a database server until you actually need them (e.g. you're considering hiring dedicated IT/DBA staff instead of just developers). And there's a good chance you'll never need anything other than SQLite.
bob1029 7y ago
I am glad to see other SQLite advocates out here. >Many modern startups could get pretty far with SQLite + aggressive caching Not only did we get "pretty far", we got to what is effectively a final destination for this generation of our product. We don't even need "aggressive caching" beyond what the filesystem provides to us by way of accessing the SQLite db file. Today, we have a single SQLite database persisting stateful data for an application that is used concurrently by 100+ users. I have benchmarked 512KB state update writes in a multithreaded scenario at >500 per second. 64KB updates exceed 1000 per second. It took us a lot of time and understanding to get to this point. At the end of the day with SQLite, less is always more. The first attempts we did involved connection pools, queues and all kinds of other fancy over-engineering in an attempt to extract more performance. We finally ended up with just the following: - Enable Write Ahead Logging - Set Synchronous to Normal - Use a single SQLiteConnection Justification for a single connection: We are using .NET with application-level locking and an RWLS where appropriate. This is in contrast to relying on the database to push these transactional scopes to disk. For us, losing an in-flight transaction tracked in memory is not catastrophic so we can tolerate this 1-100ms danger zone for the huge upside we get in throughput. This is a great example of something you can only do with SQLite. No hosted database solution offers this degree of exclusivity over your data store from an application perspective. One other application note to keep in mind is that SQLite (by default) operates in serialized mode, so the above approach is consistent regardless of the fact that we are hitting a single connection from multiple threads at once. The usages of RWLS are required in our case simply because we need to do multiple dependent sequential operations (select+modify+update) or insertion of new items (insert+last insert row id from connection). It is hypothetically possible to avoid locking altogether (aside from the implicit SQLite serialization) if you can structure your database such that you perform all operations in a single command - E.g.: Use application-generated keys upon insertion rather than waiting for the DB to generate one. Also, backups and restores are super trivial as mentioned elsewhere in this discussion. I have considered writing a backup agent directly into our software that is transactionally-aware. E.g.: During a backup request, I can take out a write lock on the RWLS for the SQLiteConnection while copying the database to a backup destination. Alternatively, since the DB lives on the same machine as the application, you can just take a single VM snapshot throughout the day, but this won't be as consistent a backup (tolerable for our use case though). The biggest advantage we see with using SQLite as our principal data store is that it has allowed us to completely avoid operational annoyances regarding how we actually ship our stack to various environments. .NET Self-Contained Deployments + SQLite = one bin folder that "just works". We were able to build custom deployment tooling because of the simplicity of this model, rather than relying on off-the-shelf tech that doesn't quite fit 100% into our use case.

dmarlow 7y ago

Apparently clustering too https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite

minxomat 7y ago
Hardly, that is more of a hack. There is however a distributed, clustered version of sqlite: Bloomberg's Comdb2. It has the same scaling factors as e.g. percona, can be Geo-replicated (multi-roomed) and has many advanced timing and financial data types. Plus SQL functions can be written in Lua. Oh, and it has super granular serialization guarantees, up to and including full linearizibility. By using Sqlite as the SQL frontend, it retains features other NewSQL engines struggle with (recursive CTE, deep optimization, compatibility with existing queries etc)
- e12e 7y ago
  Wow, thats: https://github.com/bloomberg/comdb2 https://github.com/bloomberg/comdb2 Looks interesting. Any experience using it in real projects?
  - minxomat 7y ago
    Yeah it works well. I also sometimes push code into the db for faster execution (sorry on mobile, can't detail now). Haven't tested out Geo replication though, but it's pretty straightforward. Clock drift maximum and all that.
hardwaresofton 7y ago
Also there is dqlite[0]. I really do think that SQLite can go even farther than people think, but the exploration makes less and less sense the better and easier to run options like postgres become. That said, I'm glad projects like dqlite and rqlite exist. There are also previous threads discussing them but here's one[1]. [0]: https://github.com/CanonicalLtd/dqlite https://github.com/CanonicalLtd/dqlite [1]: https://news.ycombinator.com/item?id=20836331 https://news.ycombinator.com/item?id=20836331
- speedplane 7y ago
  > I really do think that SQLite can go even farther than people think I know different databases support different features (e.g., atomic transactions, ACID compliance, etc.), but it seems like the big differentiator is size, namely: 1. Can your database live within a single computer's RAM? 2. Can your database live within a single computer? 3. Can your database live across a cluster of computers in a single data center? 4. Can your database live across a cluster of computers spread across the planet? Once you pick your category from the above, there are still choices to be made, but the "best" options narrow down significantly.
  - hardwaresofton 7y ago
    I'd argue that feature set (transactions, ACID, etc) are a bigger differentiator because they drive/change application development -- this can have drastic effects on application features and complexity whether immediately or down the road. I agree that the possible second biggest (if not the first) differentiator is size though -- despite the amount of choices out there, once you start narrowing down answering those questions, many options fall away very quickly. I'd also add on that the only real way to scale a database is to shard. There are a bunch of tricks you can do to improve throughput and those are always welcome, and sometimes I'll see articles that proclaim "we just changed to a system with 256GB of RAM to fix our scaling problem!" -- but these improvements don't get past the basic premise that once your data does not fit on one computer/harddrive you need to start figuring out a way for multiple to coordinate. You need to shard, and you need to organize your data in such a way that it can shard, then you need to start worrying about data access patterns. There's a lot of space before something like this is necessary (you really do have to have a lot of data for it to not fit on one machine these days), but I think people don't see this chain coming fast/often enough.

dmarlow 7y ago

I only wish I could do some basic things without having to create temporary tables and copy data around.

gigatexal 7y ago
Yeah that’s likely the one thing that keeps it from being perfect.
hardwaresofton 7y ago
I'm not sure exactly what the use case is but another awesome tool that's recently been making the rounds is datasette[0], it provides an easy to use data exploration point for SQLite databases. There's also a PyCon 2019 talk[1]. [0]: https://datasette.readthedocs.io/en/stable https://datasette.readthedocs.io/en/stable [1]: https://www.youtube.com/watch?v=pTr1uLQTJNE https://www.youtube.com/watch?v=pTr1uLQTJNE

kragen 7y ago

I only learned this week that it supports recursive CTEs (although apparently you can't refer to them in subqueries yet?) and only yesterday that you can do this: >>> import sqlite3 >>> con = sqlite3.connect(':memory:') >>> cur = con.cursor() >>> q = '''select x, sum(a * power(x, e)) ... from (select -2 as x union select -1 union select 0 union select 1 union select 2) xt, ... (select 1 as a, 2 as e union select 4, 1 union select 2, 0) et ... group by x''' >>> cur.execute(q).fetchall() Traceback (most recent call last): File "<stdin>", line 4, in <module> sqlite3.OperationalError: no such function: power >>> con.create_function('power', 2, lambda base, power: base**power) >>> cur.execute(q).fetchall() [(-2, -2), (-1, -1), (0, 2), (1, 7), (2, 14)] That is freaking cool. Not evaluating polynomials in SQL, I mean, but defining a user-defined function for SQL in one line of Python.

emj 7y ago
That is cool; Being able to call python code from SQL is very cool. Is it very slow?
- kyberias 7y ago
  Being able to call python code from SQL is super scary!
  - sbierwagen 7y ago
    What attacks do you believe it enables?
- oefrha 7y ago
  It simply uses the sqlite3_create_function API, so it’s as fast/slow as your Python function. You can even use Cython if pure Python isn’t fast enough. I use UDFs quite a bit and the performance is fine without extra tweaks most of the time, but for something like pow I’d use the math extension instead which would be way more performant. Btw, user-defined scalar functions aren’t remotely as sexy as user-defined window functions.

steve_adams_86 7y ago

Checkfront, a booking management system, got by on sqlite up until maybe 2 years ago - it was the solution for 5 or more years! These days it runs on postgres but it went very far without it.

Scarbutt 7y ago

But why?(for a server) Postgres can run in a 256MB VPS(if one can find one these days) and has more features that you might need later. Installing is as easy as 'apt-get install postgresql', create user, create database, done.

xyzzy_plugh 7y ago
Furthermore, postgres has great replication, and a read-replica is trivial to set up. sqlite is amazing (for any embedded database, absolutely) but postgres can't be beat as a go to for server side.
- cyphar 7y ago
  To paraphrase the SQLite site: "SQLite isn't competing with [PostgreSQL]; it's competing with fopen(3)." The primary purpose is to replace custom (and fragile) single-file databases with something that is far more resilient and provides uniformity in implementation. The fact that it is a suprisingly good SQL database (to the point where folks are actively arguing that much more fully-fledged databases are better in some aspects) is incidental.
hardwaresofton 7y ago
SQLite is even easier to start with -- I'm not trying to say Postgres is not the right choice, but it is certainly a more complex choice. Postgres is a fantastic database, but most of the time getting started with and using SQLite will be easier. For example -- you'd never have to secure firewall rules to a SQLite instance. Bad defaults (unlikely but possible) or some bad post-install configuration and you've got an exposed server. I want to emphasize that my point about SQLite being viable for startups was with the assumption of a low-write high read workload -- most of the time queries won't even hit the DB but instead be served by some caching layer. It's also really easy to scale read only versions of your applications horizontally if all you've got to do is ship/swap out DB files (again at this point it might make more sense to look into a bigger DB and just scaling vertically).
throwaway_bad 7y ago
I have a small blog (using Ghost) that uses sqlite as the main database and it's incredibly easy to maintain. It's just a single file. For example if you want to back up, you just copy the file or add it to git. If you want to do a diff you just do sqldiff on two files. It is as easy as working with json or csv files, except with atomic writes and better querying. From their site: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html >SQLite does not compete with client/server databases. SQLite competes with fopen().

saagarjha 7y ago

> If you haven't given it a look/aren't interested in it since it seems to be a "toy" database As you mentioned, it ships in every major browser, most OSes, and a number of extremely popular instances of consumer software. If that's still a "toy" I don't know what isn't…

erik_seaberg 7y ago

sqlite can be handy but it's not a substitute for testing against a staging instance of another database platform. It ignores a lot of mistakes in column types and size limits and sometimes even foreign keys. If anything, I would want tests to be more strict than prod.

hardwaresofton 7y ago
Your point is correct, but I'm afraid you might not have understood what I meant -- I meant that people should be considering using SQLite in production. Agreed that testing against copies of production or staging instances that are as close as possible to production is important, but what I'm suggesting is that you could ride SQLite all the way into production if you wanted to. I'd love to hear more about the ignoring column types/size limit/other issues that you've run into though, have you written about them anywhere or is there an SO post? I haven't run into anything super egregious though I've had some problems with FKs before.
- ris 7y ago
  Do a GROUP BY, and then try referencing a term which hasn't been either aggregated or mentioned in the GROUP BY clause. The result should be "abort! this query can't make sense!". The result (last I tried) is instead "sure, here's some arbitrary value from this group".
marmaduke 7y ago
It seeks to replace fopen() not postgres.
justinclift 7y ago
Foreign keys are supported with SQLite version 3.6.19 onward (~late 2009), though not enabled by default. https://sqlite.org/foreignkeys.html https://sqlite.org/foreignkeys.html If you can add "PRAGMA foreign_keys = ON;" to your testing script SQL calls - somewhere soon after connecting to SQLite - that enables it.

siscia 7y ago

Indeed, I believe as well that SQLite is underused in a lot of situations. Which is mostly the reason why I created RediSQL[1], to allow a more widespread use of it also in "modern"/"cloud native" application where you want to just connect to a database via the network, ideally through common API like the Redis one. It has the great benefit, that if you create your database in memory is crazy fast, and it retains the persistency properties of Redis, which are good enough in most cases. [1] https://RediSQL.com https://RediSQL.com

jeff_redradish 7y ago
Just FYI, on Chrome a 'Subscribe to the mail list' pop-up appears, and cannot be dismissed by clicking the X. Hitting Escape works.
- siscia 7y ago
  Thanks so much! Sorry for the annoyance!
  - philips 7y ago
    Safari on iPadOS as well.

ngrilly 7y ago

> I'd go as far as to say that many modern startups could get pretty far with SQLite + aggressive caching before even bringing in a big database like Postgres Modern startups need their services to be available, and this is difficult to achieve with SQLite because you can't run long database migrations without blocking writes during the migration. Personally, I'd use SQLite more often if there was a good solution to this (and also DROP COLUMN).

hardwaresofton 7y ago
My statement was definitely a reach, but I think there is a bunch of space in the life of an early startup for lack of highly available services. I've found startups to prioritize time-to-market (or time to PoC) over technical purity -- there's usually lots of unplanned outages early on that often have nothing to do with the database or are a result of bad application code/misunderstood requirements as well, so I'm not fully buying the availability emphasis, but a few things that I think could counteract it: - I'm not sure I agree that the delay of the blocking write from a migration is so long as to cause significant (if any) damage to the business. - If SQLite makes development and deployment easier (I think it does), this becomes a tradeoff between a deployment downtime/partial operation window likely on the order of milliseconds and hours of time spent setting up/managing the alternatives. With really robust systems like Postgres out there this is a pretty weak argument on my part, but there is at least something there. - As I've also mentioned elsewhere, a high read-to-write workload would be less affected by this problem. If your startup produces aggregated business analytics dashboards for manufacturing plant stakeholders for example, your data set could be completely static. - WAL mode[0] improves SQLite's resiliency to concurrent readers/writers. - In the extreme case, you could treat SQLite as a hybrid row & JSON document store, and manage your own schemas (I do not endorse this approach), but you'd still be saving some time because you can do stuff like make use of SQLite's FTS search features, and you'd have access to other relational features that you might not get with other document stores. All this said, SQLite is not for lots of concurrent writers, and they so themselves[1], but I think it can absolutely be done. More to the point, I think that SQLite can help you get something out and easily deployable, and is not hard to move off of later. [0]: https://sqlite.org/wal.html https://sqlite.org/wal.html [1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

beatgammit 7y ago

We used SQLite for a while until we were limited by IO on our crappy SD cards for our embedded product and wanted a database server with more tuning options. Before that point, it was a very capable database, she I wouldn't hesitate to use it for pretty much any product that doesn't need high performance, cross thread access (basically any crud app) or the ability to run in a cluster/dedicated server (e.g. high storage requirements). It's fast, simple, and easy. Perhaps my favorite feature is that backups are just a copy (in fact, when I'm debugging an app, I often copy the database into a temp directory and go to town). I totally agree that SQLite is a great first option for startups. If your database will fit on your filesystem (gigabytes, not terabytes), it'll probably do really well. It's really easy to switch to something like Postgres later, and it's really nice to not have to worry about the complexities of a database server until you actually need them (e.g. you're considering hiring dedicated IT/DBA staff instead of just developers). And there's a good chance you'll never need anything other than SQLite.

bob1029 7y ago

I am glad to see other SQLite advocates out here. >Many modern startups could get pretty far with SQLite + aggressive caching Not only did we get "pretty far", we got to what is effectively a final destination for this generation of our product. We don't even need "aggressive caching" beyond what the filesystem provides to us by way of accessing the SQLite db file. Today, we have a single SQLite database persisting stateful data for an application that is used concurrently by 100+ users. I have benchmarked 512KB state update writes in a multithreaded scenario at >500 per second. 64KB updates exceed 1000 per second. It took us a lot of time and understanding to get to this point. At the end of the day with SQLite, less is always more. The first attempts we did involved connection pools, queues and all kinds of other fancy over-engineering in an attempt to extract more performance. We finally ended up with just the following: - Enable Write Ahead Logging - Set Synchronous to Normal - Use a single SQLiteConnection Justification for a single connection: We are using .NET with application-level locking and an RWLS where appropriate. This is in contrast to relying on the database to push these transactional scopes to disk. For us, losing an in-flight transaction tracked in memory is not catastrophic so we can tolerate this 1-100ms danger zone for the huge upside we get in throughput. This is a great example of something you can only do with SQLite. No hosted database solution offers this degree of exclusivity over your data store from an application perspective. One other application note to keep in mind is that SQLite (by default) operates in serialized mode, so the above approach is consistent regardless of the fact that we are hitting a single connection from multiple threads at once. The usages of RWLS are required in our case simply because we need to do multiple dependent sequential operations (select+modify+update) or insertion of new items (insert+last insert row id from connection). It is hypothetically possible to avoid locking altogether (aside from the implicit SQLite serialization) if you can structure your database such that you perform all operations in a single command - E.g.: Use application-generated keys upon insertion rather than waiting for the DB to generate one. Also, backups and restores are super trivial as mentioned elsewhere in this discussion. I have considered writing a backup agent directly into our software that is transactionally-aware. E.g.: During a backup request, I can take out a write lock on the RWLS for the SQLiteConnection while copying the database to a backup destination. Alternatively, since the DB lives on the same machine as the application, you can just take a single VM snapshot throughout the day, but this won't be as consistent a backup (tolerable for our use case though). The biggest advantage we see with using SQLite as our principal data store is that it has allowed us to completely avoid operational annoyances regarding how we actually ship our stack to various environments. .NET Self-Contained Deployments + SQLite = one bin folder that "just works". We were able to build custom deployment tooling because of the simplicity of this model, rather than relying on off-the-shelf tech that doesn't quite fit 100% into our use case.