Accessing DuckDB using node.js with 2 functions independently

[yoonghm]

I have posted a question in https://stackoverflow.com/questions/77475071/sequential-run-of-sql-functions-in-javascript/77475150#77475150

Here is the code that to be run from node.js:

// data3.js
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();
}

Running the code with node data.js would give the following error:

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

It could be overcome at the moment if I use the same database connection among the two function.

//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(db) {
  console.log('createAndStoreData()');

  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();
          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(db) {
  console.log('readData()');

  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();
        }
      });
    });
  });
}

async function main() {
  try {
    const db = new sql.Database('power_curve.db');
    await createAndStoreData(db);
    await readData(db);
    db.close((err) => {
      if (err) {
        console.error('Error closing database:', err.message);
      }
    });
  } catch (error) {
    console.error('Error:', error.message);
  }
}

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