.. _db-tools:

Database Tooling
================

This section covers the playhouse modules for managing connections, database
URLs, schema migrations, introspection, code generation, and testing.

.. contents:: On this page
   :local:
   :depth: 1


.. _db-url:

Database URLs
-------------

.. module:: playhouse.db_url

The ``playhouse.db_url`` module lets you configure Peewee from a connection
string, which is common in twelve-factor applications where database
credentials live in environment variables.

.. code-block:: python

   import os
   from playhouse.db_url import connect

   db = connect(os.environ.get('DATABASE_URL', 'sqlite:////default.db'))

Pass additional keyword arguments in the query string:

.. code-block:: python

   db = connect('postgres://user:pass@host/db?max_connections=20')

URL format: ``scheme://user:password@host:port/dbname?option=value``

Common schemes:

+------------------------+------------------------------------------+
| Scheme                 | Database class                           |
+========================+==========================================+
| ``sqlite:///path``     | :class:`SqliteDatabase`                  |
+------------------------+------------------------------------------+
| ``postgres://``        | :class:`PostgresqlDatabase`              |
+------------------------+------------------------------------------+
| ``postgresext://``     | :class:`.PostgresqlExtDatabase`          |
+------------------------+------------------------------------------+
| ``mysql://``           | :class:`MySQLDatabase`                   |
+------------------------+------------------------------------------+

Connection pool implementations:

+-----------------------------+------------------------------------------+
| Scheme                      | Database class                           |
+=============================+==========================================+
| ``sqlite+pool:///path``     | :class:`.PooledSqliteDatabase`           |
+-----------------------------+------------------------------------------+
| ``postgres+pool://``        | :class:`.PooledPostgresqlDatabase`       |
+-----------------------------+------------------------------------------+
| ``postgresext+pool://``     | :class:`.PooledPostgresqlExtDatabase`    |
+-----------------------------+------------------------------------------+
| ``mysql+pool://``           | :class:`.PooledMySQLDatabase`            |
+-----------------------------+------------------------------------------+

Alternate drivers:

+------------------------------+------------------------------------------+
| Scheme                       | Database class                           |
+==============================+==========================================+
| ``psycopg3://``              | :class:`.Psycopg3Database`               |
+------------------------------+------------------------------------------+
| ``psycopg3+pool://``         | :class:`.PooledPsycopg3Database`         |
+------------------------------+------------------------------------------+
| ``cockroachdb://``           | :class:`.CockroachDatabase`              |
+------------------------------+------------------------------------------+
| ``cockroachdb+pool://``      | :class:`.PooledCockroachDatabase`        |
+------------------------------+------------------------------------------+
| ``cysqlite://``              | :class:`.CySqliteDatabase`               |
+------------------------------+------------------------------------------+
| ``cysqlite+pool://``         | :class:`.PooledCySqliteDatabase`         |
+------------------------------+------------------------------------------+
| ``apsw://``                  | :class:`.APSWDatabase`                   |
+------------------------------+------------------------------------------+
| ``mariadbconnector://``      | :class:`.MariaDBConnectorDatabase`       |
+------------------------------+------------------------------------------+
| ``mariadbconnector+pool://`` | :class:`.PooledMariaDBConnectorDatabase` |
+------------------------------+------------------------------------------+
| ``mysqlconnector://``        | :class:`.MySQLConnectorDatabase`         |
+------------------------------+------------------------------------------+
| ``mysqlconnector+pool://``   | :class:`.PooledMySQLConnectorDatabase`   |
+------------------------------+------------------------------------------+


.. function:: connect(url, unquote_password=False, unquote_user=False, **connect_params)

   :param url: the URL for the database, see examples.
   :param bool unquote_password: unquote special characters in the password.
   :param bool unquote_user: unquote special characters in the user.
   :param connect_params: additional parameters to pass to the Database.

   Parse ``url`` and return an appropriate :class:`Database` instance.

   Examples:

   * ``sqlite:///my_app.db`` - SQLite file in the current directory.
   * ``sqlite:///:memory:`` - in-memory SQLite.
   * ``sqlite:////absolute/path/to/app.db`` - absolute path SQLite.
   * ``postgresql://user:password@host:5432/dbname``
   * ``mysql://user:password@host:3306/dbname``

