Saving Images in Database MySQL

Can I store images in MySQL

Yes, you can store images in the database, but it's not advisable in my opinion, and it's not general practice.

A general practice is to store images in directories on the file system and store references to the images in the database. e.g. path to the image,the image name, etc.. Or alternatively, you may even store images on a content delivery network (CDN) or numerous hosts across some great expanse of physical territory, and store references to access those resources in the database.

Images can get quite large, greater than 1MB. And so storing images in a database can potentially put unnecessary load on your database and the network between your database and your web server if they're on different hosts.

I've worked at startups, mid-size companies and large technology companies with 400K+ employees. In my 13 years of professional experience, I've never seen anyone store images in a database. I say this to support the statement it is an uncommon practice.

Storing images in MySQL

Is there a particular reason why you can't store a reference to the image, rather than the actual image?

Images are big—they make databases big. SQL can be painful (as it's not object-oriented) and doesn't have an image type.

You could store them as a BLOB... if you want to write all the coding, encoding, checking, etc.

What is the best way of storing image in mysql db

Don't store your image in a blob or anything.
Save the images somewhere on disk, with a reference to the image location in the database.
This way it is a lot more flexible and your database size stays small.

(Assuming that you're using it for a website) it is also faster, since it reduces database requests. If you store the image as a blob the browser would send a http-request for each image, each resulting in a query to the database. When storing it on disk the webserver handles it directly (as a static file) without the need to request the database, so you'd have the advantage of Nginx (if using it) being able to handle static files fast.

Saving images to MySQL Database? Yes? Or not?

A few comments:

You seem to be using date('d.m.Y') I guess to get the current date. You're using it as a PHP function, but it's inside an SQL statement. I suggest you use CURDATE() if you put it inside the SQL statement. If you use the PHP function, do it outside the SQL statement and bind the result as a parameter.

Your code stores both the img content and the path to the tmpfile for the upload. I don't see the need for this. Pick one or the other. An uploaded tmpfile is removed at the end of the PHP request, so I don't know why you would store that path.

Don't use addslashes() for content you're binding to a query parameter. This will store the literal slashes in your content. Since your image file is not text, it will add slashes before binary bytes that happen to be the same as ASCII character \ (0x5C). You don't need slashes anyway when using bound parameters, even if it's for text.

See https://www.php.net/manual/en/mysqli-stmt.send-long-data.php for the right way to store larger data content via bound parameter. This allows files that are larger than MySQL's max_allowed_packet buffer, because it sends the content in chunks.

$query = "INSERT INTO `produkt` (`id`, `date`, `img`)
          VALUES('', CURDATE(), ?)";

// prepare query 
$stmt = $conn->prepare($query);

//  bind  params
$null = NULL;
$stmt->bind_param("b", $null);
$fp = fopen($_FILES['img']['tmp_name'], "r");
while (!feof($fp)) {
    $stmt->send_long_data(0, fread($fp, 8192));
}
fclose($fp);

// execution of the prepared statement
$stmt->execute();

Are you asking whether it's better to store the image or the path? There is no universal rule about this. Many developers would say, "never store big images and other files in the database" because it makes the database larger.

But there are good reasons to do that sometimes, such as:

If you delete a row, it will atomically delete the image stored on that row. Whereas if you store it outside the database, then you need to remember to delete any orphaned images when you delete a row in the database.
Data in the database obeys transaction semantics. If you insert/update/delete a row that references an image, the change is isolated until you commit. You may also choose to rollback the transaction. You can't do that with files on the filesystem.
A database backup will collect all the image data along with row data, and this will be atomic. If you keep image files outside the database, a data backup must be done in two steps, and you always worry if you got the images that are true for that database snapshot.

What is the efficient way to store an image in database?

option 2 for me because, for what I have read from other articles, converting the image to blob would make the process slower. unlike on storing the path you just retrieve it's path string to the database and retrieve much faster.

here are other related articles to that Storing Images in DB - Yea or Nay?

How can I store and retrieve images from a MySQL database using PHP?

First you create a MySQL table to store images, like for example:

create table testblob (
    image_id        tinyint(3)  not null default '0',
    image_type      varchar(25) not null default '',
    image           blob        not null,
    image_size      varchar(25) not null default '',
    image_ctgy      varchar(25) not null default '',
    image_name      varchar(50) not null default ''
);

Then you can write an image to the database like:

/***
 * All of the below MySQL_ commands can be easily
 * translated to MySQLi_ with the additions as commented
 ***/ 
$imgData = file_get_contents($filename);
$size = getimagesize($filename);
mysql_connect("localhost", "$username", "$password");
mysql_select_db ("$dbname");
// mysqli 
// $link = mysqli_connect("localhost", $username, $password,$dbname); 
$sql = sprintf("INSERT INTO testblob
    (image_type, image, image_size, image_name)
    VALUES
    ('%s', '%s', '%d', '%s')",
    /***
     * For all mysqli_ functions below, the syntax is:
     * mysqli_whartever($link, $functionContents); 
     ***/
    mysql_real_escape_string($size['mime']),
    mysql_real_escape_string($imgData),
    $size[3],
    mysql_real_escape_string($_FILES['userfile']['name'])
    );
mysql_query($sql);

You can display an image from the database in a web page with:

$link = mysql_connect("localhost", "username", "password");
mysql_select_db("testblob");
$sql = "SELECT image FROM testblob WHERE image_id=0";
$result = mysql_query("$sql");
header("Content-type: image/jpeg");
echo mysql_result($result, 0);
mysql_close($link);

How to save & retrive image to/from mysql?

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.