Creating an SVG graph from your SQLite database with serverless

While working on my previous post 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).

Second, install lambda-multipart-parser and use it in the function's handler:

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:

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:

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.

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.
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:

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.

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 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:

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:

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.

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.

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, 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.