how insert image data to postgres data base and select the image data from data base

Question

I have convert jpg image to base64img format using base64-img module, that base64img data is stored to postgres in varchar datatype.

can I store image data without conversion?

I'm using the following query to store varchar data.

create table:
CREATE TABLE images (
 name varchar,
base64data varchar
);

insert values:
INSERT INTO image(name,base64data) VALUES ('image1','base64imagedata in string type');

Possible duplicate of [Storing Images in PostgreSQL](https://stackoverflow.com/questions/54500/storing-images-in-postgresql) — Keivan Esbati, Apr 23 '19 at 05:22

Michael Buen · Answer 1 · 2019-04-23T08:12:26.800

Either you do it as text:

CREATE TABLE images (
    name varchar,
    base64data text
);

INSERT INTO image(name, base64data) 
VALUES ('image1','base64imagedata in string type')

With varchar you need to determine the size in advanced. At least with text, it saves you the trouble of determining in advance what's the maximum size for the storage of your base64'd images. text's limit is still 1GB I think, if even you can call that a limit.

Or you do it with bytea:

CREATE TABLE images (
    name varchar,
    base64data bytea
);

INSERT INTO image(name, base64data) 
VALUES ('image1', decode('base64imagedata in string type', 'base64'))

It's better to use the data type that reflects the actual data's type. For image, it's not a sequence of ASCII / Unicode characters, a string/varchar/text. Rather, it's a sequence of all possible values from 0 to 255, a byte array. bytea data type is the proper data type for images.

By the way, it's better not to transport the base64 to the database. Aside from saving bandwidth, it saves the database from performing the decoding. Instead, do the conversion of base64 to byte array on application side. It depends on your data access layer or ORM.

Here's one, on Sequelize (nodejs app): PostgreSQL - How to insert Base64 images strings into a BYTEA column?

Sequelize.BLOB('tiny')

In fact if your image is already from a byte array. You don't even need to convert it to base64 and decode it back on Postgres. You can just merely pass your byte array to your data access layer. An example in .NET: https://docs.huihoo.com/enterprisedb/8.1/dotnet-usingbytea.html

That would save network bandwidth when transporting image. base64 data representation is bigger than raw byte array, and it saves the database some work too. Offload some of the work to application layer

how insert image data to postgres data base and select the image data from data base

1 Answers1