Cant insert data no matter what I do

[hmpmarketing]

Hi,

Just installed questdb and trying to insert some data. I have created the table as such:

CREATE TABLE 'chartfeed' ( timestamp TIMESTAMP, name SYMBOL capacity 256 CACHE, quoteid INT, marketid INT, open INT, high INT, low INT, close INT, ) timestamp (timestamp);

And below is the code Im using to insert:

` const { Sender } = require('@questdb/nodejs-client');

async function run() { // create a sender using HTTP protocol const sender = new Sender({ protocol: 'tcp', host: 'localhost', port: 9009, bufferSize: 4096 }); await sender.connect();

// add rows to the buffer of the sender
await sender.table('chartfeed')
    .symbol('name', 'TEST')
    .intColumn('quoteid', 1234)
    .intColumn('marketid', 5678)
    .intColumn('open', 33450)
    .intColumn('high', 33480)
    .intColumn('low', 33410)
    .intColumn('close', 33460)
    .timestampColumn('timestamp',1715809721)

// flush the buffer of the sender, sending the data to QuestDB
// the buffer is cleared after the data is sent, and the sender is ready to accept new data
await sender.flush();

// close the connection after all rows ingested
await sender.close();

}

run().then(function(){ console.log('good') }).catch(function(err){ console.error('error',err) }); `

Console log prints:

Successfully connected to localhost:9009 Connection to 127.0.0.1:9009 is closed good

But however no data is inserted in the table when I inspect with the web console.

Cant see what Im doing wrong?

[glasstiger]

Please, use at() or atNow() with the designated timestamp column. If you just use timestampColumn(), the row is not getting closed, and never gets sent to the server.

The code should look like this:

await sender.table('chartfeed')
    .symbol('name', 'TEST')
    .intColumn('quoteid', 1234)
    .intColumn('marketid', 5678)
    .intColumn('open', 33450)
    .intColumn('high', 33480)
    .intColumn('low', 33410)
    .intColumn('close', 33460)
    .at(1715809721, 'ms');

Hope this helps.

[glasstiger]

The table name goes first (table()), then symbol columns (symbol()), then all other columns apart from the designated timestamp, and finally at the end the designated timestamp column (at() or atNow()). This corresponds to the underlying wire protocol used by the client, InfluxDB Line Protocol (ILP).

Examples in the documentation: https://questdb.github.io/nodejs-questdb-client/

[hmpmarketing]

Thank you so much @glasstiger ! It now works!

Im only having an issue now inserting in a for loop, it only inserts one record :-(

I have trying putting await sender.flush() outside of the loop and yet it still inserts one record

` for(var d of data.data.data){

    var dataArr = d.split(",")
    console.log(dataArr)

    var open = parseInt(dataArr[1])
    var high = parseInt(dataArr[2])
    var low = parseInt(dataArr[3])
    var close = parseInt(dataArr[4])

    var timestamp = moment(dataArr[0],moment.ISO_8601).valueOf()

    // add rows to the buffer of the sender
    await sender.table('chartfeed')
        .symbol('name', 'TEST')
        .intColumn('quoteid', 1234)
        .intColumn('marketid', 5678)
        .intColumn('open', open)
        .intColumn('high', high)
        .intColumn('low', low)
        .intColumn('close', close)
        .at(timestamp, 'ms');

    await sender.flush();

}

await sender.close();`

[glasstiger]

Assuming console.log(dataArr) prints the data you want to ingest, I do not see any problem with this code. Have you checked QuestDB logs? Do you see any errors?

[glasstiger]

You can also try to switch to HTTP protocol instead of TCP. The HTTP endpoint provides feedback to the client, the TCP does not, meaning if you use TCP, errors not propagated back to the client from the server.

To switch to HTTP replace this:

const sender = new Sender({ protocol: 'tcp', host: 'localhost', port: 9009, bufferSize: 4096 });

with one of the below lines:

const sender = new Sender({ protocol: 'http', host: 'localhost', port: 9000, bufferSize: 4096 });

or

const sender = Sender.fromConfig('http::addr=localhost:9000;init_buf_size=4096');

[hmpmarketing]

HI @glasstiger thank you so much for your feedback, I have managed to make it work.

I think the reason was, data.data.data returned from the API (where Im getting the data from) was ordered so that the dates were ordered from new to old. I applied .reverse() to the array and the insertion worked. I have no idea why!

What I am trying to find out now is, I want to insert and update only if the timestamp already exists. The plan is to run a job every second that pulls the data and updates the open, high low and close (OHLC). The way the script is now, it is creating new records with the same timestamp however the OHLC values are different:

I cant find docs that specify how to insert / update ? If I run a SELECT query with "SAMPLE BY 5m" I get dupe values:

[glasstiger]

I think this is what you need:

ALTER TABLE chartfeed DEDUP ENABLE UPSERT KEYS(timestamp, name);

Or rather:

DROP TABLE chartfeed;
CREATE TABLE chartfeed (
    timestamp TIMESTAMP,
    name SYMBOL,
    open DOUBLE,
    high DOUBLE,
    low DOUBLE,
    close DOUBLE
) TIMESTAMP(timestamp) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(timestamp, name);

I suggest dropping, then re-creating the table and reinsert the data. That is because enabling deduplication does not fix the data already present in the table.

It is also worth to give this one a read: https://questdb.io/docs/concept/deduplication/

[hmpmarketing]

Thank you again @glasstiger !

However after dropping and re-creating the table, the insertion is returning this error:

Inserting using the old table structure inserts the data just fine:

FYI here is the full script

[glasstiger]

Please, check for errors on server side. Since you use TCP protocol, the server cannot propagate errors back to the client, so simply disconnects it when a problem occurs. This is how communicates it to the client that there was a problem. Any reason not to use HTTP?

[hmpmarketing]

Hi @glasstiger , changing to

const sender = Sender.fromConfig('http::addr=localhost:9000;init_buf_size=4096');

now inserts everything correctly. There are no dupes.

I really would like to thank you going through this! I spent the night installing and playing around with questdb and Im loving it

FYI SAMPLE BY works like a charm!

Thank you again @glasstiger !

Now onto how to query SELECT using the NodeJS client, no docs :-)

Nevermind, found here https://questdb.io/docs/reference/sql/overview/#rest-http-api

Thank you!

[glasstiger]

Glad to hear it works!

I will close the issue now. Feel free to open a new one, if you hit any issues.