CODEWITHSUNDEEP
node.jssqliteejsnode-sqlite3
ryan
ryan

Reputation: 69

How to use a sqlite3 database in an ejs file with node.js?

I have been able to connect to my sqlite3 database with node.js, I want to use the information from the records in a separate ejs file (index.ejs). Does anyone know if this is possible?

code:

//register sqlite3
const sqlite3 = require('sqlite3').verbose();

let db = new sqlite3.Database('./db/burger.db');

  let sql = 'SELECT id, name from burger WHERE id = ?';
  let id = 5;

    //first row only
    db.get(sql, [id], (err, row) => {
        if (err) {
            console.log("Error in connecting to first row of db");
        }
        return row
        ?  console.log(row.id, row.name)
        
        :console.log('No record found with the id ${id}');
    });

  


//listen for requests
app.listen(3001);

app.use(express.static('views'));

app.get('/', (req, res) => {
    
    res.render('index');

});

//404
app.use((req, res) => {
    res.status(404).render('404', { title: '404'});
    db.close();
});

I'm totally unsure of what steps I have to take to accomplish this. Above is what is found on my app.js, this the node. In a separate folder, "views", my index.ejs file is kept.

Upvotes: 1

Views: 763

Answers (1)

Spectramollisol
Spectramollisol

Reputation: 1

Yes, it's possible. See: CRUD application with Express and SQlite in 10 steps 2019-10-08 #javascript #node #sql

https://blog.pagesd.info/2019/10/08/crud-with-express-sqlite-10-steps/

I'm currently looking into ejs myself to refactor a working Node.js, Express, HTML5, CSS3, Sqlite3 program on Glitch to use of a templating engine like EJS. The devil is in the details, though. You need a javascript file that configures the server, points to the views folder (app.set("view engine", "ejs");) and connects to the sqlite3 database. I suggest getting a basic server JavaScript file running with 2 or 3 routes and EJS file views before coding the sqlite3 connection, queries and EJS output views. That's where I'm at at the moment. The server GET routing code with use of EJS partials can be a bit complicated at first because one is coding in both the server JavaScript file and the EJS files. You are off to a good start with index.ejs in a views folder. You can add two subfolders in the views folder called "pages" and "partials".The views/pages subfolder is going to hold a slightly modified index.ejs file that makes use of reusable html code chunks from the partials folder. The pages subfolder will also hold other EJS files for views(webpages) that will output your data with appropriate EJS code. The sqlite3 data is going to be added to the EJS views with <%= ...%> code like:

<h1>List of books</h1>
<ul> 
    <% for (const book of model) { %>
        <li>
            <%= book.Title %>
            <em>(<%= book.Author %>)</em>
        </li>
    <% } %>
</ul>

Source: https://blog.pagesd.info/2019/10/08/crud-with-express-sqlite-10-steps/

The connection to the database, definition of data objects and the SQL queries can apparently be coded in the server JavaScript file. Make sure to note use of app.use(express.urlencoded({ extended: false }));

A complication I have is that I also had a client.js file that was included in the head of the html file and it might be necessary to add it as an exported module instead. So far, I can't get the old form input to post properly but it might just be that I haven't set up the route properly and coded the output view EJS file with the proper syntax yet.

See also: https://coda.io/@peter-sigurdson/lab-workbook-setting-up-a-node-js-express-server-with-sqlite-and

I got it to work with the following in the server.js file:

app.get('/authors', (req, res) => {
    const sql = "SELECT * FROM Authors"
    db.all(sql, [], (err, rows) => {
        if (err) {
            return console.error(err.message);
        }
        res.render('pages/authors', { model: rows });
    });
});

and an authors.ejs file in views/pages

<%- include('../partials/head') %>
<%- include('../partials/header') %>
<body>
    <h1>List of authors</h1>
<ul>
    <% for (const author of model) { %>
        <li>
            <%= author.id %>
            <em>(<%= author.author %>)</em>
        </li>
        <% } %>  
    </ul>
 </body>
 <%- include('../partials/footer') %>

Upvotes: 0

Related Questions