Skip to content

Sqlglot as a backup for your database migration

Preamble

One of my colleagues draws my attention to a Python library called sqlglot that can transpile SQL queries between different dialects. His idea was to use it for potential migration from Oracle to MariaDB or PostgreSQL.

Looking at the official web site (https://github.com/tobymao/sqlglot) I was really impress and wanted to perform few test to see how it could help…

At the end the product is lacking latest features and does not work at all wit PL/SQL. I rate you could even get a better result with an AI…

Anyways as it is an open source project it might evolve and using it, you do not consume any AI token (money and safe planet) !

My testing has been done with Python 3.14.2 and sqlglot 28.6.0.

sqlglot01
sqlglot01

Sqlglot installation

I create my Miniforge conda environment with this environment.yml file:

name: sqlglot
dependencies:
  - python=3.14

Create the environment and activate it with:

conda env create --file='C:\Users\yjaquier\environment.yml'
conda activate sqlglot

Then install sqlglot with:

pip3 install "sqlglot[rs]"

My Python code is simply:

import sqlglot

statement01 = """
select sysdate from dual;
"""

FROM = "oracle"
TO = "mysql"

transpile01 = sqlglot.transpile(statement01, read=FROM, write=TO)

for sql in transpile01:
  print(sql)

Sqlglot testing

I have mainly testing from MariaDB to Oracle as this could be a typical migration to save some cost (replace MariaDB by any other open source low cost database of your choice, like PostgreSQL. To test some “reverse” I have also done some testing from Oracle to MariaDB.

Oracle SYSDATE and MariaDB equivalent, working well except that sqlglot does not understand special Oracle dual pseudo-table:

OracleMariaDB
select sysdate from dual;
SELECT CURRENT_TIMESTAMP() FROM `dual`

The Oracle auto_increment equivalent of MariaDB:

OracleMariaDB
drop table users;
 
create table users 
(
  id number generated always as identity increment by 1 start with 1 not null,
  name varchar2(255),
  constraint users_pk primary key (id) enable 
);
Error, sqlglot does not yet know the new IDENTITY keyword (release in 12cR1).

If I try to go from MariaDB to Oracle it gives:

MariaDBOracle
create table users (
  id mediumint(8) unsigned not null auto_increment,
  name varchar(255) default null,
  primary key (id)
) auto_increment=1;
Not at all an Oracle compliant command:

CREATE TABLE users
(id UMEDIUMINT(8) NOT NULL AUTO_INCREMENT, name VARCHAR2(255) DEFAULT NULL, PRIMARY KEY (id))
AUTO_INCREMENT=1

The Oracle LIMIT equivalent:

OracleMariaDB
select *
from employees
order by hire_date desc
offset 20 rows
fetch next 10 rows only;
Technically working but a bit surprised by the complicated result:

SELECT * FROM employees
ORDER BY CASE WHEN hire_date IS NULL THEN 1 ELSE 0 END DESC, hire_date DESC LIMIT 10 OFFSET 20

The Oracle MERGE that should be replaced by the INSERT ON DUPLICATE KEY UPDATE MariaDB statement. Not working at all::

OracleMariaDB
MERGE INTO employees e
USING (SELECT 1   AS id,
              'Yan' AS first_name,
              'Jaquier' AS last_name,
              100000   AS salary
       FROM dual) src
ON (e.id = src.id)
WHEN MATCHED THEN
  UPDATE SET
    e.first_name = src.first_name,
    e.last_name  = src.last_name,
    e.salary     = src.salary
WHEN NOT MATCHED THEN
  INSERT (id, first_name, last_name, salary)
  VALUES (src.id, src.first_name, src.last_name, src.salary);
MERGE INTO employees AS e USING (SELECT 1 AS id, 'Yan' AS first_name, 'Jaquier' AS last_name, 100000 AS salary FROM `dual`) AS src
ON (e.id = src.id)
WHEN MATCHED THEN UPDATE SET e.first_name = src.first_name, e.last_name = src.last_name, e.salary = src.salary
WHEN NOT MATCHED THEN INSERT (id, first_name, last_name, salary) VALUES (src.id, src.first_name, src.last_name, src.salary)

Final test, more complex one, with Oracle PL/SQL. Nat handle at all as what I have used in my code is rather simple:

OracleMariaDB
CREATE OR REPLACE PROCEDURE yannick_execute_sql_with_sql_injection(param1 IN VARCHAR2)
IS
  found_rows_var NUMBER;
  v_sql VARCHAR2(100);
  TYPE empcurtyp IS REF CURSOR;
  cursor01 empcurtyp;
  item01 users%ROWTYPE;
BEGIN
  v_sql := 'select * from users where id=' || param1;
  OPEN cursor01 FOR v_sql;
  LOOP
    FETCH cursor01 INTO item01;
    EXIT WHEN cursor01%notfound;
    dbms_output.put_line('id = ' ||item01.id || ', name = ' || item01.name);
  END LOOP;
  dbms_output.put_line('Found rows = ' || cursor01%rowcount);
  CLOSE cursor01;
END;
Error: not working at all!

References

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *