Node.js – API with SQLite Uploading Images

By -Danny B.
Posted on September 26, 2021November 20, 2021
Posted in Node.js, SQLite

Overview

This tutorial will break down an example of a step form built with the Material UI library. The form will have three sections for name, additional questions and images to upload.

Source Code

Download Source

Prerequisites

Create a folder for your project and go into that directory and run the following commands.

npm init
npm install express

Nodemon

Nodemon restarts the node.js application when changes are made.

Note: The example from GitHub does not have nodemon installed. It will be updated on 09/24/2021.

npm install nodemon

Update the “scripts” section of the “package.json” file with the following:

  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon  app.js"
  },

To start the application use npm start instead of node app.js

SQLite Driver

SQLite driver to perform CRUD operations on a SQLite database file. The SQLite Transactions library is to add the functionality of wrapping multiple SQL statements as a transaction.

npm install sqlite3
npm install sqlite3-transactions

VSCode SQLite Plugin – Optional

After it is install hit Control + Shift + P then type sqlite

Choose usersdb.sqlite

Choose Show Table

Multer – Handling multipart/form-data

Multer is a node.js middleware for handling multipart/form-data, which is primarily used for uploading files. It is written on top of busboy for maximum efficiency.

npm i multer

GUID Generator

UUID creates RFC4122 UUIDs used for generating unique filenames.

npm install uuid

DotEnv If you want to use the API with an external application

npm i cors

Node.js API

API Imports

Import the following libraries

Express
SQLite3
FS – File Support
CORS – Cross Origins Resource Sharing for external web apps
Multer – Multi-Part Forms for the files
UUID – GUID Generator for the file names

const express = require('express');
const app = express();
const port = 3003;
var fs = require('fs');
const cors = require('cors');
const multer  = require('multer')
const { v4: uuidv4 } = require('uuid');
const { randomUUID } = require('crypto');

var sqlite3 = require("sqlite3"),
    TransactionDatabase = require("sqlite3-transactions").TransactionDatabase;

SQLite Database

Using a constant to hold the name of the Database carsdb.sqlite. Create a new database instance into db. If the SQLite database does not exist, insert four cars and images. Finally put the db instance in module.exports.


var sqlite3 = require("sqlite3"),
    TransactionDatabase = require("sqlite3-transactions").TransactionDatabase;

const DBSOURCE = "usersdb.sqlite";

var db = new TransactionDatabase(
    new sqlite3.Database(DBSOURCE, (err) => {
    if (err) {
      // Cannot open database
      console.error(err.message)
      throw err
    } 
    else {
        // ** EXAMPLE **
        // ** For a column with unique values **
        // email TEXT UNIQUE, 
        // with CONSTRAINT email_unique UNIQUE (email) 
        
        db.run(`CREATE TABLE Users (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,              
            Username TEXT,             
            DateModified DATE,
            DateCreated DATE
            )`,
        (err) => {
            if (err) {
                // Table already created
            }else{
                // Table just created, creating some rows
                var insert = 'INSERT INTO Users (Username, DateCreated) VALUES (?,?)'
                db.run(insert, ["lisa33",  Date('now')])
                db.run(insert, ["craig44", Date('now')])
                db.run(insert, ["alan54",  Date('now')])
                db.run(insert, ["tracy65", Date('now')])
            }
        });  

        db.run(`CREATE TABLE UserImages (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            UserId INTEGER,                         
            Mimetype TEXT,                         
            Filename TEXT,                         
            Size INTEGER,                         
            DateModified DATE,
            DateCreated DATE
            )`,
        (err) => {
            if (err) {
                // Table already created
            }
        });  
    }
  })
);

module.exports = db

Enable CORS and Express URL encoded.

To allow all jut use cors() without anything inside the curly braces.

app.use(
    express.urlencoded(),
    cors({
        origin: 'http://localhost:3000'
    })
);

Upload a Single Image

The single file upload checks if the parent record exists to avoid uploading orphaned files. If the parent record exists then get the file from the images folder and moves and renames it with a GUID into a subfolder labeled with the Parent ID.

The code for the action.

