I cannot add a line to a table using db.run() in sqlite3

Question

I have already installed Node Static, to fix the first time q error appeared, which seemed to be related to a server response delay, but msm after I took this delay, the error kept appearing, probably related to the creation of table or database.db file, but I can’t find it, I’m new to sql, maybe that’s why I can’t find the error

error q i am getting is:

[Error: SQLITE_ERROR: near "PRIMARY": syntax error] {
  errno: 1,
  code: 'SQLITE_ERROR'
}
undefined:0



Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client
    at ServerResponse.setHeader (_http_outgoing.js:518:11)
    at ServerResponse.header (C:\Users\julio\OneDrive\Área de Trabalho\MarketPlace\node_modules\express\lib\response.js:771:10)
    at ServerResponse.send (C:\Users\julio\OneDrive\Área de Trabalho\MarketPlace\node_modules\express\lib\response.js:170:12)
    at afterInsertData (C:\Users\julio\OneDrive\Área de Trabalho\MarketPlace\src\server.js:99:24)
    at Statement.errBack (C:\Users\julio\OneDrive\Área de Trabalho\MarketPlace\node_modules\sqlite3\lib\sqlite3.js:14:21) {
  code: 'ERR_HTTP_HEADERS_SENT'
}

my file for start is this:

var express = require("express")
var server = express()

var http = require('http');

var nStatic = require('node-static');

var fileServer = new nStatic.Server('./public');

http.createServer(function (req, res) {
    
    fileServer.serve(req, res);

}).listen(5000);

// configurar pasta publica

//server.use(express.static("public"))

// habilitar uso do req.body
server.use(express.urlencoded({ extended: true }))


//pegar banco de
var db = require("./database/db")


//utilizando template engine
var nunjucks = require("nunjucks")
nunjucks.configure("src/views", {
    express: server,
})

server.get("/", (req, res) => {
    return res.render("index.html")
})

server.get("/createCliente", (req, res) => {
    return res.render("create-client-user.html")
})

server.get("/createStore", (req, res) => {
    return res.render("create-store-user.html")
})

 server.post("/saveuser", (req, res) => {

    // db.run(`
    // CREATE TABLE IF NOT EXISTS clientes (
    //     cell TEXT PRIMARY KEY,
    //     name TEXT,
    //     birthDate TEXT,
    //     rg TEXT,
    //     cep TEXT,
    //     uf TEXT,
    //     city TEXT,
    //     bairro TEXT,
    //     rua TEXT,
    //     numero TEXT,
    //     complement TEXT
    // );
    // `)

    const query = `
    INSERT INTO clientes (
        cell  PRIMARY KEY,
        name,
        birthDate,
        rg,
        cep,
        uf,
        city,
        bairro,
        rua,
        numero ,
        complement
    ) value(?,?,?,?,?,?,?,?,?,?,?);
    `

    const values = [    
        req.body.cell,
        req.body.name,
        req.body.day + "/" + req.body.month + "/" + req.body.year,
        req.body.rg,
        req.body.cep,
        req.body.uf,
        req.body.city,
        req.body.bairro,
        req.body.logradouro,
        req.body.num,
        req.body.complement
    ] 

    console.log(values)

    function afterInsertData(err) {
        if (err) {
            console.log(err)
            return res.send("Erro no cadastro!")
        }
    
        console.log("Cadastrado com sucesso")
        console.log(this)
    }
    
    db.run(query, values, afterInsertData)

    db.all(`SELECT name FROM clientes`, function(err, rows) {
        if(err) {
            return console.log(err)
        }

        console.log("Aqui estão seus registros: ")
        console.log(rows)
    })
    
    return res.render("index.html")
 })


server.listen(3000)

my db.js file to startar the sqlite3 is:

``` //importar obj sqlite3
const sqlite3 = require("sqlite3").verbose()

//criar obj q vai fazer  operaç~oes no banco de daddo
const db = new sqlite3.Database("./src/database/database.db")

module.exports = db

db.serialize(() => {
//     db.run(`
//    CREATE TABLE IF NOT EXISTS clientes (
//        cell TEXT PRIMARY KEY,
//        name TEXT,
//       birthDate TEXT,
//        rg TEXT,
//        cep TEXT,
//        uf TEXT,
//        city TEXT,
//        bairro TEXT,
//        rua TEXT,
//        numero TEXT,
//        complement TEXT
//    );
//`)
}) 

my html file is this, n added also the script, pq it just served to popular some selects and give autocomplete in the zip code

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>

    <link rel="stylesheet" href="http://localhost:5000/style/main.css">
</head>
<body>
    <div class="header">
        <img src="http://localhost:5000/imgs/logos/iconeLogo.png" alt="">
        <div class="links">
            <a href="/createCliente">Registrar-se</a>
            <a href="/createStore">Registrar minha Loja</a>
            <a href="">Em alta</a>
            <a href="">Registrar-se</a>
            <a href="">Registrar-se</a>
            <a href="">Registrar-se</a>
            <a href="">Registrar-se</a>
        </div>
    </div>
<h1>Criando Conta</h1>
<form action="/saveuser" method="POST">

    <fieldset>
        <div class="field">
            <label for="name">Nome:</label>
            <input type="text" name="name" required="required">
        </div>
        
        <div class="field">
            <label for="cell">Telefone Celular:</label>
            <input type="text" name="cell">
        </div>
        
        <div class="field-group">
            <label for="birthDate">Data de nascimento:</label>
            <select class="birthDate field" name="day" required="required">
                <option value="">Selecione o dia</option>
            </select>
            <select class="birthDate field" name="month" required="required">
                <option value="">Selecione o mês</option>
            </select>
            <select class="birthDate field" name="year" required="required">
                <option value="">Selecione o ano</option>
            </select>
        </div>
        
        <div class="field">
            <label for="RG">RG:</label>
            <input type="text" name="rg">
        </div>
        
        <div class="field-group adress">
            <div class="field">
                <label for="CEP">CEP:</label>
                <input class="CEP"  type="text" name="cep">
            </div>
            <div class="field">
                <label for="UF">Unidade Federal(Estado):</label>
                <input class="CEP UF"  type="text" name="uf">
            </div>
            <div class="field">
                <label for="city">Cidade:</label>
                <input class="CEP city"  type="text" name="city">
            </div>
            <div class="field">
                <label for="bairro">Bairro:</label>
                <input class="CEP bairro"  type="text" name="bairro">
            </div>
            <div class="field">
                <label for="logradouro">Logradouro:</label>
                <input class="CEP logradouro"  type="text" name="logradouro">
            </div>
            <div class="field">
                <label for="num">Número:</label>
                <input class="num"  type="text" name="num">
            </div>
            <div class="field">
                <label for="complement">Complemento:</label>
                <input class="complement"  type="text" name="complement">
            </div>
        </div>
    </fieldset>

    <button type="submit">Submit</button>
</form>
</body>
</html>

Answer 1

To resolve this error:

[Error: SQLITE_ERROR: near "PRIMARY": syntax error] { Errno: 1, code: 'SQLITE_ERROR' }

It is necessary to correct your query that makes the insertion of the data, for this remove the text PRIMARY KEY query, it is also necessary to correct the word valuethat should be values, the final result would be:

const query = `
    INSERT INTO clientes (
        cell,
        name,
        birthDate,
        rg,
        cep,
        uf,
        city,
        bairro,
        rua,
        numero ,
        complement
    ) values (?,?,?,?,?,?,?,?,?,?,?);
    `

Your route saveuser Data entry is doing more than it should. Because she’s entering the data, and she’s also listing data. I’m not sure of the necessity of function afterInsertData. This may be why you are receiving the message:

Cannot set headers after they are sent to the client