If you need ACID, then start with SQLite.
If you need scalability as well, then PostgreSQL. Most other features aren't worth the hassle of configuration compared to a single file on disk and a library to link to.
(But I suspect that most NoSQL apps these days would do just fine with SQLite, perf-wise. As an industry, we don't have a good sense of scale.)
There isn’t some massive cost to using Postgres instead, is there some desktop divide I’m missing here?
Granted, if your ops are at the stage where "run postgres myself" is a pain, using some managed DB (heroku?) probably makes more sense than sticking to the sqlite file, if only because you'll be paying for "real backups" and availability.
It's a file or some in-memory buffer, I don't need docker or anything else to run some very simple unit/integration tests with the DB.
Appropriate Uses For SQLite:
SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
SQLite does not compete with client/server databases. SQLite competes with fopen().
SQLite is an efficient file format for relational data with excellent tooling.
If you're working on a larger scale, or expect to be growing into a larger scale, though, then I think it's wise to skip straight to using a full-blown RDBMS. It's not about scalability, per se - very, very few projects need more performance than SQLite can deliver - so much as about manageability. A top-tier RDBMS will have a better story for resiliency and availability, it will have a more robust security model, it will handle concurrency better, it will have a more complete SQL dialect that allows you to handle more complex queries while keeping them more maintainable, etc.
This is one of the few spots where I think YAGNI doesn't apply. In expectation, it's cheaper to start on the RDBMS than it is to start small and grow as needed. The costs of switching databases are so high, the costs of outgrowing your data layer are even higher, and the costs of trying (and, usually, failing) to keep your data layer database-agnostic are still higher yet, and, in that particular problem domain, the likelihood of eventually being able to benefit from an industrial-grade RDBMS is so easy to anticipate.
People don't realize just how performant SQLite can be. If you are running just 1 server and won't probably need more than SQLite is more than enough.
2 application servers and up is where PostgreSQL can come into play.
https://www.sqlite.org/whentouse.html
After reading the SQLite when-to-use document I transitioned some smaller marketing sites over from Postgres because it was overkill for the specific situation. Other multi-app server applications or ones that scale, SQLite might not be the best use, but it still has it's cases. Like copying over a subset of the original data and running queries locally instead of hitting a production database
>[slow]...This is because the entire database was locked every time someone viewed the page because it contained updates/inserts.
So avoid loads of inserts I guess https://stackoverflow.com/questions/54998/how-scalable-is-sq...
I guess 90% or performance problems with SQLite come from the filesystem.
That sounds like a bad idea in many cases. Prone to many kinds of problems, like accidentally corrupting the stored state beyond recognition from a tiny application bug.
And if you mean corrupting by the app going amok and asking to corrupt data because of logic errors in the app itself, that's just as doable with a database, and especially doable with local-file DBs (which usually don't try to limit what you can do).
read file, unpickle, do stuff in memory, pickle, write.
A bug in "do stuff in memory" could easily muck things up and I didn't get round to renaming the file for every edit. Yeah you could do some auto rename thing but it might be simpler to just use sqlite.
That was your problem then, no? If you don't do that from the get go, you're essentially saying that you don't care about atomicity, consistency and durability.
> Yeah you could do some auto rename thing but it might be simpler to just use sqlite.
You mean atomic rename? The simplest way is to just use a library that does it for you. One does exist for Python. It takes 2 lines of code to safely dump your JSON data with it.
Unique keys, foreign keys, check constraints, and DDL changes were all meant to solve real problems. I guess some apps might not benefit as much, but it seems like asking for trouble.
Given how easy it is to just use postgres, why invite all the risk?
If you do actually need advanced schema features, then I agree, just use PostgreSQL. But none of the things that you've listed are advanced features, and all of them have some trivial equivalent for in-memory data.
Side note: do you maybe have an image of a very particular kind of app in mind? Like, a moderately sized web app? Keep in mind that I'm talking in general here, about web/desktop/mobile, and about all sizes. And in practice, even for web apps, people often underestimate the actual usage and perf requirements.
I mean, you can do all of this stuff another way. But PostgreSQL represents a lot of lessons learned over decades with all kinds of applications.
Sure, there may be some level at which it's just easier to serialize the in-memory data. But that level is lower than most people realize and postgres is pretty darn easy to use given all the kinds of problems it helps you avoid.
Even an MVP of a trivial web app is probably fastest to deliver with postgres, and less risky.
And there are tons of things that are really convenient even for the tiniest app. For instance, being able to go in and browse your data to find out what's causing a minor bug.
Do you have any examples of this? It's rare I see suggestions like this.
I've done this for HTML mobile apps a few times before where the entire app (fairly simple) state is saved and loaded as JSON when you make any changes. It's super simple and very little can go wrong. This was as opposed to using Web SQL (which wasn't deprecated at the time) where it's much more complex to keep what's in-memory synchronised with what's in the database.
Edit: I'm talking about client side code to store local state here.
It’s much simpler. The idea is to start simple and avoid complexity. Do you need the extra complexity of a PostgreSQL instance or can you get away with doing it in-memory? If so you can save yourself a lot of complexity.
The complexity exists, you're just shifting it around some. The "complexity" of setting up a populate db server is non-zero, but generally a known-entity. The complexity of managing your own stuff 'in-memory', with all the attendant issues about concurrency/corruption/performance/etc - would almost always end up being far more custom to your particular project.
But we don't. Perhaps because the attendant issues often aren't issues in practice? There's no concurrency problem if it's just your app working with data, and it's logically single threaded (i.e. possibly async, but serialized) - which is by far the most common model for non-web apps. There's also no concurrency problem if you use immutable data structures. There's no corruption problem if you are using a memory-safe language, and your data structures enforce their invariants, as they should in any good design regardless of anything else. Performance is going to be better, often significantly so, until you have enough data that dumb queries over it are slower than the overhead of communicating with the database. And so on.
So, no, the complexity doesn't necessarily exist. The complexity of your data is constant, and of the kind of processing that you need to do with it, true. But we're talking about complexity of the pipeline you use to access that data - and there, you can absolutely make it more complicated that it needs to be, and I'm arguing that it is what usually happens in practice.
Think of it from the opposite perspective - if you can work directly with in-memory data, why would you prefer to run SQL queries instead? The latter is obviously more complex, but what's the advantage? There are many valid cases that have a good answer to that question, but there are many more that don't.
I can't help but think that we're conditioned to use DBMS (SQL or not) largely by inertia. When we learn about them, the examples are necessarily toy ones - tables with a dozen of records, that sort of thing. But one side effect is that it subtly normalizes the notion that this much data warrants a dedicated DBMS - so people don't balk at even patently ridiculous setups, like a separate SQL server used to hold a grand total of a couple thousand records in all its tables (this is a real thing, something that I personally did in a LOB app many years ago; I didn't have a good justification, it was pure cargo cult, and the app could have done everything it did in-memory, faster, and easier to code).
And sometimes, when you ask, people say, "yeah, it's only 1000 records now, but we're going to grow later, and then we'll need a DBMS". But you'd need to grow by many orders of magnitude to get there - and if you get that opportunity, you'll also have the resources to adapt. But everybody dreams of being Google.
And then you have apps like, say, a todo list manager app. Is it ever going to deal with millions of todo records? I don't think so. Then why does it need an SQLite DB?
The reason why data is better kept in a database system is because it often has a very different life cycle than applications. It often lives longer and gets used by more than one application. That's why modelling and storing data somewhat separately from applications often makes sense regardless of the amount of data you have.
Also, procedural code is often far more complicated than a SQL query regardless of the number of records being processed. But that obviously depends very much on the specific problem, on the programming language and on the developer's skill set.
It sounds like you're thinking about web and enterprise apps that share data sources. For those, absolutely, use DBMS. You don't want to solve concurrency and consistency yourself.
I was talking more broadly, of apps of all kinds. Does a TODO list app for your phone need a DBMS? There's not going to be any concurrent data access there, nor huge amounts of data, nor complicated queries. But that describes most apps, and most of their data! Even for web apps, an exclusive data store is more common than shared (just think about all the WordPress blogs online!).
> Also, procedural code is often far more complicated than a SQL query regardless of the number of records being processed.
If your language doesn't have some kind of declarative query framework for sequences, you're probably better off with SQL. But these days, who doesn't have that? Even Java caught up.
(I'd single out C++/STL for offering non-composable primitives, until ranges get into the standard. But C++ is a wrong choice for a data-heavy app in general, IMO. )
>So there's no impedance mismatch, and no need for the vastly more complicated code that is needed to bridge it. Serializing arbitrary data structures is far from trivial, especially because JSON doesn't support references natively and therefore things like cycles are impossible without adding IDs yourself which is incompatible across JSON parsers. The naive way to serialize objects to JSON also tends to cause duplicated objects for every reference.
Or you can just use any SQL Database and use an ORM, write your damn Domain Classes [0], call .save() when you want to save and then query the damn thing [1] and if you want eager loading you can configure that too. Honestly it's so frictionless I don't see why I should bother with whatever your preferred workflow is.
[0] http://gorm.grails.org/latest/hibernate/manual/index.html#do... [1] http://gorm.grails.org/latest/hibernate/manual/index.html#cr...
I'm sure most complaints against ORMs happen because newcomers, who don't have sufficient knowledge of relational databases, try to model arbitrarily complex objects that cannot be mapped to a relational database (and not to JSON without extra support by the parser either) or because they think they don't have to write queries at all. In other words: they are fundamentally trying to swim against the stream and then blame the tool. If you know of the limitations of relational databases then ORMs are just a tool that saves you time and lets you avoid boring work.
The same thing goes for queries, by the way. When it's in-memory, you often don't even need indices, because linear scans will be "fast enough" on many datasets. And with list comprehensions etc, this can be written just as declaratively as SQL (for an extreme example, see LINQ, which even has outer joins).
And yeah, the "very little can go wrong" part is exactly what's so great about this. It's KISS at work. If you don't really need complexity, why pay the tax?
My personal mix also contains rockdb (lmdb is interesting, didn't use yet) and redis, as potential alternatives for sqlite and Postgres, depending on the use case.
For instance, we have processes that fetches and stores data from APIs. It's trivial to share this with other developers for dev purposes. This data is read only and it reduces an annoying step of loading the data into Postgres.
This is coming from someone who starts projects with only Postgres for many years and I do love Postgres. Sqlite is an underrated tool in the web dev world.
But otherwise it's been fine to use, a good starting point for internal projects.
Last time I checked not even DataGrip could do it.
You'll have to provide some proof for that assertion, especially given that there are numerous examples of apps doing exactly that. For example, Firefox stores bookmarks and history in an SQLite database.
Sqlite is only really viable if you don't have an undefined amount of nodes pushing commits into the DB
That's a challenge desktop applications don't have
From https://www.sqlite.org/whentouse.html:
"The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time."
you need interactivity with writes for an actual webapp. sure, you could keep the writes in the browser cache, but then it wouldn't really be a webapp either, more like an app that opens in the browser.
I wouldn't recommend this to a beginner, the sqlite driver(of your language) would handle all this for you in a safe way.
I agree, that's about the only bit of complexity in all this. But it's completely generic, so it can be made into a small library, and then reused without dealing with the complexity every time. For example:
Errors can happen in both cases - you can have a corrupt SQLite DB, just as you can have invalid JSON. The way you handle them is pretty much identical.