Basic Usage ----------- The ``Alchemical`` class is a thin wrapper around SQLAlchemy that simplifies working with one or more databases. Connecting to a Database ~~~~~~~~~~~~~~~~~~~~~~~~ To connect to a database, create an instance of the ``Alchemical`` class and pass the database URL as the first argument:: db = Alchemical('sqlite:///data.sqlite') The URLs that you can use here are those supported by SQLAlchemy. See `Database URLs `_ in the SQLAlchemy documentation for details on the supported databases and their URLs. The ``Alchemical`` class accepts an optional ``engine_options`` argument, with a dictionary of options that is passed directly to SQLAlchemy's ``create_engine()`` function as keyword arguments. See `create_engine() `_ in the SQLAlchemy documentation to learn what options are supported. As an example, here is how to log all SQL statements:: db = Alchemical('sqlite:///data.sqlite', engine_options={'echo': True}) Defining Database Models ~~~~~~~~~~~~~~~~~~~~~~~~ When working with Alchemical, models are created according to SQLAlchemy's rules, using ``Model`` as the base class:: from sqlalchemy.orm import Mapped, mapped_column from alchemical import Model class User(Model): id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] fullname: Mapped[str] nickname: Mapped[str] See `Declarative Mapping `_ in the SQLAlchemy documentation. Alchemical automatically names each database table with the name of its corresponding model class converted to snake case. To override the automatic naming, a ``__tablename__`` attribute can be added to the model class:: class User(Model): __tablename__ = 'users' id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] fullname: Mapped[str] nickname: Mapped[str] Creating and Destroying Database Tables ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Alchemical can create all the tables referenced by models with a single call:: db.create_all() Note that this only creates tables that do not currently exist in the database. To make updates to existing tables matching changes made to their corresponding model class, a database migration framework such as `Alembic `_ must be used. Alchemical provides a second function that destroys (drops, in SQL jargon) all the tables:: db.drop_all() Obtaining a Database Session ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A database session class is provided as ``db.Session``, which can be used as a context-manager to open and close database sessions:: with db.Session() as session: session.add(some_object) session.add(some_other_object) session.commit() See `Basics of Using a Session `_ in the SQLAlchemy documentation for more information. Creating Database Queries ~~~~~~~~~~~~~~~~~~~~~~~~~ Alchemical uses the 2.x query model introduced in SQLAlchemy 1.4. Queries are generally started with the ``select()`` function:: from sqlalchemy import select query = select(User).where(User.name == 'mary') Since the ``select()`` is so common, Alchemical provides a shortcut to it:: query = User.select().where(User.name == 'mary') See the `ORM Querying Guide `_ in the SQLAlchemy documentation for details on all the constructs that can be used to create queries. Once a query object is created, it must be executed inside a database session. There are three main methods to execute a database query: - ``session.scalars(query)``: Returns an iterable with a single result per row. - ``session.scalar(query)``: Returns the first result from the first row. - ``session.execute(query)``: Returns an iterable with a tuple of results per row. Using Multiple Databases ~~~~~~~~~~~~~~~~~~~~~~~~ Alchemical makes it easy to manage multiple databases from a single database instance through the use of "binds". The following example shows how to connect to Postgres and in-memory SQLite databases:: db = Alchemical(binds={ 'users': 'postgresql://user:password@localhost/mydb', 'cache': 'sqlite:///', }) When using binds, each model class must indicate which of the binds it belongs to with the ``__bind_key__`` attribute:: class User(Model): __bind_key__ = 'users' id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] fullname: Mapped[str] nickname: Mapped[str] To avoid duplicating the ``__bind_key__`` attribute in many model classes, you can create an abstract parent class for each bind:: class UsersBind(Model): __abstract__ = True __bind_key__ = 'users' class User(UsersBind): id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] fullname: Mapped[str] nickname: Mapped[str] The ``Alchemical`` instance can also be configured to combine the use of a main database and binds. The following example connects to a MySQL database as the main database, plus the Postgres and SQLite databases of the previous example:: db = Alchemical('mysqldb://user:password@localhost/db', binds={ 'users': 'postgresql://user:password@localhost/mydb', 'cache': 'sqlite:///', }) When combining a main database with binds, any database models that do not have a ``__bind_key__`` attribute are assigned to the main database. Asyncio Support ~~~~~~~~~~~~~~~ SQLAlchemy 2.0 has full support for the asyncio package. Alchemical provides an async-enabled database instance that can be imported from ``alchemical.aio``:: from alchemical.aio import Alchemical When using the async version of the ``Alchemical`` class many of the methods and context-managers are asynchronous and need to be awaited, but other than this there are no differences. .. _database-migrations-with-alembic: Database Migrations with Alembic ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Alchemical provides a basic integration with Alembic that makes it possible to manage changes to the database schemas through migration scripts. To initialize a migration repository, run the following command:: python -m alchemical.alembic.cli init migrations This special way of initializing Alembic is necessary to allow Alchemical to include its own customized templates in the repository. Outside of the ``init`` command, invoking Alembic through the ``alchemical.alembic.cli`` module has the same effect as invoking it directly through the ``alembic`` command. The ``init`` command creates a *migrations* directory and initializes it as an empty Alembic migration repository (a different directory name can be given as the last argument to the command above if desired). An *alembic.ini* file is also created in the project directory. To configure the new repository, the ``alchemical_db`` setting in the *alembic.ini* file must be set to point to the location of the ``Alchemical`` instance. For example, if the ``Alchemical`` instance is called ``db`` and is in a *myproject.py* module:: [alembic] alchemical_db = myproject:db The syntax used by this setting is similar to the one used by many WSGI web servers to configure the application instance. It consists of the module import path, followed by a colon and the name of the ``Alchemical`` instance. One of the most interesting features of Alembic is its ability to automatically generate migration scripts based on the changes made to the database models. Alchemical provides an optimal configuration for this, with the following two options enabled: - ``render_as_batch``, to enable batch migrations when using SQLite. This setting should have no effect on other databases. - ``compare_type``, to enable column type comparisons. These and other autogeneration options can be edited in the file *migrations/env.py*, using the comments in that file and the Alembic documentation as reference. After the migration repository is created and configured, you can use the ``alembic`` command to autogenerate a first migration script:: alembic revision --autogenerate -m "Initial migration" If the migration script is correct, it can be applied to the database with the following command:: alembic upgrade head The Alembic integration provided by Alchemical is a superset of the three template options that come standard with Alembic. In particular, an Alchemical configured migration repository should automatically work with single or multi-database projects, and also with async projects. See the `Alembic documentation `_ to learn more about Alembic and how to use it. Integration with Web Frameworks ------------------------------- Alchemical is framework agnostic, so it should integrate well with most web frameworks, without any additional work. This section describes specific integrations that go beyond the basic usage or are of particular interest. Using with Flask ~~~~~~~~~~~~~~~~ Alchemical has full support for Flask with its own Flask extension. To use it, import the ``Alchemical`` class from the ``alchemical.flask`` package:: from alchemical.flask import Alchemical The Alchemical Flask extension imports its configuration from Flask's ``config`` object. The following configuration options are supported: - ``ALCHEMICAL_DATABASE_URL``: the database connection URL. - ``ALCHEMICAL_BINDS``: a dictionary with database binds. - ``ALCHEMICAL_ENGINE_OPTIONS``: optional engine options to pass to SQLAlchemy. - ``ALCHEMICAL_AUTOCOMMIT``: If set to ``True``, database sessions are auto-committed when the request ends (the default is ``False``). Example:: app = Flask(__name__) app.config['ALCHEMICAL_DATABASE_URL'] = 'sqlite:///app.db' db = Alchemical(app) When using the Flask extension, a database session is automatically created the first time ``db.session`` is referenced during the handling of a request. This is a pattern that will be familiar to users of the Flask-SQLAlchemy extension. A session that is allocated in this way is automatically closed when the request ends. If the ``ALCHEMICAL_AUTOCOMMIT`` option is set to ``True``, the session is committed before it is closed. The ``db.session`` is entirely optional. The ``db.Session`` class and its context manager can be used in a Flask application if preferred. Database Migrations with Flask-Migrate ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ When using the Alchemical Flask extension, use of `Flask-Migrate `_ to manage database migrations with Alembic is fully supported. Refer to the Flask-Migrate documentation for instructions. The Alchemical ``db`` object can be used in place of Flask-SQLAlchemy's ``db``. As an alternative, you can follow the instructions to set up :ref:`Database Migrations with Alembic ` in this documentation instead of using the Flask-Migrate extension. If you opt to work with Alembic without Flask-Migrate, be aware that the ``db`` instance must be fully initialized with a Flask application instance, so that it has access to the database configuration stored in the Flask ``config`` object. If your Flask application uses the application factory pattern, you may need to call your factory function in the *env.py* file to force this initialization. Using with FastAPI ~~~~~~~~~~~~~~~~~~ The async version of Alchemical can be used with the `FastAPI `_ framework, without any changes or a dedicated extension. Example:: from fastapi import FastAPI from sqlalchemy import String from sqlalchemy.orm import Mapped, mapped_column from alchemical.aio import Alchemical, Model class User(Model): id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(64)) app = FastAPI() db = Alchemical('sqlite:///app.db') @app.get('/') async def index(): async with db.Session() as session: users = await session.scalars(User.select()) return {'users': [u.name for u in users]}