Transpile Any SQL to PostgreSQL Dialect

https://gitlab.com/dalibo/transqlate

by fljdin • 2 years ago

189 44 2 years ago

gitlab.com

fljdin4• 2 years ago

A CLI tool to transpile SQL snippet from a dialect to another using an AST

wendyshu• 2 years ago

How is it different from sqlglot?

souenzzo• 2 years ago

https://www.jooq.org/translate/

JOOQ also do that, but it is in JAVA.

fljdin4• 2 years ago

The `transqlate` project aims to extend this kind of tool without limitation and under open-source license.

We're studying JOOQ from afar, with the ambition of getting closer to its functional coverage.

ThinkBeat• 2 years ago

I see that the support for Oracle is mentioned and clearly present in the source code, is that also at present the only supported database? Any hope for transactSql (Microsoft Sql. Server)

hans_castorp• 2 years ago

From the "Roadmap" section of the README:

more source dialects : mysql, mssql, sybase, etc.

https://gitlab.com/dalibo/transqlate#roadmap

staticlibs• 2 years ago

Transact-SQL support (both queries and stored procedures) for PostgreSQL is implemented in Babelfish extensions [1]. It is implemented from the ground up as a "pl/tsql" language for Postgres [2], without using SQL-to-SQL transpilation like JOOQ.

[1] https://babelfishpg.org/ [2] https://github.com/babelfish-for-postgresql/babelfish_extens...

pentaphobe• 2 years ago

`transqlate` is such a good name - I am very jealous, well done!

cipherself2• 2 years ago

Interesting, a while back at $EMPLOYER while working in a PoC, I found myself in a situation where I need to take user-provided Postgres SQL queries and run them against one of our APIs. Roughly, I converted the API response to a `pandas dataframe`, I then parsed and transformed the query from the postgres dialect to the duckdb dialect using `sqlglot` and used `duckdb` to query the `pandas dataframe`, coverted it into `json` and returned it to the user.

neeleshs3• 2 years ago

That's interesting. I'm looking to do something similar, but need wire compatibility with postgresql. This allows any postgres client to talk to our service. Didn't have a lot of luck finding a good "middleware"

hantusk• 2 years ago

you could consider hosting an empty postgresql database, compile your code as a postgresql foreign data wrapper and expose it as a view. Nothing is more compatible with the postgres wire protocol than postgresql itself ;)

turbot compiles their steampipe plugins in this way. Example: https://github.com/turbot/steampipe-plugin-net

cipherself2• 2 years ago

I think this is probably the missing piece for you? https://github.com/jwills/buenavista

anitil• 2 years ago

That's a really creative way to solve that problem! I would have spun up a temporary pg instance etc, but this is much nicer.

e12e• 2 years ago

Evidence does something similar - dumping data to parquet:

https://docs.evidence.dev/core-concepts/data-sources

https://news.ycombinator.com/item?id=35645464

I whish the implied ETL step was even clearer from the homepage - it's not really feasible for us to dump entire tables to the dev machines for working with production data - but it is an interesting concept.

pmarreck• 2 years ago

This is interesting.

Is there any standardized AST for SQL?

jerf• 2 years ago

