Do not CAST(x as JSON) in SQLite

Fixed issue where the CAST applied to a JSON column when copying a SQLite
table during batch mode would cause the data to be lost, as SQLite's CAST
with JSON appears to convert the data to the value "0". The CAST is now
skipped in a dialect-specific manner, including for JSON columns on SQLite.
Pull request courtesy Sebastián Ramírez.

Fixes: #697
Closes: #698
Pull-request: https://github.com/sqlalchemy/alembic/pull/698
Pull-request-sha: 6618325258bd90ec257b09c17d44421a5642b1b1

Change-Id: Ia152ea7386e64efb2194aa836dc57754f979e204

2 files changed, 19 insertions, 0 deletions

diff --git a/alembic/ddl/impl.py b/alembic/ddl/impl.py
index 9df2a72..7b32b01 100644
--- a/alembic/ddl/impl.py
+++ b/alembic/ddl/impl.py
@@ -1,6 +1,7 @@
 from collections import namedtuple
 import re
 
+from sqlalchemy import cast
 from sqlalchemy import schema
 from sqlalchemy import text
 
@@ -449,6 +450,12 @@ class DefaultImpl(with_metaclass(ImplMeta)):
     ):
         pass
 
+    def cast_for_batch_migrate(self, existing, existing_transfer, new_type):
+        if existing.type._type_affinity is not new_type._type_affinity:
+            existing_transfer["expr"] = cast(
+                existing_transfer["expr"], new_type
+            )
+
     def render_ddl_sql_expr(self, expr, is_server_default=False, **kw):
         """Render a SQL expression that is typically a server default,
         index expression, etc.
diff --git a/alembic/ddl/sqlite.py b/alembic/ddl/sqlite.py
index 84e9dd8..8b78966 100644
--- a/alembic/ddl/sqlite.py
+++ b/alembic/ddl/sqlite.py
@@ -1,5 +1,8 @@
 import re
 
+from sqlalchemy import cast
+from sqlalchemy import JSON
+
 from .impl import DefaultImpl
 from .. import util
 
@@ -117,6 +120,15 @@ class SQLiteImpl(DefaultImpl):
             str_expr = "(%s)" % (str_expr,)
         return str_expr
 
+    def cast_for_batch_migrate(self, existing, existing_transfer, new_type):
+        if (
+            existing.type._type_affinity is not new_type._type_affinity
+            and not isinstance(new_type, JSON)
+        ):
+            existing_transfer["expr"] = cast(
+                existing_transfer["expr"], new_type
+            )
+
 
 # @compiles(AddColumn, 'sqlite')
 # def visit_add_column(element, compiler, **kw):