Route returning empty array even though inserting into db seems to be working

Question

I'm learning how to use Sqlite3 with Node, and I'm running into a strange issue. In componentWillMount() on my react front end's main App.js, I make an axios request to the route /all so I can populate a contact list.

What's weird is that, when I hit my other route, /add with a different axios request when I add a contact, it reaches my then() as such,

axios
  .post('/add', contactData)
  .then(res =>
    console.log(`Contact ${contactData.name} added successfully`)
  )
  .catch(err => console.log('Error encountered: ', err));

With a slight delay too, because I setState before making my axios request, which makes me think that the contact is added into the contacts table.

But when I access localhost:5000/all directly, I receive an empty array [] as the response. I'm not sure what's going on.

Here's my server.js

const express = require('express');
const sqlite3 = require('sqlite3');
const path = require('path');
const cors = require('cors');

const dbName = 'my.db';
const tableName = 'Contacts';
const dbPath = path.resolve(__dirname, dbName);

const app = express();

const port = process.env.PORT || 5000;

app.use(cors());

app.listen(port, () => console.log(`Server running on port ${port}`));

app.get('/all', (req, res) => {
  let db = new sqlite3.Database(dbPath);

  let sql = `SELECT number FROM ${tableName}`;

  db.run(
    `CREATE TABLE IF NOT EXISTS ${tableName}(name text, number text, address text)`
  );

  db.all(sql, [], (err, rows) => {
    if (err) {
      return res.status(500).json(err);
    } else {
      return res.json(rows);
    }
  });
});

app.post('/add', (req, res) => {
  let db = new sqlite3.Database(dbPath);

  db.run(
    `INSERT INTO ${tableName}(name, number, address) VALUES(${req.name},${
      req.number
    },${req.address})`,
    [],
    err => {
      if (err) return res.status(500).json(err);
    }
  );

  return res.json({ msg: 'success' });
});

---

Edit:

I should note that when I navigate to /all I get this,

and when I try to post to /add, I get the error

Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client

No where am I sending multiple responses though.

Answer 1

I would not init your db and create your table every time you hit /all.

Try this:

// get this out of the `/all` route, no need to initialize the db object over and over again
let db = new sqlite3.Database(dbPath);

// also leave this outside the `/all` route:
// no need to create the table over and over again.
db.run(
  `CREATE TABLE IF NOT EXISTS ${tableName}(name text, number text, address text)`
);


app.get('/all', (req, res) => {
  let sql = `SELECT number FROM ${tableName}`;

  // according to the sqlite3 api, the second parameter is optional, so just leave it out:
  db.all(sql, (err, rows) => {
    if (err) return res.status(500).json(err); // if you use return,  you don't need 'else' because the code will never reach it.

    res.json(rows)
  });
});

Your /add route also looks a bit off. Try this:

app.post('/add', (req, res) => {
  // let db = new sqlite3.Database(dbPath);  // remove this as you already defined it at the beginning of your code.

  db.run(
    `INSERT INTO ${tableName}(name, number, address) VALUES(${req.name},${req.number},${req.address})`,
    err => {
      if (err) return res.status(500).json(err);
      res.json({ msg: 'success' }); // put the success in the callback (after the query is run!), else, when you get an error, express.js will try to send an error message AND a success message giving you the error "Can't send headers after they are sent"
    }
  );

});