.. function:: parse(url, unquote_password=False, unquote_user=False)

   :param url: the URL for the database, see :func:`connect` above for examples.
   :param bool unquote_password: unquote special characters in the password.
   :param bool unquote_user: unquote special characters in the user.

   Parse a URL and return a dictionary with ``database``, ``host``,
   ``port``, ``user``, and ``password`` keys plus any extra connect
   parameters from the query string.

   Useful if you need to construct a database class manually:

   .. code-block:: python

       params = parse('postgres://user:pass@host:5432/mydb')
       db = MyCustomDatabase(**params)

.. function:: register_database(db_class, *names)

   :param db_class: A subclass of :class:`Database`.
   :param names: A list of names to use as the scheme in the URL.

   Register a custom database class under one or more URL scheme names so
   that :func:`connect` can instantiate it:

   .. code-block:: python

       register_database(FirebirdDatabase, 'firebird')
       db = connect('firebird://my-firebird-db')


.. _pool:

Connection Pooling
------------------

.. module:: playhouse.pool

The ``playhouse.pool`` module contains a number of :class:`Database` classes
that provide connection pooling for Postgresql, MySQL and SQLite databases. The
pool works by overriding the methods on the :class:`Database` class that open
and close connections to the backend.

In multi-threaded applications, each thread gets its own connection; the
pool maintains up to ``max_connections`` open connections at any time.
In single-threaded applications, a single connection is recycled.

The application only needs to ensure that connections are *closed* when work
is done - typically at the end of an HTTP request. Closing a pooled connection
returns it to the pool rather than actually disconnecting.

.. code-block:: python

   from playhouse.pool import PooledPostgresqlDatabase

   db = PooledPostgresqlDatabase(
       'my_app',
       user='postgres',
       max_connections=32,
       stale_timeout=300)

.. tip::
   Pooled database implementations may be safely used as drop-in replacements
   for their non-pooled counterparts.

.. include:: pool-snippet.rst

.. note::
   Applications using Peewee's :ref:`asyncio integration <pwasyncio>` do not need to
   use a special pooled database - the Async databases use a connection pool by
   default.

.. class:: PooledDatabase(database, max_connections=20, stale_timeout=None, timeout=None, **kwargs)

   Mixin class mixed into the specific backend subclasses above.

   :param str database: The name of the database or database file.
   :param int max_connections: Maximum number of concurrent connections.
       Pass ``None`` for no limit.
   :param int stale_timeout: Seconds after which an idle connection is
       considered stale and will be discarded next time it would be reused.
   :param int timeout: Seconds to block when all connections are in use.
       ``0`` blocks indefinitely; ``None`` (default) raises immediately.

   .. note::
      Connections will not be closed exactly when they exceed their
      ``stale_timeout``. Instead, stale connections are only closed when a new
      connection is requested.

   .. note::
       If the pool is exhausted and no ``timeout`` is configured, a
       ``ValueError`` is raised.

   .. method:: manual_close()

      Close the current connection permanently without returning it to the
      pool. Use this when a connection has entered a bad state.

   .. method:: close_idle()

      Close all pooled connections that are not currently in use.

   .. method:: close_stale(age=600)

      :param int age: Age at which a connection should be considered stale.
      :returns: Number of connections closed.

      Close in-use connections that have exceeded ``age`` seconds.
      Use with caution.

   .. method:: close_all()

      Close all connections including those currently in use.
      Use with caution.

.. class:: PooledSqliteDatabase(database, max_connections=20, stale_timeout=None, timeout=None, **kwargs)

   Pool implementation for SQLite databases. Extends :class:`SqliteDatabase`.

.. class:: PooledPostgresqlDatabase(database, max_connections=20, stale_timeout=None, timeout=None, **kwargs)

   Pool implementation for Postgresql databases. Extends :class:`PostgresqlDatabase`.

.. class:: PooledMySQLDatabase(database, max_connections=20, stale_timeout=None, timeout=None, **kwargs)

   Pool implementation for MySQL / MariaDB databases. Extends :class:`MySQLDatabase`.


.. _migrate:

Schema Migrations
-----------------

