all that jazz just for something sqlite absolutely crushes. not to mention that it is much faster in almost every single-writer scenario than all other databases.
edit: the snapshoting for yolo experiments thingy is pretty cool!
I've seen enough odd differences in behavior between postgres and sqlite, especially when it comes to numeric fields, that I'm no longer comfortable developing (much) on sqlite when I intend to deploy with postgres.
I'm a big sqlite fan, and use it lots of places, but sometimes it's more important to match what's on the server than to get sqlite's performance in my dev environment.
Yeah, exactly. Sometimes, you use PostgreSQL because you need what it brings to the table; this way, you can do that with less ceremony and hassle. This is very clever.
I think the point here is to have the convenience of project-local database instances in development while still having the advantages of PostgreSQL in production.
This is fantastic. I really value the ability to install and run complex dependencies like PostgreSQL at the local folder level, without having to worry about them being installed globally, clashing with other versions etc.
I get that they're biting off a slightly different use case with shipping their own embedded PostgreSQL but if you have the regular server binaries installed you can create a database cluster in a local directory with initdb and then start/stop it with pg_ctl. I do it for many of my personal projects, I just leave a shell command / make target / etc out for myself to call pg_ctl with the right arguments.
PostgreSQL is a good UNIX citizen and works great for this local use case. The database doesn't need to run as root and you don't even need to worry about TCP port conflicts if you configure it for UNIX domain sockets and disable listen_addresses.
It's pg_ctl start -D ... -l ... and then later, if you're in the same directory, pg_ctl stop -D ...
I usually set -l so I can use that terminal for something else but there's nothing stopping you from leaving the logs open.
It's also useful to have a shell script that doesn't just run initdb but creates your initial databases/users, loads extensions, etc. All the work that has to happen before you can run schema migrations. For small enough databases I straight up create the database directory on a ram disk.
bernard | 6 hours ago
all that jazz just for something sqlite absolutely crushes. not to mention that it is much faster in almost every single-writer scenario than all other databases.
edit: the snapshoting for yolo experiments thingy is pretty cool!
colonelpanic | 5 hours ago
The problem is, that you will be talking to a SQLite database.
hoistbypetard | 5 hours ago
I've seen enough odd differences in behavior between postgres and sqlite, especially when it comes to numeric fields, that I'm no longer comfortable developing (much) on sqlite when I intend to deploy with postgres.
I'm a big sqlite fan, and use it lots of places, but sometimes it's more important to match what's on the server than to get sqlite's performance in my dev environment.
jfb | 3 hours ago
Yeah, exactly. Sometimes, you use PostgreSQL because you need what it brings to the table; this way, you can do that with less ceremony and hassle. This is very clever.
lpil | 5 hours ago
I think the point here is to have the convenience of project-local database instances in development while still having the advantages of PostgreSQL in production.
simonw | 3 hours ago
This is fantastic. I really value the ability to install and run complex dependencies like PostgreSQL at the local folder level, without having to worry about them being installed globally, clashing with other versions etc.
It looks like the secret sauce that makes this work is https://www.npmjs.com/package/pg-embedded - which uses the Rust crate https://crates.io/crates/postgresql_embedded - which uses the PostgreSQL binaries from https://github.com/theseus-rs/postgresql-binaries
Looks like there's a Python wheel that bundles PostgreSQL here: https://pypi.org/project/pgserver - see also https://github.com/orm011/pgserver and view the wheel contents with my tool here: https://tools.simonwillison.net/zip-wheel-explorer?package=pgserver
lordgilman | 3 hours ago
I get that they're biting off a slightly different use case with shipping their own embedded PostgreSQL but if you have the regular server binaries installed you can create a database cluster in a local directory with
initdband then start/stop it withpg_ctl. I do it for many of my personal projects, I just leave a shell command / make target / etc out for myself to callpg_ctlwith the right arguments.PostgreSQL is a good UNIX citizen and works great for this local use case. The database doesn't need to run as root and you don't even need to worry about TCP port conflicts if you configure it for UNIX domain sockets and disable
listen_addresses.oliverpool | 3 hours ago
Exactly what I wanted to ask.
So running something like:
Right?
lordgilman | 2 hours ago
It's
pg_ctl start -D ... -l ...and then later, if you're in the same directory,pg_ctl stop -D ...I usually set
-lso I can use that terminal for something else but there's nothing stopping you from leaving the logs open.It's also useful to have a shell script that doesn't just run initdb but creates your initial databases/users, loads extensions, etc. All the work that has to happen before you can run schema migrations. For small enough databases I straight up create the database directory on a ram disk.
Zurga | 5 hours ago
Would this not be better in bash? Now i need to install bun to get postgres.