Upload/store images in MySQL using Node.js, Express & Multer
Node.js upload/store image in MySQL overview
We’re gonna make a Node.js application like this:
Click on Submit button, the file will be uploaded to MySQL database:
We also store the uploaded image in upload folders before saving its data to MySQL. Then to check MySQL image data, we save the result data in tmp folder.
Project Structure
Let’s look at our project directory tree:
– db.config.js
exports configuring parameters for MySQL connection & Sequelize.
– models/index.js
: uses configuration above to initialize Sequelize, models/image.model.js
for Sequelize model Image.
– views/index.html
: contains HTML form for user to upload images.
– routes/web.js
: defines routes for endpoints that is called from views, use controllers to handle requests.
– controllers
:
home.js
returnsviews/index.html
upload.js
handles upload & store images with middleware function.
– middleware/upload.js
: initializes Multer Storage engine and defines middleware function.
– server.js
: initializes routes, runs Express app.
Setup Node.js modules
Open command prompt, change current directory to the root folder of our project. Install Express, Multer, Sequelize, Mysql2 with the following command:
The package.json file will look like this:
Configure MySQL database & Sequelize
In the src folder, we create a separate config folder for configuration with db.config.js file like this:
First five parameters are for MySQL connection.
pool
is optional, it will be used for Sequelize connection pool configuration:
max
: maximum number of connection in poolmin
: minimum number of connection in poolidle
: maximum time, in milliseconds, that a connection can be idle before being releasedacquire
: maximum time, in milliseconds, that pool will try to get connection before throwing error
For more details, please visit API Reference for the Sequelize constructor.
Initialize Sequelize
Now we initialize Sequelize in src/models folder.
Create src/models/index.js with the following code:
We’re gonna define Image
model in the next step.
Define the Sequelize Model
In models folder, create image.model.js file like this:
This Sequelize Model represents images table in MySQL database. These columns will be generated automatically: id, type, name, data, createdAt, updatedAt.
The data
field has BLOB
type. A BLOB is binary large object that can hold a variable amount of data.
Create middleware for uploading & storing image
Inside middleware folder, create upload.js file with the following code:
In the code above, we’ve done these steps:
– First, we import multer
module.
– Next, we configure multer
to use Disk Storage engine.
– We also define a filter to only allow images to pass.
You can see that we have two options here:
– destination
determines folder to store the uploaded files.
– filename
determines the name of the file inside the destination folder.
– We add the [timestamp]-bezkoder-
prefix to the file’s original name to make sure that the duplicates never occur.
Create Controller for uploading Images
controllers/upload.js
Now look at the uploadFiles
function:
– First we get and check file upload from req.file
.
– Next we use Sequelize model create()
method to save an Image object (type, name, data) to MySQL database.
data
is gotten from uploads folder (that middleware function stored the image).
– If the process is successful, we save write the image data to tmp folder.
– To read/write data, we use fs.readFileSync('/path/to/file')
and fs.writeFileSync('/path/to/file', image.data)
functions of Node.js fs
module.
Create Controller for the view
controllers/home.js
Create View for uploading image
In views folder, create index.html file with the HTML and Javascript code as below:
For HTML part, we create a form with following elements:
action="/upload"
method="POST"
enctype="multipart/form-data"
You also need to notice the input
tag with the name="file"
attribute that we use in the middleware.
The jQuery script shows preview of the chosen images. We also use Bootstrap to make the UI more comfortable to read.
Define routes
In routes folder, define routes in web.js with Express Router.
There are 2 routes:
– GET: Home page for the upload form.
– POST "/upload"
to call the upload controller. This is for action="/upload"
in the view.
The single()
function with the parameter is the name of input
tag (in html view: <input type="file" name="file">
) will store the single file in req.file
.
Create Express app server
Finally, we create an Express server.
server.js
In the code above, we call Sequelize sync()
method.
In development, you may need to drop existing tables and re-sync database. Just use force: true
as following code:
Run & Check result
First we need to create tmp and uploads folder with the path resources/static/assets
.
On the project root folder, run this command: node src/server.js
The console shows:
Open your browser with url: http://localhost:3000
.
Last updated