I have used flyway at the jvm-dayjob for almost a decade now. As I'm outside of jvm outside of the dayjob, I've been searching for similar non-bad way to manage database for a while now. I might've written even my own migration management system, with... lousy results

Currently I'm experimenting with a hack, where migration management isn't in the address space of the lisp image, but instead it's done in the makefile. The directory tree looks like this:

feuer@MacBookPro pyoratracker % ls -la
drwxr-xr-x@ 14 feuer  staff     448 Dec  6 20:34 .
drwxr-xr-x  30 feuer  staff     960 Dec  5 15:44 ..
-rw-r--r--@  1 feuer  staff    1644 Dec  6 19:21 Makefile
drwxr-xr-x@  4 feuer  staff     128 Dec  5 21:18 migrations
feuer@MacBookPro pyoratracker % ls -la migrations/ 
drwxr-xr-x@  4 feuer  staff  128 Dec  5 21:18 .
drwxr-xr-x@ 14 feuer  staff  448 Dec  6 20:34 ..
-rw-r--r--@  1 feuer  staff   99 Dec  5 19:49 1_equipment.sql
-rw-r--r--@  1 feuer  staff    0 Dec  5 21:18 1_equipment.sql.done    

The makefile's relevant parts look like this:

# DB settings. Replace if yours differ.
POSTGRES_USER = "bikes"
POSTGRES_DB = "bikes" 
POSTGRES_PASSWORD = "bikes"
POSTGRES_PORT = 5432

# Make seems to not expand * by default 
MIGRATIONS = $(wildcard migrations/*.sql)
# subst every STUFF.sql => STUFF.sql.done
MIGRATION_STAMPS = $(patsubst %.sql, %.sql.done, $(MIGRATIONS))

# implement migration system 

$(MIGRATION_STAMPS): %.sql.done: %.sql
	@echo Running $<...
	@psql -f $< postgresql://$(POSTGRES_USER):$(POSTGRES_PASSWORD)@localhost:5432/$(POSTGRES_DB)?sslmode=require && touch $<.done

.PHONY: migrate
# they can't be ran in parallel for obvious reasons 
.NOTPARALLEL: migrate
migrate: $(MIGRATION_STAMPS)	## Runs the migrations
	@echo Migrated

With this makefile, one can call `$ make migrate` and make runs every .sql-file in /migrations, that doesn't have a corresponding .sql.done file, through psql.