DEV Community: Chinedu Ogama

Send an Excel File as an attachment via Postmark Template API in NestJS.

Chinedu Ogama — Mon, 24 Mar 2025 14:16:39 +0000

Data is important to a business's success as it provides valuable insights into customer behaviour and preferences. This empowers organizations by helping them better understand their customers, influence product decisions, and identify areas for improvement. Data export is a common product feature that helps provide the required data today. Excel files are the most commonly exported file type.

This article will walk you through how to use NestJS to handle an export data request, excel file generation and email delivery. The request generates an Excel file that is sent as an email attachment through the Postmark template API. The article assumes you have an existing Nestjs application with bull queues already configured and a postmark account with API access.

The first step is to install the required packages which are exceljs and postmark.

npm install exceljs
npm install postmark

Create your report dto report.dto.ts.

import { IsNotEmpty } from 'class-validator';

export class ReportDto {
  @IsNotEmpty()
  startDate: Date;

  @IsNotEmpty()
  endDate: Date;
}

Update your controller to handle the request and response.

//report.controller.ts

  @Post('/')
  @HttpCode(HttpStatus.OK)
  async getReport(@Body() payload: ReportDto) {
    const response = await this.service.createReport(payload);
    return { message: response };
  }

The next step is to update the service and create the createReport function that is called on the report.controller.ts.

//report.service.ts

import { Injectable } from '@nestjs/common';
import { ReportDto } from './dto/report.dto';
import { InjectQueue } from '@nestjs/bull';
import { Queue } from 'bull';

@Injectable()
export class ReportService {
  constructor(@InjectQueue('report') private queue: Queue) {}

  async createReport(payload: ReportDto) {
    const startDate = new Date(payload.startDate);
    const endDate = new Date(payload.endDate);
    startDate.setHours(0, 0, 0, 0);
    endDate.setHours(23, 59, 59, 999);

    const queueData = {
      startDate: startDate,
      endDate: endDate,
    };

    await this.queue.add('report.generate-user-report', queueData);
    return 'Your report is on its way to your inbox';
  }
}

The code above converts the payload dates to the start of the day for the startDate and the end of the day for endDate. The converted dates are sent as data for the queue.

Here is the consumer class that handles the report queue.

//report.consumer.ts
import {
  OnQueueActive,
  OnQueueCompleted,
  Process,
  Processor,
} from '@nestjs/bull';
import { ConfigService } from '@nestjs/config';
import { Job } from 'bull';
import * as ExcelJS from 'exceljs';
import * as Postmark from 'postmark';
import { PrismaService } from '../prisma/prisma.service';

@Processor('report')
export class ReportConsumer {
  private readonly client = new Postmark.ServerClient(
    this.configService.getOrThrow<string>('POSTMARK_SECRET'),
  );

  constructor(
    private readonly prismaService: PrismaService,
    private readonly configService: ConfigService,
  ) {}

  @OnQueueActive()
  onActive(job: Job) {
    console.log(`Processing job ${job.id} of type ${job.name}`);
  }

  @OnQueueCompleted()
  onCompleted(job: Job) {
    console.log(`Processed job ${job.id} of type ${job.name}`);
  }
}

Add the generateUserReport to handle the report generation.

//report.consumer.ts

 @Process('report.generate-user-report')
  async generateUserReport(job: {
    data: {
      startDate: Date;
      endDate: Date;
    };
  }) {
    const { startDate, endDate } = job.data;
    // get registered user within the provided time frame
    const users = await this.prismaService.user.findMany({
      select: {
        firstname: true,
        lastname: true,
        email: true,
        createdAt: true,
      },
      where: {
        createdAt: {
          gt: startDate,
          lte: endDate,
        },
      },
    });

    //generate rows for the excel sheet
    const userRows = users.map((user) => {
      return {
        firstname: user.firstname,
        lastname: user.lastname,
        email: user.email,
        signup_date: user.createdAt,
      };
    });

    // create excel workbook and add headers
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Users Report');
    worksheet.columns = [
      { header: 'firstname', key: 'firstname', width: 32 },
      { header: 'lastname', key: 'lastname', width: 32 },
      { header: 'Email', key: 'email', width: 30 },
      { header: 'Signup Date', key: 'signup_date', width: 10 },
    ];

    worksheet.addRows(userRows);

    // writes the workbook to a buffer
    const report = await workbook.xlsx.writeBuffer();

    // send the mail to user
    await this.sendMail(report, startDate, endDate);
  }

The next step is to add the function for sending the email.

