MySQL Server API as ODBC endpoint

[Katorone]

Hi,

I'm intrigued by the idea of using node-mysql2's server API to create a unified ODBC endpoint for data aggregated from various databases, or even REST APIs.

The way I'm imagining going about this, is to add an ORM (like Prisma or Sequelize) and translate incoming requests to their interaction methods. Then aggregate the data and send it back through node-mysql2's server API. (The connection would be read-only)

Would this be at all feasible?

[sidorares]

not sure I fully follow. Do you want to federate various sources ( odbc, rest, other db clients ) and expose as a mysql server connection? Yes, definitely feasible ( but you'd need to add sql parser so that you can route queries somehow to the relevant source )

I have some examples but they are 1:1 mapping mysql -> something else

• osquery https://github.com/sidorares/mysql-osquery-proxy
• postgres https://github.com/sidorares/mysql-pg-proxy
• sqlite.js https://github.com/sidorares/mysqlite.js

[Katorone]

This is exactly what I was looking for. Thank you!
Looking at the source of those examples, mapping the sources will indeed be the hardest part.

ODBC connections are usually pretty static in which information they request. My first thought is to define a kind of virtual table that translates into a query under the hood. These queries could be predefined.

Sorry if I'm confusing by using the wrong wording, I have some experience with databases, but this is the first time I'll be doing more than just CRUD.

[sidorares]

few more pointers where to search for examples ( I searched "mysql.createServer" on github to get some leads )

• mysql gateway that feels like is close to your idea: https://github.com/implydata/plyql/blob/master/src/mysql-gateway.ts
• I believe it uses https://www.npmjs.com/package/plywood for sql parsing ( https://github.com/implydata/plywood/blob/master/src/dialect/baseDialect.ts and https://github.com/implydata/plywood/blob/master/src/dialect/mySqlDialect.ts )

Another example worth exploring - https://github.com/eugeneware/sql-engine/ ( it includes some SQL parsing )

[sidorares]

Example of using sql parser: https://github.com/eugeneware/sql-engine/blob/2c33e9b7ec43c0bcf502caad9d8e6e4bba0ae13a/lib/sql-driver.js#L86

[Katorone]

The parser from sql-engine seems closest to what I had in mind. Except parsing incoming SQL to AST, I'd parse it to the ORM-specific language. Afterwards, transforming the results from the ORM layer back to MySQL-results.

I'm probably underestimating the amount of work and caveats, and the links you've provided will be very useful when I get stuck on a problem.
Thank you for taking the time!