alchemy-utils 0.1a0

Simon Willison released alchemy-utils 0.1a0, a database-agnostic Python library and CLI built on SQLAlchemy that mirrors the core API of sqlite-utils, supporting PostgreSQL, SQLite, and DuckDB. The prototype was generated with Codex and GPT-5.6 Sol Ultra in a morning, and the alpha is available on GitHub. Willison demonstrated commands to query a PostgreSQL table and insert a CSV of San Francisco trees into DuckDB, with the latter optimized from nearly an hour to about 35 seconds.

Release: alchemy-utils 0.1a0 https://github.com/simonw/alchemy-utils/releases/tag/0.1a0 I've long pondered what a database agnostic version of my sqlite-utils https://sqlite-utils.datasette.io/ Python library and CLI utility might look like. This morning literally a shower project I tasked Codex and GPT-5.6 Sol Ultra with building a prototype: Do a research spike to see what it would take to build a library with the same core API as SQLite-utils - in particular the insert and upsert and insert all and upsert all and create and update methods, and the table introspection stuff - but backed by SQLalchemy so it works for multiple database engines Test against PostgreSQL and SQLite and duckdb Use ~/dev/sqlite-utils for reference Create a git repo for this and commit and early and often - use uv init to start the project - use red/green TDD and pytest, see ~/dev/django-sql-dashboard for one idea as to how the PostgreSQL tests could work It took very few follow-up prompts https://gist.github.com/simonw/bd10e4886688e0fd1b833e4afaabf19e to produce this project in a state good enough to release as an alpha. Here's a one-liner I can use to list the rows in a table in my local PostgreSQL copy of my blog's database: uvx --with 'alchemy-utils postgresql ' alchemy-utils rows 'postgresql+psycopg://simon@localhost:5432/simonwillisonblog' redirects redirect The output from that starts like this: { "id": 2328, "domain": "simonwillison.net", "path": "2020/May/21/apple-photos-sqlite/", "target": "/2020/May/21/dogsheep-photos/", "created": "2020-05-21T13:03:46.591692-07:00" }, { "id": 3, "domain": "feeds.simonwillison.net", "path": "swn-links", "target": "https://simonwillison.net/atom/links/", "created": "2017-10-01T14:12:54.820729-07:00" } Or if you'd like a DuckDB database with every tree in San Francisco https://github.com/simonw/sf-tree-history/blob/main/Street Tree List.csv , schema created automatically to match the file: curl 'https://raw.githubusercontent.com/simonw/sf-tree-history/refs/heads/main/Street Tree List.csv' | uvx --with 'alchemy-utils duckdb ' alchemy-utils insert 'duckdb:////tmp/trees.db' trees - --csv That one took nearly an hour the first time I ran it, so I had Codex optimize it https://github.com/simonw/alchemy-utils/commit/e3b8d03e040867b256aeff7db322a0b0eb1f8b06 and got it down to around 35 seconds. Tags: databases https://simonwillison.net/tags/databases , postgresql https://simonwillison.net/tags/postgresql , projects https://simonwillison.net/tags/projects , python https://simonwillison.net/tags/python , sql https://simonwillison.net/tags/sql , sqlalchemy https://simonwillison.net/tags/sqlalchemy , sqlite https://simonwillison.net/tags/sqlite , sqlite-utils https://simonwillison.net/tags/sqlite-utils , duckdb https://simonwillison.net/tags/duckdb , coding-agents https://simonwillison.net/tags/coding-agents , codex https://simonwillison.net/tags/codex