sandman2: automatic REST API generation from existing databases, no application code required

REST API from a connection string, using SQLAlchemy automap instead of hand-written models

sandman2 sits at the boundary between a legacy database and an HTTP client. Given only a connection string, it reflects the schema through SQLAlchemy's automap construct, registers one REST endpoint per discoverable table, and starts a Flask server. No ORM model code is needed, no Flask route definitions, no view functions. The command-line entry point, sandman2ctl, handles the full sequence.

This covers a specific situation: a database that already contains data, but has no existing HTTP API. Developers who need to access that data programmatically, from any language, get a working interface in the time it takes to run one command. The description of the original project called it freeing the data, and the use case is exactly that narrow: read and write access to rows via HTTP without involving whoever owns the database.

The original sandman served the same purpose but used an older SQLAlchemy interface. When SQLAlchemy 0.9 introduced the automap construct, the author rewrote sandman entirely as sandman2. The original sandman is no longer maintained and is considered deprecated.

sandman2ctl reflects the schema, registers endpoints per table, and starts Flask

sandman2 calls SQLAlchemy's automap feature to turn each table into an in-memory model class, then hands those classes to a Flask application that registers standard REST methods for each one. The result is GET for retrieval, POST for creation, PUT and PATCH for updates, and DELETE for removal, generated for every table in the connected database without any per-table configuration.

The service starts with hypermedia support, which allows responses to include links to related resources. What exactly that looks like in practice is in the documentation at sandman2.readthedocs.io rather than in the README.

Customization is available for cases where the automatic endpoint set is not specific enough. Integrating sandman2 into an existing Flask application, restricting which tables are exposed, and overriding per-endpoint behavior are all documented at sandman2.readthedocs.io. sandman2ctl remains the zero-code path; the library API is for cases that need finer control.

Installing via pip and connecting to a SQLite or PostgreSQL database

Install the package with:

pip install sandman2

This registers the sandman2ctl command. To connect to a SQLite database file in the current directory, pass the SQLAlchemy connection URI directly:

$ sandman2ctl sqlite+pysqlite:///database_file_name

PostgreSQL requires a separate driver. Install psycopg2 via pip, then run:

$ sandman2ctl postgresql+psycopg2://scott:tiger@localhost/mydatabase

The URI format follows SQLAlchemy's connection string specification, which is documented at docs.sqlalchemy.org. After the server starts, every discoverable table in the connected database is accessible at HTTP endpoints derived from its name. The admin interface is at /admin/ on the same address and port.

The examples/ directory includes examples/blog.sqlite3 and examples/db.sqlite3 for local testing, along with example scripts showing both automatic schema detection and explicit user-defined models.

Docker deployment and the EXPOSE 80 versus -p 9000:5000 discrepancy

An official Docker image is available at Docker Hub as jeffknupp/sandman2. Pull and run it to connect to a PostgreSQL database running on the host machine:

$ docker pull jeffknupp/sandman2
$ docker run -d -e DB_TYPE=postgres -e DB_DRIVER=psycopg2 -e USERNAME=jknupp -e DB_HOST=host.docker.internal -e DATABASE=jknupp -e DB_PORT=5432 -p 9000:5000 sandman2

After that, access the meta endpoint or admin interface:

$ curl localhost:9000/meta

The Dockerfile sets `ENV PORT 80` and `EXPOSE 80`. The docker run command in the README maps `-p 9000:5000`, connecting host port 9000 to container port 5000. Those two parts disagree about which port the container serves on. The start.sh script copied into the image at build time controls which port Flask actually binds, but start.sh is not in the README. Confirm the actual listening port before routing production traffic to this container.

DB_HOST=host.docker.internal works only on macOS for databases running on the Docker host. For a remote database, use the machine's IP address or hostname instead.

Eight natively supported databases and four more through third-party SQLAlchemy dialects

sandman2 supports any database that SQLAlchemy supports. The natively supported list is: MySQL, PostgreSQL, Oracle, Microsoft SQL Server, SQLite, Sybase, Drizzle and Firebird. Third-party packages extend that to IBM DB2, Amazon Redshift, SQL Anywhere and MonetDB.

The connection string is the only thing that changes between databases. Everything else, endpoint registration, HTTP methods, admin interface, stays the same regardless of the backend.

Drizzle is a MySQL fork and its future support depends on its driver's status in the SQLAlchemy ecosystem. Oracle and Microsoft SQL Server require their respective client libraries, which affects Docker deployments. The official Docker image installs psycopg2 and pymysql at build time, as specified in the Dockerfile, but does not install Oracle or SQL Server drivers.

Admin interface at /admin/ serves without additional configuration

sandman2 ships with a web admin interface backed by Flask-Admin. In the Docker example, it is at http://localhost:9000/admin/. On a local install the default port is Flask's standard 5000, so the admin is at http://localhost:5000/admin/.

Layout improvements were made over the original sandman, particularly for schemas with larger numbers of tables, and all original functionality carried over.

Flask-Admin is pinned to 1.5.7 in requirements.txt and to >=1.5.3 in setup.py. A pip install from PyPI follows setup.py constraints and may install a version newer than 1.5.7 if one is available, while a source install using requirements.txt gets exactly 1.5.7. That difference does not affect the admin interface, but reproducing a test environment exactly requires knowing which file controls the install.

Dependency version pins in setup.py and requirements.txt do not match

A close reading of the repository reveals a mismatch between the two dependency files. The install_requires field in setup.py lists SQLAlchemy==1.3.3 and Flask-SQLAlchemy==2.4.0. requirements.txt carries SQLAlchemy==1.3.20 and Flask-SQLAlchemy==2.4.4.

A pip install from PyPI uses setup.py. SQLAlchemy went from 1.3.3 to 1.3.20 over many patch releases, and the same is true for Flask-SQLAlchemy from 2.4.0 to 2.4.4. A developer who reports a bug in a pip-installed copy may be testing against behavior fixed in 1.3.20.

The repository has no GitHub releases, and setup.py declares version 1.2.2. Anyone building from source should use requirements.txt for a reproducible environment rather than relying on the looser setup.py constraints, since the gap between the two files is wide enough to affect real behavior.

Editorial conclusion

sandman2 suits teams that need read-write HTTP access to a legacy database without writing ORM models or route handlers. Before deploying from PyPI, check that SQLAlchemy 1.3.3, the version pinned in setup.py, covers your use case; requirements.txt carries 1.3.20, so a source install behaves differently. For Docker, verify the actual listening port: the Dockerfile sets EXPOSE 80, but the documented run command maps to container port 5000. The admin interface at /admin/ is available by default and needs no extra configuration.

Frequently asked questions

Official sources

1. Issues
2. jeffknupp/sandman2 on GitHub
3. License: Apache-2.0
4. README