Need to upload file in PostgreSQL database using TypeORM/NestJS

Question

Can anyone please help on this.

I need to upload file into PostgreSQL database using TypeORM/NestJS. file is part of a form.

I have taken below entity class.

export class Certificate {
    @ApiProperty()
    @PrimaryGeneratedColumn()
    @Exclude()
    id: number;
    @ApiProperty()
    @Column({ type: 'varchar', length: 15 })
    statusOfReport: string;

    @ApiProperty()
    @Column({ type: 'varchar', length: 100 })
    sponser: string;

    @ApiProperty()
    @Column({ type: 'varchar', length: 100 })
    address: string;

    @ApiProperty()
    @Column({ type: 'varchar', length: 100 })
    address2: string;
    @ApiProperty()
    @Column()
    zipCOde: string;

    @ApiProperty()
    @Column()
    city: string;

    @ApiProperty()
    @Column()
    protoColNo: string;

    @ApiProperty()
    @Column()
    molecules: string;

    @ApiProperty()
    @Column()
    unAuthMolecule: string;

    @ApiProperty()
    @Column()
    phaseOfTrial: number;

    @ApiProperty()
    @Column()
    noOfSubjects: number;

    @ApiProperty()
    @Column()
    startDate: Date;

    @ApiProperty()
    @Column()
    endDate: Date;

    @ApiProperty()
    @Column()
    personInCharge: string;

    @ApiProperty()
    @Column()
    country: string;

    @ApiProperty()
    @Column()
    comments: string;

    @ApiProperty()
    @Column({ type: 'bytea' })
    attachFile: Uint8Array;

Below is my controller method.

@Post()
create(@Body() createCertificateDto: CreateCertificateDto): Promise<Certificate> {
    return this.certificatesService.create(createCertificateDto);
}

below is my service class method.

async create(createCertificateDto: CreateCertificateDto): Promise<Certificate> {
    return this.certificateRepository.save(createCertificateDto);
}

I am saving file as data. what changes I need to do to upload file in database. file can be excel, pdf, text etc. Existing answers are not helping.

Well, as documented in the manual pg_read_file returns text, not bytea you need to use pg_read_binary_file - but that will only work if Postgres is also running on the computer where you run pgAdmin — user330315
– user330315, Commented Nov 5, 2021 at 7:21

Nsikan Sylvester · Accepted Answer · 2023-05-20 11:24:32Z

In Your Controller:

import {
    Controller,
    Post,
    Body,
    Patch,
    Param,
    Get,
    Delete,
    UsePipes,
    Query,
    Request,
    UseInterceptors, UploadedFile, UseGuards,
    } from '@nestjs/common';
   import {FileInterceptor} from '@nestjs/platform-express';
   import { extname, join } from 'path';
   import { diskStorage } from 'multer';

   @Post()
   @UseInterceptors(FileInterceptor('file', {
     storage: diskStorage({
     destination: './uploads/files',
     filename: (req, file, cb) => {
        const randomName = Array(32).fill(null).map(() => 
       (Math.round(Math.random() * 16)).toString(16)).join('');
         return cb(null, `${randomName}${extname(file.originalname)}`);
         },
       }),
      }))
     create(
     @Body() createCertificateDto: CreateCertificateDto, 
     @UploadedFile() file): Promise<Certificate> {
        return this.certificatesService.create(createCertificateDto, file);
    }

Update Entity to:

 @Column({ type: 'varchar', length: 300, nullable: true })
   attachFile: string;

Or you stick to convert the filename string to ByteArray i.e Uint8Array

Here is the Service:

async create(createCertificateDto: CreateCertificateDto, file): Promise<any> {
console.log(file);
console.log(createCertificateDto);
 try{
     const cert = new Certificate();
        cert.statusOfReport = createCertificateDto?.statusOfReport;
        cert.sponser = createCertificateDto?.sponser;
        cert.address = createCertificateDto?.address;
        cert.address2 = createCertificateDto?.address2;
        cert.zipCOde = createCertificateDto?.zipCOde;
        cert.city = createCertificateDto?.city;
        cert.protoColNo = createCertificateDto?.protoColNo;
        cert.molecules = createCertificateDto?.molecules;
        cert.unAuthMolecule = createCertificateDto?.unAuthMolecule;
        cert.phaseOfTrial = createCertificateDto?.phaseOfTrial;
        cert.noOfSubjects = createCertificateDto?.noOfSubjects;
        cert.startDate = createCertificateDto?.startDate;
        cert.endDate = createCertificateDto?.endDate;
        cert.personInCharge = createCertificateDto?.personInCharge;
        cert.country = createCertificateDto?.country;
        cert.comments = createCertificateDto?.comments;
        cert.attachFile = (file) ? file.filename : '';
        cert.statusOfReport = createCertificateDto?.statusOfReport;
        await cert.save();
        return {success: true, cert};
    } catch (e) {
        return {success: false, message: e.message};
    }
 }

I hope you find this helpful. You can contact me in case of any questions on the implementation.

Jedediah Smith · Accepted Answer · 2021-11-08 20:33:28Z

1

From what I can see you are missing the file decorator on the controller.

@Post()
@UseInterceptors(FileInterceptor('file'))
create(@Body() createCertificateDto: CreateCertificateDto, @UploadedFile() file: any): Promise<Certificate> {
    return this.certificatesService.create(createCertificateDto, file);
}

You may want also want to checkout this StackOverflow to look into the TypeORM side of things.

answered Nov 8, 2021 at 20:33

Jedediah Smith

5784 silver badges11 bronze badges

1 Comment

Shruti sharma Over a year ago

can you please help me with code of service layer as well? Does save allow 2 parameter to add? how to serialize/deserialize file?

Collectives™ on Stack Overflow

Need to upload file in PostgreSQL database using TypeORM/NestJS

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related