How to use variable with sqlite3 in electron app

Question

I'm beginning a journey (!) to build a personal app that records the processes I use to take, develop and print photographs with an an analogue workflow.

It will be a desktop based app, at this time just for my own use.

I have dabbled with Electron in the past and decided to use this as it would be easy for me to use the HTML and CSS knowledge I already have. Use of JS is limited but not non-existent.

As a db i want to be able to create relational connections, so have pretty much ruled out something like PouchDB or IndexedDB and wish to use SQLite3.

I have this running with Electron on my Win 10 laptop and am able to show data from the db within the app.

What I am really stuggling with though is writing to the db by using data from input fields. Below is what I have, the function addDev() is the part I need help with.

I am trying to store the data in a variable and then access that in the sql INSERT query.

<script>
// Open and create IF NOT EXISTS db
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('app/db/apr.db');

db.serialize(function () {
    db.run('CREATE TABLE IF NOT EXISTS Developers (make, name, ratio, notes)');
});

//Add html form data to db
function addDev() {

    var make = document.getElementById("make").value;
    var name = document.getElementById("name").value;
    var ratio = document.getElementById("ratio").value;
    var notes = document.getElementById("notes").value;

    db.run('INSERT INTO Developers (make, name, ratio, notes) VALUES (make, name, ratio, notes)', function(err) {
        if (err) {
            console.log(err.message);
        }    
        // get the last insert id
        console.log("A row has been inserted.");
    })
};

// Display data in table on app page
var rows = document.getElementById("developers");

db.each('SELECT rowid AS id, make, name, ratio, notes FROM Developers', function(err, row) {
var item = document.createElement("tr");

    item.innerHTML = "<td>" + row.id + "</td>" + "<td>" + row.make + "</td>" + "<td>" + row.name + "</td>" + "<td>" + row.ratio + "</td>" + "<td>" + row.notes + "</td>";

rows.appendChild(item);
});

db.close();

This does not give me an error message, but a success msg - except it is not successful.

Answer 1

I've figured this out with two changes... :)

1. I had the db.close() in the wrong place. The db was closing before I could enter the info. Moved this now into the function addDev()

2. I also rewrote the INSERT query as below:

        // Open and create IF NOT EXISTS db
        var sqlite3 = require('sqlite3').verbose();
        var db = new sqlite3.Database('app/db/apr.db');

        db.serialize(function () {
            db.run('CREATE TABLE IF NOT EXISTS Developers (make, name, ratio, notes)');
        });

        //Add html form data to db
        function addDev() {

            var make = document.getElementById("make").value;
            var name = document.getElementById("name").value;
            var ratio = document.getElementById("ratio").value;
            var notes = document.getElementById("notes").value;

            db.run('INSERT INTO Developers (make, name, ratio, notes) VALUES (?, ?, ?, ?)', [make, name,ratio,notes], function(err) {
                if (err) {
                    console.log(err.message);
                }    
                // get the last insert id
                console.log("A row has been inserted.");
            });

            db.close();
        };

        // Display data in table on app page
        var rows = document.getElementById("developers");

        db.each('SELECT rowid AS id, make, name, ratio, notes FROM Developers', function(err, row) {
        var item = document.createElement("tr");

            item.innerHTML = "<td>" + row.id + "</td>" + "<td>" + row.make      + "</td>" + "<td>" + row.name + "</td>" + "<td>" + row.ratio + "</td>" + "<td>" + row.notes + "</td>";

        rows.appendChild(item);
        });

    </script>

Data is now being entered into the table and shown in the window via the SELECT query.