Database
========

The primary interface for interacting with your database is the :class:`sqlservice.database.Database` class. It integrates with SQLAlchemy engines, sessions, and your declarative base model's metadata to provide an abstraction layer on top of it.

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

.. code-block:: python

    from sqlservice import Database

    # 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://", model_class=Model)

.. note::

    For details on the available configuration values, see :class:`sqlservice.database.Database`.

The ``Database`` 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 = db.connect()
    session = db.session()

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

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

    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.database.Database.engine`` is created using ``sqlalchemy.create_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.database.Database.connect` - Returns an instance of ``sqlalchemy.Connection``
- :meth:`sqlservice.database.Database.session` - Returns an instance of :class:`sqlservice.session.Session`` which is a subclass of ``sqlalchemy.orm.Session``
- :meth:`sqlservice.database.Database.begin` - Begins a transaction and returns an instance of :class:`sqlservice.session.Session``


Session
-------

The :meth:`sqlservice.database.Database.session` and :meth:`sqlservice.database.Database.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.session.Session` which extends ``sqlalchemy.orm.Session``. You can override the session class using ``Database(session_class=MySession)``.

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


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

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


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

Create all ORM model tables with:

.. code-block:: python

    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

    db.drop_all()


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

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

.. code-block:: python

    db.reflect()
    print(db.tables)


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