Nodejs ODBC Connection on Windows

Question

I have a project that would be perfect for Node.js, but it has to connect to a ODBC database and it has to run on windows. I see ODBC modules for nodejs on linux, but not windows. Does anyone have any suggestions on how to do this?

Answer 1

If you're like me and arrived here from Google, because you have old (ie ancient) systems, I came across Is it possible to marry WSH (wscript) with nodejs and was alerted to the npm module "win32ole": https://www.npmjs.com/package/win32ole.

While not just an ODBC solution, "win32ole" does give you the ability to do quite a lot on a windblows box, just like the old WSH did.

var win32ole = require('win32ole');
. . .
// Create an ADODB.Connection Object
dbcon = new ActiveXObject('ADODB.Connection');

If (like me), you're after an ODBC connection because you're connecting to an access DB, then there's even a sample script to connect using Jet directly:

https://github.com/idobatter/node-win32ole/blob/master/examples/access_mdb_sample.js

Edit: It does require a node-gyp, a module that requires building code to native...

Answer 2

The state of database drivers for node.js on Windows seems somewhat immature compared to the robust and highly performant database drivers we have had available in ADO.NET for years.

I would seriously consider using Edge to call C# or a CLR assembly in-process to access your database. You could write a Repository style data access layer in C# and call it from node.js.

I have proven this works in a development context with C#, PetaPoco (optional), .NET 4.5 and the Oracle ODP driver (Oracle.DataAccess.dll), and with ADO.NET + SQL Server. This should work with any database that you can talk to in .NET.

Node (server.js) example to call .NET CLR function:

var edge = require('edge');

// define CLR function proxy
var getData = edge.func({
    assemblyFile: '../Repositories/bin/Debug/Repositories.dll',
    typeName: 'Repositories.TestRepository',
    methodName: 'GetData' // This must be Func<object,Task<object>>
});

// call proxy function
getData({ myParam:1 }, function (error, result) {
    if (error) throw error;
    console.log(result);
});

GetData C# looks like this (note you need to put your connection string in node.exe.config in the folder that contains node.exe):

public async Task<object> GetData(object param)
    {
        using (var db = new Database("NameOfConnString"))
        {
            return db.Fetch<dynamic>("SELECT * FROM sometable");
        }
    }

Alternately, if using SQL Server, you can use edge-sql.

Node example (server.js) using edge-sql (note you need to put your connection string into an environment variable as per edge-sql docs):

var edge = require('edge');

// edge-sql has built in support for T-SQL / MSSQL Server
var getData = edge.func('sql', function () {/*
    select top 10 * from sometable
*/
});

getData(null, function (error, result) {
    if (error) throw error;
    console.log(result);
});

Answer 3

The node-odbc sais it is unixODBC for Linux, but it works in Windows too, I am using it here. It just does not include the binaries, so you need to install windows-build-tools and node-gyp before installing node-odbc, in order to build the binaries.

npm i -g windows-build-tools
npm i -g node-gyp
npm i odbc

Answer 4

I am starting node.js , been sick of csript.exe is the main reason. It's damn powerfull, and very impressive, took me 1h to set up a whole grid fully functionnal . Well I am not here to promote node.js , I am noob on it. I find out however something very usefull... http://syskall.com/how-to-write-your-own-native-nodejs-extension/

So rather than redoing what C++ will always do better, I use both node.js and C++ for maximum efficiencies that way.

Answer 5

NodeJS has some potential but it is still a toy. The idea that a frontend programmer and backend programmer are interchangeable is laughable. But that is off topic...

Microsoft released their own driver(s). I have not tried them so I have no idea how good they are. I imagine that they must be somewhat good as I have seen multiple job postings for NodeJS programmers in the last 8 months. (also not responsive).