SQLite the only database you will ever need in most cases
unixsheikh.com
unixsheikh.com
We have not had a single incident involving performance or data integrity issues throughout this time. The trick to this success is as follows:
- Use a single SqliteConnection instance per physical database file and share it responsibly within your application. I have seen some incorrect comments in this thread already regarding the best way to extract performance from SQLite using multiple connections. SQLite (by default for most distributions) is built with serialized mode enabled, so it would be very counterproductive to throw a Parallel.ForEach against one of these.
- Use WAL. Make sure you copy all 3 files if you are grabbing a snapshot of a running system, or moving databases around after an unclean shutdown.
- Batch operations if feasible. Leverage application-level primitives for this. Investigate techniques like LMAX Disruptor and other fancy ring-buffer-like abstractions if you are worried about millions of things per second on a single machine. You can insert many orders of magnitude faster if you have an array of contiguous items you want to put to disk.
- Just snapshot the whole VM if you need a backup. This is dead simple. We've never had a snapshot that wouldn't restore to a perfectly-functional application, and we test it all the time. This is a huge advantage of going all-in with SQLite. One app, one machine, one snapshot, etc...
I had lots of weird issues with Plex until I found out that uses SQLite, and moved the config directory from a shared NFS directory to a shared iSCSI volume.
If it’s written by someone else you’d have to maintain a modified fork (if that’s even possible)
Or use the ".backup" command in the CLI to take a clean single file snapshot if that's what you need. Or, you can call the checkpoint C API and if it can move all the transactions from WAL to DB, it will remove the WAL file when it's finished.
How do you deal with things like updates? Upgrades to your service?
Do you just accept that you have scheduled downtime when your service won't be available?
That said, we have prototypes of architectures in which we have multiple instances running in the same production environment simultaneously, each with an independent SQLite database and some light-weight replication logic in the application itself. Simple DNS RR or customer-managed LB would be responsible for routing client traffic. There aren't a whole lot of entities that we actually need to synchronously replicate between application servers, so this is far more accessible for us to iterate on than throwing our hands up and jumping to some fully-managed always-on clustered database service and throwing away all of the lessons we've learned with SQLite.
I find databases of all sorts extremely interesting and I've tried many of them, of all flavors.
In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do.
Why not use sqlite? Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite.
Another example - multitenancy is dead easy in postgres, makes things more secure and makes coding easier.
Another is that Postgres had an extremely rich set of data types, including arrays and json.
Also Postgres is designed for symmetric multiprocessing which matters in today's multicore world.
To that goal, I think it's wildly successful, instead of writing files I almost always reach for SQLite first.
When systems share a database, I, like you, reach straight for postgresql. Most people reach for mysql, which I do not prefer.
This bug has occurred to me personally multiple times.
Does it really have advantages over a flat file?
I think the odds are likely that Firefox is doing something unsuspected.
https://www.sqlite.org/testing.html
- Four independently developed test harnesses
- 100% branch test coverage in an as-deployed configuration
- Millions and millions of test cases
- Out-of-memory tests
- I/O error tests
- Crash and power loss tests
- Fuzz tests
- Boundary value tests
- Disabled optimization tests
- Regression tests
- Malformed database tests
- Extensive use of assert() and run-time checks
- Valgrind analysis
- Undefined behavior checks
- Checklists
It seems that about once a month I read something that makes me like SQLite even more. I guess that they like Valgrind as much as I do is the reason this month.
<<This bug has occurred to me personally multiple times.>>
Hello, I am AirBus engineer (not really, only pretend). Does my plane crash? No, it does not!
Dear Random Internet Person who complains that open source software is broken. Are you joking? We are talking about SQLite!!??
SQLite has whole pages dedicated to "why you should NOT use SQLite". How many open source projects are /so/ good they can do this? Incredible!
The test coverage feels like NASA.
Whatever capabilities SQLLite has to recover corrupted or inconsistent storage, it certainly exceeds what you get with flat files (none, unless you implement it yourself).
Which suggests an application error to me. I'd give decent odds to the real problem being some bounds check or pointer writing to the wrong place. To prove it, I'd instrument SQLite to log every single query that it was asked to do, then try to run those queries outside of the application. If the queries work fine, then the copy of SQLite in the application is being corrupted somehow.
In which case it really isn't fair to blame SQLite.
Interestingly enough, I don't think I've ever had this issue. I've force quit Firefox many times in the past (not often, but I'm sure I've done it countless times in the 10+ years I've been using it) and I still have localStorage data intact. There's old crap in StackEdit.io, which uses localStorage, that I haven't touched in years. (no, I have neither that service or Firefox itself set up to sync with anything)
EDIT: Maybe it's OS specific? I've been on macOS, and maybe there's something about other file systems where corruption is more likely to happen with a force-quit? Just throwing darts here.
I've never seen any error messages from Firefox suggesting that anything has been corrupted.
Are there other symptoms that I would expect to see, if this corruption is happening?
SQLite is really only my first choice for hobby projects and desktop apps that aren't running on the JVM.
I hear this too - but silently truncating my data (causing a great deal of data loss and grief for me) is likely going to continue to bias me against MySQL for the foreseeable future.
Compatibility with old decisions vs. doing the right thing™ is always a pain ...
I never programmed for berkelyDB but I don’t believe it was SQL, so I think that’s what made SQLite so successful. The combination of an SQL api backed by local files
There is a lot of macOS/iOS software using Core Data who already have "SQLite as an application document storage model" without even realizing it.
Well, depends on how wide your definition is:
Filesystems are also databases. (And eg inside of Google, they are using databases to store filesystems.)
These days, we often think of relational databases. Filesystems are more like the hierarchical databases of yore that Codd (also) talked about in his original papers.
For example, having a folder of contacts with each file named after the person and having key/value pairs. Similar to how static site generators use YAML/TOML/JSON.
Or alternatively a midnight commander for databases. :)
You'll have hard time to harden sqlite, removing all the insecure defaults, fix the broken and exploitable full text search apis, but esp. its built-in hacks. Like explained here https://github.com/rurban/hardsqlite or here https://research.checkpoint.com/2019/select-code_execution-f...
I'm struggling to picture this, do you have any links I could read?
It is much more complicated than that, but the idea is that using bigtable you create a resemblance of a fs that feels like a fs to use for the most part.
[1] http://www.pdsw.org/pdsw-discs17/slides/PDSW-DISCS-Google-Ke...
You could just as easily replace those in-memory calls with a networked DB (perhaps with speculative pre-fetching or something, I dunno, I probably wouldn't try to make a python filesystem too performant).
The salient detail here is that as far as your kernel is concerned a filesystem is an API for interacting with data (whether that's with a daemon process like the linked example or with raw function calls built into the kernel). Those APIs can and often do interact with structures physically stored on a local disk, but that isn't a requirement.
[0] http://www.rath.org/pyfuse3-docs/example.html#in-memory-file...
In a Filesystem you know where the Data lives, in a Database you know that it lives...but yeah with stuff like Ceph that view gets a little bit foggy.
For me, all I know is that my filesystem store its data on disk somewhere, I don't really have any clue how that's organised. That's pretty much exactly the same level of knowledge I have about my db.
(And yes, I could look into both of them to learn more. And yes, we also have databases and filesystems that get accessed over the network..)
File system paths perhaps sound like a location, but there are no more and no less a location than eg tablenames to me. Or URLs.
Not sure exactly what you mean by 'remote access', but if it's to debug something on a remote DB from an SQL IDE, I do this quite often. I use sshfs to mount the remote filesystem and open the SQLITE file in DBeaver.
Even in case of Postgres, you would have to connect to a remote DB via SSH or via VPN. It's just that you will be using sshfs to connect to a remote SQLITE file.
According to: https://www.fossil-scm.org/forum/forumpost/8749496886
> There's a very real possibility that you could corrupt the SQLite DB by running it over SSHFS.
I once accidentally locked a production MySQL database with some kind of recursive sub query.
Running queries locally on a copy avoids lock/mutation issues, and I have a snapshot to re-run queries on if I need to go back and see the source of the data.
How lightweight?
SQLite itself is around 600 KB. That's with all features enabled. I believe you can get it it down to about half that if you disable features you don't need. RAM usage is typically under a dozen or so KB.
It doesn't even require that you have an operating system. All you need for a minimal build is a C runtime that includes memcmp, memcpy, memmove, memset, strcmp, strlen, and strncmp, and by default also malloc, realloc, and free although it has provisions for providing different memory allocators, and you have to provide some code to access storage if you are running on a system that doesn't have something close to open, read, write, and the like.
This means it can even be used on many embedded systems far too small to run a Unix.
Heck, it is not even too unreasonable to compile SQLite to webasm and include it on a web page if for some reason you need client side SQL on the web.
With the deprecation of Web SQL, I highly doubt this is a use-case anyone cares for anymore.
Anyhow, I see your main point: SQLite is indeed "Lite", so it makes sense to use in embedded or small single-consumer cases. I just don't think those are very interesting tools to make in 2021, we expect collaboration and concurrency, so PostgreSQL wins in my imagination-space. I am a web developer though, so I am inherently biased.
I also am not the person you were just discussing with
WebSQL didn’t die as a standard because noone wanted client SQL on the web, it died because SQLite was the only reasonable way of providing it, preventing multiple really-independent implementations.
A good example is that SQLite is used inside many video games. You wouldn't put Postgres in your game engine.
I've been watching CockroachDB as a more modern alternative that can auto-scale easily, do master/master easily, etc. Those are all painful on Postgres to the point that hosted and managed pgsql is a cash cow for cloud vendors.
I've run Postgres in a pure RAM only configuration, never touching the disk except to start.
If you mean "it can't be compiled in to another application as a library", then yes that's true - it's not an embedded database.
Or has it to run as a separate process?
We have learnt to use full-fledged RDBMSs as a default because they proved really flexible and powerful over the last 30+ years. But they do have limitations and cost.
It also has javascript V8 functions to update data for migrations.
( eg. migrating your events to a new model, instead of keeping the different versions)
But while you need to do everything with a JSON1 extension for managing the json ( https://www.sqlite.org/json1.html ).
Postgress has build-in v8 javascript functions to manage this ( as stated before). Which makes it much more useable.
There's a good library for .net that handles postgress documents, which has an insane amount of usefull functions ( https://martendb.io/documentation/documents/ ). I think halve of those wouldn't be possible on another database.
Also see "Many Small Queries Are Efficient In SQLite": https://sqlite.org/np1queryprob.html
I agree, but there is one thing that it cannot do, one very important, critical thing: synchronous multimaster replication.
Well, vanilla PostgreSQL cannot. Vertica can.
https://www.postgresql.org/docs/current/ddl-rowsecurity.html
But the power of PG is that it doesn't stop there, if you combine this with a plugin like temporal_tables and you can segment by user and time:
https://github.com/arkhipov/temporal_tables
All of this mostly unknown to the thing that's accessing the DB. If that's not enough for you, why not add some auditing with pgaudit:
https://www.pgaudit.org/#section_three
All this value is just out there. There's even more if you can stomach browsing "ugly" sites like PGXN. Most of it works out of the box, though you may need to tinker for performance and some edge cases but it's there.
I think it might not actually be hyperbole to say that Postgres is the greatest RDBMS database that has ever existed.
The simple explanation is that you set a postgres environment variable prior to each query. The Postgres row level security system looks at this variable and returns nothing if the ID in the variable does not match an ID in the table.
So I wrote a function in Django which intercepts every database query and prefixes each query with a postgres environment variable set command.
That's pretty much it.
I found https://kristaps.bsd.lv/sqlbox/ to be an interesting approach.
I’m building a multi tenant web application on node/sequlize/feathers and implementing logic in code. I’ve seen table row level security but haven’t looked deeply.
I use Python/Django.
I use Postgres row level security.
Read this entire comment thread for more info from other posters on row level security.
If so, have you run into any scaling issues with the number of tenants you can support. I looked into using schemas for multi tenancy but the various reports I found said around 50 tenants or so things started breaking down very quickly.
No I am not using schemas - I found them to be brittle and complex as a solution for multitenancy.
I use Postgres row level security.
I use Python/Django.
Read this entire comment thread for more info from other posters on row level security.
Postgres has a larger feature set. Whether or not you need it depends on your use case. There are ample pages out there that will give you a side by side comparison if you’re curious.
I have often seen Postgres, MySQL and MongoDB in places where they were really overkill and added unnecessary network and deployment complexity. And upon asking the designers it turns out they simply didn't know about SQLite...
Small gripe in case the poster reads this: There's a malopropism in the opening bit:
> SQLite is properly the only database you will ever need in most cases.
Should be:
> SQLite is probably
Also it's very easy to buy PostgreSQL from various DBaaS providers. There's a docker one liner to set it up on any development machine and you need to run it just once.
Sure, SQLite is a little bit easier but it's so much less powerful as a database. Why not just go to Postgres directly and leave SQLite for what it's intended: embedded programming like mobile apps or in-car entertainment systems etc.
Because its a separate server process and install rather than an embedded library. On disk, in memory, in CPU, in config and admin workload, and in pretty much every other conceivable way its heavier than SQLite. It offers a lot extra , too, but if you don’t need the extra that it offers, its overkill.
> Also it's very easy to buy PostgreSQL from various DBaaS providers.
Yes, it is. Which reduces some burdens and increases others. Nonlocality is a cost (also, paying for a DBaaS is a cost.)
For one it requires more expertise to set it up and monitor. SQLlite is way easier to use. And if the app is coded right, it can be easy to replace SQLite with a more powerful client-server DB like Postgres when necessary.
"This has no affect on me"
They're everywhere.
"It would be who of you to be cautious openening a new toner cartridge."
Malapropism? Can just call it a typo (which is incorrect but people get it).
Typo literally means the opposite of what the OP wants to convey.
A typo is when you pick the right word, but type it wrong.
A malopropism is when you pick the wrong word, but type it correctly.
This would not be possible if I used a remote database, as there is no way to do a atomic snapshot of both the remote database on another filesystem(even if its ZFS) and the git repository.
Developers of multiple services, please support SQLite and don't lock yourself to a single database like PostgreSQL and MySQL. Even if the service is embedded in a Docker Container it is still painful to manage.
ZFS snapshots may not result in a consistent databases backup of sqlite. You should use VACUUM INTO and then do a snapshot or use the sqlite Backup API.
See my other comment: https://www.sqlite.org/backup.html
Essentially, while you have a proper atomic snapshot of changes on disk the in flight transactions won't be there and you will be on the mercy of having a sucessful recovery from the journal/wal if you have that.
From your answer it sounds like I'd get such a snapshot (even if it relies on recovery from a journal internally). So I don't understand why you see a problem here. Is the recovery process unreliable?
> 1.2. Backup or restore while a transaction is active
> Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt.
> The best approach to make reliable backup copies of an SQLite database is to make use of the backup API that is part of the SQLite library. Failing that, it is safe to make a copy of an SQLite database file as long as there are no transactions in progress by any process. If the previous transaction failed, then it is important that any rollback journal (the -journal file) or write-ahead log (the -wal file) be copied together with the database file itself.
So no, it doesn't appear that it is safe in general to just copy the file whenever. If there are transactions in progress, things can go wrong. I don't quite understand why (isn't this the same as if a power failure happens, and SQLite is resistant to that?), but this is what the docs say.
It might be different with ZFS, since the backups are atomic (and this doc might assume that they are not), but I'm not 100% sure I would rely on it.
I'd have expected recovery to take time proportional to the size of recent/uncommitted transactions, which should be quick, even for large databases.
What's unsafe is using a naive file copy tool (e.g. `cp`), which non atomically copies a running database.
I run PostgreSQL on my VPS alongside gitea and some other services, and I can take snapshots of it just fine (assuming I don't take a snapshot as it is writing to disk, but that problem exists with SQLite as well).
2. I doubt that the git part of your setup can be snapshotted correctly while an operation is in progress, since git doesn't use transactions.
So you can do pretty bad things to git and it would still work. Atomic snapshots are absolutely no problem. Copying the directory while in progress is fine.
If you try very hard, you can still make a bad backup -- say you were copying files from remote host, and backup got interrupted after HEAD but before the new data. In this case, there is reflog -- append-only file which keeps HEAD history. Using this file, you would be able to manually recover into previous state.
From sqlite.org on how to corrupt your database:
> 1.2. Backup or restore while a transaction is active Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt.
> The best approach to make reliable backup copies of an SQLite database is to make use of the backup API that is part of the SQLite library. Failing that, it is safe to make a copy of an SQLite database file as long as there are no transactions in progress by any process. If the previous transaction failed, then it is important that any rollback journal (the -journal file) or write-ahead log (the -wal file) be copied together with the database file itself.
What you need is to follow the guide[2] and use the Backup API or the VACUUM INTO[3] statement to create a new database on the side.
[1] https://www.sqlite.org/howtocorrupt.html
More detailed docs: https://www.sqlite.org/lang_vacuum.html#vacuuminto
The problem here isn’t the .wal file, but doing backups from a moving filesystem. The solution is to use snapshots. ZFS supports snapshots natively, and Linux’s device mapper supports snapshots of any block device. [0]
[0] https://www.kernel.org/doc/html/latest/admin-guide/device-ma...
Rails switched to SQLite for at least the development environment.
As soon as I started to test the app with multiple clients ( browsers, websockets, embedded websockets ), I was hit with concurrency errors.
So I switched to PostgreSQL ( which I love ). But I had to.
Internally SQLite relies on file locks to mediate access and this seems to necessitate very coarse locking (lots of table locks).
Comparatively the client server model of other databases like PostgreSQL seems to allow better coordination between writes and much finer grained (row level) locking.
What matters is the performance. Have you seen a difference between these two approaches?
My main point was that other DBs like PostgreSQL implement row level locking so can handle concurrent writes much better than SQLite.
As for my war story I hit on false deadlock detection problems writing to two separate tables from two processes with an fkey between them. Fun fact if it thinks there’s a deadlock SQLite will not call your busy callback. It will just immediately fail. I know the deadlocks were false positives because my solution was to move my backoff logic out of my busy handler and instead catch SQLITE_BUSY and call sleep myself. Identical backoff values but the “problem” disappeared.
This seems to me like a Rails bug, it should be serializing transactions when running against SQLite.
https://www.sqlite.org/faq.html#q5
https://www.sqlite.org/faq.html#q6
https://www.sqlite.org/threadsafe.html
TL;DR:
- Multiple processes can read the database, but only one can write at any given time; this is enforced using FS locking.
- For threading, there are three modes SQLite can operate in: single-threaded (unsafe for use in multiple threads), multi-threaded (safe to use in multiple threads, as long as each thread establishes its own connection) and serialized (do whatever you like, however you like).
- The caveat for threading is that if SQLite was compiled with -DSQLITE_THREADSAFE=0 (i.e. single-threaded), then multi-threaded and serialized modes cannot be enabled at runtime, because locking code gets compiled out of the binary.
Is that not normal for databases? Can't you get a transaction conflict which needs to be retried for Postgres as well?
The same thing would happen with PostgreSQL if you used a single connection in multiple threads.
Yes.
Also you have to build some basic stuff yourself like Enum or JSON support. It's not hard but honestly I would feel much better using PostgreSQL in those situations.
type schema = { field1: any, field2: any, ... }
AFAIK the column types are not really enforced and you can even leave them out.
https://stackoverflow.com/questions/2489936/sqlite3s-dynamic...
https://www.sqlite.org/json1.html
Haven't used it, was just wondering if there's something as I found myself quite happy with JSON support in mysql/aurora.
It will continuously backup your database to an S3 compatible service. There are very nice detailed instructions for various backends (AWS, Minio, B2, Digital Ocean) as well as instructions on how to run as a service.
This is why developing with MongoDB is so fast and easy in the beginning. You just go, store shit like you structure your data in code which makes the mapping extremely easy in most cases.
I think the "ever" is about "will I ever need to upgrade to a server-based database in this particular case". I.e., in cases A and B, you might do OK for a year or two with SQLite, but if your project grows, eventually you'll have to upgrade to Postgres; in these cases, SQLite is not the only database you'll ever need. But in cases C, D, E, and F, you'll never have to upgrade; in these cases, SQLite is the only database you'll ever need. The cases where SQLite is the only database you'll ever need outnumber the cases where SQLite is not the only database you'll ever need; thus, "SQLite is the only database you'll ever need in most cases."
SQLite is also much more convenient for anyone who later wants to explore your application's data than JSON structures.
Recreating relational table query operations as a set of operations unique to a specific programming language instead of integrating SQL makes, to me, as much conceptual sense as recreating a unique text pattern matching search for each language rather than integrating regular expressions.
This is only a small sliver of the topic here, but I do think that SQLite is probably a good backend for this.
So let's say a concept of SQL was "built-in," the language now understands the syntax: So what? Without access to the underlying data the SQL is about it is just as meaningless as a raw string (i.e. you cannot know if a query is valid without the underlying data to validate it against).
If your language now needs a persistent connection to some underlying SQL data-source (with all the problems that entails) building it into the language is barely better than just executing SQL during your tests.
So I'm not really sure what you want to accomplish or what value you believe this would add.
https://pandas.pydata.org/pandas-docs/stable/getting_started...
Generally, I vastly prefer the SQL operations to the pandas ones, though (and this is very important) only when pandas is essentially recreating what is in SQL's sweet spot. For example, you can use pandas operations to do joins, aggregations, filters, and so forth. I would rather write that code in sql.
I would not prefer to generate summary statistics in SQL, find correlations between columns, or do other things that are in the realm of scientific or statistical programming. There will be a grey area in there, for sure. I also find that many things that require SQL trickery (such as self-joins) often have a very very simple pandas solution such was cumulative sums on a column. So I go back and forth between SQL and pandas quit a bit (as each operation returns a data frame).
Just to be clear again, some people just can't stand SQL and want to stay away from it as much as possible. Other people, like me, greatly prefer it, but even for us there are scenarios where we'd much rather use pandas than get into leetcode style SQL trickery.
What I'm talking about is this:
https://pandas.pydata.org/pandas-docs/stable/getting_started...
I personally am not especially interested in recreating relational set operation using pandas operations. There are things I'd much rather do in pandas (such as summary statistics), and some things I'd much rather do in SQL elaborate JOINs and aggregations. I do admit there will be a grey area.
Interestingly, there are a lot of people who absolutely can't stand SQL, whereas I (and a lot of people) vastly prefer it.
As for ORMs - I actually did like them back when I did this sort of programming (managing the back and forth between objects and tables was honestly very boring), though once I was into reports, I often went straight to raw SQL. I no longer do that sort of work, and almost all code I now write is for analytical purposes, so I don't do any CRUD.
Btw I've written lots of analytical types of queries using Django ORM (to power the backend for a dashboard API, for example). It is quite powerful. You can even do window functions and such directly with the ORM.
SQLite doesn't enforce it. But people forget the major reason for DB enforcement is multiple application on one database.
This pattern has fallen out of favor in general for all databases (we prefer one service layer in front of the DB, and then multiple apps use that service layer).
And when you have one app or one service layer, that's where the enforcement can easily come from.
While that may be the major reason there are other reasons that are just as valid. Such as multiple developers working on a single application, loading "wrong" values into the database for example.
If SQLite enforced typing it would be a great alternative for me, but the way it is right now it is "nice", but not nice enough to beat PostgreSQL in any way unless I need an embedded DB. At least in my opinion.
You still have the option of embedding Firebird if you want enforced types AND embedded operation.
Even when using TypeScript, typing your local variables isn't THAT useful. Some do it out of sense of diligence, but the odds are you know what's the type of a local variable in a 30 line method, just by looking at the code.
But for libraries to cooperate, or even people to cooperate within a single large project, that represents objects and functions used by multiple callers who don't know that code by heart, and aren't looking at its implementation while using it. Hence, types.
Sure, for embedded databases with not much data and a single user, use SQLite, but for actual serious applications it's a toy.
I tend to disagree. As do a lot of others [1].
It's not really "upscaling" if it still can be handled by one machine though isn't it? And it might be much cheaper to handle the same load on a number of weaker "machines" than a single powerful "machine" (replace "machine" with cloud VMs).
And for such "small-scale" problems, it's also not a big deal to run a "proper" DB server process on the same machine (but with the advantage that it's absolutely trivial to switch to multiple machines).
Actually if anyone has some more to add to this discussion, I would be interested to hear, in case I am missing something. I am currently using an SQLite database for a small internal Django app.
When using a database stored elsewhere, the config file will still contain the user name and password, but the user's permissions could be restricted in order to prevent access to parts of the data in the database.
In the real world though, 99% of all web sites run with a user with full permissions anyway, so if the site gets compromised, you're screwed regardless.
In my professional career it's 0% for public facing apps, the simplest custom CMSs based systems I maintain has seperation between database users for a 100MB DB. Many of the newer ones have a sync to read only copies for most of the actions that the app needs to do. 99% sounds horrible, I can't even imagine how you could get to such a state!
Sure considering how bad login is handled every where (even in SAML), it's not that surprising.
1-click-installers for Joomla, WordPress, Drupal, etc. Most shared hosting providers give the database user full access to the entire database as default.
Yeah, I was kind of working under that assumption.
I.e. were the user can access the database file in Finder/Explorer and make a copy at any point in time to share, backup, etc. Would that copy be consistent?
Could this happen to the "live" file or would the workaround be to keep a temporary copy somewhere away from the user and copy that back and forth?
https://www.sqlite.org/backup.html
This wouldn’t be a great solution for large databases, but if you’re using it as a document format up to the couple dozen MB range it’s the perfectly acceptable.
Alternatively you could just go the working copy route - make a copy of the database on open and move it back in place when closed. This would also let you recover unsaved changes if you crash or are terminated before the user hits save, as well as the obvious benefit of not needing to keep the entire database loaded in memory and needing to write the entire thing out on save.
This got me thinking, had someone done a rewrite in Rust yet?
Therr have been a couple (literally) of occasions where customers have contacted me and it turned out their database was corrupt, though I don't know the circumstances under which it occurred. Presumably there are instances where it wasn't reported to me too.
Coincidentally, the backups had stopped working a couple of months ago.
Fortunately I was able to copy the data to my machine, write some python to try to retrieve each customer's data individually, verify its consistency and merge with the older backup so most people didn't notice.
Afterwards I upgraded to the latest sqlite, as the one I had been using was six years old, and I have not had a problem since.
Whatever DB you use, a backup plan is prudent for it. No DB can magically recover from some of corruption. And unless the user does something stupid, out of ignorance, SQLite is extremely resilient. Do you have any experience with it that makes you suspect otherwise?
I was under the impression that it’s very difficult to corrupt a DB file if you are using the SQLite API
You should probably keep each connection owned by a single thread.
This is a general “do not share memory” issue, you could fork any non-thread safe C code and see undefined behaviour.
Any other issues?
But only on sqlite, the "undefined behavior" means "all your data is gone". In postgres, you can crash or fail or get invalid results, but you are not going to lose all your data at once.
So in order to avoid SQLite database corruption, you need:
- hardware RAID disabled or reconfigured in JBOD ("IT") mode;
- RAID controller write cache disabled;
- RAID battery back-up cache disabled;
- individual drives' write caches disabled;
- ZFS;
- if using GNU/Linux, OOM turned off.
Even with turning off OOM, GNU/Linux's fsync() will still lie about I/O having completed, when it is in fact in transit. Therefore, if you want a reliable database, you must switch to a real UNIX, like SmartOS.
Only when all of these are done exactly as I have specified will you have a system ready for a relational database management system, and only then will a database be able to actually provide transactions.
The extra work of having to manually implement inter-thread coordination to prevent hitting the DB at the same time is a lot of work that never gets mentioned. When people talk about SQLite being able to handle tens of thousands of writes per second, there's a big asterisk, that's only if the application takes care of not making them write at the same time.
Doesn't have the functionality I was asking for, though – filed an issue here: https://github.com/beekeeper-studio/beekeeper-studio/issues/...
Writes can wait for one another. So you can still handle multiple writers, but serialized.
BTW, most people vastly overestimate their concurrency and write frequency needs. For example something like a CMS is 99.999% reads and 0.001% writes (yes, I pulled this out of my behind, but it's close).
For example: I used to work with a Couchbase cluster of 6 nodes, billions of documents (> 1TB of data) and doing roughly 25k-50k reads per second and every read was returned in < 1ms.
Are you telling me we could have used SQLite on a single instance and it would have also have an average end to end latency of < 1ms. I.e. from App server to DB and back was < 1ms? For 25k+ OPS/sec?
But SQLite has no hard limits on number of readers. There are no sockets, or Unix pipes. It reads off file cache in RAM, or if your data is bigger, it reads of disk. Like you would from a file. How'd you answer how many reads you can do from RAM? Kind of... depends on everything else, doesn't it?
I'd say it can have comparable performance to a single MySQL instance for simple read queries. If that helps.
https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
I still use Postgresql for most things - the tool support is genuinely a lot better. I also find the explain output much easier to understand.
There are also features which I occasionally use which Postgresql support such as partitioning, functional indexes, partial indexes. These can be emulated in some way with sqlite I'm sure, but they are fairly nice.
I have implemented some really critical stuff in sqlite and it's been amazing though. Anyone using sqlite in production needs to really understand the concurrency limits though.
There is only 1 situation where we actually care about the timezone of something, and it is stored on the basis of the user profile, not any specific timestamps. Time is just time. The TZ info is usually part of something entirely separate. Any timestamp can be interpreted in any tz, as long as you have a consistent UTC basis to operate from and knowledge of the desired tz.
If the concern is the final formatting, then maybe consider writing some UDFs in your app to quickly format fields. Comparison and sorting of 64 bit integers is trivial, so none of this should be a problem at all...
SQLite is probably not the right tool if you have concurrent access to the database, nor if you want to enforce database consistency through triggers.
It is the perfect application if you need a database that you can easily move around, access from a single application and your use case is simply to load and dump data with no further transformation. Anything more than that requires an RDBMS.
Which is perfectly fine for many of my sideprojects. I use SQLite and quite happy with that, but when I need to write to the DB I still prefer PostgreSQL
Sqlite is inherently single instance. What really makes this an issue is that it doesn't work safely/reliably on distributed filesystems like GlusterFS and Ceph.
See here for example of a project that needs to be forked in order to not have data corruption on remote filesystems (it depends on the WAL mode AIUI): https://github.com/Sonarr/Sonarr/issues/1886
I really do hope that this will be addressed in a future version of Sqlite, which would at least allow running it on redundant network-attached storage.
But even if it is, you will still have issues once you want to scale horizontally for performance.
If you're building strictly in-house proprietary software, none of this really matters as you have full control. But if it's either FLOSS or otherwise to be operated by anyone else than the developer and their internal organization, SQLite is not suitable.
I die inside a bit every time I am expected to take responsibility for a software built on Sqlite.
The only time you need to consider a client-server setup is:
- Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients.
- If your machine is extremely write busy, like accepting thousand upon thousands of simultaneous write requests every second, then you also need a client-server setup because a client-server database is specifically build to handle that.
- If you're working with very big datasets, like in the terabytes size. A client-server approach is better suited for large datasets because the database will split files up into smaller files whereas SQLite only works with a single file.
I have run SQLite as a web application database with thousands concurrent writes every second, coming from different HTTP requests, without any delays or issues. This is because even on a very busy site, the hardware is extremely fast and fully capable of handling that.
The claim is that most of the time you don't actually need multiple instances.
For example, deleting a column is a pain in the ass.
A single purpose script "rename_column.sh" would be nice, which combines all necessary steps and gives some guidance regarding edge cases.
What is a good way to use 3.35.4 on a system with a Debian version that comes with an older SQLite version?
Here is an article from Julia Evans explaining it: https://jvns.ca/blog/2019/10/28/sqlite-is-really-easy-to-com...
Or if you are familiar with Docker, you can use a more recent Debian in a container and install sqlite inside it.
Speaking generally, if the Debian version is the way to go, the easiest way is often to grab the source package from sid and build it. It's literally just one command. Sometimes new dependencies will cause trouble, in which case some manual tinkering is required.
[0] https://sqlite-utils.datasette.io/en/stable/cli.html#transfo... [1] https://datasette.io/
Where do you get this figure? It seems quite high.
> and no lock lasts for more than a few milliseconds
SQLite's amazing too though, when you don't need concurrency (and most websites don't really -- especially the ones that should be scaling vertically instead of horizontally).
Anyway here's some cool SQLite stuff:
- https://github.com/CanonicalLtd/dqlite
- https://github.com/rqlite/rqlite
- https://datasette.readthedocs.io/en/stable/
- https://www.sqlite.org/rtree.html
- https://github.com/sqlcipher/sqlcipher
- https://github.com/benbjohnson/litestream
- https://github.com/aergoio/aergolite
- https://sqlite.org/lang_with.html#rcex3
- https://github.com/sql-js/sql.js
- https://www.gaia-gis.it/fossil/libspatialite/index
- https://github.com/h3rald/litestore
- https://github.com/adamlouis/squirrelbyte
- https://github.com/chunky/sqlite3todot
Query execution latency for SQLite on the same machine running on NVMe disk can be measured in microseconds. You will never see this kind of performance across the network, or even loopback. Thus, there is literally no way from an information theory perspective, that Postgres, SQL Server, Oracle, DB2, MongoDB, Dynamo, Aurora, et. al. could ever hope to beat the throughput of (properly-configured) SQLite operating embedded in the application itself.
Need network? Postgres. Don't need network? SQLite.
Distributed systems are largely a mistake and amount to a lack of understanding regarding what is actually possible to extract from a single x86 server.
However, for querying, SQLite makes a nice caching layer.
Are you doing this in LocalStorage?
No.
One of the troubles with having a single machine is that your service will become unavailable when you upgrade it.
So basically I can just say the same thing you said with C: "I just fopen/fgetc strings to disk. C is my query language."
if by out-of-order you mean loading parts of a file, of course we don't do that.
we don't load everything into memory (there's more than one file). just like you wouldn't load your whole sqlite db into memory.
if the machine crashes we lose what we didn't save. same with a db.
> So basically I can just say the same thing you said with
> C: "I just fopen/fgetc strings to disk. C is my query
> language."
the main problem with our approach is that the server needs to have access to the edn files. that's not always doable ... but same problem with sqlite.
and it is oh so nice to investigate stuff in the repl:
(:prio (load-file (filter #(= "secret-id" (:id %)) index)))
With sqlite, creating an index is a one-liner. How do you cover that in clojure? With sqlite, a whole database fits in one file that can be read by almost any macos / linux machine. With clojure, you need your specific program created on top of clojure installed in the machine. Etc.
At the end of the day, you can reimplement sqlite on clojure, which basically proves that we are talking about different abstraction levels, thus apples to oranges.
SQLite is embedded in your application. You can write user-defined functions in your application logic that are then bound to SQL functions. These functions are then treated just like first-class functions built-in to the dialect:
https://www.sqlite.org/appfunc.html
The way Microsoft exposes this in their provider is really wonderful. Using aggregate UDFs feels like cheating. We have also done some next-level stuff where we can call a UDF that executes arbitrary SQL command (i.e. thus invoking subsequent UDFs recursively). We found that UDFs don't necessarily need to be deterministic and can mutate the outside world...
https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...
If you think along this axis, you would find SQLite as more a blank canvas that you can construct a very domain-specific view of the world upon and then leverage it for implementing complex business logic. You can get from "lots-of-code" to "no-code" applications pretty damn fast if you play this game right.
connection.CreateFunction(
"regexp",
(string pattern, string input)
=> Regex.IsMatch(input, pattern));
var command = connection.CreateCommand();
command.CommandText =
@"
SELECT count()
FROM user
WHERE bio REGEXP '\w\. {2,}\w'
";
var count = command.ExecuteScalar(); var myUdfInstance = new MyUdfInstance(connection);
connection.CreateFunction(
"Execute",
(string command)
=> myUdfInstance.ExecuteSql(command));
Go on
If, on the other hand, you're just using it as a transactional storage engine, none of this stuff really matters and I understand the praise--hence why I think the two modes of use should be differentiated. The other thread responding to me is basically all about extending the very barebones provided interface with custom, application-level semantics. I agree that SQLite is excellent for this, but you have to remember that this is not the only thing people want out of a database.