SQLite is one of the best pieces of software I have used in my career as a developer. It is performant, reliable, simple and consistent. There is a reason sqlite3 is deployed in so many places.
Although to be fair, I have no idea if this is any less common with other DB's.
Anyone know more details?
Edit: ok, I believe it now: https://www.sqlite.org/th3.html
I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.
None of the apps I've written have hitten any kind of crazy traffic peak, so I started wondering why the hell I was using postgres or other database types after reading https://www.sqlite.org/whentouse.html.
Seeing how simple SQLite has been for me to use has inspired me to write a bunch of dead-simple good-enough approximations for other tools that exist. High up on my list is a Graylog competitor that just uses SQLITE FTS (https://sqlite.org/fts3.html/https://sqlite.org/fts5.html), that just is super easy to start and handles that micro-to-mid-size case before you really need something like graylog or ELK.
Blog post if anyone's interested: https://vadosware.io/post/adding-sqlite-powered-fts-search-t...
I think for most smal projects SQLite + FTS offers a pretty simple easy to setup solution that doesn't require much compute power, either. The simplicity is also a bonus.
If you actually need any of these things you can enforce them in code, by convention, but you're better off installing Postgres.
Also writes are serialized. That one really can't be worked around.
SQLite has supported CHECK() for a while. So, for example, you can do:
CHECK(LENGTH(col) IS BETWEEN 1 AND 16)
Across multiple processes, but there is a shared cache and read uncommitted mode available otherwise.
This is true, and explicitly acknowledged by the author:
> SQLite does not compete with client/server databases. SQLite competes with fopen().
https://sqlite.org/whentouse.html
If you're deciding between Postgres and SQLite, in most cases you're doing it wrong (there are a few exceptions, such as serving a low-traffic almost-entirely-read dynamic website can be accomplished easily with either). Usually you want to choose between SQLite and "anything else that involves writing directly to the local filesystem".
Think about requirements first, then tech/implementation approach.
But yeah, like everyone else, I often just start with SQLite anyway!
The concurrency model just doesn’t match up well with multiple web processes doing work.
If you are writing on every request, Murphy’s law says that too many of those 500 visitors will be loading pages at exactly the same time...
If you're logging user visits for example, you can queue those up in a list (in the code) and flush it to the database every few seconds instead of flushing it on every visit.
That won't work in the language that most server-side website code is written in. PHP processes are created and destroyed per HTTP request, and even the FastCGI implementation won't let one share data across requests. PHP does have a sessions feature, but that data is written to either the filesystem (by default) or a database on every request!
I just don't understand why people can't see the logic behind using something operationally simple like SQLite.
I have written fairly large web-apps taking fairly decent traffic, and relying solely on SQLite, unless really pressing circumstances required otherwise. Healthy exercise too: Sharpens your focus, "Do I absolutely need to do a write here?".
SQLite favours a very permissive approach, while PostgreSQL favours locking everything down with strict types and strong integrity checks, and it's very easy to verify lots of details of your data before it's accepted into the database. That takes work to set up properly and maintain, but it catches bugs early and reduces the number of headaches you get when trying to use your data in new code.
Scalability is a very different problem that happens to favour PostgreSQL, but most projects indeed never get far enough that this matters.
But from a developer experience point of view, the main difference between dynamic and static typing is the following:
Dynamic typing requires a lot of extra checks to ensure everything is what you expect it to be, and a lot of annotations to document what everything is supposed to be.
In other words, dynamic typing has a high cognitive tax.
With this in mind, I find that PostgreSQL imposes a cognitive tax in terms of maintaining a separate process (potentially on a different machine) that a lot of times is outside your direct control. Not to mention all the strange ways in which it can break.
SQLite just works. There's nothing to configure. So there's a lot less cognitive tax.
There is still of course the tax of having to ensure that all your sql statements are valid and that they return what you expect them to return, but this is also the case with PostgreSQL.
RE static vs dynamic typing, I'd say that it's dynamic typing that has higher cognitive tax - you as a programmer are fully responsible for ensuring types agree up everywhere, whereas with static typing, all of that job is done by compiler. It's easier to change stuff in the code when you know the compiler will catch your dumb mistakes. And I say that as someone who loves writing in Common Lisp.
But the point about SQLite vs a typical RDBMS is spot-on, IMO. SQLite is a library. It operates on files. That's it. It's entirely local. You can use it without having to become a sysadmin, without having to set up a whole service on the OS on which you might not even have root privileges anyway. You don't have to make global changes to the system just for your program. Moreover, if your product is of the distributable kind (desktop apps, self-hosting web stuff) you don't need to make your users become sysadmins, manage a system-wide service, acquire root rights they might not have, etc.
SQLite is local. Its data is local. That's, IMO, its strongest benefit.
Yes, that's exactly what I said.
Not so much for quite a while, though.
On the flip side, setting up a PostgreSQL instance is so easy now with docker that you could go ahead and use it with a fraction of the admin overhead that it used to require:
I just find it weird and confusing.
Also, how do you setup a database in docker? Docker does not support persisting data as far as I know. So if you need to restart the thing, all the data will be wiped. At least that's how it seems to be.
There are a variety of opinions on the goodness of using docker for running a database.
Install docker and docker-compose, write a simple docker-compose.yml file, and then `docker-compose up -d` in the same directory as the docker-compose.yml file. You now have a full Postgres instance running on your machine at localhost:5432. Stop it with `docker compose down`
DB files are stored in the `pg` folder in the same directory as docker-compose.yml. You can specify this on the `volumes` property.
docker-compose.yml:
version: '2'
services:
pg:
image: postgres:9.4.10
ports:
- "5432:5432"
volumes:
- $PWD/pg:/var/lib/postgresql/data/pgdata
environment:
- PGDATA=/var/lib/postgresql/data/pgdata
- POSTGRES_DB=YOUR_DB_NAME_HERE
- POSTGRES_USER=YOUR_DB_USER_NAME_HERE
- POSTGRES_PASSWORD=YOUR_DB_PASSWORD_HERE /usr/lib/postgresql/9.5/bin/postgres -D .mydatadirThis is especially helpful if some developers use OS X or Windows.
I don't understand the "all of that" phrasing, as if what I just described was a large amount of work.
Docker is useful for running components of your own code or for isolation, not for managing versions of core applications like a database or a mail server for most people deploying ready-made apps.
I've tried several different dev environment setups and this approach yields the fewest frustrations and inconsistencies.
Production is a different story but that's not what I'm discussing here.
I can see an app relying on features not available in older releases, but databases - of all software - have rock-solid backwards compatibility.
To me, the value is in reducing the number of potential issues a developer can have in their environment and making the build and runtime as consistent as possible. And when the solution is so easy to use, I don't see much of a downside to the docker approach.
My team switched from Vagrant to docker compose for our development environments and since then we've had much more stability and have wasted much less time tending to them.
All our "system software" — Postgres, Redis, Memcached, RabbitMQ, Elasticsearch, etc. — run as containers on Kubernetes. Doing so allows us to start new versions without considering the host OS. For example, we can upgrade Postgres to a new release the moment it's out, without having to wait for Debian or Ubuntu or whatever.
It also allows us to run different versions (e.g. a new one for testing in our "staging" environment) concurrently without any concerns about which node is running what. In fact, almost nothing needs to be configured with information about "nodes"; everything is routed to the right place, and the client only needs a name to connect to.
Sometimes we start temporary instances of software, e.g. to experiment with. Bring the container up, do the testing, bring container down; nothing ever got installed on the host OS.
My whole point is that I want to simplify my setup, so that I don't need tons of third party servers.
In some systems such as kubernetes, data is not saved on a persistent disk. But by default docker containers can retain data. However this is generally solved by using volumes, which you can read about here: https://docs.docker.com/engine/admin/volumes/volumes/
So, if you want to keep it simple:
docker run postgres --detach --volume /save/my/data/in/this/path:var/lib/postgresql/data
Even if you don't specify a path for the data, the container would save its data in a Docker volume (a file), which doesn't get deleted until you explicitly ask it to. If you stop the container and restart it, it'll reattach to the same volume and find the data. If you delete the container without explicitly deleting its volumes as well, you'll be able to create another postgres container and attach the old volume to it.
Why do you ask?
How easy it is, in practice, to switch from one SQL database to another?