Making GraphQL Magic with Sqlmancer 🧙🔮✨

Today I'm excited to announce the beta release of Sqlmancer! Sqlmancer is a Node.js library that empowers you to effortlessly and efficiently translate GraphQL queries into SQL statements. This article will walk through setting up a project with Sqlmancer and showcase a few of its many features:

• Multiple dialect support. Sqlmancer supports Postgres, MySQL, MariaDB and SQLite, enabling you to incorporate it into existing projects regardless of what flavor of SQL you're using.
• Performance. Avoid the N+1 problem by building a single SQL query to fetch all necessary data, regardless of query depth.
• Robust filtering and sorting. Add complex filtering and sorting to your queries, including filtering using logical operators and filtering and sorting by fields and aggregate fields of related models.
• Arbitrarily deep nesting. Define one-to-one, one-to-many and many-to-many relationships between models. Related models can be filtered, sorted and paginated just like root-level fields.
• Mutations made easy. Create, update and delete records, with or without transactions, using a simple, fluent API.
• Views and CTEs. Take advantage of existing views in your database or create inline ones using common table expressions.
• Custom scalars. Use the scalars that make sense for your schema.
• Abstract types. Utilize unions and interfaces in your schema using views or single table inheritance.

If you want to skip the tutorial and see a working example, you can check out this CodeSandbox container which includes all the code shown in this post. Peruse the official docs for additional examples and a detailed API reference. If you like the project, please ⭐ it on GitHub!

The database

For this example, we'll use SQLite and a sample database (that you can get here courtesy of SQLite Tutorial). Here's a diagram of the database we'll be using:

The models

Let's start by writing our type definitions. Let's start by adding a model for our customers table:

type Customer @model(
  table: "customers"
  pk: "CustomerId"
) {
  id: ID! @col(name: "CustomerId")
  firstName: String!
  lastName: String!
  email: String!
}

Sqlmancer uses schema directives like @model and @col shown here to decorate your existing types and show how they relate to your database. All your models are defined right inside your type definitions. We've created a model named Customer and associated it with the customers table in our database. We've also indicated that the primary key on this table is the CustomerId column.

Each field on your type (with some exceptions noted below) will be mapped to a column in your database. We can use the @col directive to map a field to a column with a different name than our field, as we've done with our id field.

Naming conventions between GraphQL fields and database columns are usually different -- in GraphQL it's camelCase while SQL databases frequently use either snake_case or PascalCase. Our sample database happens to use PascalCase. It'd be incredibly laborious to add the @col directive to every field, so let's tell Sqlmancer to instead apply a transformation to each field name when determining the column name:

type Query @sqlmancer(
  dialect: SQLITE
  transformFieldNames: PASCAL_CASE
) {
  customers: [Customer!]!
}

The @sqlmancer directive is a required directive that is applied to your Query type. The transformFieldNames tells Sqlmancer to convert a model's field name to PascalCase to determine its matching column name. Any fields with a @col directive will still override this derived value. While transformFieldNames is an optional argument, the dialect is required and indicates which database you're using.

NOTE: There's a number of other directives available to you when defining models. For example, @depends can be used to create "virtual" fields, while @private can be used to hide fields from your API while still adding them to your client. Check out the docs for more info!

Relationships

Next, let's add another model and relate it to our Customer model:

type Customer @model(
  table: "customers"
  pk: "CustomerId"
) {
  id: ID! @col(name: "CustomerId")
  firstName: String!
  lastName: String!
  email: String!
  invoices: [Invoice!]!
    @relate(on: { from: "CustomerId", to: "CustomerId" })
}

type Invoice @model(
  table: "invoices"
  pk: "InvoiceId"
) {
  id: ID! @col(name: "InvoiceId")
  total: Float!
  createdOn: String! @col(name: "InvoiceId")
  customer: Customer
    @relate(on: { from: "CustomerId", to: "CustomerId" })
}

We use the @relate directive to indicate a relationship between two models. The on argument indicates which columns to join the two model's tables on -- from is this model's column and to is the related model's column.

Filtering, sorting and pagination

Lastly, let's add the ability to sort, filter and paginate the customers and invoices we look up through our API.

type Query @sqlmancer(
  dialect: POSTGRES
  transformFieldNames: PASCAL_CASE
) {
  customers: [Customer!]! @where @orderBy @limit @offset
  invoices: [Invoice!]! @many
}

