Facing an issue with bytea in drizzle-orm

[ayushmittal0608]

I was using bytea with drizzle-orm to ensure storage of my image files, and when I was designing its schema, I was facing an issue where there was no export named bytea in drizzle-orm/pg-core, however in documentation, it was listed as correct export. I also tried custom bytea export, still it was not working, kindly guide me how to fix this issue.

[wesleysmyth]

The bytea column type support depends on your Drizzle ORM version.

If you are on drizzle-orm 0.36+

bytea is available as a first-class column type:

import { pgTable, bytea, text, uuid } from 'drizzle-orm/pg-core';

const files = pgTable('files', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  data: bytea('data', { mode: 'buffer' }).notNull(),
});

The mode option controls the TypeScript representation:

• mode: 'buffer' — returns Buffer (Node.js), best for binary data
• mode: 'hex' — returns a hex string like \x48656c6c6f

If bytea is not available in your version

Use customType as a fallback:

import { customType, pgTable, text, uuid } from 'drizzle-orm/pg-core';

const bytea = customType<{ data: Buffer; driverData: Buffer }>({
  dataType() {
    return 'bytea';
  },
  toDriver(value: Buffer): Buffer {
    return value;
  },
  fromDriver(value: Buffer): Buffer {
    return Buffer.from(value);
  },
});

const files = pgTable('files', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  data: bytea('data').notNull(),
});

Inserting and reading binary data

// Insert
const imageBuffer = await fs.readFile('./photo.jpg');
await db.insert(files).values({
  name: 'photo.jpg',
  data: imageBuffer,
});

// Read
const [file] = await db.select().from(files).where(eq(files.id, fileId));
fs.writeFileSync('./output.jpg', file.data);

Common gotcha: PostgreSQL driver encoding

If you are getting garbled data, it is likely a driver encoding issue. With node-postgres (pg):

import { types } from 'pg';
// Force bytea to return Buffer instead of escaped string
types.setTypeParser(17, (val: string) => {
  return Buffer.from(val.slice(2), 'hex');
});

With postgres (postgres.js) or @neondatabase/serverless, bytea is handled correctly out of the box.

Recommendation

For images and large files, storing binary data directly in PostgreSQL via bytea works but is not ideal for performance. Consider storing the file in object storage (S3, R2, Supabase Storage) and keeping just the URL in the database. This keeps your database lean and lets the CDN handle delivery.

[MichelBahl]

AFAIK bytea is not available until 1.xx.beta, can you confirm that bytea is available at 36.0.0+ ?

[ayushmittal0608]

Hi @wesleysmyth,

Thank you again for your guidance on using bytea in Drizzle ORM. Following your recommendations, I have currently implemented storage of image URLs in the database while keeping the upload folder encrypted with a key, and the images themselves are encrypted using AES-256 to ensure production-level security.

I wanted to get your opinion on whether using Multer with disk storage is an optimal approach for production, particularly in terms of performance and overall system security.

Thanks again for your continued guidance.