The SQL standard nominally defines one (I don't know if it hands it to you on a silver platter as some sort of grammar but it certainly defines one one way or another), but if you just implement that and then send your code out into the world, you're going to be disappointed with the results. All the database have deviations and extensions of their own and people use them frequently.

amw-zero• 2 years ago

This in combination with [pg_query](https://github.com/pganalyze/libpg_query) could allow for writing generic static analyzers.

liotier• 2 years ago

I hope for the day such tool will convert Oracle PL/SQL to Postgres

fljdin4• 2 years ago

This is one of the major challenges the tool aims to meet! We hope to achieve reliable results this year.

https://gitlab.com/dalibo/transqlate/-/issues/20

teslatom• 2 years ago

I've been looking for a library that can convert query plans, not just ASTs, between Postgres and other query engines. The reason this is useful is because RLS (row level security) settings are injected into the query plan after the AST is constructed, so the same query string can produce different query plans depending on the security settings of the database.

Substrait comes close, but I haven't found a Postgres Substrait producer. Are there any projects working on this?

quantxx• 2 years ago

Interesting

LeftHandPath• 2 years ago

I wondered why this sort of thing didn’t already exist. I had to develop for IBM Db2 (for i Series) on a Linux environment (using MariaDB) due to the licensing that stopped us from having a test env.

I wound up writing a very ad-hoc C++ program that would parse a base SQL file and generate the appropriate DB2 and MariaDB dialect versions. Not flexible enough for reuse but it got the job done.

Symbiote• 2 years ago

I think Apache Calcite can do this, or something similar.

https://calcite.apache.org/

jakozaur• 2 years ago

I see a lot of these types of tools, each with some essential support, but usually they don't work. I would appreciate what is support what is not.

Tried this tool: go run gitlab.com/dalibo/transqlate@v0.1-beta.2

select TRUNC(SYSDATE, 'month') from dual

-- TRANSLATION ERROR at +1:8: unsupported date format: "month" rule="replace trunc() by date_trunc()" select DATE_TRUNC('month', LOCALTIMESTAMP)exit status 1

Also, at least, it would set the latest git tag, currently command from Readme.md doesn't work:

go run gitlab.com/dalibo/transqlate@latest

go: gitlab.com/dalibo/transqlate@latest: module gitlab.com/dalibo/transqlate@latest found (v0.0.0), but does not contain package gitlab.com/dalibo/transqlate

latest should point to v0.1-beta.2

fljdin4• 2 years ago

Thank you for your feedback, both issues have been added to the project tracking.

zeroxfe3• 2 years ago

I asked Google Gemini to do this, and it gave me a seemingly convincing response. Anyone know if it's right?

Prompt:

  translate the SQL statement "select TRUNC(SYSDATE, 'month') from dual" to a PostgreSQL dialect

Response (formatting removed):

  Here is the equivalent SQL statement in PostgreSQL for "select TRUNC(SYSDATE, 'month') from dual" in Oracle:

    SELECT DATE_TRUNC('month', CURRENT_DATE) AT TIME ZONE 'UTC'

  Use code with caution.

  In PostgreSQL:

    DATE_TRUNC is the function used to truncate a date value to a specified unit.

    CURRENT_DATE is a function that returns the current date.

    AT TIME ZONE 'UTC' is used to specify that the truncation should be done in Coordinated Universal Time (UTC). This is optional, but it's a good practice to be explicit about the time zone to avoid any ambiguity.

  The dual table is a dummy table specific to Oracle that is used in simple SQL statements. 

  PostgreSQL doesn't require a table reference in this case.

peter_l_downs• 2 years ago

I view a comment like yours as essentially spam and of negative value. I'm not sure if we (vaguely, hn forum community) have agreed on the etiquette here, but I have to ask why you would bother sharing an AI-generated response if you're not sure if it's correct. What are you getting at? How could this be helpful?

irq-12• 2 years ago

Looks like `AT TIME ZONE 'UTC'` should not be used, but I don't think it's illegal syntax.

https://www.postgresql.org/docs/current/datatype-datetime.ht...

PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities...
Although the date type cannot have an associated time zone, the time type can.

(emphasis mine)

To address these difficulties, we recommend using date/time types that contain both date and time when using time zones.

peter_d_sherman• 2 years ago

PRQL (higher-level abstraction language for SQL)

https://prql-lang.org/

HN Article, PRQL:

https://news.ycombinator.com/item?id=36866861

Google search, site: GitHub, "SQL Transpiler":

https://www.google.com/search?q=site%3Agithub.com+%22SQL+Tra...

GitHub general list of open-source Transpilers:

https://github.com/topics/transpiler

fuzzieozzie• 2 years ago

Many tools try to achieve similar eg. https://aws.amazon.com/rds/aurora/babelfish/

One company, CompilerWorks actually had tools that transpiled many SQL dialects. They were bought by Google https://www.crunchbase.com/acquisition/google-acquires-compi...

chaps• 2 years ago

Nice. Anyone know if similar exists going the opposite direction? Specifically thinking of postgresql -> oracle sql.

r1b• 2 years ago

Recommend checking out https://github.com/tobymao/sqlglot if you are interested in this capability for other SQL dialects

Tools like this are helpful for:

- Rendering SQL in a consistent way, eg for snapshot testing

- Testing SQL business logic in CI against a dialect with less heavyweight dependencies

- Applying AST transformations to take advantage of dialect-specific optimizations

adeelk93• 2 years ago

And column level lineage!

pjmlp• 2 years ago

Given how many SQL features are RDMS specific, I doubt the claim of being able to convert any SQL into PostgreSQL dialect.

Transpile Any SQL to PostgreSQL Dialect

Related Stories