Python, PostgreSQL and Wasm walk into a pytest bar

As a software engineer, I'm constantly searching for tools that streamline the development process. One of the most persistent friction points in backend development is database testing. We often find ourselves caught between two imperfect options: using mocks, which can drift from the real database's behavior, or managing externally-run PostgreSQL instances with containers or dedicated test servers, which adds complexity and slows down our feedback loops.

But what if there was a third way? What if you could run a real, feature-complete PostgreSQL database directly inside your Python test runner, with zero external setup?

The amazing PGlite Embeddable Postgres project from the team over at ElectricSQL presents us with this opportunity. At its core, PGlite is a full PostgreSQL server compiled to WebAssembly (WASM), allowing it to run in any environment that supports a JavaScript runtime, including browsers and Node.js. It's the magic behind innovative projects like the in-browser database editor database.build and enables incredible possibilities like embedded pgvector for AI applications.

This led to the question how do we leverage these innovations in the Python ecosystem? This seems to be what the py-pglite project is attempting. This post explores how py-pglite could be used to test Python applications.

Enter py-pglite: PostgreSQL as a Pytest Fixture

py-pglite is a Python wrapper for pglite that makes it incredibly simple to manage an in-process PostgreSQL database. It abstracts away the underlying mechanism, providing a clean API and, most importantly, ready-to-use fixtures for your pytest test suites.

Instead of configuring test database URLs or writing cleanup scripts, you can just use a fixture. It feels as lightweight as SQLite, but with all the power of Postgres.

You can add it as a dependency to your project. If using Poetry to manage your project, you can use the following command.

poetry add 'py-pglite[sqlalchemy]'

Here’s a look at how seamless testing a SQLAlchemy model can be:

from sqlalchemy import select
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import Session
from sqlalchemy.orm import mapped_column


# Define a simple SQLAlchemy model
class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str]
    email: Mapped[str]


# The test function using the py-pglite fixture
def test_user_creation_with_pglite(pglite_session: Session):
    """
    Test using py-pglite's session fixture - a real PostgreSQL!
    This requires zero-config for SQLAlchemy testing. Just request the
    fixture, and py-pglite handles the rest. 🚀
    """
    # Create the table schema for this specific test
    # The database is fresh and empty for every test function.
    Base.metadata.create_all(pglite_session.bind)

    # Use the session just like any other SQLAlchemy session
    user = User(username="testuser", email="[email protected]")
    pglite_session.add(user)
    pglite_session.commit()

    # Assert that the user was created correctly
    assert user.id is not None

    # Verify by querying the user back from the database
    found_user = pglite_session.execute(
        select(User).where(User.username == "testuser")
    ).scalar_one_or_none()

    assert found_user is not None
    assert found_user.email == "[email protected]"

The pglite_session fixture provides a fully configured SQLAlchemy session connected to a temporary, in-memory PostgreSQL instance. The database is created and destroyed automatically for each test, ensuring perfect test isolation without any manual intervention. The project also provides fixtures for Django, making it just as easy to test Django applications.

The Good, the Bad, and the Wasm

While the vision is compelling, py-pglite is still a young project. To use it effectively, it's important to understand its current state.

The Good

• Real PostgreSQL: You're not testing against a mock or a different database engine like SQLite. You can use Postgres-specific features like JSONB, arrays, and even extensions like pgvector.
• Zero-Config Testing: The pytest fixtures for SQLAlchemy and Django are incredibly powerful, removing nearly all boilerplate for setting up and tearing down test databases.
• Speed and Simplicity: It's significantly faster and less resource-intensive than spinning up containers for every test run.

The Bad (The Current Realities)

The project’s maintainers have made some pragmatic choices to get it working, which come with trade-offs.

1. The Node.js Dependency: Currently, py-pglite works by running the pglite JavaScript package in a Node.js subprocess. This means you must have node and npm (or another package manager) installed on your system. The first time you run it, it will use npm to download the necessary packages. This dependency on an external runtime and a network connection for setup is a point of friction, moving away from the ideal of a pure, self-contained Python package.
2. Asyncpg + SQLAlchemy Challenges: While the project has first class support for SQLAlchemy/SQLModel and provides an extra for asyncpg, using it with SQLAlchemy can be tricky. The root cause here is the hard-coded scheme in the connection string as far as I can tell. This can become an issue if you do not have psycopg in your dependency tree.

def get_connection_string(self) -> str:
    """Get PostgreSQL connection string for SQLAlchemy usage."""
    # For SQLAlchemy with Unix domain sockets, we need to specify the directory
    # and use the standard PostgreSQL socket naming convention
    socket_dir = str(Path(self.socket_path).parent)

    # Use the socket directory as host - psycopg will look for .s.PGSQL.5432
    connection_string = (
        f"postgresql+psycopg://postgres:postgres@/postgres?host={socket_dir}"
    )

    return connection_string

The Wasm (The Path to a Better Future)

The Node.js dependency is a pragmatic solution. The holy grail would be to run the pglite WASM build directly from Python, without any JavaScript runtime in between using a project like wasmtime or wasmer.

The main obstacle here is how the PostgreSQL WASM module is compiled. The current build provided by ElectricSQL uses the Emscripten. Emscripten is fantastic for compiling C/C++ projects to run in a browser-like JavaScript environment, providing "glue" code to bridge the gap.

However, this makes the build non-portable to other WASM runtimes that don't mimic a browser. What's truly needed is a build that targets WASI (WebAssembly System Interface), a standardized interface that allows WebAssembly modules to interact with the underlying operating system in a portable way. A pure WASI build of pglite could be instantiated and controlled by a Python WASM runtime directly.

Excitingly, this is not just a fantasy. A developer on the pglite team recently mentioned on Hacker News that they have an experimental WASI build. The primary challenge they face is that PostgreSQL's error handling relies on a C feature (longjmp) that doesn't exist in WASM. Emscripten provides a workaround, this will have to be handled natively for WASI builds as well before it can be used.

A Glimpse of the Future

Despite its recency and current dependency on Node.js, py-pglite represents a significant step towards a better developer experience for Python applications using PostgreSQL. The ability to have an ephemeral, in-process, and feature-complete PostgreSQL database for testing (and perhaps even in your main application logic) is a paradigm shift.

It replaces heavy, slow, and complex testing and development environment setups with something that is nearly as simple as using SQLite. While the dependency on Node.js is a pragmatic but imperfect first step, the future possibility of a pure WASI-based solution is incredibly exciting.

I'm keeping a close eye on this project and the underlying work on pglite. For now, it's a fascinating tool to experiment with, and it might just be the perfect fit for your next project's testing suite.