When working with PostgreSQL, we can use the bytea column to store binary data, such as images, PDFs, or other small files. In this article, we explore how to store files in a PostgreSQL database using the Drizzle ORM. We also learn how to stream the data to our users.
Using the bytea column with the Drizzle ORM
Unfortunately, the Drizzle ORM does not support the bytea column natively. Thankfully, we can define a custom type to handle it.
1import { customType } from 'drizzle-orm/pg-core';
2
3export const bytea = customType<{ data: Buffer }>({
4 dataType() {
5 return 'bytea';
6 },
7});By specifying data: Buffer, we state that when the Drizzle ORM fetches the data from the database, it will be a Buffer. Also, TypeScript will ensure that we use a Buffer when we insert the data into the database.
If you want to know more about the Buffer, check out Node.js TypeScript #3. Explaining the Buffer
Now, we can add a table to our database that uses the custom column we created.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2import { bytea } from './bytea';
3
4export const files = pgTable('files', {
5 id: serial('id').primaryKey(),
6 filename: text('filename').notNull(),
7 mimetype: text('mimetype').notNull(),
8 data: bytea('data').notNull(),
9});
10
11export const databaseSchema = {
12 files,
13};We will use the mimetype column to store the type of the file.
Let’s generate a migration that creates our table.
1npx drizzle-kit generate --name add-files-table1CREATE TABLE IF NOT EXISTS "files" (
2 "id" serial PRIMARY KEY NOT NULL,
3 "filename" text NOT NULL,
4 "mimetype" text NOT NULL,
5 "data" "bytea" NOT NULL
6);Storing the files
We first need to create a service to store the files in our database.
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4
5@Injectable()
6export class FilesService {
7 constructor(private readonly drizzleService: DrizzleService) {}
8
9 async create(data: Buffer, filename: string, mimetype: string) {
10 const createdFiles = await this.drizzleService.db
11 .insert(databaseSchema.files)
12 .values({
13 data,
14 filename,
15 mimetype,
16 })
17 .returning();
18
19 return createdFiles.pop();
20 }
21}Our service requires the user to provide the name of the file, its type, and the buffer containing the binary data. To receive that from the user, we need to use the FileInterceptor, which utilizes the multer library under the hood.
1import {
2 Controller,
3 Post,
4 UploadedFile,
5 UseInterceptors,
6} from '@nestjs/common';
7import { Express } from 'express';
8import { FilesService } from './files.service';
9import { FileInterceptor } from '@nestjs/platform-express';
10
11@Controller('files')
12export class FilesController {
13 constructor(private readonly filesService: FilesService) {}
14
15 @Post()
16 @UseInterceptors(FileInterceptor('file'))
17 async create(
18 @UploadedFile() { originalname, buffer, mimetype }: Express.Multer.File,
19 ) {
20 const createdFile = await this.filesService.create(
21 buffer,
22 originalname,
23 mimetype,
24 );
25 return {
26 id: createdFile.id,
27 filename: createdFile.filename,
28 mimetype: createdFile.mimetype,
29 };
30 }
31}To access the Express.Multer.File type, we need to install the @types/multer package.
To send us a file, the user needs to make a request containing the multipart/form-data.
If you want to know more about multipart/form-data, check out Node.js TypeScript #6. Sending HTTP requests, understanding multipart/form-data
Streaming the files
Let’s create a way for our users to receive the stored files. The first step is to add a new method to our service.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5
6@Injectable()
7export class FilesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async getById(fileId: number) {
11 const files = await this.drizzleService.db
12 .select()
13 .from(databaseSchema.files)
14 .where(eq(databaseSchema.files.id, fileId));
15
16 const file = files.pop();
17
18 if (!file) {
19 throw new NotFoundException();
20 }
21
22 return file;
23 }
24
25 // ...
26}Now, we need to create an endpoint that streams the file to the user. The most straightforward approach is to use the response object through the @Res() decorator.
1import { Controller, Get, Param, ParseIntPipe, Res } from '@nestjs/common';
2import { Response } from 'express';
3import { FilesService } from './files.service';
4import { Readable } from 'stream';
5
6@Controller('files')
7export class FilesController {
8 constructor(private readonly filesService: FilesService) {}
9
10 @Get(':id')
11 async getById(
12 @Param('id', ParseIntPipe) id: number,
13 @Res() response: Response,
14 ) {
15 const file = await this.filesService.getById(id);
16 const stream = Readable.from(file.data);
17
18 stream.pipe(response);
19 }
20
21 // ...
22}If you want to know more about writeable streams, check out Node.js TypeScript #5. Writable streams, pipes, and the process streams
To help the browser recognize the file type and treat it accordingly, we should attach additional headers, such as Content-Type and Content-Disposition.
1import { Controller, Get, Param, ParseIntPipe, Res } from '@nestjs/common';
2import { Response } from 'express';
3import { FilesService } from './files.service';
4import { Readable } from 'stream';
5
6@Controller('files')
7export class FilesController {
8 constructor(private readonly filesService: FilesService) {}
9
10 @Get(':id')
11 async getById(
12 @Param('id', ParseIntPipe) id: number,
13 @Res() response: Response,
14 ) {
15 const file = await this.filesService.getById(id);
16 const stream = Readable.from(file.data);
17
18 response.set({
19 'Content-Disposition': `inline; filename="${file.filename}"`,
20 'Content-Type': file.mimetype,
21 });
22
23 stream.pipe(response);
24 }
25
26 // ...
27}Thanks to attaching the filename in the Content-Disposition header, the browser will suggest the correct filename if the user tries to store the file on their drive.
However, when we use the @Res() decorator in the above way, we give up some of the features provided by NestJS. Fortunately, we can deal with that by using the passthrough: true option together with the StreamableFile constructor.
1import {
2 Controller,
3 Get,
4 Param,
5 ParseIntPipe,
6 StreamableFile,
7 Res,
8} from '@nestjs/common';
9import { Response } from 'express';
10import { FilesService } from './files.service';
11import { Readable } from 'stream';
12
13@Controller('files')
14export class FilesController {
15 constructor(private readonly filesService: FilesService) {}
16
17 @Get(':id')
18 async getById(
19 @Param('id', ParseIntPipe) id: number,
20 @Res({ passthrough: true }) response: Response,
21 ) {
22 const file = await this.filesService.getById(id);
23 const stream = Readable.from(file.data);
24
25 response.set({
26 'Content-Disposition': `inline; filename="${file.filename}"`,
27 'Content-Type': file.mimetype,
28 });
29
30 return new StreamableFile(stream);
31 }
32
33 // ...
34}Summary
In this article, we’ve explored a simple way of uploading files to PostgreSQL and streaming them to our users using PostgreSQL, NestJS, and the Drizzle ORM.
Using the bytea column in PostgreSQL makes it very simple to store binary data. This approach avoids relying on external services such as S3 from AWS. It also allows us to take advantage of database features such as transactions and backups. Unfortunately, using the bytea column to store large files can reduce our performance. Therefore, we should avoid using the bytea column for large files or when performance and scalability are critical.