alchemy-utils 0.1a0: Cross-Database Operations with a Familiar API

Listing rows from a PostgreSQL table can be as simple as a single command. With alchemy-utils, developers can query a PostgreSQL database using a one-liner like this:

uvx --with 'alchemy-utils[postgresql]' alchemy-utils rows 'postgresql+psycopg://simon@localhost:5432/simonwillisonblog' redirects_redirect

This command outputs JSON-formatted rows from the specified table, starting with entries like {"id": 2328, "domain": "simonwillison.net", "path": "2020/May/21/apple-photos-sqlite/",...}.

Use Cases

The alchemy-utils library provides a database-agnostic alternative to sqlite-utils, built on SQLAlchemy to work across multiple database engines. According to the announcement, the project emerged from exploring what a cross-database version of sqlite-utils would look like, maintaining the same core API while supporting PostgreSQL, SQLite, and DuckDB.

The library replicates key sqlite-utils methods including insert, upsert, insert_all, upsert_all, create, and update. It also includes table introspection capabilities, allowing developers to examine database schema and structure programmatically. This makes alchemy-utils suitable for data migration tasks, database exploration, and building tools that need to work consistently across different database backends.

Configuration

Installation requires specifying database-specific extras. For PostgreSQL support, developers install the package with alchemy-utils[postgresql]. The connection string format follows SQLAlchemy conventions, as shown in the PostgreSQL example: postgresql+psycopg://simon@localhost:5432/simonwillisonblog.

The project was initialized using uv init and follows test-driven development practices with pytest. According to the release notes, the development process included testing against PostgreSQL, SQLite, and DuckDB to ensure compatibility across these database engines.

Advanced Usage

The rows command demonstrated above represents one of the CLI utilities available in alchemy-utils. The command accepts a database connection string and table name, returning results in JSON format suitable for piping to other tools or further processing.

Because alchemy-utils maintains API compatibility with sqlite-utils, developers familiar with that library can apply their existing knowledge when working with other database systems. The library handles the underlying differences between database engines through SQLAlchemy’s abstraction layer, while presenting a consistent interface for common operations like inserting records or updating existing rows.

Caveats

The 0.1a0 version number indicates this is an alpha release. According to the announcement, the project reached a state “good enough to release as an alpha” after relatively few prompts during development. This suggests the library is in early stages and may lack features or stability expected in production-ready software.

The project was created as a prototype to explore feasibility - described as “literally a shower project” that involved tasking AI models with building the initial implementation. While the core functionality works across the tested database engines, developers should expect potential limitations or rough edges typical of alpha software.

The library requires database-specific drivers installed via extras, which means the base installation may not include everything needed for a particular database. Developers must explicitly specify which database backends they need when installing the package.