GitHub
08/17/2025, 2:23 PMwith self._connect() as conn, conn.begin():.
But I think this does not allow us to group several operations in a single transaction. I know not all DBMS allow to include DDL operations in a transaction, but a lot of them do.
Concretly, I am working on the target-postgres here. Right now:
• SQLConnector is extended and sublcassed into PostgresConnector.
• A lot of methods are overriden with a different signature, with a lot of copy&paste from the original method, but only changing a couple of lines to include everything in one transaction.
Over time, this makes the overriden implementation diverge (a lot) from the Meltano SDK, which is not ideal.
Having a single transaction can make sense in targets, where I might want to have the ALTER statement of a table and the bulk_insert_records() in a single transaction.
Ideas
1. View SQLConnector as just a wrapper on SQLAlchemy to encapsulate some simple operations and it should resemble as much as possible SQLAlchemy's implementation. SQLAlchemy expects commits and connections to be managed by the caller, therefore maybe this class is not the best place to manage connections. Thinking of targets now, maybe SQLSink is a better place? (that's where the current target-postgres implementation manages it). Therefore, all DDL methods should have a connector or session argument that you can pass to them. If this argument was None, we could default to the current implementation of with self._connect() as conn, conn.begin():.
2. Create another intermediate class to encapsulate the DDL but not the execution itself. For example, now we have:
class SQLConnector:
def _create_empty_column(
self,
full_table_name: str,
column_name: str,
sql_type: sa.types.TypeEngine,
) -> None:
if not self.allow_column_add:
msg = "Adding columns is not supported."
raise NotImplementedError(msg)
column_add_ddl = self.get_column_add_ddl(
table_name=full_table_name,
column_name=column_name,
column_type=sql_type,
)
with self._connect() as conn, conn.begin():
conn.execute(column_add_ddl)
We could have something like:
class SQLConnectorBase:
def _create_empty_column(
self,
full_table_name: str,
column_name: str,
sql_type: sa.types.TypeEngine,
) -> sa.DDL:
if not self.allow_column_add:
msg = "Adding columns is not supported."
raise NotImplementedError(msg)
column_add_ddl = self.get_column_add_ddl(
table_name=full_table_name,
column_name=column_name,
column_type=sql_type,
)
# COMMENTED OUT
# with self._connect() as conn, conn.begin():
# conn.execute(column_add_ddl)
class SQLConnector(SQLConnectorBase):
def _create_empty_column(
self,
full_table_name: str,
column_name: str,
sql_type: sa.types.TypeEngine,
) -> None:
column_ddl = super()._create_empty_column(full_table_name, column_name, sql_type)
with self._connect() as conn, conn.begin():
conn.execute(column_add_ddl)
# Now, in target-postgres, I could create this:
class PostgresConnector(SQLConnectorBase):
def _create_empty_column(
self,
full_table_name: str,
column_name: str,
sql_type: sa.types.TypeEngine,
# New argument
connection
) -> None:
column_ddl = super()._create_empty_column(full_table_name, column_name, sql_type)
connection.execute(column_add_ddl)
# ... commit transaction somewhere else outside PostgresConnector
@qbatten , you were the OP of the issue I link, if you have any ideas they would be very welcome!
meltano/sdkGitHub
08/17/2025, 2:23 PM