.. module:: playhouse.migrate

The ``playhouse.migrate`` module provides a lightweight API for making
incremental schema changes to an existing database without writing raw SQL.

The peewee migration philosophy is that tools relying on database
introspection, versioning, and auto-detection are often fragile, brittle and
unnecessarily complex. Migrations can be written as simple python scripts and
executed from the command-line. Since the migrations only depend on your
application's :class:`Database` object, migration scripts to not introduce new
dependencies.

Supported operations:

- Add, rename, or drop columns.
- Make columns nullable or not nullable.
- Change a column's type.
- Rename a table.
- Add or drop indexes and constraints.
- Add or drop column default values.

.. seealso:: :ref:`schema`

.. code-block:: python

   from playhouse.migrate import SchemaMigrator, migrate

   migrator = SchemaMigrator.from_database(db)

   with db.atomic():
       migrate(
           migrator.add_column('tweet', 'is_published', BooleanField(default=True)),
           migrator.add_column('user', 'email', CharField(null=True)),
           migrator.drop_column('user', 'old_bio'),
       )

.. tip::
   Wrap migrations in ``db.atomic()`` to ensure changes are not partially
   applied.

Operations
^^^^^^^^^^

**Add columns:**

.. code-block:: python

   # Non-null fields must supply a default value.
   migrate(
       migrator.add_column('comment', 'pub_date', DateTimeField(null=True)),
       migrator.add_column('comment', 'body', TextField(default='')),
   )

**Add a foreign key** (the column name must include the ``_id`` suffix that
Peewee appends by default):

.. code-block:: python

   user_fk = ForeignKeyField(User, field=User.id, null=True)
   migrate(
       migrator.add_column('tweet', 'user_id', user_fk),
   )

**Rename a column:**

.. code-block:: python

   migrate(
       migrator.rename_column('story', 'pub_date', 'publish_date'),
       migrator.rename_column('story', 'mod_date', 'modified_date'),
   )

**Drop a column:**

.. code-block:: python

   migrate(migrator.drop_column('story', 'old_field'))

**Nullable / not nullable:**

.. code-block:: python

   migrate(
       migrator.drop_not_null('story', 'pub_date'),  # Allow NULLs.
       migrator.add_not_null('story', 'modified_date'),  # Disallow NULLs.
   )

**Change type:**

.. code-block:: python

   # Change a VARCHAR(...) to a TEXT field.
   migrate(migrator.alter_column_type('person', 'email', TextField()))

**Rename table:**

.. code-block:: python

   migrate(migrator.rename_table('story', 'stories'))

**Add / drop indexes:**

.. code-block:: python

   # Specify table, column(s), and unique/non-unique.
   migrate(
       # Create an index on the `pub_date` column.
       migrator.add_index('story', ('pub_date',), False),  # Normal index.

       # Create a unique index on the category and title fields.
       migrator.add_index('story', ('category_id', 'title'), True),  # Unique.

       # Drop the pub-date + status index.
       migrator.drop_index('story', 'story_pub_date_status'),
   )

**Add / drop constraints:**

.. code-block:: python

   from peewee import Check

   # Add a CHECK() constraint to enforce the price cannot be negative.
   migrate(migrator.add_constraint(
       'products',
       'price_check',
       Check('price >= 0')))

   # Remove the price check constraint.
   migrate(migrator.drop_constraint('products', 'price_check'))

   # Add a UNIQUE constraint on the first and last names.
   migrate(migrator.add_unique('person', 'first_name', 'last_name'))

**Column defaults:**

.. code-block:: python

   # Add a default value:
   migrate(migrator.add_column_default('entry', 'status', 'draft'))

   # Use a function (not supported in SQLite):
   migrate(migrator.add_column_default('entry', 'created_at', fn.NOW()))

   # SQLite-compatible function syntax:
   migrate(migrator.add_column_default('entry', 'created_at', 'now()'))

   # Remove a default:
   migrate(migrator.drop_column_default('entry', 'status'))

.. note::
   Postgres users may need to set the search-path when using a non-standard
   schema. This can be done as follows:

   .. code-block:: python

      migrator = PostgresqlMigrator(db)
      migrate(
          migrator.set_search_path('my_schema'),
          migrator.add_column('table', 'field', TextField(default='')),
      )

