Sequential run of SQL functions in JavaScript

Question

It seems that the question has been answered here, however, I do not know how to make it work as I am still new to JavaScript.

I have tried to use Node.js with DuckDB and Sqlite3. Both could not not work as expected. Here is my code: data2.js:

// data2.js
const fs = require('fs');
// const sql = require('sqlite3');
const sql = require('duckdb');
const csvParser = require('csv-parser');
const stream = require('stream');


// CSV data as a string
const csvData = `Wind speed (m/s),Output power (kW)
0,0
1,0
2,0
3,0
4,80
5,140
6,360
7,610
8,1000
9,1470
10,1900
11,2320
12,2690
13,2850
14,2950
15,3000
16,3000
17,3000
18,3000
19,3000
20,3000
21,3000
22,3000
23,3000
24,3000
25,3000`;


// Function to create a database, table, and insert data
function createAndStoreData() {
  console.log('createAndStoreData()');
  const db = new sql.Database('power_curve.db');

  db.serialize(() => {
    db.run('DROP TABLE IF EXISTS power_curve');
    db.run('CREATE TABLE IF NOT EXISTS power_curve (ws DECIMAL PRIMARY KEY, power DECIMAL)');

    const stmt = db.prepare('INSERT INTO power_curve VALUES (?, ?)');

    const dataStream = new stream.Readable();
    dataStream.push(csvData);
    dataStream.push(null);

    dataStream
      .pipe(csvParser())
      .on('data', (row) => {
        stmt.run(row['Wind speed (m/s)'], row['Output power (kW)']);
      })
      .on('end', () => {
        stmt.finalize();
        db.close((err) => {
          if (err) {
            return console.error('Error closing database:', err.message);
          }
          console.log('Data stored in power_curve.db successfully.');
        });
      })
      .on('error', (error) => {
        console.error('Error reading CSV data:', error.message);
      });
  });
}


// Function to read data from the database
function readData() {
  console.log('readData()');
  const db = new sql.Database('power_curve.db');

  db.serialize(() => {
    db.all('SELECT * FROM power_curve', (err, rows) => {
      if (err) {
        console.error('Error querying data:', err.message);
      } else {
        console.table(rows);
        console.log('Data read from power_curve.db successfully.');
      }

      db.close((err) => {
        if (err) {
          console.error('Error closing database:', err.message);
        }
      });
    });
  });
}


if (require.main === module) {
  createAndStoreData();
  readData();
}

Running the code for the first time would give the following messages:

node data2.js

createAndStoreData()
readData()
Error querying data: Connection Error: Connection was never established or has been closed already
Error closing database: Database was already closed
Data stored in power_curve.db successfully.

Running it the second time, I would get the following messages:

node data2.js

createAndStoreData()
readData()
Error closing database: Database was already closed
┌─────────┬────┬───────┐
│ (index) │ ws │ power │
├─────────┼────┼───────┤
│    0    │ 0  │   0   │
│    1    │ 1  │   0   │
│    2    │ 2  │   0   │
│    3    │ 3  │   0   │
│    4    │ 4  │  80   │
│    5    │ 5  │  140  │
│    6    │ 6  │  360  │
│    7    │ 7  │  610  │
│    8    │ 8  │ 1000  │
│    9    │ 9  │ 1470  │
│   10    │ 10 │ 1900  │
│   11    │ 11 │ 2320  │
│   12    │ 12 │ 2690  │
│   13    │ 13 │ 2850  │
│   14    │ 14 │ 2950  │
│   15    │ 15 │ 3000  │
│   16    │ 16 │ 3000  │
│   17    │ 17 │ 3000  │
│   18    │ 18 │ 3000  │
│   19    │ 19 │ 3000  │
│   20    │ 20 │ 3000  │
│   21    │ 21 │ 3000  │
│   22    │ 22 │ 3000  │
│   23    │ 23 │ 3000  │
│   24    │ 24 │ 3000  │
│   25    │ 25 │ 3000  │
└─────────┴────┴───────┘
Data read from power_curve.db successfully.

Answer 1

I managed to fix it myself for sqlite3 using async and await. However, it seems that duckdb has different behaviour.

const sql = require('sqlite3');
//const sql = require('duckdb');
const csvParser = require('csv-parser');
const stream = require('stream');

// CSV data as a string
const csvData = `Wind speed (m/s),Output power (kW)
0,0
1,0
2,0
3,0
4,80
5,140
6,360
7,610
8,1000
9,1470
10,1900
11,2320
12,2690
13,2850
14,2950
15,3000
16,3000
17,3000
18,3000
19,3000
20,3000
21,3000
22,3000
23,3000
24,3000
25,3000`;

// Function to create a database, table, and insert data
async function createAndStoreData() {
  console.log('createAndStoreData()');
  const db = new sql.Database('power_curve.db');

  return new Promise((resolve, reject) => {
    db.serialize(() => {
      db.run('DROP TABLE IF EXISTS power_curve');
      db.run('CREATE TABLE IF NOT EXISTS power_curve (ws DECIMAL PRIMARY KEY, power DECIMAL)');

      const stmt = db.prepare('INSERT INTO power_curve VALUES (?, ?)');

      const dataStream = new stream.Readable();
      dataStream.push(csvData);
      dataStream.push(null);

      dataStream
        .pipe(csvParser())
        .on('data', (row) => {
          stmt.run(row['Wind speed (m/s)'], row['Output power (kW)']);
        })
        .on('end', () => {
          stmt.finalize();
          db.close((err) => {
            if (err) {
              reject(err);
            } else {
              console.log('Data stored in power_curve.db successfully.');
              resolve();
            }
          });
        })
        .on('error', (error) => {
          reject(error);
        });
    });
  });
}

// Function to read data from the database
async function readData() {
  console.log('readData()');
  const db = new sql.Database('power_curve.db');

  return new Promise((resolve, reject) => {
    db.serialize(() => {
      db.all('SELECT * FROM power_curve', (err, rows) => {
        if (err) {
          reject(err);
        } else {
          console.table(rows);
          console.log('Data read from power_curve.db successfully.');
          resolve();
        }

        db.close((err) => {
          if (err) {
            reject(err);
          }
        });
      });
    });
  });
}

async function main() {
  try {
    await createAndStoreData();
    await readData();
  } catch (error) {
    console.error('Error:', error.message);
  }
}

if (require.main === module) {
  main();
}

I have submitted a discussion topic in https://github.com/duckdb/duckdb-wasm/discussions/1484, if you are keen to follow up.