async sendMail(report: ExcelJS.Buffer, startDate: Date, endDate: Date) {
    try {
      // data for the postmark template
      const emailData = {
        startDate: new Date(startDate).toISOString().split('T')[0],
        endDate: new Date(endDate).toISOString().split('T')[0],
      };

      // send an email with the generated sheet as an attachment
      const sendMail = await this.client.sendEmailWithTemplate({
        From: this.configService.getOrThrow<string>('ADMIN_EMAIL'),
        To: 'test@dev.to',
        TemplateAlias: this.configService.getOrThrow<string>(
          'POSTMARK_TEMPLATE__REPORT',
        ),
        TemplateModel: emailData,
        Attachments: [
          {
            Name: 'user_report.xlsx',
            ContentID: new Date().toString(),
            Content: Buffer.from(report).toString('base64'),
            ContentType:
              'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
          },
        ],
      });

      if (sendMail.ErrorCode !== 0) {
        throw new Error('Message failed to deliver');
      }
    } catch (error) {
      console.log(error?.message || error);
    }
  }

The final step is to update your env file with the required variables.

POSTMARK_TEMPLATE__REPORT=test-user-report
POSTMARK_SECRET=********-****-****-****-*******
ADMIN_EMAIL=no-reply@dev.to

The postmark template value is the alias of the templatecreated on Postmark, while the secret is the Postmark API key.

This article gives a top-level implementation for the feature. It can always be optimized to improve performance and meet specific needs.

How to deploy a Laravel/Vue App to Heroku

Chinedu Ogama — Wed, 01 Jan 2020 08:15:13 +0000

In this article, I will be showing you how to deploy a Laravel/Vue application to Heroku; a container-based cloud Platform as a Service (PaaS), which developers use to deploy, manage and scale modern apps.

Prerequisites:

• PHP and Laravel Knowledge
• Heroku User Account
• Heroku CLI (Download here)
• Git (get git here)

This article assumes that you have an existing Laravel/Vue application on your local server which is ready for deployment

Step 1: Initialize a git
Initialize a git repository in your current working project directory with git init command

Step 2: Create a Procfile
In your project directory create a Procfile without an extension and add this line web: vendor/bin/heroku-php-apache2 public/ . The Procfile can also be created and updated through the terminal, to do this, run echo "web: vendor/bin/heroku-php-apache2 public/" > Procfile command on your terminal

Step 3: Create a new application on Heroku
In other to create a new application on Heroku where you can push your application to, use the heroku create command. When this is done a random name will be automatically chosen for your application. To change this name use heroku apps:rename newAppName command. Replace “newAppName” with your preferred new name.

Step 4: Enable node.js
You need to enable node.js in other to run commands like npm install and npm production. To do this you need to add heroku/nodejs build pack using heroku buildpacks:add heroku/nodejs command. With this, the node dependencies in your package.json file will be installed on deployment but it won’t install any of your devDependencies. To solve this you need to set an environment variable to tell Heroku to install all dependencies including devDependencies using heroku config:set NPM_CONFIG_PRODUCTION=false command then add postinstall in package.json scripts
"scripts": { "postinstall": "npm run prod" }

Step 5: Setup a Laravel encryption key
To set up your Laravel encryption key copy the APP_KEY environment value from your .env file and run heroku config:set APP_KEY=”Your app key” or you can generate a new one and set it as your new key using heroku config:set APP_KEY=$(php artisan --no-ansi key:generate --show) command.

Step 6: Push to Heroku
Commit the current state of your application with git and push to Heroku with git push heroku master

Step 7: Ensure that your application is using the right Heroku buildpacks
You need to ensure that your application is using the right buildpacks. To do this run the heroku buildpacks command. If you have heroku/php and heroku/nodejs listed you are good to go.

If you can’t find any, run heroku buildpacks:add [‘missing build’] command, replace the [‘missing build’] with the buildpack you wish to install and push to Heroku.

Step 8: Your app should be up and running. To view it, navigate to the address in your browser

To set the environment variables for your application you can do that using your terminal with the heroku config:set VAR_NAME=VAR_VALUE command or through your dashboard in the settings tab, click on Reveal config vars to see and set environment variables.

Heroku provides you the option to use postgres sql free. To use this run the command:

heroku addons:create heroku-postgresql:hobby-dev

Set DB_CONNECTION to pgsql through your dashboard in the settings tab, click on Reveal config vars to see environment variable.

To get DB credentials for your application click on the
Heroku Postgres Hobby Dev installed addon on the overview tab on your dashboard, this will open a new browser tab. The DB credentials can be found through the settings tab of the new browser tab.

Note:To run your regular artisan or npm commands on heroku, precede all statements with heroku run e.g. heroku run php artisan storage:link or heroku run npm install