Migration API
^^^^^^^^^^^^^

.. function:: migrate(*operations)

   Execute one or more schema-altering operations.

   Usage:

   .. code-block:: python

       migrate(
           migrator.add_column('t', 'col', CharField(default='')),
           migrator.add_index('t', ('col',), False),
       )

.. class:: SchemaMigrator(database)

   :param database: a :class:`Database` instance.

   The :class:`SchemaMigrator` is responsible for generating schema-altering
   statements.

   .. classmethod:: from_database(database)

      :param Database database: database instance to generate migrations for.
      :return: :class:`SchemaMigrator` instance appropriate to provided database.

      Factory method that returns the appropriate :class:`SchemaMigrator`
      subclass for the given database.

   .. method:: add_column(table, column_name, field)

      :param str table: Name of the table to add column to.
      :param str column_name: Name of the new column.
      :param Field field: A :class:`Field` instance.

      Add a new column to the provided table. The ``field`` provided will be used
      to generate the appropriate column definition.

      If the field is not nullable it must specify a default value.

      .. note::
         For non-null columns, the following occurs:

         1. column is added as allowing NULLs
         2. ``UPDATE`` query is executed to populate the default value
         3. column is changed to NOT NULL

   .. method:: drop_column(table, column_name, cascade=True)

      :param str table: Name of the table to drop column from.
      :param str column_name: Name of the column to drop.
      :param bool cascade: Whether the column should be dropped with `CASCADE`.

   .. method:: rename_column(table, old_name, new_name)

      :param str table: Name of the table containing column to rename.
      :param str old_name: Current name of the column.
      :param str new_name: New name for the column.

   .. method:: add_not_null(table, column)

      :param str table: Name of table containing column.
      :param str column: Name of the column to make not nullable.

   .. method:: drop_not_null(table, column)

      :param str table: Name of table containing column.
      :param str column: Name of the column to make nullable.

   .. method:: add_column_default(table, column, default)

      :param str table: Name of table containing column.
      :param str column: Name of the column to add default to.
      :param default: New default value for column. See notes below.

      Peewee attempts to properly quote the default if it appears to be a
      string literal. Otherwise the default will be treated literally.
      Postgres and MySQL support specifying the default as a peewee
      expression, e.g. ``fn.NOW()``, but Sqlite users will need to use
      ``default='now()'`` instead.

   .. method:: drop_column_default(table, column)

      :param str table: Name of table containing column.
      :param str column: Name of the column to remove default from.

   .. method:: alter_column_type(table, column, field, cast=None)

      :param str table: Name of the table.
      :param str column_name: Name of the column to modify.
      :param Field field: :class:`Field` instance representing new
          data type.
      :param cast: (postgres-only) specify a cast expression if the
          data-types are incompatible, e.g. ``column_name::int``. Can be
          provided as either a string or a :class:`Cast` instance.

      Alter the data-type of a column. This method should be used with care,
      as using incompatible types may not be well-supported by your database.

   .. method:: rename_table(old_name, new_name)

      :param str old_name: Current name of the table.
      :param str new_name: New name for the table.

   .. method:: add_index(table, columns, unique=False, using=None)

      :param str table: Name of table on which to create the index.
      :param list columns: List of columns which should be indexed.
      :param bool unique: Whether the new index should specify a unique constraint.
      :param str using: Index type (where supported), e.g. GiST or GIN.

   .. method:: drop_index(table, index_name)

      :param str table: Name of the table containing the index to be dropped.
      :param str index_name: Name of the index to be dropped.

   .. method:: add_constraint(table, name, constraint)

      :param str table: Table to add constraint to.
      :param str name: Name used to identify the constraint.
      :param constraint: either a :func:`Check` constraint or for
          adding an arbitrary constraint use :class:`SQL`.

   .. method:: drop_constraint(table, name)

      :param str table: Table to drop constraint from.
      :param str name: Name of constraint to drop.

   .. method:: add_unique(table, *column_names)

      :param str table: Table to add constraint to.
      :param str column_names: One or more columns for UNIQUE constraint.

