5 ms·

Open-Source Babelfish for Postgres

170 points by ke4qqq 5y ago

bobnamob 5y ago
The posted title seems a little editorialised. Is the (finally) necessary?
- dang 5y ago
  We've made the title be (a subtstring of) the article title now. Submitted title was "AWS (finally) drops code for Babelfish – the SQL Server to Postgres translator".
ahachete 5y ago
This is fantastic news. I already blogged about it when it was announced on December last year [0]. Adding SQL Server compatibility opens Postgres to new users, use cases, markets. It makes one of the most general-purpose databases to reach even more "purposes". I hope work and discussions to integrate this upstream will start soon and will be fruitful. It won't be easy, but I believe it would be definitely worth the effort. [0]: https://www.ongres.com/blog/aws_announces_open_source_postgres_with_sql_server_compatibility/ https://www.ongres.com/blog/aws_announces_open_source_postgr...
Mortiffer 5y ago
Very cool. Will see if we can reuse some of these super complicated views we have in our backlog with SQL server apply
XorNot 5y ago
Well this is a big deal for me: my father has run his business off a sprawling MS Access application which I've struggled at various times to try and move to something more modern with no success. I wonder if this will work for connecting to MS Access directly.
- snerbles 5y ago
  If the MS Access application is backed by an actual MS SQL server, maybe. But if (and in my experience this is more likely) it uses MS Access itself with MDB files (Microsoft's Jet database engine - imagine SQLite but inferior in every conceivable way), you're probably better off writing a tool in .NET or similar to migrate the data. In a past job supporting an industrial controls firm, I wrote an interactive merge tool for AutoCAD Electrical parts catalog files (hideous little MDBs behind the scenes)...it was an unholy clusterfuck of C#, Lucene, NodeJS, and a smidgen of VueJS on the frontend. Not my proudest work, but it sure beat the usual tasks involving VBA in FactoryTalk.
deleted 5y ago
[deleted]
data_ders 5y ago
as a user of TSQL in a MSFT/Azure shop, I feel increasingly isolated from other dialects. Virtually all other databases these days use a dialect that's closer to PostGres. Does TSQL have a future? If you don't have legacy dbs and you have a choice of dbs, would TSQL ever make sense to use today?
- kardianos 5y ago
  T-SQL has huge warts, but is one of the better procedural SQL languages I've used. TDS (the wire protocol) also has warts, but it is vastly superior to the pg wire v3 protocol. Lastly, T-SQL is a single, integrated language, which means you can go from basic SQL to procedural SQL in the same batch. T-SQL also has first class support for multiple result sets which is very useful. T-SQL and TDS both support named parameters, where as PG only supports ordinal position. Yes, it has a future.
manishsharan 5y ago
I don't see any enterprise customer using this. MS SQL server is relatively cheap. Now if this thing can work for Oracle or IBM DB2 , then may be it will be worth the risk.
- Conan_Kudo 5y ago
  PostgreSQL already supports Oracle DB dialect and protocol, I thought?
  - setr 5y ago
    postgres takes a lot of design inspiration from Oracle, but AFAIK has never made any real attempt at being strictly compatible with it. You might be thinking of dblink in oracle's world, and foreign data wrapper (fdw) in postgres, which at least lets you transparently interact between the two for just DQL. DDL, procedural logic, etc definitely differs.
    - jabl 5y ago
      There's also EnterpriseDB, a commercial version of PostgreSQL that has added support for the Oracle SQL dialect, including the stored proc language (pl/SQL or whatever it's called). I don't think it speaks the Oracle wire protocol though.
      - eb0la 5y ago
        No, it doesn't speak the Oracle wire protocol. But the Oracle pl/slq (source code compatibility) from Postgres was ported to DB2 and IBM claims 100% compatibility.
- mrweasel 5y ago
  Those customers also know how to manage SQL Server, and the software running on won’t support Postgresql with Babelfish, in the sense that any support contract goes right out the window. I think this is mostly for companies that run Postgresql, but need to bring in a single application which only supports SQL Server. I wonder if you can make SQL Management Studio run against Babelfish.
throwaway81523 5y ago
TLDR: this is about some kind of adapter layer ("Babelfish") that lets you serve Microsoft SQL Server clients from a PostgreSQL backend, sort of like how Samba serves Microsoft SMB clients from a Linux file system. It has nothing to do with natural language translation. Oh well.
deleted 5y ago
[deleted]
pabs3 5y ago
And for the client side there is FreeTDS: https://www.freetds.org/ https://www.freetds.org/
cies 5y ago
This may help PG take serious market share from MSSQL. Also: I wonder if the shiny GUI tools that exist for MSSQL will then work from PG, and ... to what extend these tools will work with regular PG tables. I really like DBeaver[1] (what I use myself), but I've seen stuff done by MSSQL DBAs using expensive GUI tools that have amazed me. [1]: https://dbeaver.io/ https://dbeaver.io/
ThinkBeat 5y ago
It must be compiled from the provided source code if you want to run it. The instruction for doing so is far from simple if you do not have a dev. background. I look forward to using it. T-Sql is still the dialect I know best.
GlennS 5y ago
It's interesting, I wouldn't have guessed the SQL dialect differences to be the main reason to pick one database over another. Other comments on this suggest it's very important to them, which is a surprise to me. Rather, I would have bet on either the performance characteristics for your particular situation, the knowledge of your programmers (or db admins) / recruitment pool, or perhaps integrations with other software. Still, a very useful looking tool, don't want to knock it.
- nicoburns 5y ago
  I think this is for organisations that already have a large system using T-SQL and want to migrate it to postgres.

