dbds

DBDataSource (dbds) is primarily a lightweight PostgreSQL-backed data access object library. It also includes a simple CLI utility for generating TypeScript types for a PostgreSQL database.

It is built with dataloader and slonik for simple, out-of-the-box query batching and request-level caching while keeping the developer as close to the SQL as possible and avoiding any "magic" ORM-style implicit queries.

Table of Contents

• Background
• Install
  • Dependencies
• Usage
  • CLI
• Contributing
• License

Background

My biggest beef with any query builders is that they add an unnecessary level of abstraction on top of what is already a language designed specifically for creating-reading-updating and deleting data. JavaScript is not designed for this.

— Stop using Knex.js, Gajus Kuizinas, author of slonik

dbds does supply abstractions for several common basic queries, but anything more (such as queries involving complex joins, etc.) is up to the developer to write the query for.

I created it originally for several personal projects that used similar tech stacks, and it eventually found its way to my team's project at work.

One remaining frustration was type generation; although various options exist for generating TypeScript types for PostgreSQL databases, many of them are unmaintained, have outdated/obsolete dependencies, or otherwise did not meet the requirements for the projects I'm working on. Here is some prior art that inspired various features in the type generation aspect of dbds.

• schemats
• database-types
• sq-reflect

Install

Using yarn:

yarn add @fallingfish/dbds

Using npm:

npm install @fallingfish/dbds

Dependencies

• node-config - must be installed for the --config--prefixed CLI options to function
• typescript - dbds has typescript as a peer dependency; it's possible that it will work with versions earlier than the one that is specified in the package.json, since no super complicated features are used.

Usage

dbds is primarily used by creating child classes of the DBDataSource class; Subsequent documentation will refer to those classes as simply "datasources".

Creating Datasources

class ExampleDataSource extends DBDataSource<
  Examples,
  ContextType,
  Examples$Insert
> {}

The above example shows the simplest example datasource, which uses Examples and Examples$Insert interfaces that could be generated by the dbds CLI (see below), along with your own GraphQL ContextType. Note: the order of the generic type arguments is historical; the insert type is a relatively new addition.

dataloader integration

Technically, dataloader integration is opt-in, but it is an easy way to improve performance of a GraphQL api with little effort.

Creating DataLoaders

Dataloaders can be created by using the LoaderFactory, which is held in the loaders property of a datasource. This API should be a significant improvement over the original API spread across numerous functions.

class ExampleDataSource extends DBDataSource<...> {
  private idLoader = this.loaders.create('id', 'uuid')
}

This will create a DataLoader based on the id column, which has the type uuid in the table.

Given a table with a compound unique index on (for example) the first_name and last_name columns, creating a DataLoader based on those columns is not currently supported, due to limitations in generalized SQL queries (both in terms of actually generating the queries and the performance of those queries). Generally, it's not worth it anyway.

Using DataLoaders

Most commonly, you will create a wrapper function around each DataLoader. This can be accomplished by using the create method on the FinderFactory (i.e. the finders property of a datasource).

class ExampleDataSource extends DBDataSource<...> {
  private idLoader = this.loaders.create('id', 'uuid');
  public readonly findById = this.finders.create(this.idLoader);
}

Rows could then be looked up by calling someDataSourceInstance.findById(someId), and those lookups will be cached until the end of the request.

Custom queries

The only public query methods are get (formerly known as all) and count, both of which peform SELECT queries. For any other query, datasources should implement their own methods using one of the protected query functions: insert, update, and delete.

Previously there was a complicated API with a variety of functions that had widely varying failure cases. Now, all queries have been united under a single API using the options object.

| option | description | | ------------ | ----------------------------------------------------- | | eachResult | function to call once for each object in a result set | | expected | number of rows to expect; see slonik docs | | where | WHERE clause options | | groupBy | GROUP BY clause options | | orderBy | ORDER BY clause options | | having | HAVING clause options |

keyToColumn and columnToKey key transformation functions are still present in the options object, but they are not currently used while I figure out the best way to do so.

CLI

dbds comes with a CLI (perhaps unsurprisingly, dbds) for generating types for your database:

$ dbds generate --help
Usage: dbds generate

Generation options
  -o, --output            Destination filename for generated types
                                                      [string] [default: STDOUT]
      --gen-tables        Generate table types         [boolean] [default: true]
      --gen-enums         Generate enum types          [boolean] [default: true]
      --gen-insert-types  Generate table insert types  [boolean] [default: true]
      --gen-type-objects  Generate column type objects [boolean] [default: true]
  -N, --newline           Type of newline to use
                                         [choices: "lf", "crlf"] [default: "lf"]

Options:
  -D, --database         Database connection URL, e.g. postgres:///dbname
                                                [string] [default: DATABASE_URL]
  -S, --schema           Name of the target schema in the database
                                                    [string] [default: "public"]
      --config-schema    Name of the config key containing the schema name
                         (requires node-config)                         [string]
      --config-database  Name of the config key containing the database url
                         (requires node-config)                         [string]
      --help             Show help                                     [boolean]

Contributing

Pull requests, questions, and bug reports are gladly accepted!

License

MIT License