7 ms·
SQLite is a wonderful database. We use it in production many times over for all of our clients. Not having to worry about whatever arbitrary SQL Server installa
by bob1029 7y ago
SQLite is a wonderful database. We use it in production many times over for all of our clients. Not having to worry about whatever arbitrary SQL Server installation is available in a particular environment has saved us so much time and frustration. Combine SQLite with .NET Self-Contained Deployments means that we (our automated tools) now copy our software distribution zip to target, extract to path, run executable as administrator and walk away. Without SQLite we could not do this.
Migrations are also hilariously easy with SQLite if you use the user_version pragma. We have a monotonically-incrementing number to indicate each version. Our migrator first queries this upon startup and runs all the migrations that exist between current user_version and whatever our highest sequence # is (defined as a constant in code). Some might argue that you should just use EF (or whatever language-specific ORM), but I prefer the level of control and the substantial performance boost you get with raw SQL and a simple migrator approach. I cannot speak for EF Core, but EF6 performance was absolutely abysmal in some more complex cases.
I would also say that performance might surprise you if you turn on WAL. I have saturated NVMe disks inserting lots of data into SQLite. The trick is to use a single connection and let SQLite handle the serialization for you, rather than try to manage multiple connections to the same file in code (i.e. one connection per logical operation sucks for SQLite).
- api 7y agoI came here to say this. It succeeded because it's an extraordinarily high quality piece of code! The 'lite' is even a bit of a misnomer. Properly tuned I've seen 1TB+ SQLite databases work just fine. It doesn't scale horizontally with CPU cores as well as a "real" database but it can perform very well with large data sets if there's only one or two writers.
- StreamBright 7y agoWhat is the underlying storage structure that scales to TB+?
- pjscott 7y agoJust regular B-trees for row and index storage. There's a reason why it's such a popular data structure! https://www.sqlite.org/arch.html https://www.sqlite.org/arch.html
- v64 7y agoNot to mention many filesystems are implemented as B-trees.
- bob1029 7y agoIf you want TB+, you are talking about multiple computers (in context of indefinite scaling). For most who browse HN that immediately means you reach for Postgres (and you would be wise to do so), but I would argue you can continue to use SQLite indefinitely (one instance per node) if you manage replication and transactions between nodes at your application layer. I realize this is probably beyond what most developers want to screw with, but I have seen some extremely promising results in experimental implementations of this scheme. Being able to replicate items conditionally based directly on business needs is a very powerful capability on paper - E.g. User session data must be synchronously replicated to all nodes, while other entities can by asynchronously replicated based on their use cases. You could handle replication and transactions based upon any business factor or state if you go down this path.
- chaz6 7y agoThere is a derivative called rqlite which uses the raft consensus protocol to give you a highly fault-tolerant distributed database. https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- senderista 7y agoIt’s a great idea and I’m working on something similar, but Raft or any other quorum-based consensus protocol is a poor fit for the data plane.
- nbevans 7y agoThere is actually no "lite" word in SQLite. Think of it as SQL-ite. The "ite" is a deliberate connotation to rocks/geology i.e. granite, magnetite, etc. Make of that what you will. This is documented BTW on the SQLite docs.
- spondyl 7y agoWhile I couldn't find anything in the docs, there's a snippet of a transcript here: https://changelog.com/podcast/201#transcript-67 https://changelog.com/podcast/201#transcript-67
- kohtatsu 7y agoAnother: http://sqlite.1065341.n5.nabble.com/SQLite-Pronunciation-td88186.html#a88194 http://sqlite.1065341.n5.nabble.com/SQLite-Pronunciation-td8... >[...] I think it should be pronounced "S-Q-L-ite". Like a mineral. But I'm cool with y'all pronouncing it any way you want. :-)
- inferiorhuman 7y agoone connection per logical operation sucks for SQLite How is this typically handled? Using OS file locking primitives?
- zahrc 7y agoYes https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5
- jessermeyer 7y agoI know SQLite's SQL query engine is implemented as a VM. Are there ways of optimizing repeated queries, similar to compiling a query to avoid the VM churn? Perhaps more importantly, have you ever been disappointed by the query engine's performance?
- nbevans 7y agoYou can "prepare" a SQL statement and use it multiple times. I.e. you can use the same VM for a statement many times.
- kccqzy 7y agoYou can put each query and the result of preparing that query into an LRU cache (or even just a static pointer in C if your application only ever deals with a single connection). Then you can reuse these without preparing them over and over.
- zzo38computer 7y agoYou can prepare a query to use multiple times (if you are using the C interface, at least; as far as I know the command-line interface does not support this), as another comment mentions. You can also prefix a SQL statement by EXPLAIN to view the VDBE codes. But, I see that sometimes the VDBE code is not as good as it should be.
- miohtama 7y agoMy only grievance, and a blocker, is that SQLite does not support decimal numbers, making it less practical to work with finance related data.
- webpaymentsguy 7y agoYou probably already heard of storing amount in cents as an integer, but it's a pattern worth mentioning if it fits your use case.
- marton78 7y agoI don't know how it's done in the US, but in Europe financial calculations are done with four digits after the dot.
- catmanjan 7y agoSo store the value as milli-cents? (haha)
- Loranubi 7y agoShouldn't that be centi-cents? ^^
- inferiorhuman 7y agoOr in MySQL: innocents.
- to11mtm 7y ago>Some might argue that you should just use EF (or whatever language-specific ORM), but I prefer the level of control and the substantial performance boost you get with raw SQL and a simple migrator approach. I cannot speak for EF Core, but EF6 performance was absolutely abysmal in some more complex cases. Look into Linq2Db [1] (Disclaimer, I contribute to the project a little bit.) It's the perfect in-between for if you want LINQ like abstractions but don't want the overhead of an ORM, it's more like Dapper than, say, EF or NHibernate. Best of both worlds: I can quickly prototype a design using SQLite, and then flip it over to SQL Server/Postgres by changing a config string. EF Core is somewhat improved from EF in some ways (It's faster, often as fast as Linq2Db or Raw SQL) but has moved backwards in others (lots of stuff still doesn't work right, more importantly things that -used- to work in EF6). IMO EF Core tries to do too much; It tries to give the same API to interface with both Relational and KV stores, and results in not being great at either. [1] https://github.com/linq2db/linq2db/ https://github.com/linq2db/linq2db/
- bob1029 7y agoWe exclusively use Dapper for all database access. I have zero problems with a few string constants in my services that contain the requisite SQL statements for managing my business entities against the database. It's really nice having such a consolidated context where your SQLite implementations are just single partial classes tacked onto the services you are providing persistence for. That is one other point - SQLite has enabled us to do some really deep consolidation of our codebase. We generally use one SQLite database per business entity or abstraction. Users.db, Logs.db, Sessions.db, Settings.db, etc. It allows for a truly 100% independent vertical stack (aside from coupling against business models) for purposes of developing various pieces of business functionality. This is, in my view, is the perfect ideal of 'microservice' architecture. One big folder called services, one class per 'microservice', one SQLite database per class, one collection of mappers per class, etc. I honestly feel like we have settled on the perfect grain for our codebase structure (at least in C#/.NET Core land). The benefit in our world is that instead of HTTP-based RPC, we are doing direct method invocation throughout. We also use Blazor in order to extend this concept as close to the screen as technically feasible.
- SNACKeR99 7y ago
- GordonS 7y agoI've been using SQLite for something like 20 years - how did I not know about `user_version` until now?! I've always added a `schema_version` table with a single row containing a single column with the version, so thanks for letting me know about this!
- scottlamb 7y agoI didn't know about user_version or application_id either until just now. I think I'll start setting application_id. Probably not registering it in the SQLite repository - there's no procedure to do that at the top of the file and it looks like almost no one does. I'll just pick a random 32-bit int and assume I don't collide. I don't think I'll use user_version though. I prefer my current solution: a version table with multiple rows. One for initial installation and one for each schema upgrade with timestamps and the software version that performed the operation. The current version is just the max. If my migration procedure goes badly, I have some clue what happened. If my application supported schema downgrades, I'd want to have a separate operation_id and schema_version; the max operation_id would win rather than the max schema_version.
- int_19h 7y agoapplication_id is really meant for scenarios where the SQLite database is treated as a document - a file that is intended to be copied around by the user, and opened by the same or another app elsewhere. According to their mailing list, the way to register your own ID is to submit a patch that adds your ID to the file.
- zzo38computer 7y agoI tried to do that, but maybe the message didn't go through. Some of the application IDs are made up using hexadecimal (such as Fossil), and some using ASCII (everything else listed in the magic.txt file in the SQLite repository). I have used base thirty-six; I later saw RADIX-50 mentioned, which would be another way.
- plq 7y ago
- zmj 7y ago> Some might argue that you should just use EF (or whatever language-specific ORM), but I prefer the level of control and the substantial performance boost you get with raw SQL and a simple migrator approach. Agreed. I wrote a performance-oriented lightweight ORM on top of SQLite calls: https://github.com/zmj/sqlite-fast https://github.com/zmj/sqlite-fast . Its focus is minimizing allocations - treating SQLite as something closer to an in-memory store than a network API.
- OrgNet 7y agoDoesnt sqlite fail fast when many users commit at once?
- samatman 7y agoThere's a level of write-heaviness after which SQLite, even in WAL mode, is the wrong choice. But one can get pretty far with this design. Thousands of writes per second from a few dozen writers, no problem. Millions and hundreds, I'd use something else for that.
- Scarbutt 7y agoOr if you have 10,000 writers each taking 10ms, that's 100 WPS, not bad for most apps.
- mrighele 7y ago> The trick is to use a single connection and let SQLite handle the serialization for you, rather than try to manage multiple connections to the same file in code (i.e. one connection per logical operation sucks for SQLite). It seems to me that in that case you cannot use transactions though, or am I wrong ?
- easytiger 7y agoYou can use transactions. They determine batch persistence of the data rather than persisting every insert. You can just batch them up by mod N inserts or Z milliseconds if you have enough data If you don't particularly care you can disable syncing too.
- felixge 7y agoI love SQLite, but I'd be curious how you were able to saturate an NVMe drive with it. Were you doing the inserts in large batches? Saturating an NVMe drive requires writing with very large block sizes or very high queue depths.
- bob1029 7y agoInserting rows with ~500KB blob columns (JSON data) on a single connection w/ WAL enabled & pragma synchronous == NORMAL or OFF. I will admit that it would be very difficult to saturate a disk that can write on the order of gigabytes per second if you are trying to insert rows that are only a few KB in size (at least without batching these into larger entities or requests).
- NicoJuicy 7y agoFor programming, the use-case for EF or expression-tree queries is the specification pattern. Although you are correct that it doesn't handle complex queries very fast ( the ORM), there is definitely a valid motivation to use/enforce it. Complex queries are mostly handled through something like elastic search or search engine ( azure), the goal is to keep queries simple and if the code is loosely coupled, it's easier to do this.