170 points by ke4qqq 5y ago

bobnamob 5y ago

The posted title seems a little editorialised. Is the (finally) necessary?

dang 5y ago
We've made the title be (a subtstring of) the article title now. Submitted title was "AWS (finally) drops code for Babelfish – the SQL Server to Postgres translator".

ahachete 5y ago

This is fantastic news. I already blogged about it when it was announced on December last year [0]. Adding SQL Server compatibility opens Postgres to new users, use cases, markets. It makes one of the most general-purpose databases to reach even more "purposes". I hope work and discussions to integrate this upstream will start soon and will be fruitful. It won't be easy, but I believe it would be definitely worth the effort. [0]: https://www.ongres.com/blog/aws_announces_open_source_postgres_with_sql_server_compatibility/ https://www.ongres.com/blog/aws_announces_open_source_postgr...

Mortiffer 5y ago

Very cool. Will see if we can reuse some of these super complicated views we have in our backlog with SQL server apply

XorNot 5y ago

Well this is a big deal for me: my father has run his business off a sprawling MS Access application which I've struggled at various times to try and move to something more modern with no success. I wonder if this will work for connecting to MS Access directly.

snerbles 5y ago
If the MS Access application is backed by an actual MS SQL server, maybe. But if (and in my experience this is more likely) it uses MS Access itself with MDB files (Microsoft's Jet database engine - imagine SQLite but inferior in every conceivable way), you're probably better off writing a tool in .NET or similar to migrate the data. In a past job supporting an industrial controls firm, I wrote an interactive merge tool for AutoCAD Electrical parts catalog files (hideous little MDBs behind the scenes)...it was an unholy clusterfuck of C#, Lucene, NodeJS, and a smidgen of VueJS on the frontend. Not my proudest work, but it sure beat the usual tasks involving VBA in FactoryTalk.

deleted 5y ago

[deleted]

data_ders 5y ago

as a user of TSQL in a MSFT/Azure shop, I feel increasingly isolated from other dialects. Virtually all other databases these days use a dialect that's closer to PostGres. Does TSQL have a future? If you don't have legacy dbs and you have a choice of dbs, would TSQL ever make sense to use today?

kardianos 5y ago
T-SQL has huge warts, but is one of the better procedural SQL languages I've used. TDS (the wire protocol) also has warts, but it is vastly superior to the pg wire v3 protocol. Lastly, T-SQL is a single, integrated language, which means you can go from basic SQL to procedural SQL in the same batch. T-SQL also has first class support for multiple result sets which is very useful. T-SQL and TDS both support named parameters, where as PG only supports ordinal position. Yes, it has a future.

manishsharan 5y ago

I don't see any enterprise customer using this. MS SQL server is relatively cheap. Now if this thing can work for Oracle or IBM DB2 , then may be it will be worth the risk.

Conan_Kudo 5y ago
PostgreSQL already supports Oracle DB dialect and protocol, I thought?
- setr 5y ago
  postgres takes a lot of design inspiration from Oracle, but AFAIK has never made any real attempt at being strictly compatible with it. You might be thinking of dblink in oracle's world, and foreign data wrapper (fdw) in postgres, which at least lets you transparently interact between the two for just DQL. DDL, procedural logic, etc definitely differs.
  - jabl 5y ago
    There's also EnterpriseDB, a commercial version of PostgreSQL that has added support for the Oracle SQL dialect, including the stored proc language (pl/SQL or whatever it's called). I don't think it speaks the Oracle wire protocol though.
    - eb0la 5y ago
      No, it doesn't speak the Oracle wire protocol. But the Oracle pl/slq (source code compatibility) from Postgres was ported to DB2 and IBM claims 100% compatibility.
mrweasel 5y ago
Those customers also know how to manage SQL Server, and the software running on won’t support Postgresql with Babelfish, in the sense that any support contract goes right out the window. I think this is mostly for companies that run Postgresql, but need to bring in a single application which only supports SQL Server. I wonder if you can make SQL Management Studio run against Babelfish.

throwaway81523 5y ago

TLDR: this is about some kind of adapter layer ("Babelfish") that lets you serve Microsoft SQL Server clients from a PostgreSQL backend, sort of like how Samba serves Microsoft SMB clients from a Linux file system. It has nothing to do with natural language translation. Oh well.

deleted 5y ago

[deleted]

pabs3 5y ago

And for the client side there is FreeTDS: https://www.freetds.org/ https://www.freetds.org/

cies 5y ago

This may help PG take serious market share from MSSQL. Also: I wonder if the shiny GUI tools that exist for MSSQL will then work from PG, and ... to what extend these tools will work with regular PG tables. I really like DBeaver[1] (what I use myself), but I've seen stuff done by MSSQL DBAs using expensive GUI tools that have amazed me. [1]: https://dbeaver.io/ https://dbeaver.io/

ThinkBeat 5y ago

It must be compiled from the provided source code if you want to run it. The instruction for doing so is far from simple if you do not have a dev. background. I look forward to using it. T-Sql is still the dialect I know best.

GlennS 5y ago

It's interesting, I wouldn't have guessed the SQL dialect differences to be the main reason to pick one database over another. Other comments on this suggest it's very important to them, which is a surprise to me. Rather, I would have bet on either the performance characteristics for your particular situation, the knowledge of your programmers (or db admins) / recruitment pool, or perhaps integrations with other software. Still, a very useful looking tool, don't want to knock it.

nicoburns 5y ago
I think this is for organisations that already have a large system using T-SQL and want to migrate it to postgres.