1. I try to create a small API program. I used my postman. while I'm using a GET request I get a result but if I put POST request I got this error(SQLITE_ERROR: no such a table products"). I add my 3 files server.js, database.js, and, package.json

2. which code will I use to get this JSON message from the POSTMAN result:-

"message": "customer A.D.Lakith Dharmasiri has registered", "customerId": 2

// server.js file below

var express = require("express");
var app = express();
var db = require("./database.js");
var bodyParser = require("body-parser");
const { request, response } = require("express");
const res = require("express/lib/response");
app.use(bodyParser.json());

let HTTP_PORT = 8080;

app.listen(HTTP_PORT, () => {
    console.log("Server is running on %PORT%".replace("%PORT%", HTTP_PORT))
});

app.post("/api/products/", (req, res, next) => {

    try {
        var errors = []

        if (!req.body) {
            errors.push("An invalid input");
        }

        const { 
            customerName,
            address,
            email,
            dateOfBirth,
            gender,
            age,
            cardHolderName,
            cardNumber,
            expirytDate,
            cvv,
            timeStamp
        } = req.body;

        var sql = 'INSERT INTO products (customerName, address, email, dateOfBirth, gender, age, cardHolderName, cardNumber, expirytDate, cvv, timeStamp) VALUES (?,?,?,?,?,?,?,?,?,?,?)'
        var params = [customerName, address, email, dateOfBirth, gender, age, cardHolderName, cardNumber, expirytDate, cvv, timeStamp]
        db.run(sql, params, function (err, result) {

            if (err) {
                res.status(400).json({ "error": err.message })
                return;
            } else {
                res.json({
                    "message": "success",
                    "data": req.body,
                    "id": this.lastID
                })
            }

        });
    } catch (E) {
        res.status(400).send(E);
    }
});

app.get("/api/products", (req, res, next) => {

    try {
        var sql = "select * from customer"
        var params = []
        db.all(sql, params, (err, rows) => {
            if (err) {
                res.status(400).json({ "error": err.message });
                return;
            }
            res.json({
                "message": "success",
                "data": rows
            })
        });
    } catch (E) {
        res.status(400).send(E);
    }
});

app.put("/api/products/", (req, res, next) => {
    const {
            id,
            customerName,
            address,
            email,
            dateOfBirth,
            gender,
            age,
            cardHolderName,
            cardNumber,
            expirytDate,
            cvv,
            timeStamp
    } = req.body;

    db.run(`UPDATE customer set customerName=?, address=?, email=?, dateOfBirth=?, gender=?, age=?, cardHolderName=?, cardNumber=?, expirytDate=?, cvv=?, timeStamp=? WHERE id = ?`,
    [customerName, address, email, dateOfBirth, gender, age, cardHolderName, cardNumber, expirytDate, cvv, timeStamp, id],
        function (err, result) {
            if (err) {
                res.status(400).json({ "error": res.message })
                return;
            }
            res.status(200).json({ updated: this.changes });
        });
});

app.delete("/api/products/delete/:id", (req, res, next) => {
    try {
        db.run('DELETE FROM customer WHERE id = ?',
            req.params.id,
            function (err, result) {
                if (err) {
                    res.status(400).json({ "error": res.message })
                    return;
                }
                res.json({ "message": "deleted", rows: this.changes })
            });
    } catch (E) {
        res.status(400).send(E)
    }

});
```
`

//below database.js file 



```
var sqlite3 = require('sqlite3').verbose()
var md5 = require('md5')

const DBSOURCE = "db.sqlite"


let db = new sqlite3.Database(DBSOURCE, (err) => {
    if (err) {
        // Cannot open database
        console.error(err.message)
        throw err
    } else {
        console.log('Connected to the SQlite database.')
        db.run(`CREATE TABLE customer (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            customerName text,
            address text,
            email text,
            dateOfBirth text,
            gender text,
            age INTEGER,
            cardHolderName text,
            cardNumber INTEGER,
            expirytDate text,
            cvv INTEGER,
            timeStamp INTEGER
            )`, (err) => {
            if (err) {
                // Table already created

            } else {
                // Table just created, creating some rows
                var insert = 'INSERT INTO customer (customerName, address, email, dateOfBirth, gender, age, cardHolderName, cardNumber, expirytDate, cvv, timeStamp) VALUES (?,?,?,?,?,?,?,?,?,?,?)'
                db.run(insert, ["A.D.Lakith Dharmasiri", "No 324/A Ra De Mel Road,Colombo", "[email protected]", "1991.02.25", "female", 28, "A,D,L,Dharmasiri", 102445217895, "12/2022", 246, "2022-12-31 23.59.59"],(err)=>{
                    if(err==null){ console.log("Data Inserted."); } else { console.log(err); }
                })
            }
        })

    }
})

module.exports = db
```
//below package.json file


```
{
  "dependencies": {
    "body-parser": "^1.20.1",
    "express": "^4.18.2",
    "md5": "^2.3.0",
    "sqlite3": "^5.1.2"
  },
  "scripts": {
    "start": "node server.js"
  },
  "name": "uom-5",
  "version": "1.0.0",
  "main": "database.js",
  "devDependencies": {},
  "author": "",
  "license": "ISC",
  "description": ""
}

```

CodePudding user response：

The name of your table is customer, not products.

So the line should be

var sql = 'INSERT INTO customer (customerName, address, email, dateOfBirth, gender, age, cardHolderName, cardNumber, expirytDate, cvv, timeStamp) VALUES (?,?,?,?,?,?,?,?,?,?,?)'