SQL Query Translation

Orientation

SQL query translation is the conversion of a SQL statement from one dialect to another, or from raw SQL into a format consumable by a different storage engine, ORM, reporting tool, or migration pipeline. It matters whenever teams migrate databases, consolidate polyglot persistence, embed queries in tools that impose a specific dialect constraint, or expose internal schemas to external consumers. Translation is often invisible until a migration stalls, a report returns incomplete data, or an ORM generates unsupported syntax for the target engine. This page covers the concrete steps, tools, and failure modes you encounter when moving or adapting SQL across environments.

1. Dialect-to-Dialect Conversion

The most common translation need is moving a schema or query between relational engines. A widely used open‑source utility for this is sqlines. It is shipped as a Python script and is available from the project’s repository; no vendor‑specific SDK is required.

Command syntax

sqlines convert -i <source_dialect> -o <target_dialect> <file>

-i selects the input dialect (e.g., mysql, postgresql, sqlite, oracle).
-o selects the output dialect.
<file> is a path to a .sql file containingDDL or DML.

*Example: translating a MySQL schema to PostgreSQL*

sqlines convert -i mysql -o postgresql db_schema.sql

If db_schema.sql contains:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME NOT NULL,
    status VARCHAR(64) DEFAULT 'pending'
);

The generated PostgreSQL output will be:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL,
    status VARCHAR(64) DEFAULT 'pending'
);

Note that AUTO_INCREMENT becomes SERIAL and DATETIME becomes TIMESTAMP; the translator applies a type‑mapping table maintained by the project. If a type is not in the table, the tool emits a warning and leaves the clause unchanged, which can cause a ERROR: type "datetime" does not exist when the file is fed to psql.

2. Schema and Type Translation

Dialects disagree on column types, default constraints, and identifier quoting. Translators typically handle the most common mappings, but edge cases surface silently.

Source type	Common target mapping	Typical gotcha
`VARCHAR(n)`	`VARCHAR(n)`	`n` omitted or truncated in some tools
`DATETIME`	`TIMESTAMP` (PG) / `DATETIME2` (SQL Server)	Fractional‑second precision lost
`AUTO_INCREMENT`	`SERIAL` (PG) / `IDENTITY` (SQL Server)	Reserved‑word quoting changes
`ENGINE=InnoDB`	No‑op (ignored)	PostgreSQL ignores the clause; if the translator writes it into a PG dump, `psql` rejects it

Concrete failure example

A MySQL dump includes ENGINE=InnoDB on every table. Running sqlines convert -i mysql -o postgresql dump.sql produces a file that psql refuses with:

ERROR: syntax error at or near "ENGINE"
Position: 123

The fix is to post‑process the output and strip engine‑specific clauses, or use --skip-engines if the version of sqlines supports it.

3. Query Pattern Mismatches

Not all SQL patterns have a one‑to‑one equivalent. Translators that assume syntactic similarity will silently drop or rewrite clauses, often producing queries that run but return different results.

`LIMIT` / `OFFSET` ordering

MySQL uses LIMIT <count> OFFSET <offset>, while PostgreSQL uses LIMIT <count> OFFSET <offset> as of PG 10, but pre‑PG 10 syntax is LIMIT <offset>, <count>. A translator that blindly rewrites MySQL LIMIT 10, 20 into PostgreSQL LIMIT 20 OFFSET 10 will shift the result set.

Before (MySQL):

SELECT * FROM orders ORDER BY created_at DESC LIMIT 10, 20;

After (PG, wrong order):

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10;

The PG version fetches the first 20 rows then skips 10, returning rows 11‑30 instead of rows 11‑30 in MySQL’s meaning (which is actually rows 11‑30 when counting from 0, but the offset/count semantics differ). The only way to catch this is to run the translated query against a test dataset and compare row counts and ordering.

`ROWNUM` (Oracle) vs window functions

Oracle’s ROWNUM is pseudo‑column that filters *before* sorting. Translators that replace it with ROW_NUMBER() OVER (ORDER BY ...) change the logical evaluation order.

Oracle query:

SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM big_table a WHERE ROWNUM <= 100
) WHERE rn > 50;

A naïve translator may emit:

SELECT * FROM (
    SELECT a.*, ROW_NUMBER() OVER (ORDER BY id) rn FROM big_table a
) WHERE rn > 50 AND rn <= 100;

The PG‑equivalent will include rows that Oracle would have excluded because ROWNUM is evaluated in the filter step, not after the ORDER BY.

Function name drift

NOW() is MySQL/MariaDB; PostgreSQL accepts it as an alias for CURRENT_TIMESTAMP, but older engines may reject it.
GETDATE() is T‑SQL; PG does not recognize it.
SYSDATE (Oracle) vs CURRENT_TIMESTAMP (standard).

