API with NestJS #178. Storing files inside of a PostgreSQL database with Drizzle

December 9, 2024

This entry is part 178 of 178 in the API with NestJS

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.

bytea.ts

import { customType } from 'drizzle-orm/pg-core';

export const bytea = customType<{ data: Buffer }>({

dataType() {

return 'bytea';

});

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.

database-schema.ts

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

import { bytea } from './bytea';

export const files = pgTable('files', {

id: serial('id').primaryKey(),

filename: text('filename').notNull(),

mimetype: text('mimetype').notNull(),

data: bytea('data').notNull(),

});

export const databaseSchema = {

files,

};

We will use the mimetype column to store the type of the file.

Let’s generate a migration that creates our table.

1	npx drizzle-kit generate --name add-files-table

0000_add-files-table.sql

CREATE TABLE IF NOT EXISTS "files" (

"id" serial PRIMARY KEY NOT NULL,

"filename" text NOT NULL,

"mimetype" text NOT NULL,

"data" "bytea" NOT NULL

);

Storing the files

We first need to create a service to store the files in our database.

files.service.ts

import { Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

@Injectable()

export class FilesService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(data: Buffer, filename: string, mimetype: string) {

const createdFiles = await this.drizzleService.db

.insert(databaseSchema.files)

.values({

data,

filename,

mimetype,

})

.returning();

return createdFiles.pop();

}

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.

files.controller.ts

import {

Controller,

Post,

UploadedFile,

UseInterceptors,

} from '@nestjs/common';

import { Express } from 'express';

import { FilesService } from './files.service';

import { FileInterceptor } from '@nestjs/platform-express';

@Controller('files')

export class FilesController {

constructor(private readonly filesService: FilesService) {}

@Post()

@UseInterceptors(FileInterceptor('file'))

async create(

@UploadedFile() { originalname, buffer, mimetype }: Express.Multer.File,

) {

const createdFile = await this.filesService.create(

buffer,

originalname,

mimetype,

);

return {

id: createdFile.id,

filename: createdFile.filename,

mimetype: createdFile.mimetype,

};

}

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.

files.service.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

@Injectable()

export class FilesService {

constructor(private readonly drizzleService: DrizzleService) {}

async getById(fileId: number) {

const files = await this.drizzleService.db

.select()

.from(databaseSchema.files)

.where(eq(databaseSchema.files.id, fileId));

const file = files.pop();

if (!file) {

throw new NotFoundException();

}

return file;

}

// ...

}

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.

files.controller.ts

import { Controller, Get, Param, ParseIntPipe, Res } from '@nestjs/common';

import { Response } from 'express';

import { FilesService } from './files.service';

import { Readable } from 'stream';

@Controller('files')

export class FilesController {

constructor(private readonly filesService: FilesService) {}

@Get(':id')

async getById(

@Param('id', ParseIntPipe) id: number,

@Res() response: Response,

) {

const file = await this.filesService.getById(id);

const stream = Readable.from(file.data);

stream.pipe(response);

}

// ...

}

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.

files.controller.ts

import { Controller, Get, Param, ParseIntPipe, Res } from '@nestjs/common';

import { Response } from 'express';

import { FilesService } from './files.service';

import { Readable } from 'stream';

@Controller('files')

export class FilesController {

constructor(private readonly filesService: FilesService) {}

@Get(':id')

async getById(

@Param('id', ParseIntPipe) id: number,

@Res() response: Response,

) {

const file = await this.filesService.getById(id);

const stream = Readable.from(file.data);

response.set({

'Content-Disposition': `inline; filename="${file.filename}"`,

'Content-Type': file.mimetype,

});

stream.pipe(response);

}

// ...

}

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.

files.controller.ts

import {

Controller,

Get,

Param,

ParseIntPipe,

StreamableFile,

Res,

} from '@nestjs/common';

import { Response } from 'express';

import { FilesService } from './files.service';

import { Readable } from 'stream';

@Controller('files')

export class FilesController {

constructor(private readonly filesService: FilesService) {}

@Get(':id')

async getById(

@Param('id', ParseIntPipe) id: number,

@Res({ passthrough: true }) response: Response,

) {

const file = await this.filesService.getById(id);

const stream = Readable.from(file.data);

response.set({

'Content-Disposition': `inline; filename="${file.filename}"`,

'Content-Type': file.mimetype,

});

return new StreamableFile(stream);

}

// ...

}

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.

Series Navigation<< API with NestJS #177. Response serialization with the Drizzle ORM