From d526cedef3cd742259af51dac7a8b35e6be6c9f2 Mon Sep 17 00:00:00 2001
From: Tip ten Brink <75669206+tiptenbrink@users.noreply.github.com>
Date: Mon, 15 Sep 2025 08:58:40 -0400
Subject: [PATCH] Fix get_columns sqlite reflection rejecting tables with
 WITHOUT_ROWID and/or STRICT for generated column case

Fixed issue where SQLite table reflection would fail for tables using
``WITHOUT ROWID`` and/or ``STRICT`` table options when the table contained
generated columns. The regular expression used to parse ``CREATE TABLE``
statements for generated column detection has been updated to properly
handle these SQLite table options that appear after the column definitions.
Pull request courtesy Tip ten Brink.

Fixes: #12864

Closes: #12865
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12865
Pull-request-sha: 833420afc72f1918a9ca315209ce6664a18ba07f

Change-Id: Ia25c05746055a66234531b4f1275bd3a2f3b44ad
---
 doc/build/changelog/unreleased_20/12864.rst | 10 +++++++++
 lib/sqlalchemy/dialects/sqlite/base.py      |  5 ++++-
 test/dialect/test_sqlite.py                 | 23 +++++++++++++++++++++
 3 files changed, 37 insertions(+), 1 deletion(-)
 create mode 100644 doc/build/changelog/unreleased_20/12864.rst

diff --git a/doc/build/changelog/unreleased_20/12864.rst b/doc/build/changelog/unreleased_20/12864.rst
new file mode 100644
index 0000000000..f8d1e5b44e
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/12864.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: bug, sqlite
+    :tickets: 12864
+
+    Fixed issue where SQLite table reflection would fail for tables using
+    ``WITHOUT ROWID`` and/or ``STRICT`` table options when the table contained
+    generated columns. The regular expression used to parse ``CREATE TABLE``
+    statements for generated column detection has been updated to properly
+    handle these SQLite table options that appear after the column definitions.
+    Pull request courtesy Tip ten Brink.
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index d1abf26c3c..6530b7eb5c 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -2377,7 +2377,10 @@ class SQLiteDialect(default.DefaultDialect):
                 )
                 # remove create table
                 match = re.match(
-                    r"create table .*?\((.*)\)$",
+                    (
+                        r"create table .*?\((.*)\)"
+                        r"(?:\s*,?\s*(?:WITHOUT\s+ROWID|STRICT))*$"
+                    ),
                     tablesql.strip(),
                     re.DOTALL | re.IGNORECASE,
                 )
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index 05c8ea250d..97fced1200 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -3980,6 +3980,25 @@ class ComputedReflectionTest(fixtures.TestBase):
                 x INTEGER GENERATED ALWAYS AS (INSTR(s, ",")) STORED,
                 y INTEGER GENERATED ALWAYS AS (INSTR(x, ",")) STORED
             );""",
+            """CREATE TABLE test9 (
+                id INTEGER PRIMARY KEY,
+                s VARCHAR,
+                x VARCHAR GENERATED ALWAYS AS (s || 'x')
+            ) WITHOUT ROWID;""",
+            """CREATE TABLE test10 (
+                s TEXT,
+                x TEXT GENERATED ALWAYS AS (s || 'x')
+            ) STRICT;""",
+            """CREATE TABLE test11 (
+                id INTEGER PRIMARY KEY,
+                s TEXT,
+                x TEXT GENERATED ALWAYS AS (s || 'x')
+            ) STRICT, WITHOUT ROWID;""",
+            """CREATE TABLE test12 (
+                id INTEGER PRIMARY KEY,
+                s TEXT,
+                x TEXT GENERATED ALWAYS AS (s || 'x')
+            ) WITHOUT ROWID, STRICT;""",
         ]
 
         with testing.db.begin() as conn:
@@ -4013,6 +4032,10 @@ class ComputedReflectionTest(fixtures.TestBase):
             "x": {"text": 'INSTR(s, ",")', "stored": True},
             "y": {"text": 'INSTR(x, ",")', "stored": True},
         },
+        "test9": {"x": {"text": "s || 'x'", "stored": False}},
+        "test10": {"x": {"text": "s || 'x'", "stored": False}},
+        "test11": {"x": {"text": "s || 'x'", "stored": False}},
+        "test12": {"x": {"text": "s || 'x'", "stored": False}},
     }
 
     def test_reflection(self, connection):
-- 
2.47.3