If a translator copies NOW() verbatim into a PG‑targeted dump, psql parses it without error, but a strict mode SET datestyle = 'ISO, MDY' can change the interpretation. More commonly, a translator that maps NOW() → GETDATE() for a SQL Server target will produce:

ERROR at line 1: Invalid column name 'GETDATE'.

because GETDATE() is a function, not a column identifier, and the server expects it in a SELECT list or expression context.

4. ORM and Abstraction Layer Bridging

Many teams sit an ORM (Hibernate, Entity Framework, Sequelize, etc.) between application code and the database. The ORM generates dialect‑specific SQL from a dialect‑agnostic query language (HQL, JPQL, raw find() calls). Translation errors at this layer surface when the ORM emits a function or join pattern that the target engine does not support.

Pattern

An application using Hibernate with MySQL may write:

String hql = "SELECT u FROM User u WHERE u.created > :cutoff";
Query query = session.createQuery(hql);
query.setParameter("cutoff", cutoffInstant);
List<User> users = query.list();

When the same code is pointed at a PostgreSQL backend, Hibernate generates:

SELECT user0_.id AS id1_0_ FROM user user0_ WHERE user0_.created > ?

The ? placeholder is correct, but if the application later switches to a SQLite backend, the ORM may emit CAST(? AS INTEGER) because SQLite lacks native TIMESTAMP comparison semantics. The translator (the ORM’s dialect code) has no generic “fallback”; it either has the mapping or it throws org.hibernate.dialect.DialectNotSupportedException.

What to watch for

Functions that are vendor‑specific (e.g., RAND(), RANDOM(), NEWID()) will be emitted as‑is if the dialect mapping is incomplete.
JOIN syntax variations: MySQL’s STRAIGHT_JOIN has no PG equivalent; the translator may drop the hint, silently changing the optimizer’s join order.
LIMIT/TOP/FETCH: MySQL LIMIT, T‑SQL TOP, PG LIMIT/OFFSET, Oracle FETCH FIRST n ROWS ONLY. An ORM that auto‑detects the target dialect will emit the correct form, but a hand‑written query in a generic SQL file will not be rewrote automatically.

5. Sharp Edges — What Silently Fails, What You Learn Too Late

Symptom	Root cause	What to check
Rows silently disappear after translation	`LIMIT`/`OFFSET` order inversion (MySQL `LIMIT count, offset` → PG `LIMIT offset, count`)	Run `EXPLAIN` on both sides; compare row counts
`ERROR: type "datetime" does not exist`	Type not in `sqlines` mapping table; left as‑is and fed to PG	Post‑process output; map missing types manually
`ERROR 1064: You have an error in your SQL syntax` near `DEFAULT 'pending'`	Quoting style mismatch: MySQL allows unquoted defaults; PG requires `DEFAULT 'pending'` or `DEFAULT value` without quotes around the word	Verify the translator’s quoting rules; escape reserved words
Aggregate window functions produce `ERROR: window function call without OVER`	Translator drops `OVER` clause when moving from PG to older engines that lack it	Keep `OVER` clauses explicit; test with `PARTITION BY` present
Production migration drops `CHECK` constraints	Type‑mapping table does not include domain‑type equivalents; constraint text is stripped	Export constraints separately; re‑apply after translation
Application logs show “Query timed out” after moving from MySQL to PG	`STRAIGHT_JOIN` removed, optimizer picks a different join order; cardinality stats differ	Capture `EXPLAIN ANALYZE` on both engines; adjust indexes or hints

What you only learn after it breaks in production

A migration that used sqlines to move a 12‑TB schema from MySQL to PostgreSQL silently dropped every CHECK constraint because the tool’s internal type‑mapping table had no entry for MySQL ENUM types. The constraints were not flagged as errors—they were simply omitted from the output. The team only discovered the gap when a INSERT that should have been rejected by a CHECK (status IN ('open','closed')) silently accepted invalid values. The fix was to run a diff between the source information_schema and the translated output, then manually re‑inject the constraints using ALTER TABLE ... ADD CONSTRAINT.

Another common blind spot: function‑level quoting. Moving a query that uses backtick‑escaped identifiers (SQLite convention) into MySQL often works because MySQL also accepts backticks, but if the original query references a table named order (a reserved word in MySQL), the translator may not auto‑add #order style quoting, and the query fails with ERROR 1064: You have an error in your SQL syntax... near 'order'. Always run the translated file through the target engine’s EXPLAIN or a quick SELECT 1 before deploying to production.

This page was rewritten on 14 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.

Join the Newsletter

Get the latest updates and deep insights shipped directly to your inbox.