Получить данные из SQLite
 

Всем доброго времени.
есть такая проблема вывести данные из БД SQLite с применением шаблона ejs (неделю не могу найти правильного решения). Испытываю трудность объяснить чего я хочу. Есть controller возвращающий dataset. С использованием статического массива тип

var arr = [{id:1, name:"Name1"}]

проблем нет. если dataset из базы то все не фунциклирует.

вот пример роута

router.get('/rooms', function (req, res, next) {
  var rooms = require('../controllers/rooms');
  res.render('app/rooms',{arr:rooms.getRooms()})
});

вот кусок rooms

let arr = [
    {"ID":"1","Name":"Родительская","roomLink":"parents","roomDateCreate":"","roomSort":"1"},
    {"ID":"2","Name":"Детская","roomLink":"kinds","roomDateCreate":"","roomSort":"2"},
    {"ID":"3","Name":"Кухня","roomLink":"kitchen","roomDateCreate":"","roomSort":"3"},
    {"ID":"4","Name":"Ванная","roomLink":"bedroom","roomDateCreate":"","roomSort":"4"},
    {"ID":"5","Name":"Туалет","roomLink":"restroom","roomDateCreate":"","roomSort":"5"},
    {"ID":"6","Name":"Коридор","roomLink":"corridor","roomDateCreate":"","roomSort":"6"},
    {"ID":"7","Name":"Подвал","roomLink":"vault","roomDateCreate":"","roomSort":"7"},
    {"ID":"8","Name":"Котельная","roomLink":"boiler","roomDateCreate":"","roomSort":"8"},
    {"ID":"9","Name":"Гараж","roomLink":"garage","roomDateCreate":"","roomSort":"9"}
]

function getRooms(){
    var db = require('./sqlite3DB');
    db.connDB('../data/mydb.db3');

    var DB = db.getDB();
    DB.all('SELECT * FROM rooms', onRoomsReady);
    DB.close();
}

function onRoomsReady(err, data){
    return data
}

function getArr(){
    return arr
}


module.exports = {
    getRooms,
    getArr
    //dataset
};

Еще такой вариант

let dataset = function(){
    obj = [];
    for (let i = 0; i < 10; i++) {
        obj.push({Name:'Name'+i, Link:'Link'+i});
    }
    return obj;
}

а как сделать что бы из БД?

Запрос к базе асинхронный, надо передать ф-цию обратного вызова (callback), которая будет вызвана когда отработает запрос к БД

route

router.get('/rooms', function (req, res, next) {
  var rooms = require('../controllers/rooms');
  rooms.getRooms((err, arr) => {
    res.render('app/rooms', { arr })
  })
  // res.render('app/rooms', { arr: rooms.getRooms() })
});

rooms

let arr = [
  { "ID": "1", "Name": "Родительская", "roomLink": "parents", "roomDateCreate": "", "roomSort": "1" },
  { "ID": "2", "Name": "Детская", "roomLink": "kinds", "roomDateCreate": "", "roomSort": "2" },
  { "ID": "3", "Name": "Кухня", "roomLink": "kitchen", "roomDateCreate": "", "roomSort": "3" },
  { "ID": "4", "Name": "Ванная", "roomLink": "bedroom", "roomDateCreate": "", "roomSort": "4" },
  { "ID": "5", "Name": "Туалет", "roomLink": "restroom", "roomDateCreate": "", "roomSort": "5" },
  { "ID": "6", "Name": "Коридор", "roomLink": "corridor", "roomDateCreate": "", "roomSort": "6" },
  { "ID": "7", "Name": "Подвал", "roomLink": "vault", "roomDateCreate": "", "roomSort": "7" },
  { "ID": "8", "Name": "Котельная", "roomLink": "boiler", "roomDateCreate": "", "roomSort": "8" },
  { "ID": "9", "Name": "Гараж", "roomLink": "garage", "roomDateCreate": "", "roomSort": "9" }
]

function getRooms(callback) {
  var db = require('./sqlite3DB');
  db.connDB('../data/mydb.db3');

  var DB = db.getDB();
  DB.all('SELECT * FROM rooms', callback);
  DB.close();
}

// function onRoomsReady(err, data) {
//   return data
// }

// function getArr() {
//   return arr
// }


module.exports = {
  getRooms,
  // getArr
  //dataset
};

--------- cut here ---------

Альтернативный вариант %)

package.json

{
  "name": "express_ejs_sequelize_sqlite",
  "version": "1.0.0",
  "main": "index.js",
  "license": "MIT",
  "dependencies": {
    "ejs": "^2.6.1",
    "express": "^4.16.4",
    "sequelize": "^4.41.2",
    "sqlite3": "^4.0.4"
  }
}

server.js

let express = require('express');
let app = express();

let Sequelize = require('sequelize')

let sequelize = new Sequelize('product', null, null, {
  dialect: "sqlite",
  storage: './rooms.sqlite',
})

var Room = sequelize.define('Room', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  name: Sequelize.STRING,
  roomLink: Sequelize.STRING,
  roomDateCreate: {
    type: Sequelize.DATE,
    defaultValue: Sequelize.NOW
  },
  roomSort: Sequelize.INTEGER
})

app.set('view engine', 'ejs');

app.get('/', (req, res) => {
  Room.findAll({ raw: true }).then(function (rooms) {
    res.render('pages/index', { rooms });
  }, function (err) {
    res.render('pages/error')
  })
});

sequelize
  .sync({ force: true })
  .then(function () {
    Room.bulkCreate([
      { "id": "1", "name": "Родительская", "roomLink": "parents", "roomSort": "1" },
      { "id": "2", "name": "Детская", "roomLink": "kinds", "roomSort": "2" },
      { "id": "3", "name": "Кухня", "roomLink": "kitchen", "roomSort": "3" },
      { "id": "4", "name": "Ванная", "roomLink": "bedroom", "roomSort": "4" },
      { "id": "5", "name": "Туалет", "roomLink": "restroom", "roomSort": "5" },
      { "id": "6", "name": "Коридор", "roomLink": "corridor", "roomSort": "6" },
      { "id": "7", "name": "Подвал", "roomLink": "vault", "roomSort": "7" },
      { "id": "8", "name": "Котельная", "roomLink": "boiler", "roomSort": "8" },
      { "id": "9", "name": "Гараж", "roomLink": "garage", "roomSort": "9" }
    ])
  })
  .then(function (err) {
    if (err) throw err
    else app.listen(4000, () => console.log('listening on port 4000'));
  })

views/pages/index.ejs

<!DOCTYPE html>
<html lang="en">

<head>
  <title>EXPRESS EJS SEQUELIZE SQLITE3</title>
</head>

<body class="container">
  <ul>
    <% rooms.forEach(function(room) { %>
    <li>
        <%= room.id %> - <%= room.name %> - <%= room.roomDateCreate %> -  <%= room.roomSort %>
    </li>
    <% }); %>
  </ul>
</body>

</html>