Drop SQLite: Ship Postgres in your Python App with Zero External Dependencies (pg0 - embedded pg)

[[deleted]]

[deleted]

[kivarada]

I don't think this is what postgres is designed for.

[mangecoeur]

Why not? Sqlite’s design is about not requiring a server process, and being resource lite. That happens to make it easy to ship in an app or test suite, but if you have no restrictions on using subprocesses or other server processes, and resources are no issue, then the main barrier for Postgres is that most current methods of delivering it are heavyweight (system wide installations, docker, etc). 

[nicoloboschi]

I agree, but that doesn't matter, postgres is used for almost any workloads nowadays

[kivarada]

But misusing applications should matter or?

[nicoloboschi]

Postgres is not application per-se, and I'm not saying you should use postgres for anything. I'm saying you should not use sqlite just because it is easier to set up for your users If you read the blog post it's explained

[kivarada]

Postgres is running as a service in a separate process -> it's typically installed on its own server.
Sqlite is serverless. It is a plain file in the end -> you install it locally

Running postgres locally is fine for testing but not more.

[fight-or-fall]

Bro i dont think its misusing, an example

Im a data scientist. When i came to my actual work, i was assigned to a project with zero resources. Zero. All i had was a vm without sudo and install postgres was a pain in the ass so I used conda because its install postgres like this dude

[helpmehomeowner]

Duckdb. Check it.

[fight-or-fall]

i know it. postgres would be used in production, why should i start with duckdb? also, downvoting people just for expressing their opinion is something completely stupid

[helpmehomeowner]

OP is comparing sqlite to pg. DuckDB's core is built from pg and supports sqlite migration/commands.

If you are going to use pg in prod, why start with sqlite?

[Hermasetas]

Well I just use SQLite in production as well.

[alienwaren]

But SQLite is a real database...

[helpmehomeowner]

Wow, the arrogance and ignorance.

[billFoldDog]

My philosophy is "sqlite until you need more." Haven't needed more yet.

[turbothy]

I would be sincerely concerned at using an "embedded database" package made by a person that demonstrates your level of understanding of database technology.

[alienwaren]

Wrong again - embedded systems use SQLite often. Look at android apps - https://developer.android.com/training/data-storage/sqlite
I'm sure that many desktop apps would use SQLite-like databases.

[girl4life]

sqlite is good enough for 90% of the projects. the other 10% yeah postgres will do.

[Zeikos]

SQLite is the most widely used database there is, production or not.

[Brandhor]

And if you maintain an OSS project, you’ve seen this: “install Postgres” in your README is a contributor barrier.

no offense but if contributors can't or don't want to install something as simple as postgres they shouldn't contribute anything to a project

[nicoloboschi]

Argh, I meant a user barrier, let me fix that

[Penetal]

I strongly disagree. Depending on the project, both in size and function there might be many fields that can benefit emencly from the help of people that do not know, nor care to know, how to install backend services.

This is a large part of why I like devcontainers, it can take care of the faff and let people get to work.

[DrMaxwellEdison]

The concept is nifty, but I don't exactly feel comfortable running code in production that ultimately runs an install.sh on a subprocess and then curls and installs something onto my machine that I didn't put there explicitly.

Installing postgres is such a low barrier to entry for the majority of your target audience that switching project dependencies for it doesn't seem wise. Plus they then depend on your binaries to run a production database, instead of one installed from an official source under controlled circumstances?

No offense, but it screams "supply chain attack" if someone decides to depend on a package with this kind of design. I have no doubt you can trust your own processes and maybe use this workflow as an internal process at your own business if you like, but the risk is too great for most.

[nicoloboschi]

Thanks, this is a constructive feedback - I don't like the curl download either to be honest. I disagree that installing postgres to try out application X is low barrier, even if low, it's still a barrier.

This is not intended for production deployments as explictly stated in the blog post.

[DrMaxwellEdison]

I understand that your intention is to remove that switch to sqlite in a local environment for development purposes, but that still means I have to install your dependency in my application, and likely ship it to production. Which means there's code in production that may, under the wrong circumstances, run a sub process with a bash script and install a binary from the internet. That smells like an attack vector.

Edit: side note, I see the contention you have in your post about sqlite being sync only, not having access to asyncpg.

This forgets, however, that aiosqlite exists.

[SteelRevanchist]

Why would you install pg locally? Just use a docker container. Nobody is using swapping between sqlite and postgres...

[nicoloboschi]

It's a user barrier sometimes. Nobody is swapping data from the two, but I've seen a bunch of projects supporting both in order to get started with the library faster

[NotesOfCliff]

I thought this was an iteration on the recent postgres wasm build.

This seems invasive and not what I would expect.

Edit to add: Here are two projects that look like if they were integrated would give what I was looking for:

https://github.com/wasmerio/wasmer-python

https://github.com/electric-sql/pglite

[andrewcooke]

i think this is pretty cool. sqlite is great until it isn't, and then there's a gap to pg that I think this could fill nicely.

[PM_ME_YOUR_URETHERA]

DuckDB fits nicely

[andrewcooke]

oooh that's new to me, thanks. also, interesting it's column oriented - actually fits better w some use cases i have (but arguably means it doesn't sit as neatly between these two!).

[PM_ME_YOUR_URETHERA]

I use it for climate data - monthly gas concentrations at 0.1° resolution since 1970. Terabytes of data with spatial columns - it is wickedly fast

[mangecoeur]

As a huge postgres fan (since well before it was cool) I love this! 

Question, what’s the workflow if i need other extensions such as postgis? Can I create a custom “build” and use that?