1========================
2Developing new Dialects
3========================
4
5.. note::
6
7   When studying this file, it's probably a good idea to also
8   familiarize with the  README.unittests.rst file, which discusses
9   SQLAlchemy's usage and extension of the pytest test runner.
10
11While SQLAlchemy includes many dialects within the core distribution, the
12trend for new dialects should be that they are published as external
13projects.   SQLAlchemy has since version 0.5 featured a "plugin" system
14which allows external dialects to be integrated into SQLAlchemy using
15standard setuptools entry points.  As of version 0.8, this system has
16been enhanced, so that a dialect can also be "plugged in" at runtime.
17
18On the testing side, SQLAlchemy includes a "dialect compliance
19suite" that is usable by third party libraries, in the source tree
20at ``lib/sqlalchemy/testing/suite``.   There's no need for a third party
21dialect to run through SQLAlchemy's full testing suite, as a large portion of
22these tests do not have dialect-sensitive functionality.  The "dialect
23compliance suite" should be viewed as the primary target for new dialects.
24
25
26Dialect Layout
27===============
28
29The file structure of a dialect is typically similar to the following::
30
31    sqlalchemy-<dialect>/
32                         setup.py
33                         setup.cfg
34                         sqlalchemy_<dialect>/
35                                              __init__.py
36                                              base.py
37                                              <dbapi>.py
38                                              requirements.py
39                         test/
40                                              __init__.py
41                                              conftest.py
42                                              test_suite.py
43                                              test_<dialect_specific_test>.py
44                                              ...
45
46An example of this structure can be seen in the MS Access dialect at
47https://github.com/gordthompson/sqlalchemy-access .
48
49Key aspects of this file layout include:
50
51* setup.py - should specify setuptools entrypoints, allowing the
52  dialect to be usable from create_engine(), e.g.::
53
54        entry_points = {
55         'sqlalchemy.dialects': [
56              'access.pyodbc = sqlalchemy_access.pyodbc:AccessDialect_pyodbc',
57              ]
58        }
59
60  Above, the entrypoint ``access.pyodbc`` allow URLs to be used such as::
61
62    create_engine("access+pyodbc://user:pw@dsn")
63
64* setup.cfg - this file contains the traditional contents such as
65  [tool:pytest] directives, but also contains new directives that are used
66  by SQLAlchemy's testing framework.  E.g. for Access::
67
68    [tool:pytest]
69    addopts= --tb native -v -r fxX --maxfail=25 -p no:warnings
70    python_files=test/*test_*.py
71
72    [sqla_testing]
73    requirement_cls=sqlalchemy_access.requirements:Requirements
74    profile_file=test/profiles.txt
75
76    [db]
77    default=access+pyodbc://admin@access_test
78    sqlite=sqlite:///:memory:
79
80  Above, the ``[sqla_testing]`` section contains configuration used by
81  SQLAlchemy's test plugin.  The ``[tool:pytest]`` section
82  include directives to help with these runners.  When using pytest
83  the test/conftest.py file will bootstrap SQLAlchemy's plugin.
84
85* test/conftest.py - This script bootstraps SQLAlchemy's pytest plugin
86  into the pytest runner.  This
87  script can also be used to install your third party dialect into
88  SQLAlchemy without using the setuptools entrypoint system; this allows
89  your dialect to be present without any explicit setup.py step needed.
90  The other portion invokes SQLAlchemy's pytest plugin::
91
92    from sqlalchemy.dialects import registry
93    import pytest
94
95    registry.register("access.pyodbc", "sqlalchemy_access.pyodbc", "AccessDialect_pyodbc")
96
97    pytest.register_assert_rewrite("sqlalchemy.testing.assertions")
98
99    from sqlalchemy.testing.plugin.pytestplugin import *
100
101  Where above, the ``registry`` module, introduced in SQLAlchemy 0.8, provides
102  an in-Python means of installing the dialect entrypoint(s) without the use
103  of setuptools, using the ``registry.register()`` function in a way that
104  is similar to the ``entry_points`` directive we placed in our ``setup.py``.
105  (The ``pytest.register_assert_rewrite`` is there just to suppress a spurious
106  warning from pytest.)
107
108* requirements.py - The ``requirements.py`` file is where directives
109  regarding database and dialect capabilities are set up.
110  SQLAlchemy's tests are often annotated with decorators   that mark
111  tests as "skip" or "fail" for particular backends.  Over time, this
112  system   has been refined such that specific database and DBAPI names
113  are mentioned   less and less, in favor of @requires directives which
114  state a particular capability.   The requirement directive is linked
115  to target dialects using a ``Requirements`` subclass.   The custom
116  ``Requirements`` subclass is specified in the ``requirements.py`` file
117  and   is made available to SQLAlchemy's test runner using the
118  ``requirement_cls`` directive   inside the ``[sqla_testing]`` section.
119
120  For a third-party dialect, the custom ``Requirements`` class can
121  usually specify a simple yes/no answer for a particular system. For
122  example, a requirements file that specifies a database that supports
123  the RETURNING construct but does not support nullable boolean
124  columns might look like this::
125
126      # sqlalchemy_access/requirements.py
127
128      from sqlalchemy.testing.requirements import SuiteRequirements
129
130      from sqlalchemy.testing import exclusions
131
132      class Requirements(SuiteRequirements):
133          @property
134          def nullable_booleans(self):
135              """Target database allows boolean columns to store NULL."""
136              # Access Yes/No doesn't allow null
137              return exclusions.closed()
138
139          @property
140          def returning(self):
141              return exclusions.open()
142
143  The ``SuiteRequirements`` class in
144  ``sqlalchemy.testing.requirements`` contains a large number of
145  requirements rules, which attempt to have reasonable defaults. The
146  tests will report on those requirements found as they are run.
147
148  The requirements system can also be used when running SQLAlchemy's
149  primary test suite against the external dialect.  In this use case,
150  a ``--dburi`` as well as a ``--requirements`` flag are passed to SQLAlchemy's
151  test runner so that exclusions specific to the dialect take place::
152
153    cd /path/to/sqlalchemy
154    pytest -v \
155      --requirements sqlalchemy_access.requirements:Requirements \
156      --dburi access+pyodbc://admin@access_test
157
158* test_suite.py - Finally, the ``test_suite.py`` module represents a
159  stub test suite, which pulls in the actual SQLAlchemy test suite.
160  To pull in the suite as a whole, it can   be imported in one step::
161
162      # test/test_suite.py
163
164      from sqlalchemy.testing.suite import *
165
166  That's all that's needed - the ``sqlalchemy.testing.suite`` package
167  contains an ever expanding series of tests, most of which should be
168  annotated with specific requirement decorators so that they can be
169  fully controlled.  In the case that the decorators are not covering
170  a particular test, a test can also be directly modified or bypassed.
171  In the example below, the Access dialect test suite overrides the
172  ``get_huge_int()`` test::
173
174      from sqlalchemy.testing.suite import *
175
176      from sqlalchemy.testing.suite import IntegerTest as _IntegerTest
177
178      class IntegerTest(_IntegerTest):
179
180          @testing.skip("access")
181          def test_huge_int(self):
182              # bypass this test because Access ODBC fails with
183              # [ODBC Microsoft Access Driver] Optional feature not implemented.
184              return
185
186AsyncIO dialects
187----------------
188
189As of version 1.4 SQLAlchemy supports also dialects that use
190asyncio drivers to interface with the database backend.
191
192SQLAlchemy's approach to asyncio drivers is that the connection and cursor
193objects of the driver (if any) are adapted into a pep-249 compliant interface,
194using the ``AdaptedConnection`` interface class. Refer to the internal asyncio
195driver implementations such as that of ``asyncpg``, ``asyncmy`` and
196``aiosqlite`` for examples.
197
198Going Forward
199==============
200
201The third-party dialect can be distributed like any other Python
202module on PyPI. Links to prominent dialects can be featured within
203SQLAlchemy's own documentation; contact the developers (see AUTHORS)
204for help with this.
205
206While SQLAlchemy includes many dialects built in, it remains to be
207seen if the project as a whole might move towards "plugin" model for
208all dialects, including all those currently built in.  Now that
209SQLAlchemy's dialect API is mature and the test suite is not far
210behind, it may be that a better maintenance experience can be
211delivered by having all dialects separately maintained and released.
212
213As new versions of SQLAlchemy are released, the test suite and
214requirements file will receive new tests and changes.  The dialect
215maintainer would normally keep track of these changes and make
216adjustments as needed.
217
218