Making GraphQL Magic with Sqlmancer 🧙🔮✨
Tags: graphql, sql, node, showdev
Published: 2020-05-15 03:09:32

Today I'm excited to announce the beta release of [Sqlmancer](https://github.com/danielrearden/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](https://codesandbox.io/s/sqlmancer-basic-example-4kwpw) which includes all the code shown in this post. Peruse the [official docs](https://sqlmancer.netlify.app) for additional examples and a detailed API reference. If you like the project, please [⭐ it on GitHub](https://github.com/danielrearden/sqlmancer)!

## The database

For this example, we'll use SQLite and a sample database (that you can get [here](https://www.sqlitetutorial.net/sqlite-sample-database/) courtesy of SQLite Tutorial). Here's a diagram of the database we'll be using:

![Database Diagram](https://dev-to-uploads.s3.amazonaws.com/i/nirvhuf8ngcnausmh0rj.png)

## The models

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

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

```graphql
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](https://sqlmancer.netlify.app) for more info!

## Relationships

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

```graphql
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.

```graphql
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!

```graphql
  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](https://knexjs.org) under the hood. In order to initialize the Sqlmancer client, we'll need a Knex instance pointed at our database:

```ts
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.

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

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

```ts
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`.

```ts
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.

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

```graphql
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](https://codesandbox.io/s/sqlmancer-basic-example-4kwpw) 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:

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

then import and use the generated definition

```ts
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](https://sqlmancer.netlify.app/)! I'll be publishing subsequent tutorials on more advanced topics like mutations, inline views, custom scalars, aggregation, and more. Stay tuned!