const upload = multer({ dest: './images/' })
app.post('/api/upload-single-file', upload.single('files'), function async (req, res) {

    var dir = `./images/${req.body.UserId}/`;

    if (!fs.existsSync(dir)){
        fs.mkdirSync(dir, { recursive: true });
    }
    var oldPath = `./images/${req.file.filename}`    
    var newPath = `./images/${req.body.UserId}/${req.file.filename}.jpg`;

    fs.rename(oldPath, newPath, function (err) {
      if (err) throw err
      console.log('Successfully Moved File')
    })

    var data = {
        UserId: req.body.UserId,
        Name: req.file.filename,
        Mimetype: req.file.mimetype,
        Size: req.file.size,
        DateCreated: Date('now')
    }

    var sql ='INSERT INTO UserImages (UserId, Filename, Mimetype, Size, DateCreated) VALUES (?,?,?,?,?)'
    var params = [data.UserId, data.Name, data.Mimetype, data.Size, Date('now')]

    db.run(sql, params, function (err, result) {
        if (err){
            res.status(400).json({"error": err.message})
            return;
        }
    });   

    res.status(200).json(req.file)
});

Upload Multiple Images

Uploading multiple files takes a different approach from a single file upload. Instead of a file uploaded to the images folder then moved and renamed, the image binaries are in an array. The array contains the following data

File  {
  fieldname: 'files',
  originalname: 'iphone.jpg',
  encoding: '7bit',
  mimetype: 'image/jpeg',
  buffer: <Buffer ff d8 ff e0 00 10 4a 46 49 46 00 01 01 00 00 01 00 01 00 00 ff db 00 43 00 06 04 05 06 05 04 06 06 05 06 07 07 06 08 0a 10 0a 0a 09 09 0a 14 0e 0f 0c ... 11511 more bytes>,
  size: 11561
}

The Upload Multiple Files action creates a directory under the images folder with the parent ID as the subfolder. Then the action checks if the parent record exists to avoid uploading orphaned filed. If the parent record exits then loop through the files array and uses UUID() to generate unique filenames while saving the mime type, Size, and uses the Image Buffer to create the images using fs.

The code for the action.

