Store images/files for API, using MySQL/MariaDB, Express and Sequelize

DezsiCsaba — Wed, 01 Nov 2023 13:04:46 +0000

Co-authored with @pakos

This guide is focused on storing and serving images, but you can store any kind of data in MySQL/MariaDB in this manner
We also need to install the multer extension to Express to easily deal with file uploads
- multer is an npm plugin. Here is the complete multer documentation

DB side of the story

First we have to define what datatype we're going to use in our db. In our project we used the BLOB datatype, because it can store raw binary data.
It has four sub-types:
- TINYBLOB: Maximum length of 255 bytes
- BLOB: Maximum length of 65535 bytes
- MEDIUMBLOB: Maximum length of 16777215 bytes, or 16MB in storage
- LONGBLOB: Maximum length of 4294967295 bytes or 4GB in storage
You thought this is that easy, weell you are mistaken. We have to specify how much data we want to transfer between database and the API, since the default values are too small.
- We can do this by setting the following two variables.
- (Variables can be set by editing the mysqld config file):

  [mysqld]
  ......
  net_buffer_length = 1000000
  max_allowed_packet=256M
  ......

Another way is to use the MySQL command line using these commands

  set global net_buffer_length=1000000; 
  set global max_allowed_packet=256M;

Don't forget to change these variables based on data size

So what code do we need to write?

Sequelize supports all four types of BLOBs. This can be adjusted when you are defining your model.

  DataTypes.BLOB                // BLOB (bytea for PostgreSQL)
  DataTypes.BLOB('tiny')        // TINYBLOB (bytea for PostgreSQL)
  DataTypes.BLOB('medium')      // MEDIUMBLOB (bytea for PostgreSQL)
  DataTypes.BLOB('long')        // LONGBLOB (bytea for PostgreSQL)

First we need to define the field and datatype in our model

  const sequelize = require('sequelize')
  const {Model} = require('sequelize')
  class pictures extends Model {}


  pictures.init(
  {
      Picture: {
        type: sequelize.BLOB('long'),
        allowNull: false
    },
      ItemId: { //suppose we have an item where the image belongs to
        type: sequelize.INTEGER,
        allowNull: false
      }
    },
    {
      sequelize: _db, //data connection object: new Sequelize(....
      tableName: 'pictures',
      modelName: 'Pictures'
    }
  )
  module.exports = pictures

Receive and store image/file

In this example, we send the file with a form using the multipart/form-data MIME type and an item id to describe which item this image belongs to, server handles the request with the multer extension
From the received image we need the buffer property. It has the type of Buffer, it is a stream of binary data

router.post("/img/:itemId", upload.single('file'), async (req, res) => {
  let {itemId} = req.params
  let img = req.file
  if (!img) {
    console.error('>>>>> [ERROR] no img!!!')
    res.sendStatus(406)
    return
  }
  await connector() //function that lets us connect the sequelize to the dataBase

  //You are highly advised to have the build and save part of the code inside a picture-controller, outside the router
  //We have it here for the sake of clarity
  let picture = Pictures.build({
    Picture: img.buffer,
    ItemId: itemId
  })
  let saved = await picture.save()
          .catch((err) => {
            console.error('>>>>> [ERROR] could not save image')
            throw err //presuming you are handling your errors at the highest level of the api(api.listen...),
                      // in the index.js with a middleware
            return
          })
  res.json({
    modelInstance: saved
  })
})

Send image back to the client

To send images to the client we first get the buffer from the image after that we can convert it to the Base64 format so we can included in a JSON response.
- SIDENOTE: If you are working with lets say VueJs, the base64 conversion is not at all needed. We needed to convert the buffer to base64 in our project for many different reasons
The drawback is that the base64 conversion takes a lot of processing power.
Suppose we want to get the images that belong to an item, based on the item id in the URL:

const Pictures = require('../models/Pictures') //obviously the path for the Picture model may differ
router.get("/img/:itemId", async (req, res) => {
  const {itemId} = req.params
  await connector() //function that lets us connect the sequelize to the dataBase

  //You are highly advised to have the findAll part of the code inside a picture-controller, outside the router
  //We have it here for the sake of clarity
  const images= await Pictures.findAll({
    include: Items,
    where: {
      ItemId: itemId
    }
  })
  if (images.length === 0){
    res.sendStatus(406)
    return
  }

  //The conversion part is obviously only needed if you need to return base64 as part of your response
  let imgs = convertToBase64(images)

  res.setHeader("Content-Type", "application/json") //!!IMPORTANT!!
  res.json({
    images: imgs,    //if you wannt to return the base64 encoded image array
    pictures: images //if you want to return the images received from the db without converting them to base64
  })
})

function convertToBase64(images){
    outputArray = [] //this will contain the base64 strings
    images.forEach((img) => {
      let arraybuffer= Buffer.from(img.Picture)
      let ret = {
        array: arraybuffer.toString('base64'),
        item: img.Item
      }
      outputArray.push(ret)
    })
    return outputArray
}

The definition of our Item model:

const sequelize = require('sequelize')
const {Model} = require('sequelize')
const Pictures = require('./pictures') //the Picture model

class items extends Model {}

items.init(
    {
        ProductName: {
        type: sequelize.STRING,
        allowNull: false
    }},
    {
        sequelize: _db,
        tableName: 'items',
        modelName: 'items'
    }
)
//if you are using MariaDb you can define the connection by:
//for example while using XAMPP's MySql that technically uses MariaDb
items.hasMany(Pictures, {foreignKey: 'ItemId'})
Pictures.belongsTo(items)

//if you are directly using MySql:
items.hasMany(Pictures, {foreignKey: 'ItemId'})
Pictures.belongsTo(items,{foreignKey: 'ItemId'})
module.exports = items

DEV Community: DezsiCsaba

Store images/files for API, using MySQL/MariaDB, Express and Sequelize

DB side of the story

So what code do we need to write?

Receive and store image/file

Send image back to the client

Sources