NodeJS Express Upload File Into Database (Simple Example)

Welcome to a tutorial on how to upload a file into the database in NodeJS and Express. So you want to save a file into the database? No problem, read on for the example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Click here to download

The example code is released under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

TUTORIAL VIDEO

 

QUICK SETUP

For this example, we will need SQLite, Express, and Express File Upload – npm i sqlite3 express express-fileupload

 

PART 1) THE DATABASE

1A) THE SQL

CREATE TABLE `storage` (
  `file_name` TEXT PRIMARY KEY,
  `file_mime` TEXT NOT NULL,
  `file_data` BLOB NOT NULL
);

To save a file in the database, we will need to set a column to the BLOB binary data type.

 

1B) CREATE DATABASE

const sqlite = require("sqlite3");
const db = new sqlite.Database("demo.db", err => {
  db.exec(require("fs").readFileSync("1a-database.sql", "utf8"));
  db.close();
  console.log("Database created");
});

Next, run this script to create the database itself.

 

 

PART 2) HTML UPLOAD PAGE

<form method="post" action="/upload" target="_blank" enctype="multipart/form-data">
  <input type="file" name="upload" required>
  <input type="submit" name="submit" value="Upload File">
</form>

There is nothing “special” about the HTML upload page, it’s just a regular file upload field.

 

PART 3) HTTP SERVER

3A) INIT

// (A) LOAD MODULES
const path = require("path"),
      express = require("express"),
      fileUpload = require("express-fileupload"),
      sqlite = require("sqlite3");

// (B) EXPRESS SERVER & MIDDLEWARE
const app = express();
app.use(fileUpload());

// ...

// (D) START!
app.listen(80, () => console.log(`Server running at port 80`));

The top and bottom parts of the server script should be pretty self-explanatory.

• (A) Load the required modules.
• (B) Initialize the Express server and load whatever middleware is required.
• (D) Start the server.

 

3B) UPLOAD FILE

// (C) ENDPOINTS
// (C1) HTML FILE UPLOAD FORM
app.get("/", (req, res) => res.sendFile(path.join(__dirname, "/2-upload.html")));
 
// (C2) PROCESS UPLOAD
app.post("/upload", (req, res) => {
  // (C2-1) FILE INFO + DATABASE
  let upfile = req.files.upload,
  db = new sqlite.Database("demo.db");

  // (C2-2) SAVE INTO DATABASE
  db.run(`REPLACE INTO storage (file_name, file_mime, file_data) VALUES (?,?,?)`, [
    upfile.name, upfile.mimetype, upfile.data.toString("binary")
  ], err => {
    if (err) {
      res.status(500);
      console.log(err);
      res.send("ERROR!");
    } else {
      res.status(200);
      res.send("OK - " + upfile.name);
    }
    db.close();
  });
});

• (C1) Serve the HTML upload form at the base URL /.
• (C2) We send file uploads to /upload, save the file into the database.

 

 

3C) DOWNLOAD FILE

// (C3) DOWNLOAD
app.get("/download", (req, res) => {
  let db = new sqlite.Database("demo.db");
  db.get("SELECT * FROM storage LIMIT 1", [], (err, row) => {
  (err, row) => {
    console.log(row);
    res.set({
      "Content-Type": row["file_mime"],
      "Content-Transfer-Encoding": "Binary",
      "Content-Disposition": `attachment; filename="${row["file_name"]}"`
    })
    res.send(row["file_data"]);
    db.close();
  });
});

Lastly, a small bit on “how to load files from the database” – This will fetch the file from the database and force a download.

 

 

NOT A GOOD IDEA

Yes, the above “save file into database” example works. But most RDB are not made to store massive files, nor do they work great with stuff like streaming. So unless you have no other choice but to save files in the database – A “secured folder” is the better solution.

 

LINKS & REFERENCES

• ExpressJS
• Express File Upload – NPM
• NodeJS Simple File Upload – Code Boxx