Loopback-next: Is possible to select the data from database and export that data using loopback 4?

[AnanthGopal]

i need to select and export data by using loopback 4. If it is possible, can you provide the sample link?

[achrinza]

Hi @AnanthGopal, could you clarify on "export data"? LB4's flexibility allows querying using a repository function (such as .find(), .findById(), etc.) and then use the response of those functions to fit your requirements.

[AnanthGopal]

Do i select data from the database and need to export those data into CSV,

For Ex: I create a csv file based on selected data into a particular file path. i need to download that csv file from UI

[agnes512]

@AnanthGopal Not sure if I understand your question correctly. But one of the basic usages of LB4 is that you can access the db if you set up the datasource properly.

Take MySQL as an example, if you already have your tables defined, LB4 allows you to discover schemas from DBs (ref: https://loopback.io/doc/en/lb3/Discovering-models-from-relational-databases.html). And this can also be done by the CLI lb4 discover --options, see the site. Once you've created a lb4 app and defined your datasource, you can discover models(table) from your database. Once you have those models discovered, you can create repositories and controllers to set up REST APIs to create/query data. LB4 maps the models to the tables of the database.

LoopBack has different connectors for databases. These connectors implement the data exchange logic to communicate with backend systems such as relational or NoSQL databases. You can check the database connectors page.

[AnanthGopal]

The below URL has the download option loopback "https://stackoverflow.com/questions/29372470/downloading-file-from-strongloop-loopback/31389086#31389086", I need this same option in loopback 4

[achrinza]

@AnanthGopal This can be accomplished via the controller:

import {Filter, repository} from '@loopback/repository';
import {
  post,
  param,
  get,
  getFilterSchemaFor,
  Response, // Import REST HTTP Response object
  RestBindings,
} from '@loopback/rest';
import {File} from '../models';
import {FileRepository} from '../repositories';
import {inject} from '@loopback/core';

export class FileController {
  constructor(
    @repository(FileRepository)
    public fileRepository: FileRepository,
    // Inject REST HTTP Response object into controller
    @inject(RestBindings.Http.RESPONSE)
    public response: Response,
  ) {}

  @get('/files/download', {
    responses: {
      '200': {
        description: 'File instance file download',
        content: {
          // See: https://swagger.io/docs/specification/describing-responses/
          'text/csv': {
            schema: {
              type: 'string',
              format: 'binary',
            },
          },
        },
      },
    },
  })
  async downloadById(
    @param.query.object('filter', getFilterSchemaFor(File))
    filter?: Filter<File>,
  ): Promise<string> {
    this.response;
    const files = await this.fileRepository.find(filter);
    let csv = 'Id,Name';
    files.map(x => {
      csv += `\n${x.Id},${x.Value}`;
    });

    // Manipulate response header
    this.response.set(
      'Content-Disposition',
      'attachment; filename="export.csv',
    );

    return csv;
  }
}

^ The above code assumes a File model with 2 columns: Id and Value.

Content-Disposition: attachment is usually enough to encourage a web browser to download the file.

[achrinza]

Related: #2230

[achrinza]

Hi @AnanthGopal, has the above solution helped resolve the issue?

[AnanthGopal]

Hi @AnanthGopal, has the above solution helped resolve the issue?

Thank you that code is working as expected.

Below code used to download static files from the server.

@get('/files/download', {
    responses: {
      '200': {
        description: 'File instance file download',
        content: {
          'text/csv': {
            schema: {
              type: 'string',
              format: 'binary',
            },
          },
        },
      },
    },
  })
  async download(@param.query.string('path') path: string = ""): Promise<any> {
    if (fs.existsSync(path)) {
      let testData = fs.readFileSync(path);
      this.fileResponse;
      let resposne = this.fileResponse.status(200)
        .contentType('application/csv')
        .attachment('test.csv')
        .send(testData);
         fs.unlinkSync(path);
      return JSON.stringify(resposne);
    } else {
      let errors = [];
      errors.push({
        message: 'File Not Exist',
        name: 'UnknownError',
        actualError: 'File Not Exist'
      });
      return JSON.stringify({
        errors: errors
      });
    }