docs / destinations / generic_sql

Generic SQL

Any DB-API or SQLAlchemy dialect: MySQL, SQL Server, SQLite. Batched INSERT with emulated merge.

Every destination receives the same layout; see the tables Ingest creates.

Setup

This is the MySQL and general-SQLAlchemy destination. Ingest connects as a normal database user and needs to create and write tables in one database.

Create a user (MySQL / MariaDB)

CREATE USER 'ingest'@'%' IDENTIFIED BY 'a-long-random-password';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON your_db.* TO 'ingest'@'%';

All seven are used. CREATE/ALTER build the tables and widen them when your source adds a column; INSERT/UPDATE/DELETE write rows and emulate merge, since this destination has no native MERGE; SELECT reads back the _ingest_* state tables; DROP is needed because replace mode truncates and rebuilds.

'ingest'@'%' accepts connections from anywhere. If you prefer to pin it, pin it to Ingest's egress address rather than to a hostname.

Reachable from the internet

Ingest connects from its own fleet's public address and has no route into your VPC. The endpoint must be reachable there and your security group must allow it. A private address fails the connection test saying exactly that.

What to paste

Connection string, as a SQLAlchemy URL, for example mysql+pymysql://ingest:password@host:3306/your_db. It is stored as a secret in your own namespace.

Then test it

Test connection creates a scratch schema and a table, writes a few rows through the same load path a real run uses, reads them back to check the count is exactly right, round-trips pipeline state, and drops the lot. That proves CREATE and INSERT, rather than just the password.

Staging

needs_staging: none. Staging always happens in the destination's own cloud and region; data does not take a detour through ours.

Conformance

Every adapter passes the same suite before it ships: create a table with every canonical type, append 100k rows, atomic replace, merge (latest dedup_sort wins; child tables delete-then-insert by root id), schema evolution, NULL-key rejection, package replay without duplicates, state and schema round-trip, row-count read-back, identifier edge cases, double-underscored table names (__quarantine, child tables), and rollback on a mid-load failure.