API with NestJS #54. Storing files inside a PostgreSQL database

November 1, 2021

This entry is part 54 of 180 in the API with NestJS

In the previous parts of this series, we’ve learned how to upload files to Amazon S3 and store their metadata in our PostgreSQL database. While this is a highly scalable approach, it might be overkill for our application. A great example is storing users’ avatars. They are usually small and simple files, and we might want to avoid additional costs of using services such as Amazon S3. To deal with the above case, we can use PostgreSQL.

Storing binary data in PostgreSQL

One of the ways to store binary data in PostgreSQL is with the bytea column. Due to how it works under the hood, it is appropriate for storing raw data as binary strings.

The SQL standard defines another data type called BLOB. While it works differently, it functions in a very similar manner.

Using the bytea column with TypeORM

Fortunately, the bytea data type is very straightforward to use with TypeORM. Let’s create a new entity to store our data:

databaseFile.entity.ts

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity()

class DatabaseFile {

@PrimaryGeneratedColumn()

public id: number;

@Column()

filename: string;

@Column({

type: 'bytea',

})

data: Uint8Array;

}

export default DatabaseFile;

Uint8Array is very similar to Buffer. If you want to know more about it, check out Node.js TypeScript #3. Explaining the Buffer

Let’s also set up a one-to-one relationship between the user and the above file to store avatars.

user.entity.ts

import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from 'typeorm';

import DatabaseFile from '../databaseFiles/databaseFile.entity';

@Entity()

class User {

@PrimaryGeneratedColumn()

public id: number;

@Column()

public name: string;

@JoinColumn({ name: 'avatarId' })

@OneToOne(

() => DatabaseFile,

{

nullable: true

}

)

public avatar?: DatabaseFile;

@Column({ nullable: true })

public avatarId?: number;

// ...

}

export default User;

Thanks to creating the separate avatarId property, we can get the id of the file even without joining the DatabaseFile table. This neat trick can increase our performance a bit and avoid fetching the binary data unnecessarily.

If you want to know more about relationships, check out API with NestJS #7. Creating relationships with Postgres and TypeORM

We also need to add the appropriate method in the UsersService:

users.service.ts

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

import { InjectRepository } from '@nestjs/typeorm';

import { Repository } from 'typeorm';

import User from './user.entity';

import DatabaseFilesService from '../databaseFiles/databaseFiles.services';

@Injectable()

export class UsersService {

constructor(

@InjectRepository(User)

private usersRepository: Repository<User>,

private readonly databaseFilesService: DatabaseFilesService,

) {}

async addAvatar(userId: number, imageBuffer: Buffer, filename: string) {

const avatar = await this.databaseFilesService.uploadDatabaseFile(imageBuffer, filename);

await this.usersRepository.update(userId, {

avatarId: avatar.id

});

return avatar;

}

// ...

}

Saving the files into the database

Above, we use the DatabaseFilesService to create rows in our table dedicated to storing files. Let’s make the basics of it:

databaseFiles.service.ts

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

import { InjectRepository } from '@nestjs/typeorm';

import { Repository } from 'typeorm';

import DatabaseFile from './databaseFile.entity';

@Injectable()

