7 ms·

Transpile Any SQL to PostgreSQL Dialect

189 points by fljdin 3y ago

fljdin 3y ago
A CLI tool to transpile SQL snippet from a dialect to another using an AST
wendyshu 3y ago
How is it different from sqlglot?
souenzzo 3y ago
https://www.jooq.org/translate/ https://www.jooq.org/translate/ JOOQ also do that, but it is in JAVA.
- fljdin 3y 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 3y 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 3y ago
  From the "Roadmap" section of the README: > more source dialects : mysql, mssql, sybase, etc. https://gitlab.com/dalibo/transqlate#roadmap https://gitlab.com/dalibo/transqlate#roadmap
- staticlibs 3y 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/ https://babelfishpg.org/ [2] https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/47#issuecomment-994107040 https://github.com/babelfish-for-postgresql/babelfish_extens...
pentaphobe 3y ago
`transqlate` is such a good name - I am very jealous, well done!
deleted 3y ago
[deleted]
cipherself 3y 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.
- neeleshs 3y 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 3y 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 https://github.com/turbot/steampipe-plugin-net
    - neeleshs 3y ago
      That's a good idea and we considered FDW (for this and more stuff), but having a middleware makes it more flexible - FDW has limitations around pushdown with subselects, we are still constrained to a single postgresql instance for execution, when in theory we could parallelize (certain) queries across nodes.
  - cipherself 3y ago
    I think this is probably the missing piece for you? https://github.com/jwills/buenavista https://github.com/jwills/buenavista
    - 3y ago
pmarreck 3y ago
This is interesting. Is there any standardized AST for SQL?
- jerf 3y 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 3y ago
This in combination with [pg_query](https://github.com/pganalyze/libpg_query https://github.com/pganalyze/libpg_query) could allow for writing generic static analyzers.
liotier 3y ago
I hope for the day such tool will convert Oracle PL/SQL to Postgres
- fljdin 3y 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 https://gitlab.com/dalibo/transqlate/-/issues/20
teslatom 3y 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 3y ago
Interesting
LeftHandPath 3y 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 3y ago
  I think Apache Calcite can do this, or something similar. https://calcite.apache.org/ https://calcite.apache.org/
jakozaur 3y 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
- fljdin 3y ago
  Thank you for your feedback, both issues have been added to the project tracking.
- zeroxfe 3y 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 3y 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?
throwitaway222 3y ago
[flagged]
- ramraj07 3y ago
  No one trusts these LLMs enough to plug sql that comes out of it directly into a compiler.
peter_d_sherman 3y ago
Related: PRQL (higher-level abstraction language for SQL) https://prql-lang.org/ https://prql-lang.org/ HN Article, PRQL: https://news.ycombinator.com/item?id=36866861 https://news.ycombinator.com/item?id=36866861 Google search, site: GitHub, "SQL Transpiler": https://www.google.com/search?q=site%3Agithub.com+%22SQL+Transpiler%22 https://www.google.com/search?q=site%3Agithub.com+%22SQL+Tra... GitHub general list of open-source Transpilers: https://github.com/topics/transpiler https://github.com/topics/transpiler
fuzzieozzie 3y ago
Many tools try to achieve similar eg. https://aws.amazon.com/rds/aurora/babelfish/ 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-compilerworks--b0adc10c https://www.crunchbase.com/acquisition/google-acquires-compi...
chaps 3y ago
Nice. Anyone know if similar exists going the opposite direction? Specifically thinking of postgresql -> oracle sql.
r1b 3y ago
Recommend checking out https://github.com/tobymao/sqlglot 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 3y ago
  And column level lineage!
pjmlp 3y ago
Given how many SQL features are RDMS specific, I doubt the claim of being able to convert any SQL into PostgreSQL dialect.

189 points by fljdin 3y ago

fljdin 3y ago

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

wendyshu 3y ago

How is it different from sqlglot?

souenzzo 3y ago

https://www.jooq.org/translate/ https://www.jooq.org/translate/ JOOQ also do that, but it is in JAVA.

fljdin 3y 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 3y 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 3y ago
From the "Roadmap" section of the README: > more source dialects : mysql, mssql, sybase, etc. https://gitlab.com/dalibo/transqlate#roadmap https://gitlab.com/dalibo/transqlate#roadmap
staticlibs 3y 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/ https://babelfishpg.org/ [2] https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/47#issuecomment-994107040 https://github.com/babelfish-for-postgresql/babelfish_extens...

pentaphobe 3y ago

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

deleted 3y ago

[deleted]

cipherself 3y 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.

neeleshs 3y 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 3y 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 https://github.com/turbot/steampipe-plugin-net
  - neeleshs 3y ago
    That's a good idea and we considered FDW (for this and more stuff), but having a middleware makes it more flexible - FDW has limitations around pushdown with subselects, we are still constrained to a single postgresql instance for execution, when in theory we could parallelize (certain) queries across nodes.
- cipherself 3y ago
  I think this is probably the missing piece for you? https://github.com/jwills/buenavista https://github.com/jwills/buenavista
  - 3y ago

pmarreck 3y ago

This is interesting. Is there any standardized AST for SQL?

jerf 3y 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 3y ago

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

liotier 3y ago

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

fljdin 3y 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 https://gitlab.com/dalibo/transqlate/-/issues/20

teslatom 3y 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 3y ago

Interesting

LeftHandPath 3y 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 3y ago
I think Apache Calcite can do this, or something similar. https://calcite.apache.org/ https://calcite.apache.org/

jakozaur 3y 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

fljdin 3y ago
Thank you for your feedback, both issues have been added to the project tracking.
zeroxfe 3y 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 3y 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?

throwitaway222 3y ago

[flagged]

ramraj07 3y ago
No one trusts these LLMs enough to plug sql that comes out of it directly into a compiler.

peter_d_sherman 3y ago

Related: PRQL (higher-level abstraction language for SQL) https://prql-lang.org/ https://prql-lang.org/ HN Article, PRQL: https://news.ycombinator.com/item?id=36866861 https://news.ycombinator.com/item?id=36866861 Google search, site: GitHub, "SQL Transpiler": https://www.google.com/search?q=site%3Agithub.com+%22SQL+Transpiler%22 https://www.google.com/search?q=site%3Agithub.com+%22SQL+Tra... GitHub general list of open-source Transpilers: https://github.com/topics/transpiler https://github.com/topics/transpiler

fuzzieozzie 3y ago

Many tools try to achieve similar eg. https://aws.amazon.com/rds/aurora/babelfish/ 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-compilerworks--b0adc10c https://www.crunchbase.com/acquisition/google-acquires-compi...

chaps 3y ago

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

r1b 3y ago

Recommend checking out https://github.com/tobymao/sqlglot 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 3y ago
And column level lineage!

pjmlp 3y ago

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