simonwillisonblog-backup

I expect the bigger risk to dynamic database-backed CMS platforms right now is that AI assistance makes static site generator tools run against a version controlled repository of content less intimidating for most users... and static sites are cheaper to run (especially in this era of badly coded scrapers flooding the internet) and much less likely to fall vulnerable to security problems. I expect we'll see a further wave of CMS interfaces which provide a nicer editing experience on top of flat files stored in Git. Maybe the strategic move for platforms like WordPress (and maybe Django too! The Django admin remains a very popular CMS platform) is to invest more in separation of admin editing from serving, such that there's an obvious path to edit your content in the CMS but deploy it as static files. My own blog uses the Django admin and

> I’ve been running that for a couple of years in this repo: https://github.com/simonw/simonwillisonblog-backup - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite- I'm curious, what is the reason you chose not to use pgdump, but instead opted to convert to to sqlite and then dump the DB using sqlite-diffable? On a project I'm working on, I'd like to dump our Postgres schema into individual files for each object (i.e., one file for each table, function, stored proc, etc.), but haven't spent enough time to see if pgdump could actually do that. We're just outputting files by object type for now (one tables, function, and stored procs files). It looks like sqlite-diffable does something similar to what we're looking for (separate files for each object).

This approach works by storing the actual SQLite binary files in Git and then using a custom "diff" configuration to dump each file as SQL and compare the result. It's a neat trick, but storing binary files like that in Git isn't as space efficient as using a plain text format. I built my own tooling to solve this problem: https://datasette.io/tools/sqlite-diffable - which outputs a “diffable” copy of the data in a SQLite database, precisely so you can store it in Git and look at the differences later. I’ve been running that for a couple of years in this repo: https://github.com/simonw/simonwillisonblog-backup - which provides a backup of my blog’s PostgreSQL Django database (first converted to SQLite and then dumped out using sqlite-diffable). Here’s an example diff: https://github.com/simonw/simonwillisonblog-backup/commit/72...

He's doing diffs: https://github.com/simonw/simonwillisonblog-backup/commit/82...

In case it's useful to anyone, I've been running the cheapest possible implementation of Git-based revision history for my blog's PostgreSQL database for a few years now. I run a GitHub Actions workflow every two hours which grabs the latest snapshot of the database, writes the key tables out as newline-delimited JSON and commits them to a Git repository. https://github.com/simonw/simonwillisonblog-backup This gives me a full revision history (1,500+ commits at this point) for all of my content and I didn't have to do anything extra in my PostgreSQL or Django app to get it. If you need version tracking for audit purposes or to give you the ability to manually revert a mistake, and you're dealing with tens-of-thousands of rows, I think this is actually a pretty solid simple way to get that.

I've been thinking about this quite a bit recently. I want to start adding features where LLMS can help with data cleanup, but for that to be useful it will need VERY robust "undo" for if they make mistakes. I wrote up one of my explorations here: sqlite-history https://simonwillison.net/2023/Apr/15/sqlite-history/ I've also had a lot of success using GitHub itself for versioned data. If your data is less than a GB (and each file is under 50MB) you can dump it out to a GitHub repo and use that to track changes over time. One example of that is my personal blog, here: https://github.com/simonw/simonwillisonblog-backup/tree/main... That's using this tool: https://datasette.io/tools/sqlite-diffable I imagine Datasette Cloud will end up with some sort of hybrid of those approaches.

I wrote a tool to do that: https://datasette.io/tools/db-to-sqlite You can see an example of it in use here: https://github.com/simonw/simonwillisonblog-backup/blob/main...

My personal blog runs on Django + PostgreSQL, and I got fed up of not having a version history of changes I made to my content there. I solved that by setting up a GitHub repo that mirrors the content from my database to flat files a few times a day and commits any changes. It's worked out really well so far. It wasn't much trouble to setup and it's now been running for nearly three years, capturing 1400+ changes. I'd absolutely consider using the same technique for a commercial project in the future: Latest commits are here: https://github.com/simonw/simonwillisonblog-backup/commits/m... Workflow is https://github.com/simonw/simonwillisonblog-backup/blob/main...

If you really, really need to be able to see a SQL schema representing the current state, a cheap trick is to run an automation on every deploy that snapshots the schema and writes it to a GitHub repository. I do a version of that for my own (Django-powered) blog here: https://github.com/simonw/simonwillisonblog-backup/blob/main...

My blog is Django and PostgreSQL on Heroku, but last year I decided I wanted a reliable long-term public backup... so I set up a scheduled GitHub Actions workflow to back it up to a git repository. Bonus feature: since it runs nightly it gives me diffs of changes I make to my content, including edits to old posts. The backups are in this repo: https://github.com/simonw/simonwillisonblog-backup

I wrote a tool that dumps a SQLite database out to disk as diffable JSON, precisely so you can easily record changes made to the database over time: https://github.com/simonw/sqlite-diffable I have a demo of it running against my blog's database here: https://github.com/simonw/simonwillisonblog-backup

I actually think git is MUCH better suited to this than a traditional database. I've built track-changes-over-times things in relational databases before and it's a real pain to implement! I've actually started using a GitHub repository to back up the PostgreSQL database that powers my blog, because it's a really cheap way of getting a revision history of that database without having to write any extra code: https://github.com/simonw/simonwillisonblog-backup/commits/m...