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, 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, ) {} 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. Defini