error column cannot be null, trying to upload file into sql

Question

i am still new in node js, and i am trying to make some backend with file upload/ image upload function that can be stored in sql, i am trying using multer but it cant read my file while testing in postman body. anybody can help me where i do wrong? here is my controller

const { db } = require('./db');
const bodyParser = require('body-parser');


const getgambar = (req, res) => {
const sqlQuery = "SELECT * FROM gambar";

  db.query(sqlQuery, (err, result) => {
    if (err) {
      console.log(err);
    } else {
      res.send(result);
      console.log(result);
    }
  });
};

const addgambar = (req,res) => {
    
    const idimg = req.body.idimg;
    const gambar = req.file.gambar;

    console.log()
    
    const sqlQuery = "INSERT INTO image (idimg,gambar) VALUE (?,?)";
    
    db.query(sqlQuery, [idimg,gambar], (err, result) => {
      if (err) {
        res.send({
            message: "error",
            err
        })
      } else {
        res.send({
            message: "YES"
        })
      }
    });

};
module.exports = {
    getgambar,
    addgambar,
};

here is my route

const express = require('express');
const router = express.Router();
const multer = require('multer');
const path = require('path');
const ctrl = require('./gambarctrl');

const storange = multer.diskStorage({
    destination: './uploads',
    filename: (req, file, cb) => {
        return cb(null, `${file.fieldname}_${Date.now()}${path.extname(file.originalname)}`)
    }
})

const upload = multer({
    storange: storange
})

router.get('/image/display', ctrl.getgambar)
router.post('/image',upload.single('gambar'), ctrl.addgambar)

module.exports = router;

and here my index

const { db } = require('./db');
const express = require('express');
const bodyParser = require('body-parser')
const cors = require('cors');
const app = express();
const fileUpload = require('express-fileupload');
const gambarroute = require ('./gambarroute');
const multer = require('multer');



app.use(cors());
app.use(express.json());
app.use(bodyParser.urlencoded({ extended: true }));

app.use(gambarroute);

app.listen(3000, () => {
    console.log('on port 3000!');
  });

i am still quite new in node js and i am still searching for tutorial, i appriciate for the help.

What DBMS are you using (eg MySQL, PostgreSQL, etc) and what library are you using to connect to it and execute queries? — Phil
– Phil, Commented Oct 17, 2022 at 2:01

Phil · Accepted Answer · 2022-10-17 07:32:28Z

1

Two problems here...

Multer puts the single uploaded file into req.file so you should use
```
const gambar = req.file; // no `.gambar`
```
Assuming your DB column is a BLOB or BINARY type, you need to provide a Buffer.

Since you're storing the images within the DB, you don't need to use DiskStorage. Use MemoryStorage instead which provides a Buffer out-of-the-box

const upload = multer({
  storage: multer.memoryStorage(), // watch your spelling
})

Then bind the .buffer property in your query.

db.query(sqlQuery, [idimg, gambar.buffer], (err, result) => {
  // ...
});

To respond with the image from Express, use something like this

router.get("/image/display/:id", (req, res, next) => {
  db.query(
    "SELECT `gambar` FROM `image` WHERE `idimg` = ?",
    [req.params.id],
    (err, results) => {
      if (err) {
        return next(err);
      }

      if (!results.length) {
        return res.sendStatus(404);
      }

      // set the appropriate content type
      res.set("Content-Type", "image/jpg");
      res.send(results[0].gambar);
    }
  );
});

and from the frontend...

<img src="http://localhost:3000/image/display/some-id" />

edited Oct 17, 2022 at 7:32

answered Oct 17, 2022 at 2:09

Phil

166k25 gold badges265 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Recometa 5094 Over a year ago

thanks, it solved the problem. but may i ask question ? what is BLOB type? and how to show the image/file if the DB column is BLOB? i am trying to use get all data and it show the some like random text in it. i am sorry for asking some question again. but thank you for helping me solving the problem.

Phil Over a year ago

"what is BLOB type"... see dev.mysql.com/doc/refman/8.0/en/blob.html. I'll update my answer with an example of responding with an image buffer

Recometa 5094 Over a year ago

thank you for the answer, i have some question regarding those display code, what the meading about the content type? and the image/jpg is if jpg only extention or something different?

Recometa 5094 Over a year ago

oh and it is normal when i try it with postman it still show text like "????JFIF??C $.' ",#(7),01444'9=82<.342??C 2!!22222222222222222222222222222222222222222222222222???"????M !1AQaq?"2???R?" is this normal?

Phil Over a year ago

No, Postman should be able to show an image. TBH, I wouldn't recommend storing images in your DB. Store them in the filesystem and save the path in the DB

|

Collectives™ on Stack Overflow

error column cannot be null, trying to upload file into sql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related