.. class:: PostgresqlMigrator(database)

   .. method:: set_search_path(schema_name)

      Set the Postgres search path for subsequent operations.

.. class:: SqliteMigrator(database)

   SQLite has limited support for ``ALTER TABLE`` queries, so the following
   operations are currently not supported for SQLite:

   * ``add_constraint``
   * ``drop_constraint``
   * ``add_unique``

.. class:: MySQLMigrator(database)

   MySQL-specific subclass.


.. _reflection:

Reflection
----------

.. module:: playhouse.reflection

The ``playhouse.reflection`` module introspects an existing database and
generates Peewee model classes from its schema. It is used internally by
:ref:`pwiz` and :ref:`dataset`.

.. code-block:: python

   from playhouse.reflection import generate_models

   db = PostgresqlDatabase('my_app')
   models = generate_models(db)   # Returns {table_name: ModelClass}

   # list(models.keys())
   # ['account', 'customer', 'order', 'orderitem', 'product']

   # Get a reference to a generated model.
   Customer = models['customer']

   # Or inject into the current namespace:
   # globals().update(models)

   # Query generated models:
   for customer in Customer.select():
       print(customer.name, customer.email)

.. function:: generate_models(database, schema=None, **options)

   :param Database database: database instance to introspect.
   :param str schema: optional schema to introspect.
   :param options: arbitrary options, see :meth:`Introspector.generate_models` for details.
   :returns: a ``dict`` mapping table names to model classes.

.. function:: print_model(model)

   Print a human-readable summary of a model's fields and indexes to
   stdout. Useful for interactive exploration:

   .. code-block:: pycon

      >>> print_model(Tweet)
      tweet
        id AUTO PK
        user INT FK: User.id
        content TEXT
        timestamp DATETIME

      index(es)
        user_id
        timestamp

.. function:: print_table_sql(model)

   Print the ``CREATE TABLE`` SQL for a model class (without indexes or
   constraints):

   .. code-block:: pycon

      >>> print_table_sql(Tweet)
      CREATE TABLE IF NOT EXISTS "tweet" (
        "id" INTEGER NOT NULL PRIMARY KEY,
        "user_id" INTEGER NOT NULL,
        "content" TEXT NOT NULL,
        "timestamp" DATETIME NOT NULL,
        FOREIGN KEY ("user_id") REFERENCES "user" ("id")
      )

.. class:: Introspector(metadata, schema=None)

   Metadata can be extracted from a database by instantiating an :class:`Introspector`.
   Rather than instantiating this class directly, it is recommended to use the
   factory method :meth:`~Introspector.from_database`.

   .. classmethod:: from_database(database, schema=None)

      :param database: a :class:`Database` instance.
      :param str schema: an optional schema (supported by some databases).

      Creates an :class:`Introspector` instance suitable for use with the
      given database.

      .. code-block:: python

         db = SqliteDatabase('my_app.db')
         introspector = Introspector.from_database(db)
         models = introspector.generate_models()

         # User and Tweet (assumed to exist in the database) are
         # peewee Model classes generated from the database schema.
         User  = models['user']
         Tweet = models['tweet']

   .. method:: generate_models(skip_invalid=False, table_names=None, literal_column_names=False, bare_fields=False, include_views=False)

      :param bool skip_invalid: Skip tables whose names are not valid Python
          identifiers.
      :param list table_names: Only generate models for the given tables.
      :param bool literal_column_names: Use the exact database column names
          as field names (rather than converting to Python naming conventions).
      :param bool bare_fields: Do not attempt to detect field types; use
          :class:`BareField` for all columns (**SQLite only**).
      :param bool include_views: Also generate models for views.
      :return: A dictionary mapping table-names to model classes.

      Introspect the database, reading in the tables, columns, and foreign
      key constraints, then generate a dictionary mapping each database table
      to a dynamically-generated :class:`Model` class.


.. _pwiz:

pwiz - Model Generator
-----------------------

.. module:: pwiz

``pwiz`` is a command-line tool that introspects a database and prints
ready-to-use Peewee model code. If you have an existing database, running
``pwiz`` saves significant time generating the initial model definitions.

