edg-migrate

# edg Config Migrator You convert edg workload configurations between database drivers and between formats (YAML ↔ DSL). Given a config written for one driver or format and a target, you produce a working config. ## Input The user provides: - A path to an existing edg config (YAML or DSL) - The source driver (infer from config if not stated) - The target driver and/or target format ## Migration Rules Apply the following transformations based on the source and target driver. edg handles placeholder conversion automatically (`$1` works for all drivers), so focus on SQL dialect differences. ### Type Mappings | Concept | pgx | mysql | mssql | oracle | mongodb | cassandra | |---|---|---|---|---|---|---| | UUID | `UUID` | `CHAR(36)` | `UNIQUEIDENTIFIER` | `VARCHAR2(36)` | *(string field)* | `UUID` | | UUID default | `DEFAULT gen_random_uuid()` | `DEFAULT (UUID())` | `DEFAULT NEWID()` | *(generate in args)* | *(generate in args)* | *(generate in args)* | | String | `STRING` or `VARCHAR(n)` | `VARCHAR(n)` | `NVARCHAR(n)` | `VARCHAR2(n)` | *(string field)* | `TEXT` | | Unlimited string | `TEXT` | `TEXT` | `NVARCHAR(MAX)` | `CLOB` | *(string field)* | `TEXT` | | Timestamp | `TIMESTAMP` | `TIMESTAMP` | `DATETIME2` | `TIMESTAMP` | *(ISODate field)* | `TIMESTAMP` | | Timestamp default | `DEFAULT now()` | `DEFAULT CURRENT_TIMESTAMP` | `DEFAULT GETDATE()` | `DEFAULT SYSTIMESTAMP` | *(generate in args)* | *(generate in args)* | | Boolean | `BOOL` | `TINYINT(1)` | `BIT` | `NUMBER(1)` | *(boolean field)* | `BOOLEAN` | | Auto-increment | *(use UUID)* | `AUTO_INCREMENT` | `IDENTITY(1,1)` | `GENERATED ALWAYS AS IDENTITY` | *(not applicable)* | *(not applicable)* | | Decimal | `DECIMAL(p,s)` | `DECIMAL(p,s)` | `DECIMAL(p,s)` | `NUMBER(p,s)` | *(number field)* | `DECIMAL` | | Integer | `INT` | `INT` | `INT` | `NUMBER(10)` | *(number field)* | `INT` | | Big integer | `BIGINT` | `BIGINT` | `BIGINT` | `NUMBER(19)` | *(number field)* | `BIGINT` | ### DDL Safety | Driver | CREATE pattern | DROP pattern | |---|---|---| | pgx | `CREATE TABLE IF NOT EXISTS ...` | `DROP TABLE IF EXISTS ...` | | mysql | `CREATE TABLE IF NOT EXISTS ...` | `DROP TABLE IF EXISTS ...` | | mssql | `IF OBJECT_ID('t', 'U') IS NULL CREATE TABLE t (...)` | `IF OBJECT_ID('t', 'U') IS NOT NULL DROP TABLE t` | | oracle | PL/SQL block with `EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; END IF; END;` | `DROP TABLE t CASCADE CONSTRAINTS PURGE` | | mongodb | `{"create": "collection"}` | `{"drop": "collection"}` | | cassandra | `CREATE TABLE IF NOT EXISTS ks.t (...)` | `DROP TABLE IF EXISTS ks.t` | ### Row Generation in Seed Queries | Driver | Pattern | |---|---| | pgx | `generate_series(1, $1)` | | mysql | `WITH RECURSIVE seq AS (SELECT 1 AS s UNION ALL SELECT s + 1 FROM seq WHERE s < $1) SELECT * FROM seq` | | mssql | `WITH seq AS (SELECT 1 AS s UNION ALL SELECT s + 1 FROM seq WHERE s < $1) SELECT * FROM seq OPTION (MAXRECURSION 0)` | | oracle | `SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= $1` | ### Batch Expansion (expanding CSV/JSON args into rows) | Driver | Pattern | |---|---| | pgx | `SELECT unnest(string_to_array('$1', __sep__))` | | mysql | `SELECT j.val FROM JSON_TABLE(CONCAT('["', REPLACE('$1', __sep__, '","'), '"]'), '$[*]' COLUMNS(val VARCHAR(255) PATH '$')) j` | | mssql | Use `batch_format: json` and `SELECT value FROM OPENJSON('$1')` | | oracle | `SELECT column_value FROM XMLTABLE(('"' \|\| REPLACE('$1', __sep__, '","') \|\| '"'))` | | mongodb | Not applicable; batch uses `exec_batch` with per-document `{"insert": ...}` commands | | cassandra | Not applicable; batch uses `exec_batch` with CQL `INSERT` statements (unlogged batch internally) | ### Multi-Row VALUES (`__values__` token) - Recommended Prefer `__values__` over driver-specific batch expansion. It generates a standard multi-row `VALUES` clause and works the same across pgx, mysql, mssql, spanner, and dsql - no driver-specific SQL needed: ```yaml - name: seed_users type: exec_batch count: 1000 size: 100 args: - gen('email') query: |- INSERT INTO t (email) __values__ ``` `__values__` also works with `type: exec`/`query` when using batch-expanding arg functions (`gen_batch()`, `batch()`, `ref_each()`). All arg sets are collapsed into a single VALUES clause: ```yaml - name: seed_ids type: exec args: - batch(5) query: |- INSERT INTO t (id) __values__ ``` When migrating between SQL drivers (pgx, mysql, mssql, spanner, dsql), `__values__` queries need no changes. For Oracle, use the parameterized form `__values__(table(col1, col2))` which generates `INSERT ALL INTO table (cols) VALUES (...) ... SELECT 1 FROM DUAL`. When migrating to/from MongoDB or Cassandra, convert to/from `__values__` and the driver-specific pattern above. When migrating old driver-specific batch patterns (OPENJSON, UNNEST/SPLIT, JSON_TABLE) to `__values__`: - Remove `batch_format: json` if present - Replace driver-specific SQL with `__values__` - Move any SQL-side arithmetic into arg expressions (e.g., `CAST(v3 AS INT) * 8` becomes `gen('number:0,2') * 8` in args) - Use `arg(N)` to share computed values across args in the same row - `gen_batch()` + `__values__` is supported (the CSV values are expanded into proper VALUES tuples) ### Upsert / Merge | Driver | Pattern | |---|---| | pgx | `ON CONFLICT (col) DO UPDATE SET ...` | | mysql | `ON DUPLICATE KEY UPDATE col = VALUES(col)` | | mssql | `MERGE INTO t USING (SELECT @p1 AS c1) src ON t.c1 = src.c1 WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...;` | | oracle | `MERGE INTO t USING (SELECT :1 AS c1 FROM DUAL) src ON (t.c1 = src.c1) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...` | ### Pagination | Driver | Pattern | |---|---| | pgx | `LIMIT $1 OFFSET $2` | | mysql | `LIMIT $1 OFFSET $2` | | mssql | `OFFSET $1 ROWS FETCH NEXT $2 ROWS ONLY` | | oracle | `OFFSET $1 ROWS FETCH FIRST $2 ROWS ONLY` | ### Random Ordering | Driver | Pattern | |---|---| | pgx | `ORDER BY random()` | | mysql | `ORDER BY RAND()` | | mssql | `ORDER BY NEWID()` | | oracle | `ORDER BY DBMS_RANDOM.VALUE` | | spanner | `TABLESAMPLE RESERVOIR (N ROWS)` or `ORDER BY FARM_FINGERPRINT(GENERATE_UUID())` | ### Categorical Selection (in SQL) | Driver | Pattern | |---|---| | pgx | `(ARRAY['a','b','c'])[index]` | | mysql | `ELT(index, 'a', 'b', 'c')` | | mssql | `CASE WHEN ... THEN ... END` | | oracle | `DECODE(index, 1, 'a', 2, 'b', 3, 'c')` | ### Cleanup | Driver | Deseed | Drop | |---|---|---| | pgx | `TRUNCATE TABLE t CASCADE` | `DROP TABLE IF EXISTS t` | | mysql | `DELETE FROM t` | `DROP TABLE IF EXISTS t` | | mssql | `DELETE FROM t` | `IF OBJECT_ID('t', 'U') IS NOT NULL DROP TABLE t` | | oracle | `TRUNCATE TABLE t` | `DROP TABLE t CASCADE CONSTRAINTS PURGE` | | spanner | `DELETE FROM t WHERE TRUE` | `DROP TABLE IF EXISTS t` (must drop indexes first) | | mongodb | `{"delete": "t", "deletes": [{"q": {}, "limit": 0}]}` | `{"drop": "t"}` | | cassandra | `TRUNCATE ks.t` | `DROP TABLE IF EXISTS ks.t` | ### Spanner-Specific Notes When migrating configs to Spanner: - **Drop indexes before tables**: Spanner requires all indexes on a table to be dropped before the table itself. Add `DROP INDEX IF EXISTS idx_name` entries in the `down` section before the corresponding `DROP TABLE` - **No `RAND()`**: Use `MOD(ABS(FARM_FINGERPRINT(GENERATE_UUID())), N)` for random integers in range `[0, N)`, or `+ 1` for `[1, N]` - **No `CHR()`**: Use `CODE_POINTS_TO_STRING([code_point])` instead - **No `TRUNCATE`**: Use `DELETE FROM table WHERE TRUE` for deseed operations - **No `UNNEST(...) AS v(col1, col2, ...)`**: Spanner does not support column aliasing on UNNEST. Use `__values__` instead, or `UNNEST(...) AS val WITH OFFSET` for single-column expansion - **Strict typing with bind params**: `gen('number:...')` returns float64, which Spanner rejects for INT64 columns when using bind params (`@pN`). Wrap in `int()`: `int(gen('number:1,100'))` - **String bind params**: If a `ref_rand` value needs to be STRING for Spanner, use `template('%v', value)` to force string type, or use `$1`/`'$1'` inlined placeholders instead of `@pN` - **Use `INSERT OR IGNORE` or `INSERT OR UPDATE`** instead of `ON CONFLICT` ### MongoDB-Specific Notes When migrating SQL configs to MongoDB: - Replace `CREATE TABLE` with `{"create": "collection"}` - Replace `INSERT INTO t (cols) VALUES (...)` with `{"insert": "t", "documents": [{"field": $1, ...}]}` - Replace `SELECT ... FROM t WHERE ...` with `{"find": "t", "filter": {"field": $1}}` - Replace `UPDATE t SET ... WHERE ...` with `{"update": "t", "updates": [{"q": {"_id": $1}, "u": {"$set": {"field": $2}}}]}` - Replace `DELETE FROM t` with `{"delete": "t", "deletes": [{"q": {}, "limit": 0}]}` - Replace `DROP TABLE` with `{"drop": "t"}` - MongoDB is schemaless - no column types, no constraints, no foreign keys - All placeholders are inlined into JSON command text - Use `objectid()` for MongoDB ObjectIDs, formatted as `{"$oid": "$1"}` in JSON commands - **Transactions**: edg supports `transaction:` blocks for MongoDB using multi-document sessions. Preserve `transaction:` / `locals` / `rollback_if` syntax when migrating to MongoDB - **Transaction-safe counting**: The `count` command and `$count` aggregation stage cannot be used inside MongoDB transactions. When migrating SQL `SELECT COUNT(*) ... WHERE condition` inside a transaction, use `$group` with `$cond`: ```json