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:
1import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
2
3@Entity()
4class DatabaseFile {
5 @PrimaryGeneratedColumn()
6 public id: number;
7
8 @Column()
9 filename: string;
10
11 @Column({
12 type: 'bytea',
13 })
14 data: Uint8Array;
15}
16
17export 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.
1import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
2import DatabaseFile from '../databaseFiles/databaseFile.entity';
3
4@Entity()
5class User {
6 @PrimaryGeneratedColumn()
7 public id: number;
8
9 @Column()
10 public name: string;
11
12 @JoinColumn({ name: 'avatarId' })
13 @OneToOne(
14 () => DatabaseFile,
15 {
16 nullable: true
17 }
18 )
19 public avatar?: DatabaseFile;
20
21 @Column({ nullable: true })
22 public avatarId?: number;
23
24 // ...
25}
26
27export 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:
1import { Injectable } from '@nestjs/common';
2import { InjectRepository } from '@nestjs/typeorm';
3import { Repository } from 'typeorm';
4import User from './user.entity';
5import DatabaseFilesService from '../databaseFiles/databaseFiles.services';
6
7@Injectable()
8export class UsersService {
9 constructor(
10 @InjectRepository(User)
11 private usersRepository: Repository<User>,
12 private readonly databaseFilesService: DatabaseFilesService,
13 ) {}
14
15 async addAvatar(userId: number, imageBuffer: Buffer, filename: string) {
16 const avatar = await this.databaseFilesService.uploadDatabaseFile(imageBuffer, filename);
17 await this.usersRepository.update(userId, {
18 avatarId: avatar.id
19 });
20 return avatar;
21 }
22
23 // ...
24}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:
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { InjectRepository } from '@nestjs/typeorm';
3import { Repository } from 'typeorm';
4import DatabaseFile from './databaseFile.entity';
5
6@Injectable()
7class DatabaseFilesService {
8 constructor(
9 @InjectRepository(DatabaseFile)
10 private databaseFilesRepository: Repository<DatabaseFile>,
11 ) {}
12
13 async uploadDatabaseFile(dataBuffer: Buffer, filename: string) {
14 const newFile = await this.databaseFilesRepository.create({
15 filename,
16 data: dataBuffer
17 })
18 await this.databaseFilesRepository.save(newFile);
19 return newFile;
20 }
21
22 async getFileById(fileId: number) {
23 const file = await this.databaseFilesRepository.findOne(fileId);
24 if (!file) {
25 throw new NotFoundException();
26 }
27 return file;
28 }
29}
30
31export 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.
1import { UsersService } from './users.service';
2import { Controller, Post, Req, UploadedFile, UseGuards, UseInterceptors } from '@nestjs/common';
3import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';
4import RequestWithUser from '../authentication/requestWithUser.interface';
5import { FileInterceptor } from '@nestjs/platform-express';
6import { Express } from 'express';
7
8@Controller('users')
9export class UsersController {
10 constructor(
11 private readonly usersService: UsersService,
12 ) {}
13
14 @Post('avatar')
15 @UseGuards(JwtAuthenticationGuard)
16 @UseInterceptors(FileInterceptor('file'))
17 async addAvatar(@Req() request: RequestWithUser, @UploadedFile() file: Express.Multer.File) {
18 return this.usersService.addAvatar(request.user.id, file.buffer, file.originalname);
19 }
20}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.
1import {
2 Controller,
3 Get,
4 Param,
5 UseInterceptors,
6 ClassSerializerInterceptor,
7 Res,
8 ParseIntPipe,
9} from '@nestjs/common';
10import DatabaseFilesService from './databaseFiles.services';
11import { Readable } from 'stream';
12import { Response } from 'express';
13
14@Controller('database-files')
15@UseInterceptors(ClassSerializerInterceptor)
16export default class DatabaseFilesController {
17 constructor(
18 private readonly databaseFilesService: DatabaseFilesService
19 ) {}
20
21 @Get(':id')
22 async getDatabaseFileById(@Res() response: Response, @Param('id', ParseIntPipe) id: number) {
23 const file = await this.databaseFilesService.getFileById(id);
24
25 const stream = Readable.from(file.data);
26 stream.pipe(response);
27 }
28}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.
1import {
2 Controller,
3 Get,
4 Param,
5 UseInterceptors,
6 ClassSerializerInterceptor,
7 StreamableFile,
8 ParseIntPipe,
9} from '@nestjs/common';
10import DatabaseFilesService from './databaseFiles.services';
11import { Readable } from 'stream';
12
13@Controller('database-files')
14@UseInterceptors(ClassSerializerInterceptor)
15export default class DatabaseFilesController {
16 constructor(
17 private readonly databaseFilesService: DatabaseFilesService
18 ) {}
19
20 @Get(':id')
21 async getDatabaseFileById(@Param('id', ParseIntPipe) id: number) {
22 const file = await this.databaseFilesService.getFileById(id);
23 const stream = Readable.from(file.data);
24
25 return new StreamableFile(stream);
26 }
27}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.
1import {
2 Controller,
3 Get,
4 Param,
5 UseInterceptors,
6 ClassSerializerInterceptor,
7 StreamableFile,
8 Res,
9 ParseIntPipe,
10} from '@nestjs/common';
11import DatabaseFilesService from './databaseFiles.services';
12import { Readable } from 'stream';
13import { Response } from 'express';
14
15@Controller('database-files')
16@UseInterceptors(ClassSerializerInterceptor)
17export default class DatabaseFilesController {
18 constructor(
19 private readonly databaseFilesService: DatabaseFilesService
20 ) {}
21
22 @Get(':id')
23 async getDatabaseFileById(@Param('id', ParseIntPipe) id: number, @Res({ passthrough: true }) response: Response) {
24 const file = await this.databaseFilesService.getFileById(id);
25
26 const stream = Readable.from(file.data);
27
28 response.set({
29 'Content-Disposition': `inline; filename="${file.filename}"`,
30 'Content-Type': 'image'
31 })
32
33 return new StreamableFile(stream);
34 }
35}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:
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { InjectRepository } from '@nestjs/typeorm';
3import { QueryRunner, Repository } from 'typeorm';
4import DatabaseFile from './databaseFile.entity';
5
6@Injectable()
7class DatabaseFilesService {
8 constructor(
9 @InjectRepository(DatabaseFile)
10 private databaseFilesRepository: Repository<DatabaseFile>,
11 ) {}
12
13 async uploadDatabaseFileWithQueryRunner(dataBuffer: Buffer, filename: string, queryRunner: QueryRunner) {
14 const newFile = await queryRunner.manager.create(DatabaseFile, {
15 filename,
16 data: dataBuffer
17 })
18 await queryRunner.manager.save(DatabaseFile, newFile);
19 return newFile;
20 }
21
22 async deleteFileWithQueryRunner(fileId: number, queryRunner: QueryRunner) {
23 const deleteResponse = await queryRunner.manager.delete(DatabaseFile, fileId);
24 if (!deleteResponse.affected) {
25 throw new NotFoundException();
26 }
27 }
28
29 // ...
30}
31
32export default DatabaseFilesService;When we do the above, we can pass the query manager to the above methods from other services:
1import { Injectable, InternalServerErrorException } from '@nestjs/common';
2import { InjectRepository } from '@nestjs/typeorm';
3import { Repository, Connection, In } from 'typeorm';
4import User from './user.entity';
5import DatabaseFilesService from '../databaseFiles/databaseFiles.services';
6
7@Injectable()
8export class UsersService {
9 constructor(
10 @InjectRepository(User)
11 private usersRepository: Repository<User>,
12 private readonly databaseFilesService: DatabaseFilesService,
13 private connection: Connection,
14 ) {}
15
16 async addAvatar(userId: number, imageBuffer: Buffer, filename: string) {
17 const queryRunner = this.connection.createQueryRunner();
18
19 await queryRunner.connect();
20 await queryRunner.startTransaction();
21
22 try {
23 const user = await queryRunner.manager.findOne(User, userId);
24 const currentAvatarId = user.avatarId;
25 const avatar = await this.databaseFilesService.uploadDatabaseFileWithQueryRunner(imageBuffer, filename, queryRunner);
26
27 await queryRunner.manager.update(User, userId, {
28 avatarId: avatar.id
29 });
30
31 if (currentAvatarId) {
32 await this.databaseFilesService.deleteFileWithQueryRunner(currentAvatarId, queryRunner);
33 }
34
35 await queryRunner.commitTransaction();
36
37 return avatar;
38 } catch {
39 await queryRunner.rollbackTransaction();
40 throw new InternalServerErrorException();
41 } finally {
42 await queryRunner.release();
43 }
44 }
45
46 // ...
47}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.