.. code-block:: shell

   # Introspect a Postgresql database and write models to a file:
   python -m pwiz -e postgresql -u postgres my_db > models.py

   # Introspect a SQLite database:
   python -m pwiz -e sqlite path/to/my.db

   # Introspect a MySQL database (prompts for password):
   python -m pwiz -e mysql -u root -P my_db

   # Introspect only specific tables:
   python -m pwiz -e postgresql my_db -t user,tweet,follow


Command-line options:

+--------+-------------------------------------------+-------------------------+
| Option | Meaning                                   | Example                 |
+========+===========================================+=========================+
| ``-e`` | Database backend                          | ``-e mysql``            |
+--------+-------------------------------------------+-------------------------+
| ``-H`` | Host                                      | ``-H 10.0.0.1``         |
+--------+-------------------------------------------+-------------------------+
| ``-p`` | Port                                      | ``-p 5432``             |
+--------+-------------------------------------------+-------------------------+
| ``-u`` | Username                                  | ``-u postgres``         |
+--------+-------------------------------------------+-------------------------+
| ``-P`` | Password (prompts interactively)          |                         |
+--------+-------------------------------------------+-------------------------+
| ``-s`` | Schema                                    | ``-s public``           |
+--------+-------------------------------------------+-------------------------+
| ``-t`` | Comma-separated list of tables to include | ``-t user,tweet``       |
+--------+-------------------------------------------+-------------------------+
| ``-v`` | Include views                             |                         |
+--------+-------------------------------------------+-------------------------+
| ``-i`` | Embed database info as a comment          |                         |
+--------+-------------------------------------------+-------------------------+
| ``-o`` | Preserve original column order            |                         |
+--------+-------------------------------------------+-------------------------+
| ``-I`` | Ignore fields whose type is unknown       |                         |
+--------+-------------------------------------------+-------------------------+
| ``-L`` | Use legacy table and column naming        |                         |
+--------+-------------------------------------------+-------------------------+

Valid ``-e`` values: ``sqlite``, ``mysql``, ``postgresql``.

.. warning::
   If a password is required to access your database, you will be prompted to
   enter it using a secure prompt.

   **The password will be included in the output**. Specifically, at the top
   of the file a :class:`Database` will be defined along with any required
   parameters - including the password.

Example output for a SQLite database with ``user`` and ``tweet`` tables:

.. code-block:: python

   from peewee import *

   database = SqliteDatabase('example.db', **{})

   class UnknownField(object):
       def __init__(self, *_, **__): pass

   class BaseModel(Model):
       class Meta:
           database = database

   class User(BaseModel):
       username = TextField(unique=True)

       class Meta:
           table_name = 'user'

   class Tweet(BaseModel):
       content = TextField()
       timestamp = DateTimeField()
       user = ForeignKeyField(column_name='user_id', field='id', model=User)

       class Meta:
           table_name = 'tweet'

Note that ``pwiz`` detects foreign keys, unique constraints, and preserves
explicit table names.

.. note::
    The ``UnknownField`` is a placeholder that is used in the event your schema
    contains a column declaration that Peewee doesn't know how to map to a
    field class.

.. _test-utils:

Test Utilities
--------------

.. module:: playhouse.test_utils

``playhouse.test_utils`` provides helpers for testing peewee projects.

.. class:: count_queries(only_select=False)

   Context manager that counts the number of SQL queries executed within
   its block.

   :param bool only_select: If ``True``, count only ``SELECT`` queries.

   .. code-block:: python

      with count_queries() as counter:
          user = User.get(User.username == 'alice')
          tweets = list(user.tweets)   # Triggers a second query.

      assert counter.count == 2

   .. attribute:: count

      Number of queries executed.

   .. method:: get_queries()

      Return a list of ``(sql, params)`` 2-tuples for each query executed.

.. function:: assert_query_count(expected, only_select=False)

   Decorator or context manager that raises ``AssertionError`` if the number
   of queries executed does not match ``expected``.

   As a decorator:

   .. code-block:: python

       class TestAPI(unittest.TestCase):
           @assert_query_count(1)
           def test_get_user(self):
               user = User.get_by_id(1)

   As a context manager:

   .. code-block:: python

       with assert_query_count(3):
           result = my_function_that_should_make_exactly_three_queries()
