Async Database
==============

An asyncio-compatible database interface is the :class:`sqlservice.async_database.AsyncDatabase` class which has the same interface as the :class:`sqlservice.database.Database` class.

Before we get to the good stuff, let's first start by creating our asynchronous database object:

.. code-block:: python

    from sqlservice import AsyncDatabase

    # Let's assume you've define the User, UserAbout, UserDevice, and UserDeviceKey
    # as illustrated in the "Model" section of the docs in a module called models.py
    # with a declarative base named Model.
    from models import Model

    db = Database("sqlite+aiosqlite://", model_class=Model)

.. note::

    For details on the available configuration values, see :class:`sqlservice.async_database.AsyncDatabase`.

The ``AsyncDatabase`` class can be used as a provider of database connections and sessions using the `2.0 style <https://docs.sqlalchemy.org/en/14/glossary.html#term-2.0-style>`_ of SQLAlchemy:

.. code-block:: python

    conn = await db.connect()
    session = await db.session()

    # Or as context-managers...
    async with db.connect() as conn:
        # high-level DB-API connection

    async with db.session() as session:
        # ORM session, commit as you go

    async with db.begin() as session:
        # ORM session, automatic commit when context-manager exits

.. seealso::

    Learn more about the new 2.0 style from the `SQLAlchemy 1.4 / 2.0 Tutorial <https://docs.sqlalchemy.org/en/14/tutorial/>`_.


Engine
------

Nothing fancy here. The :attr:`sqlservice.async_database.AsyncDatabase.engine`` is created using ``sqlalchemy.create_async_engine`` under the hood using the database settings provided. Typically, the engine won't be used directly. Instead, database connections and sessions can be created using these methods:

- :meth:`sqlservice.async_database.AsyncDatabase.connect` - Returns an instance of ``sqlalchemy.ext.asyncio.AsyncConnection``
- :meth:`sqlservice.async_database.AsyncDatabase.session` - Returns an instance of :class:`sqlservice.async_session.AsyncSession`` which is a subclass of ``sqlalchemy.ext.asyncio.AsyncSession``
- :meth:`sqlservice.async_database.AsyncDatabase.begin` - Begins a transaction and returns an instance of :class:`sqlservice.session.Session``


Session
-------

The :meth:`sqlservice.async_database.AsyncDatabase.session` and :meth:`sqlservice.async_database.AsyncDatabase.begin` methods are factories that provide ORM sessions. The main difference is that ``begin()`` is a context-manager that will start a transaction when entering the context-manager, commit the transaction on exit, and automatically rollback if an unhandled exception occurs.

The default class used to create sessions is :class:`sqlservice.async_session.AsyncSession` which extends ``sqlalchemy.ext.asyncio.AsyncSession``. You can override the session class using ``AsyncDatabase(session_class=MySession)``.

For more details, see the :mod:`sqlservice.async_session` module.


ORM Models
----------

The declarative base model passed into ``AsyncDatabase`` (or autogenerated when excluded) has its metadata available at :attr:`sqlservice.async_database.AsyncDatabase.metadata`. There are several ``metadata`` based methods available.


Create Model Tables
+++++++++++++++++++

Create all ORM model tables with:

.. code-block:: python

    await db.create_all()


This will issue the appropriate SQL DDL statements that can get your database up and running quickly. For full migration integration, see `alembic <http://alembic.zzzcomputing.com/>`_.


Drop Model Tables
+++++++++++++++++

Drop all ORM model tables with:

.. code-block:: python

    await db.drop_all()


Reflect Models
++++++++++++++

Reflect existing database schema without predefining ORM models or Table objects:

.. code-block:: python

    await db.reflect()
    print(db.tables)


For more details, see the :mod:`sqlservice.async_database` module.