class DatabaseFilesService {

constructor(

@InjectRepository(DatabaseFile)

private databaseFilesRepository: Repository<DatabaseFile>,

) {}

async uploadDatabaseFile(dataBuffer: Buffer, filename: string) {

const newFile = await this.databaseFilesRepository.create({

filename,

data: dataBuffer

})

await this.databaseFilesRepository.save(newFile);

return newFile;

}

async getFileById(fileId: number) {

const file = await this.databaseFilesRepository.findOne(fileId);

if (!file) {

throw new NotFoundException();

}

return file;

}

export default DatabaseFilesService;

The crucial part above is that we require the user to provide a buffer. We can make it possible by following the NestJS documentation and using FileInterceptor that utilizes the multer library under the hood.

users.controller.ts

import { UsersService } from './users.service';

import { Controller, Post, Req, UploadedFile, UseGuards, UseInterceptors } from '@nestjs/common';

import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';

import RequestWithUser from '../authentication/requestWithUser.interface';

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

import { Express } from 'express';

@Controller('users')

export class UsersController {

constructor(

private readonly usersService: UsersService,

) {}

@Post('avatar')

@UseGuards(JwtAuthenticationGuard)

@UseInterceptors(FileInterceptor('file'))

async addAvatar(@Req() request: RequestWithUser, @UploadedFile() file: Express.Multer.File) {

return this.usersService.addAvatar(request.user.id, file.buffer, file.originalname);

}

To provide us with files, the user needs to perform a request with multipart/form-data. We go in-depth explaining how it works in Node.js TypeScript #6. Sending HTTP requests, understanding multipart/form-data

Retrieving the image

The last step in implementing the basics of managing avatars is a way to retrieve them.

Above, we can see that we expose just the id of the avatar. We now need to create a route that allows our frontend application to fetch the avatar. There are a few ways to implement that. The most straightforward approach would be to use the fact that the response object is a writeable stream.

databaseFiles.controller.ts

import {

Controller,

Get,

Param,

UseInterceptors,

ClassSerializerInterceptor,

Res,

ParseIntPipe,

} from '@nestjs/common';

import DatabaseFilesService from './databaseFiles.services';

import { Readable } from 'stream';

import { Response } from 'express';

@Controller('database-files')

@UseInterceptors(ClassSerializerInterceptor)

export default class DatabaseFilesController {

constructor(

private readonly databaseFilesService: DatabaseFilesService

) {}

@Get(':id')

async getDatabaseFileById(@Res() response: Response, @Param('id', ParseIntPipe) id: number) {

const file = await this.databaseFilesService.getFileById(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

The above approach has a downside, though. When we use the @Res() decorator in the above way and pipe the stream manually, we strip ourselves of some of the features provided by NestJS. Fortunately, NestJS tried to address that by introducing StreamableFile.

databaseFiles.controller.ts

import {

Controller,

Get,

Param,

UseInterceptors,

ClassSerializerInterceptor,

StreamableFile,

ParseIntPipe,

} from '@nestjs/common';

import DatabaseFilesService from './databaseFiles.services';

import { Readable } from 'stream';

@Controller('database-files')

@UseInterceptors(ClassSerializerInterceptor)

export default class DatabaseFilesController {

constructor(

private readonly databaseFilesService: DatabaseFilesService

) {}

@Get(':id')

async getDatabaseFileById(@Param('id', ParseIntPipe) id: number) {

const file = await this.databaseFilesService.getFileById(id);

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

return new StreamableFile(stream);

}

By default, the above results in setting the Content-Type header to application/octet-stream. Because of that, the browser doesn’t recognize it as an image.

databaseFiles.controller.ts

import {

Controller,

Get,

Param,

UseInterceptors,

ClassSerializerInterceptor,

StreamableFile,

Res,

ParseIntPipe,

} from '@nestjs/common';

import DatabaseFilesService from './databaseFiles.services';

import { Readable } from 'stream';

import { Response } from 'express';

@Controller('database-files')

@UseInterceptors(ClassSerializerInterceptor)

export default class DatabaseFilesController {

constructor(

private readonly databaseFilesService: DatabaseFilesService

) {}

@Get(':id')

async getDatabaseFileById(@Param('id', ParseIntPipe) id: number, @Res({ passthrough: true }) response: Response) {

const file = await this.databaseFilesService.getFileById(id);

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

response.set({

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

'Content-Type': 'image'

})

return new StreamableFile(stream);

}

Above, we still inject the response object, but with the passthrough: true option. Thanks to that, we still rely on NestJS to parse the return of the getDatabaseFileById method and return it to the user.

We also set Content-Type to 'image' so that the browser can interpret it correctly. Thanks to setting the Content-Disposition header, the browser can also recognize the filename of our avatar. For example, we can notice that when the user attempts to save the file to the hard drive.

Above, we can see that the browser automatically suggested the file’s name to the user when saving.

Deleting files with transactions

A significant advantage of storing files in the SQL database is that we can manage them within a database transaction. This might come in handy when the user wants to replace an already existing avatar. When this happens, we need to delete the old file first and then upload a new one. Thanks to transactions, we can revert the whole operation when uploading the new avatar fails for some reason.

If you want to know more about transactions, check out API with NestJS #15. Defining transactions with PostgreSQL and TypeORM

The first step in achieving that is allowing the methods in DatabaseFilesService to run with a query manager:

databaseFiles.service.ts

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

import { InjectRepository } from '@nestjs/typeorm';

import { QueryRunner, Repository } from 'typeorm';

import DatabaseFile from './databaseFile.entity';

@Injectable()

class DatabaseFilesService {

constructor(

@InjectRepository(DatabaseFile)

private databaseFilesRepository: Repository<DatabaseFile>,

) {}

async uploadDatabaseFileWithQueryRunner(dataBuffer: Buffer, filename: string, queryRunner: QueryRunner) {

const newFile = await queryRunner.manager.create(DatabaseFile, {

filename,

data: dataBuffer

})

await queryRunner.manager.save(DatabaseFile, newFile);

return newFile;

}

async deleteFileWithQueryRunner(fileId: number, queryRunner: QueryRunner) {

const deleteResponse = await queryRunner.manager.delete(DatabaseFile, fileId);

if (!deleteResponse.affected) {

throw new NotFoundException();

}

// ...

}

export default DatabaseFilesService;

When we do the above, we can pass the query manager to the above methods from other services:

users.service.ts

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

import { InjectRepository } from '@nestjs/typeorm';

import { Repository, Connection, In } from 'typeorm';

import User from './user.entity';

import DatabaseFilesService from '../databaseFiles/databaseFiles.services';

@Injectable()

export class UsersService {

constructor(

@InjectRepository(User)

private usersRepository: Repository<User>,

private readonly databaseFilesService: DatabaseFilesService,

private connection: Connection,

) {}

async addAvatar(userId: number, imageBuffer: Buffer, filename: string) {

const queryRunner = this.connection.createQueryRunner();

await queryRunner.connect();

await queryRunner.startTransaction();

try {

const user = await queryRunner.manager.findOne(User, userId);

const currentAvatarId = user.avatarId;

const avatar = await this.databaseFilesService.uploadDatabaseFileWithQueryRunner(imageBuffer, filename, queryRunner);

await queryRunner.manager.update(User, userId, {

avatarId: avatar.id

});

if (currentAvatarId) {

await this.databaseFilesService.deleteFileWithQueryRunner(currentAvatarId, queryRunner);

}

await queryRunner.commitTransaction();

return avatar;

} catch {

await queryRunner.rollbackTransaction();

throw new InternalServerErrorException();

} finally {

await queryRunner.release();

}

// ...

}

Summary

In this article, we’ve learned a simple way of uploading files to PostgreSQL. While it might not be the most scalable approach, it might prove helpful in situations that could use a simple solution. Besides that, it has some advantages, such as the possibility of implementing transactions and easy backups. Thanks to that, it might prove to be valid, and it is worth knowing.

Series Navigation<< API with NestJS #53. Implementing soft deletes with PostgreSQL and TypeORMAPI with NestJS #55. Uploading files to the server >>

6 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Nestor

3 years ago

Hello I love this series … it is the best thing that I read … I wanted to consult you … I could not make my registration in aws, my sinternational credit card from a Panamanian bank rejected me … do you have any idea of why? in my bank they say that everything is correct … and I buy any product with my card in amazon. I don’t know what I’m doing wrong

Author

Marcin Wanago

Reply to Nestor

Thanks a lot, I’m glad that you enjoy this series. When it comes to your issue, it might be worth contacting AWS support.

catato.developer

2 years ago

I’m getting undefined when accessing file.buffer. Is there any workaround?

UPD:
I turned out that the problem was in my MulterModule initialization. I had specified dest option and that what made buffer be undefined. After I removed it everything became fine.

Last edited 2 years ago by catato.developer

maiky

Good content

Kevin

1 year ago

Hello, Thank you so much for this post, it’s great! But I am having a write after end error from using stream.pipe() in retrieving. I wonder do you have any idea why that might be? Thanks!

Marc

1 month ago

Great blog! I would love to see a Drizzle version of this post. I’m especially interested in streaming the binary data (readable/writeable). How do I do this with Drizzle (postgres)?