DEV Community

Cover image for Type-safe S3 Select queries with Kysely
Thomas Aribart for Serverless By Theodo

Posted on

Type-safe S3 Select queries with Kysely

S3 Selectย is an Amazon S3 feature that enables retrieving subsets of S3 Objects content via SQL expressions. You can use clauses like SELECT and WHERE to fetch data from CSVs, JSONs, or Apache Parquet files, even if they are compressed with GZIP and/or server-side encrypted.

S3 Select is simple to use, cost-effective and can drastically improve the performances of your application depending on your query. It is overall a great addition to the Serverless developer toolkit, particularly when:

  • You need to access a large amount of data (which would make it unfit for other storage solutions like DynamoDB)
  • You need to query and filter it in a complex and/or dynamic way
  • You donโ€™t need to use a JOIN clause (as S3 Select can only query a single file) or to update a single record of the data (it is called S3 Select, not S3 Insert ๐Ÿ™‚)

In this article, weโ€™ll learn how to run S3 Select commands in Typescript, and how we can improve our DevX and type-safety using Kysely.

Querying with S3 Select

Letโ€™s say that we have a DB of Pokemons in the shape of a CSV, stored somewhere in a S3 Bucket:



id ; name      ; customName ; type     ; level ; generation
1  ; pikachu   ;            ; electric ; 42    ; 1
2  ; charizard ;            ; fire     ; 54    ; 1
3  ; meganium  ; plantyDino ; grass    ; 26    ; 2
...


Enter fullscreen mode Exit fullscreen mode

What if we want to retrieve the fire Pokemons from generation 1 and 2? Well, S3 Select let us do that with the following query:



import { S3Client, SelectObjectContentCommand } from '@aws-sdk/client-s3';

export const s3Client = new S3Client({
  region: 'us-east-1', // <= Your region here
});

const { Payload } = await s3Client.send(
  new SelectObjectContentCommand({
    // ๐Ÿ‘‡ Those first params are required but don't mind them
    ExpressionType: 'SQL',
    OutputSerialization: {
      JSON: {
        RecordDelimiter: ',',
      },
    },
    // ๐Ÿ‘‡ Those depends on the CSV
    InputSerialization: {
      CSV: {
        FileHeaderInfo: 'USE',
        FieldDelimiter: ';',
        QuoteCharacter: '"',
      },
    },
    // ๐Ÿ‘‡ Those are the most important
    Bucket: 'my-super-bucket-name',
    Key: 'pokedex.csv',
    Expression: `
            select "id", "name", "customName", "type" as "pokemonType", "level"
                from "S3Object"
                where
                    "generation" in ('1', '2')
                    and "type" = 'fire'
      `,
  }),
);

// Note that this command requires the s3:GetObject permission


Enter fullscreen mode Exit fullscreen mode

This is great and all but we can make it better:

  • ๐Ÿ“ย We need to add some custom parsing to actually use the response Payload
  • ๐Ÿ‘ย We can use Kysely to produce the SQL expression in a type-safe and devX-friendly way
  • ๐ŸŒˆย We can also use it to type the command result

Parsing the query response

The Payload is not a plain Javascript object but an instance of AsyncInterable. Now what the hell is an AsyncIterable you ask me? Well, you could look for the official Async Iterator documentation on MDNโ€ฆ or you can just stop asking questions and use the following helper:



import type { SelectObjectContentEventStream } from '@aws-sdk/client-s3';

// ๐Ÿ‘‡ TextDecoder is a native Node/Browser class
const textDecoder = new TextDecoder();

const parseS3SelectEventStream = async (
  s3SelectEventStream:
    | AsyncIterable<SelectObjectContentEventStream>
    | undefined,
): Promise<unknown[]> => {
  if (!s3SelectEventStream) {
    return [];
  }

  const stringifiedJSONOutputs: string[] = [];

  for await (const event of s3SelectEventStream) {
    if (event.Records) {
      const stringifiedJSONOutput = textDecoder.decode(event.Records.Payload);
      stringifiedJSONOutputs.push(stringifiedJSONOutput);
    }
  }

  const rows = JSON.parse(
    '[' + stringifiedJSONOutputs.join('').slice(0, -1) + ']',
  ) as unknown[];

  return rows;
};


Enter fullscreen mode Exit fullscreen mode

Now we can parse the output like this:



const { Payload: s3SelectEventStream } = await s3Client.send(
  new SelectObjectContentCommand({
    // ...
  }),
);

// ๐ŸŽ‰ Ta-da!
const myPokemons: unknown[] = await parseS3SelectEventStream(
  s3SelectEventStream,
);


Enter fullscreen mode Exit fullscreen mode

Building the query with Kysely

Letโ€™s face it, writing SQL queries by hand is a pain and very error prone. Besides, wouldn't it be nice to have some type error if the CSV suddenly changes shape?

Thatโ€™s where Kysely comes to the rescue: Kysely is a type-safe and devX-friendly typescript SQL query builder. It was designed to work with PostgreSQL and MySQL, but it exposes a few classes that can let us write queries without being connected to an actual relational database.

Letโ€™s begin by designing our CSV type:



enum PokemonType {
  Water = 'water',
  Grass = 'grass',
  Fire = 'fire',
  // ...
}