var uploads = multer();
app.post('/api/upload-multiple-files', uploads.array('files', 3), function async (req, res) {
    var file = req.files;
    var fileCount = 0;
    
    var isUserExists = true;

    var sql = "SELECT * FROM Users WHERE Id = ?"
    db.all(sql, req.body.UserId, (err, rows) => {
        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }
        console.log('rows ', rows);
        isUserExists = (rows.length > 0)? true : false;
        console.log('isUserExists ', isUserExists);
        
        if(isUserExists) {

            var dir = `./images/${req.body.UserId}/`;
    
            file.forEach(element => {
    
                if (!fs.existsSync(dir)){
                    fs.mkdirSync(dir, { recursive: true });
                }
                var newFileName = `${uuidv4()}.jpg`;        
                var newPath = `./images/${req.body.UserId}/${newFileName}`;
                var imageBinary = element.buffer;
                try {
                    fs.writeFile(newPath, imageBinary, 'base64', function(err){});                
                } catch (error) {
                    console.log(error);
                }                
                var data = {
                    UserId: req.body.UserId,
                    Filename: newFileName,
                    Mimetype: element.mimetype,
                    Size: element.size,
                    DateCreated: Date('now')
                }
                var sql ='INSERT INTO UserImages (UserId, Filename, Mimetype, Size, DateCreated) VALUES (?,?,?,?,?)'
                var params = [data.UserId, data.Filename, data.Mimetype, data.Size, Date('now')]
            
                db.run(sql, params, function (err, result) {
                    if (err){
                        res.status(400).json({"error": err.message})
                        return;
                    }
                });       
                fileCount++;
            });
    
            res.json({
                message: `Successfully uploaded ${fileCount} files`
            })    
        }
        else {
            res.json({
                message: `Record does not exist`
            })    
        }
        
 });

Example of the subfolder and filenames.

Add a User Record

This is done with an insert statement for the Users table. Check for missing fields and then insert a record. Return the Success message and the new record ID as the Last ID.

app.post("/api/vehicle", async (req, res) => {
    var errors=[]
    
    if (!req.body.Make){
        errors.push("Make is missing");
    }
    if (!req.body.Model){
        errors.push("Model is missing");
    }
    if (errors.length){
        res.status(400).json({"error":errors.join(",")});
        return;
    }
    var data = {
        Make: req.body.Make,
        Model: req.body.Model,
        DateCreated: Date('now')
    }

    var sql ='INSERT INTO Vehicles (Make, Model, DateCreated) VALUES (?,?,?)'
    var params =[data.Make, data.Model, Date('now')]

    db.run(sql, params, function (err, result) {
        if (err){
            res.status(400).json({"error": err.message})
            return;
        }
        res.json({
            "message": "success",
            "data": data,
            "id" : this.lastID
        })
    });   
})

Update User Record

Using Patch send the updated Username. Check if Username exists and use the id parameter to patch the record with the new username and ModifiedDate.

app.patch("/api/user/:id", async (req, res) => {
    var errors=[]

    if (!req.body.Username){
        errors.push("Username is missing");
    }

    // Just in case there are more fields missing
    if (errors.length){
        res.status(400).json({"error":errors.join(",")});
        return;
    }

    var data = [req.body.Username, Date('now'), req.params.id];
    
    let sql = `UPDATE Users SET 
               Username = ?, 
               DateModified = ?
               WHERE Id = ?`;
    
    await db.run(sql, data, function(err) {
      if (err) {
        return console.error(err.message);
      }
      console.log(`Row(s) updated: ${this.changes}`);
    
    });
    
    res.json({
        message: "success",
        id: req.params.id,                
        changes: this.changes
    })    
})

Delete Parent Record and Remove Images

This is usually considered a bad practice to blow away all a parent, child records, and the uploaded files, but it is sometimes necessary to have an all in one delete action.

In order to delete a profile, we need the vehicle ID to query the Vehicle Images table for the location and file names, remove the files, delete the image records, and finally delete the parent record. All of this needs to be wrapped in a transaction to not commit the changes just in case something goes wrong during the process.

Note: To do this correctly a check should be in place to make sure the parent record exists before trying to delete records. The front end shouldn’t allow for non-existing records to be deleted but it’s still a good step to take to prevent crashing the API.

Get the file names and use the vehicle ID for the subfolder path. Loop through the result set, delete the files, and then remove the directory.

app.delete("/api/car/:id", async (req, res, next) => {

    db.beginTransaction(function(err, transaction) {

        // SELECT IMAGES FOR THIS RECORD - DELETE THE FILES & SUB DIRECTORY
        db.all('SELECT Name FROM VehicleImages WHERE VehicleId = ?', req.params.id, (err, rows) => {
            if (err) {
              res.status(400).json({"error":err.message});
              return;
            }

            // DELETE THE FILES FROM THE QUERY DATA (rows)
            rows.forEach(item => {
                console.log(item.Name);
                var filePath = `./images/${req.params.id}/${item.Name}`;
                
                console.log('filePath', filePath);
                try {
                    fs.unlinkSync(filePath)
                    //file removed
                } catch(err) {
                   console.error(err)
                }
                
            }) 

            // REMOVE THE SUB DIRECTORY
            var dirPath = `./images/${req.params.id}`;
            fs.rmdir(dirPath, function(err) {
                if (err) {
                  throw err
                } else {
                  console.log("Successfully removed the empty directory!")
                }
              })
        }); 

        // DELETE IMAGE RECORDS
        db.run(
            'DELETE FROM VehicleImages WHERE VehicleId = ?',
            req.params.id,
            function (err, result) {
                if (err){
                    res.status(400).json({"error": res.message})
                    return;
                }
        });

        // DELETE PARENT RECORD
        db.run(
            'DELETE FROM Vehicles WHERE id = ?',
            req.params.id,
            function (err, result) {
                if (err){
                    res.status(400).json({"error": res.message})
                    return;
                }
        });

        transaction.commit(function(err) {
            if (err) return console.log("Sad panda :-( commit() failed.", err);
            console.log("Happy panda :-) commit() was successful.");
        });
    });
})

Run it with Postman

Single file upload

http://localhost:3003/api/upload-single-file

Upload Multiple Files

http://localhost:3003/api/upload-multiple-files

Add New Parent Record

http://localhost:3003/api/user

Update Parent Record

http://localhost:3003/api/user

Delete Parent Record

http://localhost:3003/api/user

This will remove the files , subdirectory, images records, and the parent record.

Full Stack Soup

Node.js – API with SQLite Uploading Images

Overview

Source Code

Prerequisites

Nodemon

SQLite Driver

Multer – Handling multipart/form-data

GUID Generator

Node.js API

SQLite Database

Enable CORS and Express URL encoded.

Upload a Single Image

Upload Multiple Images

Add a User Record

Update User Record

Delete Parent Record and Remove Images

Run it with Postman

Single file upload

Upload Multiple Files

Add New Parent Record

Update Parent Record

Delete Parent Record

Previous Article

Next Article

Node.js – API with SQLite Uploading Images

Overview

Source Code

Prerequisites

Nodemon

SQLite Driver

Multer – Handling multipart/form-data

GUID Generator

CORS – Cross Origins Resource Sharing

Node.js API

SQLite Database

Enable CORS and Express URL encoded.

Upload a Single Image

Upload Multiple Images

Add a User Record

Update User Record

Delete Parent Record and Remove Images

Run it with Postman

Single file upload

Upload Multiple Files

Add New Parent Record

Update Parent Record

Delete Parent Record

Previous Article

Next Article