Derek Sivers's database and web apps

Summary

I'm Derek Sivers @ https://sive.rs/ This is my database and the web apps that use it. My data is private but code is public. See LICENSE. Master code repository is on my private server, but mirrors to: These are only mirrors. I don't monitor these sites or take pull requests. This is the heart of it. Start there. Read that. tables.sql has circular references, so foreign keys added after data is loaded. All functions - (except for a few at the top of tables.sql) - are in schemas. Schemas can be dropped and reloaded without losing the data in the tables. Run scripts/reset.sh every time a function has changed, to reload them all. This also creates a database for testing. More on that, below. Functions used by multiple web apps. Schema name: o . One file per function except TRIGGERS.sql For each function, tests in the test/ directory, in a file with the same name. Tests use pgTAP. See https://pgtap.org/ Tests are run on the test database ( siverstest ) a clone of the live database. Minimal data is inserted for testing just this function. This makes it easier to understand without keeping anything else in mind. To run a test, boilerplate and psql env vars are in a script called tap . It's in scripts/tap here, but put it in your $PATH so you can always do ... tap thatfile.sql ... to run that file's tests with nice output and rollback. Or to run all tests: for f in *.sql ; do tap $f ; done Hands-on or cron, shell scripts are here. HTML templates to be loaded into templates table, where code = file name. Never used on disk, only in the database, but put here for easy editing. Mustache parser inside PostgreSQL - see omni/template.sql HTML in PostgreSQL? Typical db-driven web apps get values from db, then merge into HTML templates. Aiming for simplicity - less coupling - I do that step directly in PostgreSQL. Router calls PostgreSQL functions and gets a full HTML response. Pass it directly to HTTP or write it to disk. HTTP headers in PostgreSQL? What about when I need a value...