How to save & retrive image to/from mysql?

Question

Two part quersion.

Part 1:

Im uploading an image to my server and want to save it to my database.

So far:

table:

resolver:

registerPhoto: inSequence([
  async (obj, { file }) => {
    const { filename, mimetype, createReadStream } = await file;
    const stream = createReadStream();

    const t = await db.images.create({
      Name: 'test',
      imageData: stream ,
    });
  },
])

executing query:

Executing (default): INSERT INTO `images` (`Id`,`imageData`,`Name`) VALUES (DEFAULT,?,?);

But nothing is saved.

Im new to this and im probably missing something but dont know what.

Part2:

This is followed by part 1, lets say I manage to save the image, how do I read it and send it back to my FE?

An edit: Ive read alot of guides saving the an image name to the db and then tha actuall image in a folder. This is NOT what im after, want to save the image to the DB and then be able to fetch it from the DB abd present it.

storing binary data in db always was not a good idea .... stream can be saved in db? ... return url, render it, it's over — xadm, May 19 '20 at 21:27
Im aware but want to save it to the db. a learning experience. Tried converting the stram to a bloob but did not work. Trying to figure this out, any link would be appreciated. — CodingLittle, May 19 '20 at 21:33
eh.... stream can't be saved in db - you have to operate on the whole data (buffer) at once ... memory hungry operation, one of bad reasons :D — xadm, May 19 '20 at 22:44
@xadm, first of your timing is priceless! seconds before I figured out the buffer. As for the reasons...Im building an app where I will store alot of images, and cant see how I would benefit from storing them on the server instead of the DB? articles are much appreciated, I dont mind a good read. — CodingLittle, May 19 '20 at 22:46
store in db info about files stored on disk - benefits of handling data (about files) without (much) worrying where they are (randomized/equally distributed folder structures - instead one maybe big/umnaganeable folder) .... read "why storing files in db is bad" — xadm, May 19 '20 at 22:53
I havent figured out the second part yet. Trying to retrive the image and show it on the page. — CodingLittle, May 20 '20 at 11:34
just return url and use it as a src of rendered img tag - browser will do separate request for it — xadm, May 20 '20 at 11:47
if I understand you correctly, a url to a new resolver method that returns a buffer? Recently found this: https://stackoverflow.com/questions/38503181/how-to-display-a-jpg-image-from-a-node-js-buffer-uint8array Was thinking of giving it a try but it has no mention of a url as you are suggesting. — CodingLittle, May 20 '20 at 11:51
no, url to uploaded file, served statically like `index.html` file, not processed, just returned file content — xadm, May 20 '20 at 11:53
Im still saving the file in the DB and not on the filesystem. I read a few articles but stil want to fullfill what I set out to do so I know how to do it like this aswell even though it isn't best practice. — CodingLittle, May 20 '20 at 11:55
next reason - requires processing, db connection, affecting time and resources (lot of images? prepare for problems).... not resolver (graphql can't return binary data, additional header, etc.), more like service for some `/images/*` urls ... `use(.path..`, convert url path to db param, get data from db, return header, return data ... you can learn, you shouldn't use it [at scale] — xadm, May 20 '20 at 12:05
current im stuck on "String cannot represent value: " when I try to return buffer data as string. — CodingLittle, May 20 '20 at 12:46
@xadm Im stuck on the last part. Apperently it is possible to create a custom scalar that returns a binary from graphql but im unable to find any example on how. Basically asking you for a code example for this if possible. — CodingLittle, May 21 '20 at 14:05

score 1 · Accepted Answer · answered May 30 '20 at 12:05

This took me some time but I finaly figured it out.

First step (saving to the db):

Have to get the entire stream data and read it like this:

export const readStream = async (stream, encoding = 'utf8') => {
  stream.setEncoding('base64');

  return new Promise((resolve, reject) => {
    let data = '';

    // eslint-disable-next-line no-return-assign
    stream.on('data', chunk => (data += chunk));
    stream.on('end', () => resolve(data));
    stream.on('error', error => reject(error));
  });
};

use like this:

const streamData = await readStream(stream);

Before saving I tur the stream into a buffer:

const buff = Buffer.from(streamData);

Finaly the save part:

db.images.create(
            {
              Name: filename,
              imageData: buff,
              Length: stream.bytesRead,
              Type: mimetype,
            },
            { transaction: param }
          );

Note that I added Length and Type parameter, this is needed if you like to return a stream when you return the image.

Step 2 (Retrieving the image).

As @xadm said multiple times you can not return an image from GRAPHQL and after some time I had to accept that fact, hopefully graphql will remedy this in the future.

S What I needed to do is set up a route on my fastify backend, send a image Id to this route, fetch the image and then return it.

I had a few diffirent approaches to this but in the end I simpy returned a binary and on the fronted I encoded it to base64.

Backend part:

const handler = async (req, reply) => {
  const p: postParams = req.params;

  const parser = uuIdParserT();

  const img = await db.images.findByPk(parser.setValueAsBIN(p.id));

  const binary = img.dataValues.imageData.toString('binary');

  const b = Buffer.from(binary);

  const myStream = new Readable({
    read() {
      this.push(Buffer.from(binary));
      this.push(null);
    },
  });
  reply.send(myStream);
};

export default (server: FastifyInstance) =>
  server.get<null, any>('/:id', opts, handler);

Frontend part:

  useEffect(() => {
     // axiosState is the obj that holds the image
    if (!axiosState.loading && axiosState.data) {
      // @ts-ignore
      const b64toBlob = (b64Data, contentType = '', sliceSize = 512) => {
        const byteCharacters = atob(b64Data);
        const byteArrays = [];

        for (let offset = 0; offset < byteCharacters.length; offset += sliceSize) {
          const slice = byteCharacters.slice(offset, offset + sliceSize);

          const byteNumbers = new Array(slice.length);
          // @ts-ignore
          // eslint-disable-next-line no-plusplus
          for (let i = 0; i < slice.length; i++) {
            byteNumbers[i] = slice.charCodeAt(i);
          }

          const byteArray = new Uint8Array(byteNumbers);
          byteArrays.push(byteArray);
        }

        const blob = new Blob(byteArrays, { type: contentType });
        return blob;
      };

      const blob = b64toBlob(axiosState.data, 'image/jpg');

      const urlCreator = window.URL || window.webkitURL;
      const imageUrl = urlCreator.createObjectURL(blob);
      setimgUpl(imageUrl);
    }
  }, [axiosState]);

and finaly in the html:

   <img src={imgUpl} alt="NO" className="imageUpload" />

OTHER: For anyone who is attempting the same NOTE that this is not a best practice thing to do. Almost every article I found saved the images on the sever and save an image Id and other metadata in the datbase. For the exact pros and cons for this I have found the following helpful:

Storing Images in DB - Yea or Nay?

I was focusing on finding out how to do it if for some reason I want to save an image in the datbase and finaly solved it.

you could use base64 directly ... https://stackoverflow.com/a/8499716/6124657 — xadm, May 30 '20 at 14:35

score 0 · Answer 2 · answered May 19 '20 at 21:33

0

There are two ways to store images in your SQL database. You either store the actual image on your server and save the image path inside your mySql db OR you create a BLOB using the image and store it in db. Here is a handy read https://www.technicalkeeda.com/nodejs-tutorials/nodejs-store-image-into-mysql-database

answered May 19 '20 at 21:33

ichaudry

17
1

Please clink the link I attached. It has extensive example on how to configure mySql for blobs and the nodejs script for adding new entries – ichaudry May 19 '20 at 21:53
I have, the problem Im facing is that the link shows how to store files that are used on disk. Mine is uploaed from a frontend ---> backend. fs.readFileSync(file), gives me ERROR TypeError [ERR_INVALID_ARG_TYPE]: The "path" argument must be one of type string, Buffer, or URL. Received type object – CodingLittle May 19 '20 at 21:58
Trying to figure out how to create a BLOB? I dont want to add the image on disk at all. – CodingLittle May 19 '20 at 22:13

score -2 · Answer 3 · answered May 30 '20 at 12:10

-2

you should save the image in a directory and save the link of this image in the database

answered May 30 '20 at 12:10

David

301
1
2
12

How to save & retrive image to/from mysql?

3 Answers3