How to upload a base64 encoded image and save to mysql database?

Question

Currently, I am encoding images in a Vue.js application to base64 and save them in a MySQL database using feathers.js and feathers-objection. The data type in the table is LONGTEXT, which works fine for small images.

Sometimes, if the image is larger (>1MB) I get a Data too long for column error from the database driver mysql2.

In the feathers documentation they are saving images to a blob storage, like google drive, which unfortunately is no option for this use case.

The table definition is done via knex.js:

exports.up = function (knex) {
  return knex.schema
    .createTable("attachments", (table) => {
      table.increments("id");
      table.integer("ticketId");
      table.text("uri", "LONGTEXT");
      table.integer("size");
      table.string("type");
      table.string("name");
      table.timestamp("createdAt").nullable();
      table.timestamp("updatedAt").nullable();
    })
    .then(() => console.log("Created attachments table")) // eslint-disable-line no-console
    .catch((e) => console.error("Error creating attachments table", e)); // eslint-disable-line no-console
};

Are there some other options, e.g. datatypes to save longer base64 encoded URIs?

score 2 · Accepted Answer · answered Oct 18 '22 at 14:20

I've looked at the code of knex.js and found this:

  text(column) {
    switch (column) {
      case 'medium':
      case 'mediumtext':
        return 'mediumtext';
      case 'long':
      case 'longtext':
        return 'longtext';
      default:
        return 'text';
    }
  }

In other words, the text input is NOT case insensitive, so the following should create a column with a LONGTEXT type:

table.text("uri", "longtext"); // or just "long"

What happened is that the text type in your instruction defaulted to normal TEXT although you thought it was LONGTEXT. So when you've tried to store an image that was longer than 64 Kilobytes, you've got an error.

To know which type you want to use here is a table:

Type	Approx. Length	Exact Max. Length Allowed
TINYTEXT	256 Bytes	255 characters
TEXT	64 Kilobytes	65,535 characters
MEDIUMTEXT	16 Megabytes	16,777,215 characters
LONGTEXT	4 Gigabytes	4,294,967,295 characters

P.S. Storing images in databases is still not the best option and has a few downsides. So you may want to have the possibility to switch later.

How to upload a base64 encoded image and save to mysql database?

1 Answers1