Storing files inside of a PostgreSQL database with Drizzle

December 9, 2024

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.

Now, we can add a table to our database that uses the custom column we created.

database-schema.ts#

import { pgTable, serial, text } 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.

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.

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)
  } // ...
}

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.