Creating an SVG graph from your SQLite database with serverless
Tags: serverless, node, awslambda
Published: 2020-08-14 17:55:33

---
title: Creating an SVG graph from your SQLite database with serverless
published: true
description: Tutorial on how to create a serverless function that returns an SVG graph from your SQLite database
tags: serverless, nodejs, awslambda
cover_image: https://imgur.com/grJ7sf8.jpg
---



<table>
<tbody><tr><td align="center">Table of contents</td></tr>
  <tr>
    <th>1. <a href="#sending-a-binary-file-to-aws-lambda">Sending a binary file to an AWS Lambda function</a>
</th>
  </tr>
<tr>
    <th>2. <a href="#svg-generation">SVG generation</a>
  </th>
</tr>
<tr>
    <th>3. <a href="#sending-svg-as-response">Sending SVG as response</a>
</th>
  </tr>
<tr>
    <th>4. <a href="#putting-it-all-together">Putting it all together</a> </th>
  </tr>
<tr>
    <th>5. <a href="#next-steps">Next steps</a>
</th>
  </tr>
</tbody></table>

While working on my [previous post](https://dev.to/a0viedo/handling-sensitive-fields-with-sequelize-js-54lo) I stumbled into great tools for visualizing SQLite databases into SVG graphs, but some of their drawbacks was that I needed to install something first. I started to wonder if I could make it easier, with no dependencies required at all.

I will split the work required for this project into three parts:

- sending a binary file to an AWS Lambda function
- SVG generation from a SQLite file stored in the filesystem
- given an existing SVG on the filesystem, getting the same SVG as a request response

Thinking and coding it separately will help by having smaller tasks that can be independently verified.

## Sending a binary file to AWS Lambda
For this step we want to allow multipart uploads into our serverless function. First, make sure you have enabled binary support in your API Gateway (see [step-by-step here](https://docs.aws.amazon.com/apigateway/latest/developerguide/api-gateway-payload-encodings-configure-with-console.html)). 

![screenshot from API Gateway console](https://dev-to-uploads.s3.amazonaws.com/i/72onpiik7n0ei7g7h7el.png)
<figcaption>
Screenshot of my API Gateway configuration for this project
</figcaption>

Second, install [lambda-multipart-parser](https://www.npmjs.com/package/lambda-multipart-parser) and use it in the function's handler:

```js
const parser = require('lambda-multipart-parser');
module.exports.handler = async (event, context) => {
  const result = await parser.parse(event);
  console.log('multipart files:', result.files);
  return {
    statusCode: 200
  };
};
```

Here's a minimal `serverless.yml` file for our function:
```yaml
service: sls-sqleton

provider:
  name: aws
  runtime: nodejs12.x

functions:
  binary-upload:
    handler: index.handler
    events:
      - http:
          path: /binary-upload
          method: POST
```

Deploy the function. If everything went ok, when executing the function you should see something like the following:

{% asciinema 353421 %}

### SVG generation
For this step we will assume the SQLite file is safely stored in our filesystem (or AWS's filesystem but you get what I mean). The tool I will be using for the SVG generation is [sqleton](https://github.com/inukshuk/sqleton).

There is one requirement that `sqleton` lists on its README and that is the `graphviz` library. I searched the web to see if there was an existing Lambda layer or a statically linked binary that I could just import in my function to get `sqleton` working but the ones I found were compiled with really old versions of graphviz, like [graphviz-lambda](https://github.com/chrisns/graphviz-lambda).
The amount of effort and trial and error to get graphviz working on AWS Lambda was totally unexpected. For that reason I think it deserves its own article and for the purpose of this post I'll just assume it's working.
For an initial test `sqleton` with some hardcoded parameters:

```js
module.exports.svgGeneration = async (event, context) => {
  console.log(execSync(`ls -lha`, { encoding: 'utf8' }));

  await generateSVGFromSqliteFile2('test.sqlite', '/tmp/result.svg');
  console.log(execSync(`ls -lha /tmp`, { encoding: 'utf8' }));
  return {
    statusCode: 200
  };
}

function generateSVGFromSqliteFile(sqliteFilePath, svgFilename) {
  return new Promise(async (resolve, reject) => {
    const db = new sqlite.Database(sqliteFilePath, sqlite.OPEN_READONLY, async error => {
      if (error) {
        console.log('error', error);
        return;
      }
    
      let format = extname(svgFilename).slice(1);
      let stream, proc;
      if (format !== 'dot') {
        proc = spawn('dot_static', [`-T${format}`, `-o${svgFilename}`]);
        stream = proc.stdin;
      }

      await sqleton(db, stream, {});
      db.close();
      stream.end();
      proc.on('close', async (code) => {
        resolve();
      });
      proc.on('error', err => {
        reject(err);
      });
    });
  });
}
```

After deploying and invoking the function we should be able to confirm on the `ls` output that the SVG file was generated.

{% asciinema 353436 %}

### Sending SVG as response
Since we mentioned being able to verify each part individually we will not use the generated SVG but will upload one specifically for this purpose.

In the root of the project I created the `test.svg` file which displays a simple red dot:
```svg
<svg height="100" width="100" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink">
  <circle cx="50" cy="50" r="40" stroke="black" stroke-width="3" fill="red"/>
</svg>
```

In order to return it as a Buffer response in the body we will need to load it into memory first:

```js
const fs = require('fs');
const readFile = util.promisify(fs.readFile);
module.exports.handler = async (event, context) => {
  const svgFile = await readFile('test.svg', { encoding: 'utf8' });
  return {
    headers: {
      'Content-Type': 'image/svg+xml',
    },
    statusCode: 200,
    body: svgFile
  };
};
```

After deploying this part of the function you could use `curl` or even Postman (which should render the red dot) to verify that the response is working as expected.

## Putting it all together
After we've verified all three parts are working as expected we can start glueing them together.
To avoid filename collisions we will be using `context.awsRequestId` as the name for the SVG files being stored:

```js
module.exports.generateSVG = async (event, context) => {
  const svgFilename = `/tmp/${context.awsRequestId}.svg`;
  const result = await parser.parse(event);

  const sqliteFilePath = `/tmp/${context.awsRequestId}.sqlite`;
  await writeFile(
    sqliteFilePath,
    result.files[0].content,
    { encoding: 'base64' }
  );
  const svgContent = await generateSVGFromSqliteFile(
  sqliteFilePath,
  svgFilename,
  qsParams);
  return {
    statusCode: 200,
    headers: {
      'Access-Control-Allow-Origin': '*',
      'Access-Control-Allow-Credentials': true,
      'Content-Type': 'image/svg+xml',
    },
    body: svgContent
  }
};
```

I built a _beautiful_ frontend that uses the serverless function [here](https://elendur.com/sls-sqleton-frontend/).

![GIF doing a demo](https://dev-to-uploads.s3.amazonaws.com/i/jl3vlh56j8b76l8sctmw.gif)

As best-practice, every time you store files in the `/tmp` directory within an AWS Lambda execution remember to clean up before exiting.
Also, have in mind that the payload limit for AWS Lambda is [6mb](https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-limits.html).

## Next steps
Given that `sqleton` offers some flexibility on the parameters you can use for the SVG generation (like the font or to enable edge labels) it would be nice to add those inputs in the frontend. 
After I started working on this project I learnt that graphviz was being used for **lots of cool projects** like [GraphvizOnline](https://github.com/dreampuf/GraphvizOnline), so I might end up wrapping the library in its own serverless function that receives commands and returns images as the result.
For now, feel free to copy the `layers/graphviz` directory of the repo or you could also use this public ARN `arn:aws:lambda:us-east-1:332013964457:layer:graphviz:11`.

{% github a0viedo/sls-sqleton %}