Still, PGSQL requires some level of setup/maintenance: create user, permissions, database, service with config/security effort. If there is a way to run PGSQL as lib and say to it: just store your data in this dir without other steps, I am very interested to learn.
Steps will be:
- sudo apt install postgres (more steps are needed if you are not satisfied with OS default version)
- sudo su postgres
- psql -> create db, user, grant permissions
- modify hba.conf file to change connection permissions
- modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware
Another options would be to build your docker image, but I am not good with this.
- sudo apt install sqlite3 # you're going to want the client
- sudo chmod myapp db.sqlite3
- sudo chmod myapp . # make sure you have directory write permissions for WAL/journal
- sudo -u myapp sqlite3 db.sqlite3 'PRAGMA journal_mode=WAL;'
Nothing is ever single-command easy in all cases.
I am familiar with Java and H2, you just say in config somewhere: store db in this directory, and you are all set.
I think there is no such route currently for PGSQL.
Which isn't bad. This is a pretty low bar to clear.
(All that said, I am of the opinion that separating data storage from application logic is better for operations, for security, and for disaster recovery unless you desperately need to prioritize avoiding network latency above everything else, so this is all pretty moot to me.)
That seems pretty... odd on their part. I know that Ruby gems in particular can and do include C sources, so there's no reason it couldn't ship sqlite3.c (or even the various .c files that get combined into sqlite3.c); if you're able to install Nokogiri (i.e. you have a usable C toolchain), then you should be able to compile sqlite3.c no problem.
Meanwhile, Python ships with an "sqlite3" module in its standard library (last I checked), so if you have Python installed at all then you almost certainly already have SQLite's library installed.
I think the thinking is that packing The Entire Library rather than your necessary bindings is overkill/duplication/etc etc. - it's the static versus dynamic argument in a sense, even though you may be dynamically linking while you pack your own library. Duplication versus OS-controlled security patching? Admittedly when most deployables end up being Docker containers (for good or for ill) that's less important, but most of these tools are from a distinctly earlier era of deployment tools.
It does, but it still compiles bindings for it. My point is that if you're able to compile C code at all (including Nokogiri's bindings to libxml2), then strictly requiring some external libsqlite3.so is unnecessary - because it's pretty dang simple to just compile sqlite3.c alongside whatever other C code you're compiling.
> And I think--don't quote me, Python isn't my thing and I only looked briefly--that Python on Ubuntu requires the `python-sqlite` APT package, which depends on both `python` and `libsqlite0`
Right, and my point there is that if you've installed Python, then in doing so you've already installed SQLite (because your package manager did so automatically, because it's a dependency), so you don't need to run an additional command to install SQLite.
I think usual way to do it is say having shared library file like sqlite.so in some directory or installed through OS package manager, and thin API binding for specific runtime in form of runtime package (e.g. npm).
> you desperately need to prioritize avoiding network latency above everything else, so this is all pretty moot to me
Goal here is easy distribution of embedded DB. Many apps are doing this with SQLite, for example Dropbox client. Many other examples: https://www.sqlite.org/famous.html
> you could conceivably run postgres on your app server
So what you've been saying has been taken in the context of packing sqlite or Postgres next to your web application, which has pretty limited use cases, and reads really weirdly in that light.
But there is another approach: one self contained binary/artifact which can include embedded DB too, and be launched by one command.
I don't want stand-alone client for app which customer downloaded from my site.
Also, docker adds complexity itself.
docker run -it -e POSTGRES_PASSWORD -p 5432:5432 postgres:12
And bam, you have a server
Like:
FROM postgres:14.3
COPY pg/pg-config.sql /docker-entrypoint-initdb.d/000-pg-config.sql
COPY pg/schemas.sql /docker-entrypoint-initdb.d/010-schemas.sql
COPY pg/meddra-tables.sql /docker-entrypoint-initdb.d/020-meddra-tables.sql
# ...
RUN chmod a+r /docker-entrypoint-initdb.d/*
ENV POSTGRES_USER=myapp
ENV POSTGRES_PASSWORD=xxx
ENV POSTGRES_DB=mydb
docker exec postgres psql -h localhost -p 5432 -U ${DBADMINUSER} -c "some SQL statement"
Also, I usually change hba.conf file too, to disallow outside connections.
If you need all this customization then did SQLite really fit the bill in the first place?
I need to change config for prod pgsql instance, because default config performance wise is not necessary suitable for serious prod machines.
> If you need all this customization then did SQLite really fit the bill in the first place?
I am not familiar with SQLite, but somehow familiar with Java H2 DB, which has similar idea. You can embed it and all configs into self contained java jar file together with your app and no moves in host OS is needed.
- [1] https://aws.amazon.com/rds/
- [2] https://azure.microsoft.com/en-us/services/postgresql/#overv...
yeah, premium is very large if I want to do a lot of data crunching (lots of iops, cores and ram is needed), plus latency/throughput to those services will be much higher than to my local disk through PCIE bus.
I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.
Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc.
It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS).
If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
Recent SQLite versions have STRICT mode where they forbid that.
https://www.sqlite.org/stricttables.html
> or too-long data in a too-short column
You can use a constraint for that:
CREATE TABLE test (
name TEXT NOT NULL CHECK(length(name) <= 20)
) STRICT;For most business applications used by more than one user there are usually some expectations that data will be retained and the application will be available.
If you might want to use multiple app instances connected to a shared DB, I would say it's probably easier to just use a local postgres container for dev and a managed cloud DB. Really not that much effort and you get automatic backups.
If you plan to never use a shared DB, SQLite is great though.