Get all data from different tables in a SQLite database in Node.js

Question

Is it a way to get all data from a database?

So far I accomplished to get all data from a table:

router.get('/', function (req, res, next) {
  db.serialize(function () {
    db.all('SELECT id, name, surname FROM Table1', function (
      err,
      rows
    ) {
      return res.send(rows);
    });
  });

  db.close();
});

but if there are more tables, Table1, Table2 and Table3 for example.

Trying to put all the queries in the db.all call doesn't seem to work.

 router.get('/', function (req, res, next) {
      db.serialize(function () {
        db.all('SELECT id, name, surname FROM Table1, SELECT date, city FROM Table2, SELECT person, year FROM Table3', function (
          err,
          rows
        ) {
          return res.send(rows);
        });
      });
    
      db.close();
    });

UPDATE

these are the tables, how can I get all their data and send it into front-end? that's what I want to do.

I would rather want to have them like separate tables in the front-end where the call is fetched like this:

callAPI() {
    fetch('http://localhost:9000/testAPI')
      .then((res) => res.text())
      .then((res) => this.setState({ apiResponse: res }));
  }

is there a way to send all these tables?

So you can nest multiple db.all calls, putting the next one in the callback function(){} portion of the previous. Your'e doing that correctly if your code keeps indenting to the right. Call res.send only in the last one... That only helps if you have only a few tables and know how the queries in advance. If you want to ask the database for the table list, and then act on it, it gets more complex. Specifically, you need a way to iterate asynchronously. You can do that and avoid nesting callbacks using node's async library or perhaps Promise.all if your framework understands promises. — Paul
– Paul, Commented Jul 13, 2020 at 8:09
I'm using Node.js so I guess Promise.all works fine. I updated the question with the tables, I don't understand why isn't it working. Sending one table is working fine but sending them all doesn't work — Leo Messi
– Leo Messi, Commented Jul 13, 2020 at 8:18

vizsatiz · Accepted Answer · 2020-07-13 12:04:04Z

1

Provided that all your tables have the same columns or at-least the output can be aliased into same column names (maybe put nulls if the column is not in one of the tables), you can make use of UNION

eg:

SELECT id, name FROM table1 
 UNION 
   SELECT id, name FROM table2
 UNION 
   SELECT myid as id, test as name FROM table3

This way you can append all such tables into a single output table. Also if you want it to aggregate through code, you can use the below query to get all table names and work with each table:

SELECT name FROM my_db.sqlite_master WHERE type='table';

edited Jul 13, 2020 at 12:04

answered Jul 13, 2020 at 7:58

vizsatiz

2,2111 gold badge21 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Leo Messi Over a year ago

just updated the question, the tables do not have the same column names. I've tried with UNION and UNION ALL but it doesn't return any data

vizsatiz Over a year ago

You will have to use alias names for your column outputs, use AS to make sure that the column names are some for each table

vizsatiz Over a year ago

I would recommend going for the second approach if the tables are completely different. Btw your 700th goal was awesome ! :)

vizsatiz Over a year ago

Look at table3 of my example query

Leo Messi Over a year ago

so I did it like this:` 'SELECT name FROM ocs_athletes.sqlite_master WHERE type="table"',` , the name of my db is ocs_athletes. is it wrong? because it still doesn't show anything

|

Collectives™ on Stack Overflow

Get all data from different tables in a SQLite database in Node.js

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related