Export related data as CSV

This example shows you how to create a Smart Action "Export orders as CSV" that allows a user to download all the orders associated to a customer.

Requirements

• The json2CSV npm package

$ npm install json2csv

How it works

Directory: /models

This directory contains:

• the customers.js file where the customers model is declared.

• the orders.js file where the orders model is declared

customers.js

module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  const Customers = sequelize.define('customers', {
    firstname: {
      type: DataTypes.STRING,
    },
    lastname: {
      type: DataTypes.STRING,
    },
    email: {
      type: DataTypes.STRING,
    },
    createdAt: {
      type: DataTypes.DATE,
    },
    updatedAt: {
      type: DataTypes.DATE,
    },
  }, {
    tableName: 'customers',
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

  Customers.associate = (models) => {
    Customers.hasMany(models.orders, {
      foreignKey: {
        name: 'customerIdKey',
        field: 'customer_id',
      },
      as: 'orders',
    });
  };

  return Customers;
};

orders.js

module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  const Orders = sequelize.define('orders', {
    ref: {
      type: DataTypes.STRING,
      primaryKey: true,
      defaultValue: Sequelize.literal('nextval(orders_id_seq::regclass)'),
      allowNull: false,
    },
    shippingStatus: {
      type: DataTypes.ENUM(['In transit', 'Shipped', 'Being processed', 'Ready for shipping']),
    },
    createdAt: {
      type: DataTypes.DATE,
    },
    updatedAt: {
      type: DataTypes.DATE,
    },
    beingProcessedAt: {
      type: DataTypes.DATE,
    },
    readyForShippingAt: {
      type: DataTypes.DATE,
    },
    inTransitAt: {
      type: DataTypes.DATE,
    },
    shippedAt: {
      type: DataTypes.DATE,
    },
  }, {
    tableName: 'orders',
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

  Orders.associate = (models) => {
    Orders.belongsTo(models.customers, {
      foreignKey: {
        name: 'customerIdKey',
        field: 'customer_id',
      },
      as: 'customer',
    });
  };

  return Orders;
};

Directory: /forest

This directory contains the customers.js file where the smart action Export orders as CSV is declared.

You need to set the attribute download as true to enable a download

customers.js

const { collection } = require('forest-express-sequelize');

collection('customers', {
  actions: [
    {
      name: 'Export orders as CSV',
      type: 'single',
      download: true,
    },
  ],
  fields: [],
  segments: [],
});

Directory: /utils

This directory contains a csv-exporter.js file where the method to export the orders as CSV is declared.

The json2csv package allows you to format the data exported. It can be useful in particular when you have fields of the type JSON that you may want to unwind. You can take a look at the json2csv documentation here.

csv-exporter.js

const { parse } = require('json2csv');

function exportCustomerOrdersAsCSV(response, data) {
  // set the response header to tell the browser to expect a csv
  response.setHeader('Content-Type', 'text/csv');
  response.setHeader('Content-Disposition', `attachment; filename=cust-${data[0].customerIdKey}-orders.csv`);
  response.setHeader('Access-Control-Expose-Headers', 'Content-Disposition');
  // list the fields of the orders that you want to display as columns of the csv
  const fields = [
    'ref',
    'shippingStatus',
    'createdAt',
    'UpdatedAt',
    'beingProcessedAt',
    'readyForShippingAt',
    'inTransitAt',
    'shippedAt',
  ];
  // convert the array of records into a csv
  try {
    const csv = parse(data, { fields });
    return response.send(csv);
  } catch (err) {
    return response.status(500).json({ err });
  }
}

module.exports = exportCustomerOrdersAsCSV;

Directory: /routes

This directory contains the customers.js file where the logic of the smart action is implemented.

customers.js

const express = require('express');
const { PermissionMiddlewareCreator } = require('forest-express-sequelize');
const { orders } = require('../models');
const exportCustomerOrdersAsCSV = require('../utils/csv-exporter');


const router = express.Router();
const permissionMiddlewareCreator = new PermissionMiddlewareCreator('customers');

router.post('/actions/export-orders-as-csv', permissionMiddlewareCreator.smartAction(), async (req, res) => {
  // Get the current record id
  const recordId = req.body.data.attributes.ids[0];
  // get an array of records that are the orders of the customer and export them
  return orders.findAll({ where: { customerIdKey: recordId } })
    .then((data) => exportCustomerOrdersAsCSV(res, data));
});

module.exports = router;