Skip to main content

Access Teiid from node.js

Are you writing a "node.js" application and would like to access Teiid VDB from it?

If "yes", this is currently possible using NPM package "pg".  Since, Teiid supports the PG transport, you can use this PostgreSQL client for "node.js" for accessing the Teiid.

For example if you have VDB called "northwind" deployed on your Teiid server, and it has table called "customers" and you are using default configuration such as

user = 'user'
password = 'user'
host = 127.0.0.1
port = 35432

then you can use following to write simple access program to Teiid
    
    var pg = require('pg');
    var connectionString = "pg://user:user@localhost:35432/northwind"
    pg.connect(connectionString, function(err, client) {
        client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers', function(err, result) {
        console.log(result.rows)
    });
    });
If you want access one row at a time, you can also use event mechanism and write
    var pg = require('pg');
    var connectionString = "pg://user:user@localhost:35432/northwind"
    pg.connect(connectionString, function(err, client) {
        var query = client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers');
        query.on('row', function(row) {
            console.log(row);
        });
        query.on('error', function(error) {
            console.log("failed to query");
        });
        query.on('end', function(error) {
            console.log("closing client");
            client.end();
        });
    });
For more information please take look at

https://npmjs.org/package/pg
https://github.com/brianc/node-postgres

If you do write an application using these please do share your experiences with us.

Comments

  1. Which version of TEIID support this feature?
    With TEIID 8.2 there are problems.
    Any hint?
    Thanks
    Luca

    ReplyDelete
    Replies
    1. I tested with 8.3, but it should work with 8.2 as I did not find any fixes that related to postgres port in 8.3 cycle. what errors are you seeing?

      Delete
  2. well I'm seeing nothing it seems that the node application does not issue any request.
    Are you testing it just local or also on different machines?
    I have TEIID on host1 and node on host2 and nothing ...

    ReplyDelete
  3. Luca,

    I only tested locally, if there is any trick you had to make it work, please do explain here for others.

    Thanks

    Ramesh..

    ReplyDelete
  4. Hello all,

    Has anyone tried using a parameterized query with node pg and JBoss? Sending a text query works fine just as you explained (client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers').

    However, if we try:
    client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers WHERE CustomerID = $1', [100])

    Then, the JBoss connection is closed. We can confirm that rows of data are returned, however, the node pg module seems to detect that stream has ended, and thus it throws the error "Error: Connection terminated unexpectedly".

    Has anyone else experienced this?

    ReplyDelete
  5. Hi Unknown,

    Could you turn this question into a JIRA issue? Can you provide information such as the Teiid version and the pg package version?

    ReplyDelete
    Replies
    1. Hi Steven!

      Thank you so much for the fast response! We will go ahead and turn the question into a JIRA issue!

      In the meantime, would you happen to have an example of using a parameterized query with the node pg module and Teiid? Are you aware if other people have successfully done this?

      Thank you so much, Steven!

      Brian

      Delete
    2. Brian,

      Thanks for logging https://issues.jboss.org/browse/TEIID-5014 it will be addressed in subsequent releases.

      Delete
  6. Hi man! Good article! I have one question, its possible connect to Teiid with PHP.. im use PHP a lot.... indeed, i use Codeigniter, you thinks that the connection its posible?
    Thanks! :D

    ReplyDelete
  7. PHP should be possible using their pg connectivity. We have not explicitly tested that path though. If you run into issues, please use the forums or JIRA - https://issues.jboss.org/projects/TEIID

    ReplyDelete

Post a Comment

Popular posts from this blog

Teiid Runtimes Explained

If you have been following Teiid lately we have been going through a whole lot of renovations. Yes, renovations or reorganization or refactoring or whatever you want to call it. Basically, we are making Teiid more modular with fewer dependencies that can be used by however your use case dictates rather than use it as one monolith application deployed into WildFly JEE Application Server. There is nothing wrong in using Teiid as server model, but with the proliferation of container-based workloads and cloud-based architectures, the previous server-based model does not work or simply won't scale. So, we needed to think of alternatives, thus Teiid team introduced a couple different versions modular Teiid what we are calling as "Teiid Runtimes".

Note that in these modular Teiid runtimes, not all the features you were used to using in Teiid Server model may not be there but you will have extensions to add in those that are most appropriate for your domain. If you are looking …

Teiid Platform Sizing Guidelines and Limitations

Users/customers always ask us about the sizing of their Data Virtaulization infrastructure based on Teiid or the JDV product from Redhat. Typically this is very involved question and not a very easy one answer in plain terms. This is due to fact that it involves taking into consideration questions like:
What kind of sources that user is working with? Relational, file, CRM, NoSQL etc.How many sources they are trying to integrate? 10, 20, 100?What are the volumes of data they are working with? 10K, 100K, 1M+?What are the query latency times from the sources? How you are using Teiid to implement the data integration/virtualization solution. What kind of queries that user is executing? Even small federated results may take a lot of server side processing - especially if the plan needs tweaking.Is materializing being used?Is query written in optimal way?and so on..Each and every one of the question affects the performance profoundly, and if you got mixture of those then it become that much…