135f7a0b5SJason Kirtland=====================
2749ac647SMike BayerSQLALCHEMY UNIT TESTS
335f7a0b5SJason Kirtland=====================
4749ac647SMike Bayer
531491a6aSMike BayerBasic Test Running
631491a6aSMike Bayer==================
7ea05a232SMike Bayer
8c312b3b2SMike BayerTox is used to run the test suite fully.   For basic test runs against
9c312b3b2SMike Bayera single Python interpreter::
10ea05a232SMike Bayer
11c312b3b2SMike Bayer    tox
125b8ab3e8SMike Bayer
13120dcee5SMike Bayer
14c312b3b2SMike BayerAdvanced Tox Options
15c312b3b2SMike Bayer====================
16a5c9696aSChris Withers
1731491a6aSMike BayerFor more elaborate CI-style test running, the tox script provided will
1831491a6aSMike Bayerrun against various Python / database targets.   For a basic run against
19f1f1005eSStephen RosenPython 3.8 using an in-memory SQLite database::
2063cea59cSMike Bayer
21c312b3b2SMike Bayer    tox -e py38-sqlite
22ea05a232SMike Bayer
2331491a6aSMike BayerThe tox runner contains a series of target combinations that can run
2431491a6aSMike Bayeragainst various combinations of databases.  The test suite can be
25c0f9708fSLele Gaifaxrun against SQLite with "backend" tests also running against a PostgreSQL
2631491a6aSMike Bayerdatabase::
27b38a76cdSMike Bayer
28c312b3b2SMike Bayer    tox -e py38-sqlite-postgresql
29b38a76cdSMike Bayer
3096e8ca71SGord ThompsonOr to run just "backend" tests against a MySQL database::
3139fddb8bSMike Bayer
32c312b3b2SMike Bayer    tox -e py38-mysql-backendonly
33120dcee5SMike Bayer
3431491a6aSMike BayerRunning against backends other than SQLite requires that a database of that
3531491a6aSMike Bayervendor be available at a specific URL.  See "Setting Up Databases" below
3631491a6aSMike Bayerfor details.
3739fddb8bSMike Bayer
38405fc971SGord ThompsonThe pytest Engine
3996e8ca71SGord Thompson=================
40120dcee5SMike Bayer
41405fc971SGord ThompsonThe tox runner is using pytest to invoke the test suite.   Within the realm of
42405fc971SGord Thompsonpytest, SQLAlchemy itself is adding a large series of option and
43405fc971SGord Thompsoncustomizations to the pytest runner using plugin points, to allow for
44c312b3b2SMike BayerSQLAlchemy's multiple database support, database setup/teardown and
45c312b3b2SMike Bayerconnectivity, multi process support, as well as lots of skip / database
46c312b3b2SMike Bayerselection rules.
47ea05a232SMike Bayer
48405fc971SGord ThompsonRunning tests with pytest directly grants more immediate control over
4931491a6aSMike Bayerdatabase options and test selection.
5045cec095SMike Bayer
51405fc971SGord ThompsonA generic pytest run looks like::
52d0509919SMichael Trier
53405fc971SGord Thompson    pytest -n4
54ea05a232SMike Bayer
5531491a6aSMike BayerAbove, the full test suite will run against SQLite, using four processes.
5631491a6aSMike BayerIf the "-n" flag is not used, the pytest-xdist is skipped and the tests will
5731491a6aSMike Bayerrun linearly, which will take a pretty long time.
58d0509919SMichael Trier
59405fc971SGord ThompsonThe pytest command line is more handy for running subsets of tests and to
6031491a6aSMike Bayerquickly allow for custom database connections.  Example::
61d0509919SMichael Trier
62405fc971SGord Thompson    pytest --dburi=postgresql+psycopg2://scott:tiger@localhost/test  test/sql/test_query.py
6335f7a0b5SJason Kirtland
6431491a6aSMike BayerAbove will run the tests in the test/sql/test_query.py file (a pretty good
6531491a6aSMike Bayerfile for basic "does this database work at all?" to start with) against a
66c0f9708fSLele Gaifaxrunning PostgreSQL database at the given URL.
674c61875aSMike Bayer
68405fc971SGord ThompsonThe pytest frontend can also run tests against multiple kinds of databases at
69c312b3b2SMike Bayeronce - a large subset of tests are marked as "backend" tests, which will be run
70c312b3b2SMike Bayeragainst each available backend, and additionally lots of tests are targeted at
71c312b3b2SMike Bayerspecific backends only, which only run if a matching backend is made available.
72c312b3b2SMike BayerFor example, to run the test suite against both PostgreSQL and MySQL at the
73c312b3b2SMike Bayersame time::
74ea05a232SMike Bayer
75405fc971SGord Thompson    pytest -n4 --db postgresql --db mysql
76ea05a232SMike Bayer
77ea05a232SMike Bayer
7831491a6aSMike BayerSetting Up Databases
7931491a6aSMike Bayer====================
80ea05a232SMike Bayer
8131491a6aSMike BayerThe test suite identifies several built-in database tags that run against
8231491a6aSMike Bayera pre-set URL.  These can be seen using --dbs::
83ea05a232SMike Bayer
84405fc971SGord Thompson    $ pytest --dbs
85ea05a232SMike Bayer    Available --db options (use --dburi to override)
8631491a6aSMike Bayer                 default    sqlite:///:memory:
87603f7d30SGeorg Richter                 mariadb    mariadb://scott:tiger@192.168.0.199:3307/test
88c312b3b2SMike Bayer                   mssql    mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server
8931491a6aSMike Bayer           mssql_pymssql    mssql+pymssql://scott:tiger@ms_2008
9023b62c72SGord Thompson                   mysql    mysql://scott:tiger@127.0.0.1:3306/test?charset=utf8mb4
9131491a6aSMike Bayer                  oracle    oracle://scott:tiger@127.0.0.1:1521
9231491a6aSMike Bayer                 oracle8    oracle://scott:tiger@127.0.0.1:1521/?use_ansi=0
9331491a6aSMike Bayer                  pg8000    postgresql+pg8000://scott:tiger@127.0.0.1:5432/test
9431491a6aSMike Bayer              postgresql    postgresql://scott:tiger@127.0.0.1:5432/test
9531491a6aSMike Bayer    postgresql_psycopg2cffi postgresql+psycopg2cffi://scott:tiger@127.0.0.1:5432/test
9623b62c72SGord Thompson                 pymysql    mysql+pymysql://scott:tiger@127.0.0.1:3306/test?charset=utf8mb4
9731491a6aSMike Bayer                  sqlite    sqlite:///:memory:
9831491a6aSMike Bayer             sqlite_file    sqlite:///querytest.db
9931491a6aSMike Bayer
100c312b3b2SMike BayerNote that a pyodbc URL **must be against a hostname / database name
101c312b3b2SMike Bayercombination, not a DSN name** when using the multiprocessing option; this is
102c312b3b2SMike Bayerbecause the test suite needs to generate new URLs to refer to per-process
103c312b3b2SMike Bayerdatabases that are created on the fly.
104c312b3b2SMike Bayer
10531491a6aSMike BayerWhat those mean is that if you have a database running that can be accessed
10631491a6aSMike Bayerby the above URL, you can run the test suite against it using ``--db <name>``.
10731491a6aSMike Bayer
10831491a6aSMike BayerThe URLs are present in the ``setup.cfg`` file.   You can make your own URLs by
10931491a6aSMike Bayercreating a new file called ``test.cfg`` and adding your own ``[db]`` section::
11031491a6aSMike Bayer
11131491a6aSMike Bayer    # test.cfg file
112ea05a232SMike Bayer    [db]
11331491a6aSMike Bayer    my_postgresql=postgresql://username:pass@hostname/dbname
114ea05a232SMike Bayer
11531491a6aSMike BayerAbove, we can now run the tests with ``my_postgresql``::
116ea05a232SMike Bayer
117405fc971SGord Thompson    pytest --db my_postgresql
118ea05a232SMike Bayer
11931491a6aSMike BayerWe can also override the existing names in our ``test.cfg`` file, so that we can run
12031491a6aSMike Bayerwith the tox runner also::
121ea05a232SMike Bayer
12231491a6aSMike Bayer    # test.cfg file
12331491a6aSMike Bayer    [db]
12431491a6aSMike Bayer    postgresql=postgresql://username:pass@hostname/dbname
125ea05a232SMike Bayer
12631491a6aSMike BayerNow when we run ``tox -e py27-postgresql``, it will use our custom URL instead
12731491a6aSMike Bayerof the fixed one in setup.cfg.
128ea05a232SMike Bayer
12931491a6aSMike BayerDatabase Configuration
13031491a6aSMike Bayer======================
131ea05a232SMike Bayer
132c312b3b2SMike BayerStep one, the **database chosen for tests must be entirely empty**.  A lot
133c312b3b2SMike Bayerof what SQLAlchemy tests is creating and dropping lots of tables
134c312b3b2SMike Bayeras well as running database introspection to see what is there.  If there
135c312b3b2SMike Bayerare pre-existing tables or other objects in the target database already,
136c312b3b2SMike Bayerthese will get in the way.   A failed test run can also be followed by
137c312b3b2SMike Bayer a run that includes the "--dropfirst" option, which will try to drop
138c312b3b2SMike Bayerall existing tables in the target database.
139c312b3b2SMike Bayer
140c312b3b2SMike BayerThe above paragraph changes somewhat when the multiprocessing option
141c312b3b2SMike Bayeris used, in that separate databases will be created instead, however
142c312b3b2SMike Bayerin the case of Postgresql, the starting database is used as a template,
14365bea954SFederico Caselliso the starting database must still be empty.  See below for example
14465bea954SFederico Caselliconfigurations using docker.
145c312b3b2SMike Bayer
14631491a6aSMike BayerThe test runner will by default create and drop tables within the default
147c312b3b2SMike Bayerdatabase that's in the database URL, *unless* the multiprocessing option is in
148405fc971SGord Thompsonuse via the pytest "-n" flag, which invokes pytest-xdist.   The
149c312b3b2SMike Bayermultiprocessing option is **enabled by default** when using the tox runner.
150c312b3b2SMike BayerWhen multiprocessing is used, the SQLAlchemy testing framework will create a
151c312b3b2SMike Bayernew database for each process, and then tear it down after the test run is
152c312b3b2SMike Bayercomplete.    So it will be necessary for the database user to have access to
153c312b3b2SMike BayerCREATE DATABASE in order for this to work.   Additionally, as mentioned
154c312b3b2SMike Bayerearlier, the database URL must be formatted such that it can be rewritten on
155c312b3b2SMike Bayerthe fly to refer to these other databases, which means for pyodbc it must refer
156c312b3b2SMike Bayerto a hostname/database name combination, not a DSN name.
1578fc5005dSMike Bayer
15839fd3442SMike BayerSeveral tests require alternate usernames or schemas to be present, which
15939fd3442SMike Bayerare used to test dotted-name access scenarios.  On some databases such
16096e8ca71SGord Thompsonas Oracle these are usernames, and others such as PostgreSQL
16139fd3442SMike Bayerand MySQL they are schemas.   The requirement applies to all backends
162568de1efSMike Bayerexcept SQLite and Firebird.  The names are::
16315bc27bfSMike Bayer
16415bc27bfSMike Bayer    test_schema
16562b7daceSFederico Caselli    test_schema_2 (only used on PostgreSQL and mssql)
16615bc27bfSMike Bayer
167568de1efSMike BayerPlease refer to your vendor documentation for the proper syntax to create
16839fd3442SMike Bayerthese namespaces - the database user must have permission to create and drop
16915bc27bfSMike Bayertables within these schemas.  Its perfectly fine to run the test suite
17039fd3442SMike Bayerwithout these namespaces present, it only means that a handful of tests which
17115bc27bfSMike Bayerexpect them to be present will fail.
17215bc27bfSMike Bayer
173568de1efSMike BayerAdditional steps specific to individual databases are as follows::
17415bc27bfSMike Bayer
175bf70f556SMike Bayer    POSTGRESQL: To enable unicode testing with JSONB, create the
176bf70f556SMike Bayer    database with UTF8 encoding::
177bf70f556SMike Bayer
178bf70f556SMike Bayer        postgres=# create database test with owner=scott encoding='utf8' template=template0;
179bf70f556SMike Bayer
180bf70f556SMike Bayer    To include tests for HSTORE, create the HSTORE type engine::
181bf70f556SMike Bayer
182bf70f556SMike Bayer        postgres=# \c test;
183bf70f556SMike Bayer        You are now connected to database "test" as user "postgresql".
184bf70f556SMike Bayer        test=# create extension hstore;
185bf70f556SMike Bayer        CREATE EXTENSION
186bf70f556SMike Bayer
18731491a6aSMike Bayer    Full-text search configuration should be set to English, else
18831491a6aSMike Bayer    several tests of ``.match()`` will fail. This can be set (if it isn't so
18931491a6aSMike Bayer    already) with:
1909211ecb6SMike Bayer
19131491a6aSMike Bayer     ALTER DATABASE test SET default_text_search_config = 'pg_catalog.english'
19231491a6aSMike Bayer
19392fd25f3SFederico Caselli    For two-phase transaction support, the max_prepared_transactions
19492fd25f3SFederico Caselli    configuration variable must be set to a non-zero value in postgresql.conf.
19592fd25f3SFederico Caselli    See
19692fd25f3SFederico Caselli    https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS
19792fd25f3SFederico Caselli    for further background.
19892fd25f3SFederico Caselli
19931491a6aSMike Bayer    ORACLE: a user named "test_schema" is created in addition to the default
20031491a6aSMike Bayer    user.
201350aed3fSMike Bayer
20239fd3442SMike Bayer    The primary database user needs to be able to create and drop tables,
20339fd3442SMike Bayer    synonyms, and constraints within the "test_schema" user.   For this
20439fd3442SMike Bayer    to work fully, including that the user has the "REFERENCES" role
20566227a93SDiana Clarke    in a remote schema for tables not yet defined (REFERENCES is per-table),
20639fd3442SMike Bayer    it is required that the test the user be present in the "DBA" role:
207350aed3fSMike Bayer
208d9af1828SMike Bayer        grant dba to scott;
209350aed3fSMike Bayer
2102b1937a3SMike Bayer    MSSQL: Tests that involve multiple connections require Snapshot Isolation
21166227a93SDiana Clarke    ability implemented on the test database in order to prevent deadlocks that
2122b1937a3SMike Bayer    will occur with record locking isolation. This feature is only available
2132b1937a3SMike Bayer    with MSSQL 2005 and greater. You must enable snapshot isolation at the
2142b1937a3SMike Bayer    database level and set the default cursor isolation with two SQL commands:
215350aed3fSMike Bayer
2162b1937a3SMike Bayer     ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
217350aed3fSMike Bayer
2182b1937a3SMike Bayer     ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
21915bc27bfSMike Bayer
22065bea954SFederico CaselliDocker Configurations
22165bea954SFederico Caselli---------------------
22265bea954SFederico Caselli
22365bea954SFederico CaselliThe SQLAlchemy test can run against database running in Docker containers.
22465bea954SFederico CaselliThis ensures that they are empty and that their configuration is not influenced
22565bea954SFederico Caselliby any local usage.
22665bea954SFederico Caselli
22765bea954SFederico CaselliThe following configurations are just examples that developers can use to
22865bea954SFederico Caselliquickly set up a local environment for SQLAlchemy development. They are **NOT**
22965bea954SFederico Caselliintended for production use!
23065bea954SFederico Caselli
23165bea954SFederico Caselli**PostgreSQL configuration**::
23265bea954SFederico Caselli
23365bea954SFederico Caselli    # create the container with the proper configuration for sqlalchemy
2347ae4a43dSDaniel Black    docker run --rm -e POSTGRES_USER='scott' -e POSTGRES_PASSWORD='tiger' -e POSTGRES_DB='test' -p 127.0.0.1:5432:5432 -d --name postgres postgres
23565bea954SFederico Caselli
23665bea954SFederico Caselli    # configure the database
23765bea954SFederico Caselli    sleep 10
23865bea954SFederico Caselli    docker exec -ti postgres psql -U scott -c 'CREATE SCHEMA test_schema; CREATE SCHEMA test_schema_2;' test
23965bea954SFederico Caselli    # this last command is optional
2409ec75882SFederico Caselli    docker exec -ti postgres sed -i 's/#max_prepared_transactions = 0/max_prepared_transactions = 10/g' /var/lib/postgresql/data/postgresql.conf
24165bea954SFederico Caselli
24265bea954SFederico Caselli    # To stop the container. It will also remove it.
24365bea954SFederico Caselli    docker stop postgres
24465bea954SFederico Caselli
24565bea954SFederico Caselli**MySQL configuration**::
24665bea954SFederico Caselli
24765bea954SFederico Caselli    # create the container with the proper configuration for sqlalchemy
2487ae4a43dSDaniel Black    docker run --rm -e MYSQL_USER='scott' -e MYSQL_PASSWORD='tiger' -e MYSQL_DATABASE='test' -e MYSQL_ROOT_PASSWORD='password' -p 127.0.0.1:3306:3306 -d --name mysql mysql --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
24965bea954SFederico Caselli
25065bea954SFederico Caselli    # configure the database
25165bea954SFederico Caselli    sleep 20
2527ae4a43dSDaniel Black    docker exec -ti mysql mysql -u root -ppassword -w -e "CREATE DATABASE test_schema CHARSET utf8mb4; GRANT ALL ON test_schema.* TO scott;"
25365bea954SFederico Caselli
25465bea954SFederico Caselli    # To stop the container. It will also remove it.
25565bea954SFederico Caselli    docker stop mysql
25665bea954SFederico Caselli
257603f7d30SGeorg Richter**MariaDB configuration**::
258603f7d30SGeorg Richter
259603f7d30SGeorg Richter    # create the container with the proper configuration for sqlalchemy
2607ae4a43dSDaniel Black    docker run --rm -e MARIADB_USER='scott' -e MARIADB_PASSWORD='tiger' -e MARIADB_DATABASE='test' -e MARIADB_ROOT_PASSWORD='password' -p 127.0.0.1:3306:3306 -d --name mariadb mariadb --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
261603f7d30SGeorg Richter
262603f7d30SGeorg Richter    # configure the database
263603f7d30SGeorg Richter    sleep 20
2647ae4a43dSDaniel Black    docker exec -ti mariadb mysql -u root -ppassword -w -e "CREATE DATABASE test_schema CHARSET utf8mb4; GRANT ALL ON test_schema.* TO scott;"
265603f7d30SGeorg Richter
266603f7d30SGeorg Richter    # To stop the container. It will also remove it.
267603f7d30SGeorg Richter    docker stop mariadb
268603f7d30SGeorg Richter
26965bea954SFederico Caselli**MSSQL configuration**::
27065bea954SFederico Caselli
27165bea954SFederico Caselli    # create the container with the proper configuration for sqlalchemy
27265bea954SFederico Caselli    # it will use the Developer version
2737ae4a43dSDaniel Black    docker run --rm -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 127.0.0.1:1433:1433 -d --name mssql mcr.microsoft.com/mssql/server
27465bea954SFederico Caselli
27565bea954SFederico Caselli    # configure the database
27665bea954SFederico Caselli    sleep 20
277e3871c07SGord Thompson    docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -Q "sp_configure 'contained database authentication', 1; RECONFIGURE; CREATE DATABASE test CONTAINMENT = PARTIAL; ALTER DATABASE test SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON; CREATE LOGIN scott WITH PASSWORD = 'tiger^5HHH'; ALTER SERVER ROLE sysadmin ADD MEMBER scott;"
27865bea954SFederico Caselli    docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d test -Q "CREATE SCHEMA test_schema"
27965bea954SFederico Caselli    docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d test -Q "CREATE SCHEMA test_schema_2"
28065bea954SFederico Caselli
28165bea954SFederico Caselli    # To stop the container. It will also remove it.
28265bea954SFederico Caselli    docker stop mssql
28365bea954SFederico Caselli
28465bea954SFederico CaselliNOTE: with this configuration the url to use is not the default one configured
28565bea954SFederico Caselliin setup, but ``mssql+pymssql://scott:tiger^5HHH@127.0.0.1:1433/test``.  It can
286405fc971SGord Thompsonbe used with pytest by using ``--db docker_mssql``.
287120dcee5SMike Bayer
288749ac647SMike BayerCONFIGURING LOGGING
28935f7a0b5SJason Kirtland-------------------
29035f7a0b5SJason KirtlandSQLAlchemy logs its activity and debugging through Python's logging package.
29135f7a0b5SJason KirtlandAny log target can be directed to the console with command line options, such
292568de1efSMike Bayeras::
293120dcee5SMike Bayer
294405fc971SGord Thompson    $ ./pytest test/orm/test_unitofwork.py -s \
29535f7a0b5SJason Kirtland      --log-debug=sqlalchemy.pool --log-info=sqlalchemy.engine
29635f7a0b5SJason Kirtland
297405fc971SGord ThompsonAbove we add the pytest "-s" flag so that standard out is not suppressed.
298749ac647SMike Bayer
29935f7a0b5SJason Kirtland
300568de1efSMike BayerDEVELOPING AND TESTING NEW DIALECTS
301568de1efSMike Bayer-----------------------------------
302749ac647SMike Bayer
30335551841SMike BayerSee the file README.dialects.rst for detail on dialects.
304749ac647SMike Bayer
305dceadfcaSMarc Abramowitz
306