Sqlmancer includes a number of "utility" directives to help generate types from your models. Here, @where, @orderBy, @limit and @offset add arguments to a field with the corresponding name and appropriate input type.

Each directive can be applied separately, or you can use @many to apply all four. Also note that we can add these directives to our relation fields too!

  invoices: [Invoice!]!
    @relate(on: { from: "CustomerId", to: "CustomerId" })
    @many

Directives like these are a great way to quickly implement a schema with robust capabilities. However, they are completely optional. If you want more control, you can define the types yourself and as long as they are the appropriate shape, they'll work all the same.

The database client

Now that we've got our type definitions, we can use them to create a database client. Sqlmancer uses Knex.js under the hood. In order to initialize the Sqlmancer client, we'll need a Knex instance pointed at our database:

import Knex from "knex";

const knex = Knex({
  client: "sqlite3",
  connection: {
    filename: "./sample.db"
  }
});

Now, let's initialize the client, pointing it to where our type definitions our located. In this example, we're using the same file, but you can pass in a glob pattern to indicate any number of files elsewhere.

import { createSqlmancerClient } from "sqlmancer";

const client = createSqlmancerClient(__filename, knex);

Our client includes all the methods of our knex instance, as well as a models property that includes the generated models. Let's use destructuring assignment syntax and get our two models:

const { models: { Customer, Invoice } } = createSqlmancerClient(__filename, knex);

The models can be used inside your resolvers or in anywhere else in your application (REST APIs, webhooks, scripts, etc.). Adding the resolver can be as simple as this:

const resolvers = {
  Query: {
    customers: (root, args, ctx, info) => {
      return Customer.findMany()
        .resolveInfo(info)
        .execute();
    },
    invoices: (root, args, ctx, info) => {
      return Invoice.findMany()
        .resolveInfo(info)
        .execute();
    }
  }
};

findMany returns a query builder instance with a number of methods like select, where, orderBy, etc. used to build your SQL query. However, it also includes a resolveInfo method that can be used inside your resolvers to automatically parse the field's arguments and selection set and generate the appropriate query builder options! Once we're done building our query, we call the execute method to actually execute it and get our results.

The schema

The last step is creating our schema. Because Sqlmancer relies on schema directives, these need to be added to our schema as well. We could do this ourselves, but we can also use the convenient makeSqlmancerSchema function, which is just a wrapper around makeExecutableSchema from graphql-tools.

import { createSqlmancerClient, makeSqlmancerSchema } from "sqlmancer";

const schema = makeSqlmancerSchema({ typeDefs, resolvers });

Now we're good to go. We can use our schema with apollo-server, express-graphql or another HTTP library of our choice.

const apollo = new ApolloServer({ schema })

The magic

Let's spin up our server and navigate to the GraphQL Playground interface. Even though we hardly wrote any code, our schema supports all sorts of complex queries, like this one:

query {
  customers(
    where: { firstName: { like: "L%" } }
    orderBy: [{ invoices: { sum: { total: DESC } } }]
    limit: 5
  ) {
    id
    firstName
    lastName
    invoices(
      limit: 3
      orderBy: { total: DESC }
    ) {
      id
      total
    }
  }
}

Even though this GraphQL query includes filtering, sorting, pagination and a relationship field that is itself paginated and sorted, Sqlmancer compiles it to a single SQL query. And we added these features with a handful of directives and a single line of code in our resolver. You can see the magic for yourself using this CodeSandbox container, which includes all the code shown in this article.

BONUS: TypeScript integration

Sqlmancer was created with TypeScript in mind. You can use Sqlmancer's CLI to generate TypeScript types for your client. Just tell the CLI where your type definitions are and where to create the file with the typings:

sqlmancer generate ./some/glob/**/*.graphql ./generated.ts

then import and use the generated definition

import { createSqlmancerClient } from "sqlmancer";
import { SqlmancerClient } from "./generated";

const client = createSqlmancerClient<SqlmancerClient>(__filename, knex);

Now you will not only have type-safety and autocompletion for the different model methods, but you'll also be able to work with fully typed result objects -- down to the individual columns you've selected and associations you've loaded!

We've only scratched the surface of what you can accomplish with Sqlmancer, but I hope I've peaked your interest enough to take the library for a spin or to at least poke your nose in the docs! I'll be publishing subsequent tutorials on more advanced topics like mutations, inline views, custom scalars, aggregation, and more. Stay tuned!