Access to foreign data via PostgreSql + FDW

[khalidalkassoum]

Hello,

I've successfully used this library with PostgreSql connected to Oracle through a foreign data wrapper.

Only 4 lines were added to generator/internal/metadata/schema_meta_data.go, as follows:

const (
     baseTable    = "BASE TABLE"
+     foreignTable = "FOREIGN" 
     view         = "VIEW"
)
// GetSchemaMetaData returns schema information from db connection.
 func GetSchemaMetaData(db *sql.DB, schemaName string, querySet DialectQuerySet) (schemaInfo SchemaMetaData) {
        schemaInfo.TablesMetaData = getTablesMetaData(db, querySet, schemaName, baseTable)
 +      for _, s := range getTablesMetaData(db, querySet, schemaName, foreignTable) {
 +              schemaInfo.TablesMetaData = append(schemaInfo.TablesMetaData, s)
 +      }
        schemaInfo.ViewsMetaData = getTablesMetaData(db, querySet, schemaName, view)
        schemaInfo.EnumsMetaData = querySet.GetEnumsMetaData(db, schemaName)

After these modifications, all pre-existing tests were successful (even those specific to MySql/Mariadb).

I have not written any tests for FOREIGN tables since once generated in the Go project, they are no different from 'normal' tables.

Since FDWs are standardized, this (potentially) opens the door to a large number of databases through PostgreSql: https://wiki.postgresql.org/wiki/Foreign_data_wrappers

I have not made a PR because I don't know if this approach would be useful for you, but since the modification is very slight it could be made directly by you in the source code if you find it useful.

go-jet/jet: v2.1.0 Postgresql: 11.5 https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_2_1_0 Oracle 11.2 Ubuntu 16.04

[tredoe]

That library does not use a known license for free/open source code (https://github.com/laurenz/oracle_fdw/blob/master/LICENSE);

So I hope that the Jet's author be careful at adding external libraries into a project that is fully free/open source.

[go-jet]

@khalidalkassoum Thanks for sharing. Interesting library.

The idea is to have as little as possible third party dependencies, and add dependency only when there is no other option. This why jet does not depends from the bugs and missing functionalities from the 3rd party libraries.

Eventually most of the SQL databases will be supported, oracle among them as well.

[khalidalkassoum]

Thank you for your feedback.

I'm not suggesting the addition of any new library to this project.

I was sharing an approach to enhance Jet so that we can access "foreign data" through PostgreSQL. And this by simply adding 4 lines of code to a function.

Here is the problem: when using Jet, the model/table generation process only takes into account tables of type "BASE TABLE" because the query on information_schema is as follows:

SELECT table_name FROM information_schema.tables 
where table_schema = $1 and table_type = $2;  
-- Where $2 only receives the value 'BASE TABLE'.

So when the PostgreSql server contains foreign tables, they are ignored.

To correct this, the query to information_schema should also receive the value 'FOREIGN' and append all tables in schemaInfo.TablesMetaData

Foreign Data Wrapper's are an important feature of PostgreSQL server. If supporting them in Jet only requires 4 lines of code, it seems the good way to go. Thank you.

[khalidalkassoum]

@tredoe

https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_2_1_0 CAN NOT be added to this project even if we try, this a server side extension only intended to be used inside PostgreSQL. It's an implementation of FDW as specified here https://wiki.postgresql.org/wiki/SQL/MED (Management of External Data).

[go-jet]

@khalidalkassoum Thanks for the explanation. I'll definitely take a closer look at FDW.

[go-jet]

FTW looks like interesting feature. The only drawback I see is that programmer will have to define all foreign tables in PostgreSQL, before it can call jet generator. Which isn't a small work to do. Also communication wouldn't go directly to remote database but it would go through PostgreSQL. This might cause performance issues.

But it is useful feature and it will be eventually added to jet. At this moment there are more priority things.
I've created branch ftw that enables foreign tables as you suggested, so it can be used If someone finds it useful. When I have enough time to thoroughly investigate it and test it, it will be added as part of official release.

[khalidalkassoum]

I may have raised the issue in a misleading way when I mentioned Oracle. But FDW are not only related to Oracle but to many database systems being accessed through PostgreSQL. Foreign data wrappers are defined by SQL (this extension to the SQL standard is defined by ISO/IEC 9075-9:2008) In instance, FDW are also implemented in MariaDB (Connect protocol)

Your comments are relevant if the use case is to directly connect to Oracle (or any other RDBMS) as easy as Jet connects to PostgreSQL or MySql/MariaDB . New adapters are needed for that.

The use case I have in mind is actually the other way around.

In production we have some legacy databases running on Oracle (with some legacy client/server applications connected to Oracle). New databases are running on PostgreSQL. All new applications target PostgreSQL. FDW are used in PostgreSQL as a mean to get access to all data (One database to rule them all). In this integration pattern we can access the data stored in other RDBMs as if it was stored inside PostgreSQL.

In this use case the foreign data wrappers are already present in PostgreSQL.

Your comments are also relevant regarding the performance issue. But I can say from experience that the FDWs are way faster then the old 'DB links'. Queries are actually pushed through the wrappers to the remote database and all is about how you tweak your queries and set your indexes.

I agree with you, this feature needs to be useful for others. This is why this post is detailed (My personal experience being that Jet can easly access foreign data when FDW are already in place).

Our primary development platform is the JVM with Java/Kotlin, we haven't yet made the choice to go with Go but if we do Jet, is certainly the first library we will use.

Thank you very much for the great work you've done. It is not easy to make a Go library with SQL type safe queries. IMHO you have designed it right.

[gedw99]

wow this is really useful aspects.

I have the same issues where old oracles DBs are there ( legacy that no one wants to touch of course ) and new stuff is done in Postgresql.

I have seen this pattern on many projects actually.

SO i just want to say that this pass through aspects is really useful !!!

thank you for persisting with this @khalidalkassoum

I would be happy to try and help....

[gedw99]

@tredoe

https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_2_1_0 CAN NOT be added to this project even if we try, this a server side extension only intended to be used inside PostgreSQL. It's an implementation of FDW as specified here https://wiki.postgresql.org/wiki/SQL/MED (Management of External Data).

I am guessing it should be fine because this code is installed in postgresql as an extension. It's got nothing to do with jet basically.