Co-authored with @dezsicsaba
- 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