interface PokemonCSV {
  id: string;
  name: string;
  customName?: string;
  type: PokemonType;
  // ๐Ÿ‘‡ In CSVs everything is a string
  level: string;
  generation: '1' | '2'; // ...up to 9
}


Enter fullscreen mode Exit fullscreen mode

Next, letโ€™s install Kysely and instanciate a Kysely database:



# npm
npm install kysely

# yarn
yarn add kysely


Enter fullscreen mode Exit fullscreen mode


import {
  Kysely,
  DummyDriver,
  SqliteAdapter,
  SqliteIntrospector,
  SqliteQueryCompiler,
} from 'kysely';

interface Database {
  S3Object: PokemonCSV;
}

const db = new Kysely<Database>({
  dialect: {
    createAdapter: () => new SqliteAdapter(),
    createDriver: () => new DummyDriver(),
    createIntrospector: ($db: Kysely<unknown>) => new SqliteIntrospector($db),
    createQueryCompiler: () => new SqliteQueryCompiler(),
  },
});

// Thatโ€™s it ๐ŸŽ‰


Enter fullscreen mode Exit fullscreen mode

Simple, no? Now, letโ€™s write the same query, but while enjoying some type-safety and auto-completion:



const kyselyQuery = db
  .selectFrom('S3Object')
  .select([
    'id',
    'name',
    'customName',
    // ๐Ÿ™Œ You can rename columns as you like
    'type as pokemonType',
    'level',
    // ๐Ÿ’ฅ Will trigger an error:
    'unexistingColumn',
  ])
  // ๐Ÿ™Œ Every method is type-safe!
  .where('generation', 'in', ['1', '2'])
  .where('type', '=', PokemonType.Fire);


Enter fullscreen mode Exit fullscreen mode

To protect us from nasty SQL injections, Kysely doesnโ€™t directly provide us with the SQL expression but with a sql string and parameters array:



const {
  sql, // ๐Ÿ‘ˆ SQL query with '?' as placeholders
  parameters, // ๐Ÿ‘ˆ Array of parameters
} = kyselyQuery.compile();


Enter fullscreen mode Exit fullscreen mode

Because S3 Select doesnโ€™t accept parameters in its API, we have to hydrate the parameters ourselves:



const dangerouslyHydrateSQLParameters = (
  sql: string,
  parameters: readonly unknown[],
): string => {
  for (const parameter of parameters) {
    sql = sql.replace('?', `'${String(parameter)}'`);
  }

  return sql;
};

const { sql, parameters } = kyselyQuery.compile();

// โ›”๏ธ **BE SURE TO VALIDATE DYNAMIC PARAMETERS FIRST** โ›”๏ธ
const sqlExpression = dangerouslyHydrateSQLParameters(sql, parameters);
console.log(sqlExpression);

// ๐Ÿ‘‡ We retrieve the same expression as above:
// select "id", "name", "customName", "type" as "pokemonType", "level"
//   from "S3Object"
//   where
//     "generation" in ('1', '2')
//     and "type" = 'fire'


Enter fullscreen mode Exit fullscreen mode

We just have to provide it to our S3 Select command and voilร ! Weโ€™re done!

Well almost: Notice that we only typed our SQL query expression! What about the response that comes from S3?

Inferring the response type

Initially, Kysely was not only made to build queries but also execute them. We can just benefit from the inferred type by inspecting the execute property of our Kysely query. That can be done with the help of some TS wizardry:



import type { SelectQueryBuilder } from 'kysely';

type QueryResponseRow<
  // ๐Ÿ‘‡ Add a large type constraint
  KyselyQuery extends SelectQueryBuilder<
    Record<string, unknown>,
    string,
    unknown
  >,
> =
  // ๐Ÿ‘‡ Remove the Promise wrapper
  Awaited<
    // ๐Ÿ‘‡ Get the return type of the execute method
    ReturnType<KyselyQuery['execute']>
    // ๐Ÿ‘‡ Unpack the array
  >[number];

type Pokemon = QueryResponseRow<typeof kyselyQuery>;

// ๐Ÿ‘‡ Equivalent to:
type Pokemon = {
  id: string;
  name: string;
  // ๐Ÿ‘ customName is indeed possibly undefined
  customName: string | undefined;
  level: string;
  // ๐Ÿ™Œ "type" property has been renamed
  pokemonType: PokemonType;
};


Enter fullscreen mode Exit fullscreen mode

Now we can just use this type the response of our parseS3SelectEventStream util and voilร ! Weโ€™re done! For good this time ๐Ÿ™‚

Conclusion

Both S3 Select and Kysely are awesome tools. By joining both of them, we can run performant, scalable and cost-effective queries while benefitting from strong and DRY type-safety and inference ๐Ÿฅณ

Type-safe S3 Select queries with Kysely

Note that there is one minor drawback, though: Kysely will add 120KB in your Lambdas bundles (props to for serverless-analyze-bundle-plugin for helping me out with this ๐Ÿ™Œ). It is not a lot, but not negligible either as NodeJS Lambdas bundles above 5MB negatively impacts their cold starts. So you might want to re-evaluate adding Kysely to your bundles if your query is not changing often.

Top comments (1)

Collapse
 
thomasaribart profile image
Thomas Aribart • Edited

EDIT: You can actually use the CAST sql clause to cast the pokemon levels as number. Is is both supported by Kysely and S3 Select ๐Ÿ™‚ See: