Managing local development environments kind of sucks when databases are involved. I solved that for me utilizing Docker and VS Code Tasks so I never have to think about it again.
tl;dr: Code.
I don't really have a homelab. I sometimes envy people that do, but it's entirely too much work for me. There's a few Raspberry Pis laying around but they all have a job and don't have the capacity to do much more than that. So I don't have a local server standing around that could run a database.
When developing something that needs a database I still need one for testing, though. Using different database adapters locally and on the server is not always possible (looking at you, Prisma!). So I need to run as PostgreSQL server on my own computer. I don't want to have it running all the time even when doing something else, but I also don't want to start and stop it manually every time I need it. And while we're at it, if I could have strict separation between projects without thinking about schema names, that would be great!
So I wrote a small hand full of scripts and config files to do just that for me. The main script is a shell script that starts PostgreSQL in a docker container utilizing nothing but local directories. That script is made to run in macOS and should be usable in Linux without additional work. This should be portable to Windows, but I don't need that. If you made it work get in touch and I'll add the script!
run_postgres.sh
1HOST_PORT=5432
2NAME=postgres-dev
3DOCKER_REPO=postgres
4TAG=15.4
5
6cleanup() {
7 echo "Stopping docker container..."
8 docker stop ${NAME}
9}
10trap 'cleanup' SIGTERM EXIT
11
12docker run --rm --name $NAME \
13 --volume `pwd`/pgdata:/var/lib/pgsql/data \
14 --volume `pwd`/mnt_data:/mnt/data \
15 --volume `pwd`/pg_hba.conf:/etc/postgresql/pg_hba.conf \
16 --volume `pwd`/postgresql.conf:/etc/postgresql/postgresql.conf \
17 -e POSTGRES_PASSWORD=password \
18 -e POSTGRES_USER=postgres \
19 -e PGDATA=/var/lib/pgsql/data/pgdata14 \
20 -e POSTGRES_INITDB_ARGS="--data-checksums --encoding=UTF8" \
21 -e POSTGRES_DB=db \
22 -p ${HOST_PORT}:5432 \
23 ${DOCKER_REPO}:${TAG} \
24 postgres \
25 -c 'config_file=/etc/postgresql/postgresql.conf' \
26 -c 'hba_file=/etc/postgresql/pg_hba.conf'
27
This starts a docker container, mounts the data directories and all necessary config files to local paths and binds the port to one I can access. The user and password are configured here and they may be very simple as shown here – the database is only reachable from your machine and only used for development work after all. You wouldn't put sensitive data into your local database, right?
The first config file is the general PostgreSQL configuration.
postgresql.conf
1# PostgreSQL configuration file
2# See https://github.com/postgres/postgres/blob/master/src/backend/utils/misc/postgresql.conf.sample
3
4#------------------------------------------------------------------------------
5# CONNECTIONS AND AUTHENTICATION
6#------------------------------------------------------------------------------
7listen_addresses = '*'
8port = 5432
9max_connections = 100
10
11#------------------------------------------------------------------------------
12# RESOURCE USAGE (except WAL)
13#------------------------------------------------------------------------------
14shared_buffers = 1024MB
15work_mem = 40MB
16maintenance_work_mem = 640MB
17dynamic_shared_memory_type = posix
18max_parallel_workers_per_gather = 6
19max_parallel_workers = 12
20
21#------------------------------------------------------------------------------
22# WRITE-AHEAD LOG
23#------------------------------------------------------------------------------
24checkpoint_timeout = 40min
25max_wal_size = 1GB
26min_wal_size = 80MB
27checkpoint_completion_target = 0.75
28
29#------------------------------------------------------------------------------
30# REPORTING AND LOGGING
31#------------------------------------------------------------------------------
32logging_collector = off
33log_autovacuum_min_duration = 0
34log_checkpoints = on
35log_connections = on
36log_disconnections = on
37log_error_verbosity = default
38log_min_duration_statement = 20ms
39log_lock_waits = on
40log_temp_files = 0
41log_timezone = 'UTC'
42
43#------------------------------------------------------------------------------
44# AUTOVACUUM
45#------------------------------------------------------------------------------
46autovacuum_vacuum_scale_factor = 0.02
47autovacuum_analyze_scale_factor = 0.01
48
49#------------------------------------------------------------------------------
50# CLIENT CONNECTION DEFAULTS
51#------------------------------------------------------------------------------
52datestyle = 'iso, mdy'
53timezone = 'UTC'
54lc_messages = 'C.UTF-8'
55lc_monetary = 'C.UTF-8'
56lc_numeric = 'C.UTF-8'
57lc_time = 'C.UTF-8'
58default_text_search_config = 'pg_catalog.english'
59shared_preload_libraries = 'pg_stat_statements'
60
I put some lines in here to make it not take up that many resources and have a database that works more or less like one I'd use in a production environment. If you know how to configure PostgreSQL you can change whatever you prefer – if you just want a working database this configuration should be good enough.
The second configuration file is the PostgreSQL client authentication configuration..
pg_hba.conf
1# PostgreSQL Client Authentication Configuration File
2# ===================================================
3# TYPE DATABASE USER CIDR-ADDRESS METHOD
4
5# Database administrative login by UNIX sockets
6# "local" is for Unix domain socket connections only
7local all postgres ident
8local all all ident
9
10# IPv4 local connections:
11host all all 172.17.0.0/16 md5
12host all all 192.168.0.0/16 md5
13
14# IPv6 local connections:
15host all all ::1/128 md5
16
I just configured it to allow access to all databases to all users coming from the local network, either via IPv4 or IPv6. I expect calls to be authenticated with a password. I'm allowing md5 just to make sure I can use older clients that do not support newer methods – please do not use md5 in production as it hasn't been considered secure for literal decades. I could have just allowed "authentication" without password for all the users (the password I use is password after all) but as nobody would ever do that in the real world for sure I've went with something more realistic.
I put all these files in a directory called database as I have never used that as a folder in an app. Calling run_postgres.sh boots up the database, creates all the folders if necessary and I can start using it and the database winds down as soon as I end the script. But that's still not automated enough for my liking.
Visual Studio Code allows me to create tasks I can run from the command palette with the command Tasks: Run Task. You may have already used that to call npm scripts or something, but there's a way to create arbitrary tasks. In the project root folder you have to create a file called ./vscode/tasks.json. The documentation what to put there is available here but a simple configuration to start the database would look like this:
tasks.json
1{ 2 "version": "2.0.0", 3 "tasks": [ 4 { 5 "label": "Database", 6 "type": "shell", 7 "command": "./run_postgres.sh", 8 "options": { 9 "cwd": "./database" 10 }, 11 "runOptions": { 12 "runOn": "folderOpen" 13 } 14 } 15 ] 16} 17
The task is called Database, it's a shell task, the working directory for that is ./database (relative to the project root) and the command to run is ./run_postgres.sh (relative to the working directory). That's basically it. But the real magic is the runOptions! Here we tell Visual Studio Code to run this task whenever we open the project so the database is available automagically. As Visual Studio Code closes all shell sessions and in doing so terminating the script the cleanup() function is called when closing VS Code or switching workspaces, so the database will be stopped.
Generally, I add all this to my .gitignore. I don't want to tell other people how to do it or even to use VS Code. If you want to put this under version control you should at least ignore the folders database/mnt_data and database/pgdata as that's where PostgreSQL puts its data.
There are a few problems with this approach. First, it does not allow multiple workspaces to be open in parallel, as they would use the same docker container name and port. That could be alleviated by setting a different name and port per project, but I don't need to have multiple workspaces open very often and prefer the easier setup of a standardized config.
Second, especially when using the Developer: Reload Window command often (the Typescript language server seems to need a well-meant slap from time to time) the stop and run command for the same container are sometimes executed out of order. I can live with that as that just means that the task will fail and I have to restart the task and it doesn't happen very often to begin with.
So, yeah. Writing and explaining all those files took some time, but once you have them it's just a matter of seconds copying them to another project and get going with your local database. I really like that workflow of not thinking about databases at all and I hope I inspired someone to think about databases less. Be sure to let me know how you handle your databases!