1568de1efSMike Bayer========================
2568de1efSMike BayerDeveloping new Dialects
3568de1efSMike Bayer========================
4568de1efSMike Bayer
5568de1efSMike Bayer.. note::
6568de1efSMike Bayer
7568de1efSMike Bayer   When studying this file, it's probably a good idea to also
8568de1efSMike Bayer   familiarize with the  README.unittests.rst file, which discusses
9405fc971SGord Thompson   SQLAlchemy's usage and extension of the pytest test runner.
10568de1efSMike Bayer
11568de1efSMike BayerWhile SQLAlchemy includes many dialects within the core distribution, the
12568de1efSMike Bayertrend for new dialects should be that they are published as external
13568de1efSMike Bayerprojects.   SQLAlchemy has since version 0.5 featured a "plugin" system
14568de1efSMike Bayerwhich allows external dialects to be integrated into SQLAlchemy using
15568de1efSMike Bayerstandard setuptools entry points.  As of version 0.8, this system has
16568de1efSMike Bayerbeen enhanced, so that a dialect can also be "plugged in" at runtime.
17568de1efSMike Bayer
18dd755ca5SMike BayerOn the testing side, SQLAlchemy includes a "dialect compliance
19dd755ca5SMike Bayersuite" that is usable by third party libraries, in the source tree
20dd755ca5SMike Bayerat ``lib/sqlalchemy/testing/suite``.   There's no need for a third party
21dd755ca5SMike Bayerdialect to run through SQLAlchemy's full testing suite, as a large portion of
22dd755ca5SMike Bayerthese tests do not have dialect-sensitive functionality.  The "dialect
23dd755ca5SMike Bayercompliance suite" should be viewed as the primary target for new dialects.
24568de1efSMike Bayer
25ea05a232SMike Bayer
265eaeb701SMike BayerDialect Layout
275eaeb701SMike Bayer===============
285eaeb701SMike Bayer
29568de1efSMike BayerThe file structure of a dialect is typically similar to the following::
30568de1efSMike Bayer
31568de1efSMike Bayer    sqlalchemy-<dialect>/
32568de1efSMike Bayer                         setup.py
33568de1efSMike Bayer                         setup.cfg
34568de1efSMike Bayer                         sqlalchemy_<dialect>/
35568de1efSMike Bayer                                              __init__.py
36568de1efSMike Bayer                                              base.py
37568de1efSMike Bayer                                              <dbapi>.py
389e3cd16cSMike Bayer                                              requirements.py
39568de1efSMike Bayer                         test/
40568de1efSMike Bayer                                              __init__.py
41b5927dd9SGord Thompson                                              conftest.py
42568de1efSMike Bayer                                              test_suite.py
43568de1efSMike Bayer                                              test_<dialect_specific_test>.py
44568de1efSMike Bayer                                              ...
45568de1efSMike Bayer
46b5927dd9SGord ThompsonAn example of this structure can be seen in the MS Access dialect at
47b5927dd9SGord Thompsonhttps://github.com/gordthompson/sqlalchemy-access .
48568de1efSMike Bayer
495eaeb701SMike BayerKey aspects of this file layout include:
50568de1efSMike Bayer
51568de1efSMike Bayer* setup.py - should specify setuptools entrypoints, allowing the
525eaeb701SMike Bayer  dialect to be usable from create_engine(), e.g.::
53568de1efSMike Bayer
54a9eaea20SGord Thompson        entry_points = {
55568de1efSMike Bayer         'sqlalchemy.dialects': [
56568de1efSMike Bayer              'access.pyodbc = sqlalchemy_access.pyodbc:AccessDialect_pyodbc',
57568de1efSMike Bayer              ]
58568de1efSMike Bayer        }
59568de1efSMike Bayer
60a9eaea20SGord Thompson  Above, the entrypoint ``access.pyodbc`` allow URLs to be used such as::
61568de1efSMike Bayer
62568de1efSMike Bayer    create_engine("access+pyodbc://user:pw@dsn")
63568de1efSMike Bayer
64b5927dd9SGord Thompson* setup.cfg - this file contains the traditional contents such as
65b5927dd9SGord Thompson  [tool:pytest] directives, but also contains new directives that are used
66568de1efSMike Bayer  by SQLAlchemy's testing framework.  E.g. for Access::
67568de1efSMike Bayer
6831491a6aSMike Bayer    [tool:pytest]
6931491a6aSMike Bayer    addopts= --tb native -v -r fxX --maxfail=25 -p no:warnings
70ea05a232SMike Bayer    python_files=test/*test_*.py
71ea05a232SMike Bayer
72568de1efSMike Bayer    [sqla_testing]
739e3cd16cSMike Bayer    requirement_cls=sqlalchemy_access.requirements:Requirements
7431491a6aSMike Bayer    profile_file=test/profiles.txt
75568de1efSMike Bayer
76568de1efSMike Bayer    [db]
77568de1efSMike Bayer    default=access+pyodbc://admin@access_test
78568de1efSMike Bayer    sqlite=sqlite:///:memory:
79568de1efSMike Bayer
80568de1efSMike Bayer  Above, the ``[sqla_testing]`` section contains configuration used by
8131491a6aSMike Bayer  SQLAlchemy's test plugin.  The ``[tool:pytest]`` section
821a3792c3SMike Bayer  include directives to help with these runners.  When using pytest
831a3792c3SMike Bayer  the test/conftest.py file will bootstrap SQLAlchemy's plugin.
84ea05a232SMike Bayer
85ea05a232SMike Bayer* test/conftest.py - This script bootstraps SQLAlchemy's pytest plugin
86ea05a232SMike Bayer  into the pytest runner.  This
87ea05a232SMike Bayer  script can also be used to install your third party dialect into
88ea05a232SMike Bayer  SQLAlchemy without using the setuptools entrypoint system; this allows
89ea05a232SMike Bayer  your dialect to be present without any explicit setup.py step needed.
90ea05a232SMike Bayer  The other portion invokes SQLAlchemy's pytest plugin::
91ea05a232SMike Bayer
92ea05a232SMike Bayer    from sqlalchemy.dialects import registry
93a9eaea20SGord Thompson    import pytest
94ea05a232SMike Bayer
95ea05a232SMike Bayer    registry.register("access.pyodbc", "sqlalchemy_access.pyodbc", "AccessDialect_pyodbc")
96ea05a232SMike Bayer
97a9eaea20SGord Thompson    pytest.register_assert_rewrite("sqlalchemy.testing.assertions")
98a9eaea20SGord Thompson
99ea05a232SMike Bayer    from sqlalchemy.testing.plugin.pytestplugin import *
100ea05a232SMike Bayer
101ea05a232SMike Bayer  Where above, the ``registry`` module, introduced in SQLAlchemy 0.8, provides
102a9eaea20SGord Thompson  an in-Python means of installing the dialect entrypoint(s) without the use
103ea05a232SMike Bayer  of setuptools, using the ``registry.register()`` function in a way that
104ea05a232SMike Bayer  is similar to the ``entry_points`` directive we placed in our ``setup.py``.
105a9eaea20SGord Thompson  (The ``pytest.register_assert_rewrite`` is there just to suppress a spurious
106a9eaea20SGord Thompson  warning from pytest.)
107ea05a232SMike Bayer
108568de1efSMike Bayer* requirements.py - The ``requirements.py`` file is where directives
1099e3cd16cSMike Bayer  regarding database and dialect capabilities are set up.
110568de1efSMike Bayer  SQLAlchemy's tests are often annotated with decorators   that mark
111568de1efSMike Bayer  tests as "skip" or "fail" for particular backends.  Over time, this
112568de1efSMike Bayer  system   has been refined such that specific database and DBAPI names
113568de1efSMike Bayer  are mentioned   less and less, in favor of @requires directives which
114568de1efSMike Bayer  state a particular capability.   The requirement directive is linked
115568de1efSMike Bayer  to target dialects using a ``Requirements`` subclass.   The custom
116568de1efSMike Bayer  ``Requirements`` subclass is specified in the ``requirements.py`` file
117568de1efSMike Bayer  and   is made available to SQLAlchemy's test runner using the
118568de1efSMike Bayer  ``requirement_cls`` directive   inside the ``[sqla_testing]`` section.
119568de1efSMike Bayer
120568de1efSMike Bayer  For a third-party dialect, the custom ``Requirements`` class can
121568de1efSMike Bayer  usually specify a simple yes/no answer for a particular system. For
122568de1efSMike Bayer  example, a requirements file that specifies a database that supports
123051c2fdeSMike Bayer  the RETURNING construct but does not support nullable boolean
124051c2fdeSMike Bayer  columns might look like this::
125568de1efSMike Bayer
1269e3cd16cSMike Bayer      # sqlalchemy_access/requirements.py
127568de1efSMike Bayer
128568de1efSMike Bayer      from sqlalchemy.testing.requirements import SuiteRequirements
129568de1efSMike Bayer
130568de1efSMike Bayer      from sqlalchemy.testing import exclusions
131568de1efSMike Bayer
132568de1efSMike Bayer      class Requirements(SuiteRequirements):
133568de1efSMike Bayer          @property
134a9eaea20SGord Thompson          def nullable_booleans(self):
135a9eaea20SGord Thompson              """Target database allows boolean columns to store NULL."""
136a9eaea20SGord Thompson              # Access Yes/No doesn't allow null
137568de1efSMike Bayer              return exclusions.closed()
138568de1efSMike Bayer
139568de1efSMike Bayer          @property
140568de1efSMike Bayer          def returning(self):
141568de1efSMike Bayer              return exclusions.open()
142568de1efSMike Bayer
143568de1efSMike Bayer  The ``SuiteRequirements`` class in
144568de1efSMike Bayer  ``sqlalchemy.testing.requirements`` contains a large number of
145568de1efSMike Bayer  requirements rules, which attempt to have reasonable defaults. The
146568de1efSMike Bayer  tests will report on those requirements found as they are run.
147568de1efSMike Bayer
1489e3cd16cSMike Bayer  The requirements system can also be used when running SQLAlchemy's
1499e3cd16cSMike Bayer  primary test suite against the external dialect.  In this use case,
1509e3cd16cSMike Bayer  a ``--dburi`` as well as a ``--requirements`` flag are passed to SQLAlchemy's
1511a3792c3SMike Bayer  test runner so that exclusions specific to the dialect take place::
1529e3cd16cSMike Bayer
1539e3cd16cSMike Bayer    cd /path/to/sqlalchemy
154405fc971SGord Thompson    pytest -v \
1559e3cd16cSMike Bayer      --requirements sqlalchemy_access.requirements:Requirements \
1569e3cd16cSMike Bayer      --dburi access+pyodbc://admin@access_test
1579e3cd16cSMike Bayer
158568de1efSMike Bayer* test_suite.py - Finally, the ``test_suite.py`` module represents a
159ea05a232SMike Bayer  stub test suite, which pulls in the actual SQLAlchemy test suite.
160568de1efSMike Bayer  To pull in the suite as a whole, it can   be imported in one step::
161568de1efSMike Bayer
162568de1efSMike Bayer      # test/test_suite.py
163568de1efSMike Bayer
164568de1efSMike Bayer      from sqlalchemy.testing.suite import *
165568de1efSMike Bayer
166568de1efSMike Bayer  That's all that's needed - the ``sqlalchemy.testing.suite`` package
167568de1efSMike Bayer  contains an ever expanding series of tests, most of which should be
168568de1efSMike Bayer  annotated with specific requirement decorators so that they can be
169dd755ca5SMike Bayer  fully controlled.  In the case that the decorators are not covering
170dd755ca5SMike Bayer  a particular test, a test can also be directly modified or bypassed.
171dd755ca5SMike Bayer  In the example below, the Access dialect test suite overrides the
172dd755ca5SMike Bayer  ``get_huge_int()`` test::
173568de1efSMike Bayer
174568de1efSMike Bayer      from sqlalchemy.testing.suite import *
175568de1efSMike Bayer
176a9eaea20SGord Thompson      from sqlalchemy.testing.suite import IntegerTest as _IntegerTest
177568de1efSMike Bayer
178a9eaea20SGord Thompson      class IntegerTest(_IntegerTest):
179b5927dd9SGord Thompson
180b5927dd9SGord Thompson          @testing.skip("access")
181dd755ca5SMike Bayer          def test_huge_int(self):
182b5927dd9SGord Thompson              # bypass this test because Access ODBC fails with
183b5927dd9SGord Thompson              # [ODBC Microsoft Access Driver] Optional feature not implemented.
184568de1efSMike Bayer              return
185568de1efSMike Bayer
18626140c08SFederico CaselliAsyncIO dialects
18726140c08SFederico Caselli----------------
18826140c08SFederico Caselli
18926140c08SFederico CaselliAs of version 1.4 SQLAlchemy supports also dialects that use
19026140c08SFederico Caselliasyncio drivers to interface with the database backend.
19126140c08SFederico Caselli
19226140c08SFederico CaselliSQLAlchemy's approach to asyncio drivers is that the connection and cursor
19326140c08SFederico Caselliobjects of the driver (if any) are adapted into a pep-249 compliant interface,
19426140c08SFederico Caselliusing the ``AdaptedConnection`` interface class. Refer to the internal asyncio
19526140c08SFederico Casellidriver implementations such as that of ``asyncpg``, ``asyncmy`` and
19626140c08SFederico Caselli``aiosqlite`` for examples.
19726140c08SFederico Caselli
1985eaeb701SMike BayerGoing Forward
1995eaeb701SMike Bayer==============
2005eaeb701SMike Bayer
2015eaeb701SMike BayerThe third-party dialect can be distributed like any other Python
20266e88d30SLele Gaifaxmodule on PyPI. Links to prominent dialects can be featured within
2035eaeb701SMike BayerSQLAlchemy's own documentation; contact the developers (see AUTHORS)
2045eaeb701SMike Bayerfor help with this.
2055eaeb701SMike Bayer
2065eaeb701SMike BayerWhile SQLAlchemy includes many dialects built in, it remains to be
2075eaeb701SMike Bayerseen if the project as a whole might move towards "plugin" model for
2085eaeb701SMike Bayerall dialects, including all those currently built in.  Now that
2095eaeb701SMike BayerSQLAlchemy's dialect API is mature and the test suite is not far
2105eaeb701SMike Bayerbehind, it may be that a better maintenance experience can be
2115eaeb701SMike Bayerdelivered by having all dialects separately maintained and released.
2125eaeb701SMike Bayer
2135eaeb701SMike BayerAs new versions of SQLAlchemy are released, the test suite and
2145eaeb701SMike Bayerrequirements file will receive new tests and changes.  The dialect
2155eaeb701SMike Bayermaintainer would normally keep track of these changes and make
2165eaeb701SMike Bayeradjustments as needed.
2175eaeb701SMike Bayer
218