1=====================
2SQLALCHEMY UNIT TESTS
3=====================
4
5Basic Test Running
6==================
7
8Tox is used to run the test suite fully.   For basic test runs against
9a single Python interpreter::
10
11    tox
12
13
14Advanced Tox Options
15====================
16
17For more elaborate CI-style test running, the tox script provided will
18run against various Python / database targets.   For a basic run against
19Python 3.8 using an in-memory SQLite database::
20
21    tox -e py38-sqlite
22
23The tox runner contains a series of target combinations that can run
24against various combinations of databases.  The test suite can be
25run against SQLite with "backend" tests also running against a PostgreSQL
26database::
27
28    tox -e py38-sqlite-postgresql
29
30Or to run just "backend" tests against a MySQL database::
31
32    tox -e py38-mysql-backendonly
33
34Running against backends other than SQLite requires that a database of that
35vendor be available at a specific URL.  See "Setting Up Databases" below
36for details.
37
38The pytest Engine
39=================
40
41The tox runner is using pytest to invoke the test suite.   Within the realm of
42pytest, SQLAlchemy itself is adding a large series of option and
43customizations to the pytest runner using plugin points, to allow for
44SQLAlchemy's multiple database support, database setup/teardown and
45connectivity, multi process support, as well as lots of skip / database
46selection rules.
47
48Running tests with pytest directly grants more immediate control over
49database options and test selection.
50
51A generic pytest run looks like::
52
53    pytest -n4
54
55Above, the full test suite will run against SQLite, using four processes.
56If the "-n" flag is not used, the pytest-xdist is skipped and the tests will
57run linearly, which will take a pretty long time.
58
59The pytest command line is more handy for running subsets of tests and to
60quickly allow for custom database connections.  Example::
61
62    pytest --dburi=postgresql+psycopg2://scott:tiger@localhost/test  test/sql/test_query.py
63
64Above will run the tests in the test/sql/test_query.py file (a pretty good
65file for basic "does this database work at all?" to start with) against a
66running PostgreSQL database at the given URL.
67
68The pytest frontend can also run tests against multiple kinds of databases at
69once - a large subset of tests are marked as "backend" tests, which will be run
70against each available backend, and additionally lots of tests are targeted at
71specific backends only, which only run if a matching backend is made available.
72For example, to run the test suite against both PostgreSQL and MySQL at the
73same time::
74
75    pytest -n4 --db postgresql --db mysql
76
77
78Setting Up Databases
79====================
80
81The test suite identifies several built-in database tags that run against
82a pre-set URL.  These can be seen using --dbs::
83
84    $ pytest --dbs
85    Available --db options (use --dburi to override)
86                 default    sqlite:///:memory:
87                 mariadb    mariadb://scott:tiger@192.168.0.199:3307/test
88                   mssql    mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server
89           mssql_pymssql    mssql+pymssql://scott:tiger@ms_2008
90                   mysql    mysql://scott:tiger@127.0.0.1:3306/test?charset=utf8mb4
91                  oracle    oracle://scott:tiger@127.0.0.1:1521
92                 oracle8    oracle://scott:tiger@127.0.0.1:1521/?use_ansi=0
93                  pg8000    postgresql+pg8000://scott:tiger@127.0.0.1:5432/test
94              postgresql    postgresql://scott:tiger@127.0.0.1:5432/test
95    postgresql_psycopg2cffi postgresql+psycopg2cffi://scott:tiger@127.0.0.1:5432/test
96                 pymysql    mysql+pymysql://scott:tiger@127.0.0.1:3306/test?charset=utf8mb4
97                  sqlite    sqlite:///:memory:
98             sqlite_file    sqlite:///querytest.db
99
100Note that a pyodbc URL **must be against a hostname / database name
101combination, not a DSN name** when using the multiprocessing option; this is
102because the test suite needs to generate new URLs to refer to per-process
103databases that are created on the fly.
104
105What those mean is that if you have a database running that can be accessed
106by the above URL, you can run the test suite against it using ``--db <name>``.
107
108The URLs are present in the ``setup.cfg`` file.   You can make your own URLs by
109creating a new file called ``test.cfg`` and adding your own ``[db]`` section::
110
111    # test.cfg file
112    [db]
113    my_postgresql=postgresql://username:pass@hostname/dbname
114
115Above, we can now run the tests with ``my_postgresql``::
116
117    pytest --db my_postgresql
118
119We can also override the existing names in our ``test.cfg`` file, so that we can run
120with the tox runner also::
121
122    # test.cfg file
123    [db]
124    postgresql=postgresql://username:pass@hostname/dbname
125
126Now when we run ``tox -e py27-postgresql``, it will use our custom URL instead
127of the fixed one in setup.cfg.
128
129Database Configuration
130======================
131
132Step one, the **database chosen for tests must be entirely empty**.  A lot
133of what SQLAlchemy tests is creating and dropping lots of tables
134as well as running database introspection to see what is there.  If there
135are pre-existing tables or other objects in the target database already,
136these will get in the way.   A failed test run can also be followed by
137 a run that includes the "--dropfirst" option, which will try to drop
138all existing tables in the target database.
139
140The above paragraph changes somewhat when the multiprocessing option
141is used, in that separate databases will be created instead, however
142in the case of Postgresql, the starting database is used as a template,
143so the starting database must still be empty.  See below for example
144configurations using docker.
145
146The test runner will by default create and drop tables within the default
147database that's in the database URL, *unless* the multiprocessing option is in
148use via the pytest "-n" flag, which invokes pytest-xdist.   The
149multiprocessing option is **enabled by default** when using the tox runner.
150When multiprocessing is used, the SQLAlchemy testing framework will create a
151new database for each process, and then tear it down after the test run is
152complete.    So it will be necessary for the database user to have access to
153CREATE DATABASE in order for this to work.   Additionally, as mentioned
154earlier, the database URL must be formatted such that it can be rewritten on
155the fly to refer to these other databases, which means for pyodbc it must refer
156to a hostname/database name combination, not a DSN name.
157
158Several tests require alternate usernames or schemas to be present, which
159are used to test dotted-name access scenarios.  On some databases such
160as Oracle these are usernames, and others such as PostgreSQL
161and MySQL they are schemas.   The requirement applies to all backends
162except SQLite and Firebird.  The names are::
163
164    test_schema
165    test_schema_2 (only used on PostgreSQL and mssql)
166
167Please refer to your vendor documentation for the proper syntax to create
168these namespaces - the database user must have permission to create and drop
169tables within these schemas.  Its perfectly fine to run the test suite
170without these namespaces present, it only means that a handful of tests which
171expect them to be present will fail.
172
173Additional steps specific to individual databases are as follows::
174
175    POSTGRESQL: To enable unicode testing with JSONB, create the
176    database with UTF8 encoding::
177
178        postgres=# create database test with owner=scott encoding='utf8' template=template0;
179
180    To include tests for HSTORE, create the HSTORE type engine::
181
182        postgres=# \c test;
183        You are now connected to database "test" as user "postgresql".
184        test=# create extension hstore;
185        CREATE EXTENSION
186
187    Full-text search configuration should be set to English, else
188    several tests of ``.match()`` will fail. This can be set (if it isn't so
189    already) with:
190
191     ALTER DATABASE test SET default_text_search_config = 'pg_catalog.english'
192
193    For two-phase transaction support, the max_prepared_transactions
194    configuration variable must be set to a non-zero value in postgresql.conf.
195    See
196    https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS
197    for further background.
198
199    ORACLE: a user named "test_schema" is created in addition to the default
200    user.
201
202    The primary database user needs to be able to create and drop tables,
203    synonyms, and constraints within the "test_schema" user.   For this
204    to work fully, including that the user has the "REFERENCES" role
205    in a remote schema for tables not yet defined (REFERENCES is per-table),
206    it is required that the test the user be present in the "DBA" role:
207
208        grant dba to scott;
209
210    MSSQL: Tests that involve multiple connections require Snapshot Isolation
211    ability implemented on the test database in order to prevent deadlocks that
212    will occur with record locking isolation. This feature is only available
213    with MSSQL 2005 and greater. You must enable snapshot isolation at the
214    database level and set the default cursor isolation with two SQL commands:
215
216     ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
217
218     ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
219
220Docker Configurations
221---------------------
222
223The SQLAlchemy test can run against database running in Docker containers.
224This ensures that they are empty and that their configuration is not influenced
225by any local usage.
226
227The following configurations are just examples that developers can use to
228quickly set up a local environment for SQLAlchemy development. They are **NOT**
229intended for production use!
230
231**PostgreSQL configuration**::
232
233    # create the container with the proper configuration for sqlalchemy
234    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
235
236    # configure the database
237    sleep 10
238    docker exec -ti postgres psql -U scott -c 'CREATE SCHEMA test_schema; CREATE SCHEMA test_schema_2;' test
239    # this last command is optional
240    docker exec -ti postgres sed -i 's/#max_prepared_transactions = 0/max_prepared_transactions = 10/g' /var/lib/postgresql/data/postgresql.conf
241
242    # To stop the container. It will also remove it.
243    docker stop postgres
244
245**MySQL configuration**::
246
247    # create the container with the proper configuration for sqlalchemy
248    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
249
250    # configure the database
251    sleep 20
252    docker exec -ti mysql mysql -u root -ppassword -w -e "CREATE DATABASE test_schema CHARSET utf8mb4; GRANT ALL ON test_schema.* TO scott;"
253
254    # To stop the container. It will also remove it.
255    docker stop mysql
256
257**MariaDB configuration**::
258
259    # create the container with the proper configuration for sqlalchemy
260    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
261
262    # configure the database
263    sleep 20
264    docker exec -ti mariadb mysql -u root -ppassword -w -e "CREATE DATABASE test_schema CHARSET utf8mb4; GRANT ALL ON test_schema.* TO scott;"
265
266    # To stop the container. It will also remove it.
267    docker stop mariadb
268
269**MSSQL configuration**::
270
271    # create the container with the proper configuration for sqlalchemy
272    # it will use the Developer version
273    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
274
275    # configure the database
276    sleep 20
277    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;"
278    docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d test -Q "CREATE SCHEMA test_schema"
279    docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d test -Q "CREATE SCHEMA test_schema_2"
280
281    # To stop the container. It will also remove it.
282    docker stop mssql
283
284NOTE: with this configuration the url to use is not the default one configured
285in setup, but ``mssql+pymssql://scott:tiger^5HHH@127.0.0.1:1433/test``.  It can
286be used with pytest by using ``--db docker_mssql``.
287
288CONFIGURING LOGGING
289-------------------
290SQLAlchemy logs its activity and debugging through Python's logging package.
291Any log target can be directed to the console with command line options, such
292as::
293
294    $ ./pytest test/orm/test_unitofwork.py -s \
295      --log-debug=sqlalchemy.pool --log-info=sqlalchemy.engine
296
297Above we add the pytest "-s" flag so that standard out is not suppressed.
298
299
300DEVELOPING AND TESTING NEW DIALECTS
301-----------------------------------
302
303See the file README.dialects.rst for detail on dialects.
304
305
306