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!
Top comments (0)