Merge "Document user-defined functions for sqlite" into main

2 files changed, 41 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/aiosqlite.py b/lib/sqlalchemy/dialects/sqlite/aiosqlite.py
index 93c1a499d..97ea1b2b3 100644
--- a/lib/sqlalchemy/dialects/sqlite/aiosqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/aiosqlite.py
@@ -36,6 +36,14 @@ This dialect should normally be used only with the
 The URL passes through all arguments to the ``pysqlite`` driver, so all
 connection arguments are the same as they are for that of :ref:`pysqlite`.
 
+.. _aiosqlite_udfs:
+
+User-Defined Functions
+----------------------
+
+aiosqlite extends pysqlite to support async, so we can create our own user-defined functions (UDFs)
+in Python and use them directly in SQLite queries as described here: :ref:`pysqlite_udfs`.
+
 
 """  # noqa
 
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index f155fed36..19949441f 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -406,6 +406,39 @@ by adding the desired locking mode to our ``"BEGIN"``::
     `sqlite3 module breaks transactions and potentially corrupts data <https://bugs.python.org/issue10740>`_ -
     on the Python bug tracker
 
+.. _pysqlite_udfs:
+
+User-Defined Functions
+----------------------
+
+pysqlite supports a `create_function() <https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function>`_
+method that allows us to create our own user-defined functions (UDFs) in Python and use them directly in SQLite queries.
+These functions are registered with a specific DBAPI Connection.
+
+SQLAlchemy uses connection pooling with file-based SQLite databases, so we need to ensure that the UDF is attached to the
+connection when it is created. That is accomplished with an event listener::
+
+    from sqlalchemy import create_engine
+    from sqlalchemy import event
+    from sqlalchemy import text
+
+
+    def udf():
+        return "udf-ok"
+
+
+    engine = create_engine("sqlite:///./db_file")
+
+
+    @event.listens_for(engine, "connect")
+    def connect(conn, rec):
+        conn.create_function("udf", 0, udf)
+
+
+    for i in range(5):
+        with engine.connect() as conn:
+            print(conn.scalar(text("SELECT UDF()")))
+
 
 """  # noqa