danielrearden / sqlmancer

Conjure SQL from GraphQL queries 🧙🔮✨

Sqlmancer

Conjure SQL from your GraphQL queries 🧙🔮✨

⚠️ This project is currently on hiatus. I am hoping to resume working on Sqlmancer once I have some more free time. Feel free to submit new issues for feature requests or bug reports, although I may not address them immediately.

Sqlmancer is a Node.js library for integrating SQL with GraphQL. It empowers you to effortlessly and efficiently translate GraphQL queries into SQL statements.

How it works

Sqlmancer generates a fluent, type-safe database client from your schema based on metadata you provide through schema directives. With Sqlmancer, your resolver can be as simple as this:

function resolve (root, args, ctx, info) {
  return Film.findMany().resolveInfo(info).execute();
}

while still allowing complex queries like this:

Show query

query FilmQuery {
  films(
    where: {
      or: [
        { budget: { greaterThanOrEqual: 50000000 } },
        { language: { name: { in: ["Spanish", "French"] } } },
      ]
      actors: { count: { lessThan: 50 } },
    },
    orderBy: [{
      actors: { avg: { popularity: DESC } }
    }],
    limit: 100
  ) {
    id
    title
    actors(
      orderBy: [{
        popularity: DESC
      }],
      limit: 10
    ) {
      id
      firstName
      lastName
      films(
        orderBy: [{
          films: { min: { budget: ASC } }
        }]
        limit: 5
      ) {
        id
        title
      }
    }
  }
}

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.
• 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.
• Performance. Avoid the N+1 problem by building a single SQL query to fetch all necessary data, regardless of query depth.
• Mutations made easy. Create, update and delete records, with or without transactions, using a simple, fluent API. Easily provide WHERE, ORDER BY and LIMIT clauses to your queries when updating and deleting records.
• Views and CTEs. Take advantage of existing views in your database and 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.

Design goals

• Annotation over transformation. Sqlmancer aims to be as aspect-oriented as possible, with directives being used mostly to annotate your schema rather than outright change its behavior.
• Limited type-generation. Sqlmancer offers a number of convenient directives to generate arguments or types, but these directives are never required for Sqlmancer to work its magic. What types are exposed in the schema is ultimately left up to you.
• More than just CRUD. Sqlmancer empowers you to create the queries and mutations that are right for your schema.
• Flexible and unopinionated. Sqlmancer enabled you to easily add features like authorization, tracing, cost analysis and depth limits using existing libraries without paying for a "pro" version.

See the official documentation for API reference, guides and more.

Community

If you found a bug, have a feature request or want to contribute to the project, please open an issue. If you need help or have a question, you can ask on Stack Overflow or come chat with us on Discord!

Contributors

Thanks goes to these wonderful people (emoji key):

Daniel Rearden 💻 📖 🤔

Pavel Ravits 📖

Tadej Stanic 🐛

Tristan Siegel 💻

This project follows the all-contributors specification. Contributions of any kind welcome!

sqlmancer's Issues

Add support for full-text search

Full-text search implementations vary pretty widely from dialect to dialect, but some common abstraction may be possible. At the very least, Sqlmancer should support and document work arounds for each dialect.

Add support for SQLite

Support for SQLite should be possible assuming the json1 extension is installed.

Add directive for creating `input` arguments for create/update mutations

Similar to how we create the where and orderBy input types, it would be possible to generate appropriate input types for inserts and updates based on the provided model metadata.

Documentation fix

Occurrences such as
language @relate(on: [{ from: "language_id", to: "id" }]): Language!
should be
language: Language! @relate(on: [{ from: "language_id", to: "id" }])

add sybase support

Add sysbase support

Nested orderBy causes relationship fields to be returned as string in SQLite

Evidently using ORDER BY with json_group_array causes SQLite to return a string array instead of a JSON array. This is on top of serializing the whole field as a string to begin with (which we already parse back into JSON on the application side). This is causes child fields on relationship fields to resolve to null since the parent object ends up being a string. Since this is apparently "working as intended" on the SQLite side, we need to implement an appropriate work around.

Support more complex join conditions for relationships

The API currently only supports joining two models using a single foreign key. This can be limiting if the relationship needs to support additional join conditions. For example, if using a closure table and wanting to join based on a particular depth. Or applying arbitrary filters based on the target table's column values (like having an activeCustomers field on store). The current workaround is to utilize inline views to create separate models for these use-cases, but this is not ideal. This might warrant a bigger change to the existing API (moving away from the "from" and "to" syntax entirely).

Suggestion: Expose join API on Builder Class

I've mentioned in #132, I've now come across multiple instances where I need to alter plain one to many or many to many statements. A scenario will arise where I'd like to have a connection of one type to another, but can only return data from my API if those have a certain status. This is easy to alleviate with a simple where clause to a sql statement, which I can do by manually creating the resolver using sqlmancer models for a one to many relationship. However, for many to many there is no such easy solution in the API. Since under the hood sqlmancer is making these sql statements, I'm wondering how easy would it be to expose this API as public methods on models?

Add custom scalar mapper

Currently types are restricted to the five built-in scalars and the GraphQLJSON and GraphQLJSONObject scalars that come with the library. This is pretty limiting as custom scalars are used fairly commonly to enforce additional validation. A custom scalar map could be provided as part of the configuration file to allow additional scalar types. Since the map would be provided by the developer anyway, it may be possible to rely exclusively on the type names, as opposed to having to import the scalars inside the configuration file.

Add method to append arbitrary SQL to generated query

Add a method to every query builder to execute one or more additional SQL statements before and/or after the generated query is ran. This would allow setting the search path, role, etc., which is critical to implementing certain authorization patterns and allowing additional flexibility around how queries are executed period. multipleStatements would need to be set to true on the Knex instance used by the query builder to take advantage of this feature.

Add support for IBM Db2

Relay-style connections/pagination?

Sqlmancer looks great, but one missing functionality that would prevent me from adopting it is the lack of relay-compatible connection definition and pagination. Unless I am missing something in the documentation or code, I don't see any way to do this. Is this something that might be directly supported in the future, or made possible via a plugin?

Add support for MySQL and MariaDB

Allow composite primary keys

Recently, I created a table to handle a many-to-many relationship. In this case, I want the primary key to be a constrain on multiple columns, but the @model directive only supports a string as the value for the pk argument. I'm not sure what the API would look like, but it would definitely be useful to have the pk field support constrain primary keys.

Great work on this project. It has definitely made it easier for me to handle building my GraphQL server.

Enhancement: support for upserts

As you probably know, and upsert is a combination of insert/update. The operation will either update and existing row or insert a new one in a table. Most databases support SQL syntax necessary to do this in one operation. Unfortunately, the syntax varies depending on DBMS.

MySQL:

SET @id = 1,
    @title = 'In Search of Lost Time',
    @author = 'Marcel Proust',
    @year_published = 1913;
INSERT INTO books
    (id, title, author, year_published)
VALUES
    (@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATE
    title = @title,
    author = @author,
    year_published = @year_published;

Where ON DUPLICATE KEY triggers an UPDATE instead of an INSERT.

Postgres (9.5+):

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

where a is (usually) the primary key.

SQLite:
Appears to be the same (or close to) the syntax Postgres uses.

Here's a link on how to implement ON CONFICT in knex.js:
https://jaketrent.com/post/upsert-knexjs/

Improve error reporting when building client

Currently, the library will return a generic message when it encounters issues with the type definitions it uses for building the client:

Error: Found no files with valid type definitions using glob pattern "..."

That error message is shown whether no matching type definitions were found or there was just something wrong with the type definitions. When there are errors in the found type definitions, they should be included in the message.

Order by not working?

Using the sandbox example here, the following query does not order as expected:

{
  customers {
    lastName
    invoices (orderBy: {customer:{lastName:ASC}}){
      total
    }
  }
}

A partial result is shown below:

It seems that ASC nor DESC order appropriately.

Count returns not the total count but the count of returned results

I mean if the limit=5 and results length is 5, count = 5
Is it intended?
What I expect is count = number of filtered rows without limit

    type Order @model(table: "orders", pk: "id") {
        id: ID!
        clientId: ID!
        clientUserId: ID!
        createdAt: String
        status: String
        paymentId: String
        amount: String
        productCode: String
        quantity: String
    }

    type Query @sqlmancer(dialect: POSTGRES, transformFieldNames: SNAKE_CASE) {
        hello(name: String): String
        isAuth: AuthPayload!
        orders: Order @many @paginate
    }

        async orders(_, __, { sql, client }, info) {
            isAuth
            return client.models.Order.paginate().count().selectAll().resolveInfo(info).execute()
        },

Selection set does not limit the individual columns requested

Allow passing a schema directly to createSqlmancerClient

The current implementation currently requires loading the schema and definitions twice

import Knex from 'knex';
import { 
  createSqlmancerClient,
  makeSqlmancerSchema
} from 'sqlmancer';
import { typeDefs, resolvers } from './schema';

const knex = Knex({
  client: 'pg',
  connection: process.env.PG_CONNECTION_STRING,
});
const client = createSqlmancerClient('./src/**/*.graphql', knex);
const schema = makeSqlmancerSchema({ typeDefs, resolvers });
const apollo = new ApolloServer({ schema });

The function should allow passing the schema directly instead, or treating any first String argument as a glob, essentially being backward compatible with current implementation.

Implementation

export function createSqlmancerClient<T extends GenericSqlmancerClient = GenericSqlmancerClient>(
  schema: string | object,
  knex: Knex
): T {
  // load schema from glob pattern
  if (typeof schema === 'string') {
    const glob = schema;
    const typeDefs = getTypeDefsFromGlob(glob)

    if (!typeDefs || !typeDefs.definitions.length) {
      throw new Error(`Found no files with valid type definitions using glob pattern "${glob}"`)
    }

    schema = makeSqlmancerSchema({ typeDefs })
  }

  const { dialect, models } = getSqlmancerConfig(schema)

  return Object.assign(knex, {
    models: _.mapValues(models, (model: Model) => {
      const options = { knex, dialect }
      const { builders, readOnly } = model
      return {
        findById: (id: ID) => new builders.findById(options, id),
        findMany: () => new builders.findMany(options),
        findOne: () => new builders.findOne(options),
        paginate: () => new builders.paginate(options),
        createOne: readOnly ? undefined : (input: any) => new builders.createOne!(options, input),
        createMany: readOnly ? undefined : (input: Array<any>) => new builders.createMany!(options, input),
        deleteById: readOnly ? undefined : (id: ID) => new builders.deleteById!(options, id),
        deleteMany: readOnly ? undefined : () => new builders.deleteMany!(options),
        updateById: readOnly ? undefined : (id: ID, input: any) => new builders.updateById!(options, id, input),
        updateMany: readOnly ? undefined : (input: any) => new builders.updateMany!(options, input),
      }
    }),
  }) as any
}

GUID custom scalar produces unknown Typescript Type

I have this graphql schema definition:

scalar GUID

type User @model(table: "user", pk: "id") {
  id: GUID!
  firstName: String!
  lastName: String
}

type Query @sqlmancer(dialect: POSTGRES, customScalars: { string: ["GUID"] }) {
  userById(id: GUID!): User
}

But when I run sqlmancer generate I get some types with 'unknown' value:

export type UserIds = unknown;
    
export type UserEnums = unknown;

Because of this, I get a TSerror:

Type 'unknown' does not satisfy the constraint 'string'.

From my understanding, shouldn't unknown be a type of string[]?

Ability to specify default and maximum limit values

Sqlmancer should provide a way to enforce maximum values when calling the limit method, as well as a default limit value that could be set if the method is never called. This should be configurable globally with the ability to override it for individual fields via the @limit directive.

Issue when using esModuleInterop option

I'm running into an issue with the orderBy statement on a query. Fairly certain this is a bug with the type definitions since the code works correctly and is cribbed from the examples. I've looked for an enum to no avail, but I don't think I should need it since the definition uses a Union type called OrderByDirection.

The following code results in this error

        return client.models.Post.findMany()
          .orderBy([{ publishedAt: 'DESC' }])
          .resolveInfo(info)
          .execute()

Type 'string' is not assignable to type '("ASC" & { [x: string]: "ASC" | "DESC" | undefined; }) | ("ASC" & { sum?: { [x: string]: "ASC" | "DESC" | undefined; } | undefined; avg?: { [x: string]: "ASC" | "DESC" | undefined; } | undefined; min?: { ...; } | undefined; max?: { ...; } | undefined; } & { ...; }) | ("DESC" & { ...; }) | ("DESC" & ... 1 more ... & ...'.

Is there a Roadmap?

This project looks really great, and it's a brilliant use of directives. I saw that it's in beta, and I wondering if there was a roadmap and timeline for a first major cut. It looks like a much better fit than say join-monster for my needs.

Documentation

Hi Daniel,

Link with this text before it is broken:
Read more about defining models.

Questions:
What is the background for creating this lib and even with support for many databases? You are using it at your work?
There is prisma, how is it compares, why create something similar?

Add ability to increment/decrement columns

Knex supports chaining calls to increment/decrement/update. UpdateByIdBuilder and UpdateManyBuilder should expose this functionality somehow so that the caller doesn't have to get the column value first just to increase or decrease it by a specific amount.

GraphQL Code Generator Integration

• Need to verify that GraphQL Code Generator can be used to generate resolver typings when using Sqlmancer. Since some of the directives provided by Sqlmancer generate arguments and types, it's likely the only way to import a schema to be used by GCG is by exposing it as a module -- importing the type definitions directly won't work since GCG has no way of knowing how Sqlmancer will modify the schema. Per @lishine's comment here, we should at least document the recommended way of using both libraries.
• We could potentially get rid of the CLI and do all type generation through a GCG plugin. That would also make it possible to generate resolver typings directly from the type definitions since we could then apply the schema directives through the plugin.

The Node interface and globally unique IDs

Hi!

I'm interested in exploring and discussing what it'd take to implement the Node interface (and with that globally unique IDs) in Sqlmancer. This + connection based pagination would ultimately mean that Sqlmancer will be compatible with Relay, which would be a very nice thing 😀

Below are some initial thoughts from me (https://graphql.org/learn/global-object-identification/ is recommended reading if the reader is unfamiliar with globally unique IDs and the Node interface in GraphQL):

The Node interface

For illustrative purposes, let's say we're implementing the Node interface as a directive @nodeInterfaceyou can use on OBJECT. This is probably a bad idea (a better idea is likely some form of global setting to enable "Relay mode"), but let's use it as a way of exploring implementing the Node interface and globally unique IDs in Sqlmancer:

directive @nodeInterface on OBJECT

type Widget @model(...) @nodeInterface {
  id: ID! @hasDefault
  ...
}

Now, id defined in the model will be a database ID, which probably won't be globally unique, or contain enough information to decipher what type it's representing (which the Node interface needs). However, my thinking is that the @nodeInterface directive could do something like this;

type Widget @model(...) @nodeInterface {
  dbId: ID! # This is the old `id` field, just moved to this key so it's still accessible
  id: ID! # This is changed to be a globally unique ID with the type information needed by the Node interface
  ...
}

The id field that's changed could then be implemented to just change the resolution of itself to something Node-interface friendly:

// Manipulating the GraphQLObjectType with the `@nodeInterface` directive defined on it
id: {
  type: new GraphQLNonNull(GraphQLID),
  resolve(obj) {
    // obj.id below is the _old_, real database id at this point
    // base64.encode isn't really needed, but it's to show the clients that they shouldn't rely on the contents of the ID
    // This code below will allow us to take any `id`, decode it, and immediately know what type we need to fetch using what id. More on that below
    return base64.encode(`${typeNameFromGraphQLObject}:${obj.id}`);
  }
}

This could then be re-used in the node field on Query:

// Extending the root Query type
fields: () => ({ 
  ...rootQueryFields,
  node: {
    type: nodeInterfaceType,
   args: {...} // id: ID!
   resolve(ctx, args) {
     const [typename, id] = base64.decode(args.id).split(":");

    if (!typename || !id) {
      throw new Error("Malformed ID");
    }

   // Here we have a typename of a GraphQL type we want to resolve, and the id we want to use
   // Just need to resolve it. Made up pseudo-code below
   return ctx.models[typename].findOne({ id });
   }
  }
})

I believe this would accomplish what's needed for the Node interface and globally unique IDs.

Here's a few issues and things I've thought about that needs consideration:

• id in input positions - if the IDs are now globally unique, they'll need decoding before being used in SQL etc.
• id in relations and similar things - same as above, will need decoding.

This was a quick write up of my thoughts, I hope it's not terrible to read. What are your initial thoughts about something like this? Do you see any immediate blockers or issues?

I'm btw very willing to spend time implementing this if there's interest for it!

Replace directives with annotations

The library currently makes use of schema directives for both transforming the schema and providing metadata about the underlying database. This works well enough, but will make it difficult to integrate Sqlmancer with code-first libraries (or for that matter, vanilla GraphQL.js) without publishing plugins specific to those libraries. Schema directives were never meant to be used for relaying metadata and using them this way has been demonstrably fragile, with AST information being "lost in translation" in various contexts.

Putting annotations inside descriptions (which could then be stripped when transforming the schema), would address these points. The schema might be a bit more verbose this way, but in some ways it would also be cleaner since all the info would just be moved above the type or field instead of getting shoved inline.

Compare:

type Query
  @sqlmancer(
    dialect: POSTGRES
    transformFieldNames: SNAKE_CASE
    customScalars: { JSON: ["JSON", "JSONObject"], Date: ["DateTime"] }
  ) {
  actors: Actor @paginate @many(model: "Actor")
}

with

"""
@sqlmancer {
  dialect: 'POSTGRES',
  transformFieldNames: 'SNAKE_CASE',
  customScalars: { JSON: ['JSON'], Date: ['DateTime'] },
}
"""
type Query {
  """
  @paginate
  @many { model: 'Actor' }
  """
  actors: Actor
}

The biggest hurdle with this approach is that there is no standard way of doing annotations like this. Whatever syntax is used needs to support multi-line strings (for example, for CTE definitions), so existing libraries like graphql-metadata and graphql-annotations are out.

Depend on not passing field to the resolver unless in the query

Maybe I'm misunderstanding the docs, but @depend(on: ["id"]) would mean to me that the id field should be in the parent argument no matter what, but it isn't unless its asked for as well in the graphql query.

Add support for read-only models

It's possible to use views instead of tables already, but an additional readonly argument should be available on the @model directive to indicate a model should be generated with only read methods (e.g. findAll).

Additionally, models could be created by passing in an optional cte argument, in which case they would utilize the provided common table expression instead of an existing table. This has two distinct benefits: 1) it allows the use of virtual tables defined at the application level (i.e. no migrations necessary); and 2) it would allow self-referencing virtual tables, which cannot be expressed through views.

@model(
  cte: """
    SELECT *
    FROM customer
    WHERE active = true
  """
)

Add support for code-first libraries

Sqlmancer currently directly parses the provided type definitions when generating the database client. The type definitions could instead be parsed into a configuration object that could then be used when generating the database client. This would enable a configuration file to be used instead of a set of type definitions. This change would also set the foundation for direct integration with code-first libraries like TypeGraphQL or Nexus.

Add ability to mock database results

Users should be able to have mock data returned instead of making any database calls. This would not only enable users to stub out individual fields, but could be helpful in bootstrapping a server to work with for front end development. This feature should be configurable at the client level and the individual query level.

Found no files with valid type definitions

I get this error when I try to start a server, using SQLMancer.

Error: Found no files with valid type definitions using glob pattern "/Users/korede/Overt/Market/server/src/schema.ts"
    at Object.createSqlmancerClient (/Users/korede/Overt/Market/server/node_modules/sqlmancer/src/client/createSqlmancerClient.ts:46:11)
    at Object.<anonymous> (/Users/korede/Overt/Market/server/src/database.ts:10:23)
    at Module._compile (internal/modules/cjs/loader.js:1147:30)
    at Module.m._compile (/Users/korede/Overt/Market/server/node_modules/ts-node/src/index.ts:858:23)
    at Module._extensions..js (internal/modules/cjs/loader.js:1167:10)
    at Object.require.extensions.<computed> [as .ts] (/Users/korede/Overt/Market/server/node_modules/ts-node/src/index.ts:861:12)
    at Module.load (internal/modules/cjs/loader.js:996:32)
    at Function.Module._load (internal/modules/cjs/loader.js:896:14)
    at Module.require (internal/modules/cjs/loader.js:1036:19)
    at require (internal/modules/cjs/helpers.js:72:18)

Here's a link to the code. This might not be an issue, but I'm not sure what I'm missing, since I followed the example. Any help is appreciated.

Not nullable queries

    type Query @sqlmancer(dialect: POSTGRES, transformFieldNames: SNAKE_CASE) {
        hello(name: String): String!
        isAuth: AuthPayload!
        orders: Order! @many @paginate
    }

Here I set orders not nullable - Order!
But the result is orders: OrdersPage. not - orders: OrdersPage!
Is it fine?
I mean, if I would like to use graphql not nullable queries, here it will not work...
Or you would not recommend using not nullable queries?

Add path parameter to resolveInfo method

The resolveInfo method should take an optional path parameter that would help specify which selection set to use to populate the builder options. For example, a mutation might return a payload type with a field containing the updated record -- we would use the path parameter to indicate that its that field's selection set that should be used.

Trouble adding `@paginate` directives

I've added @paginate and it breaks on many to many relationships, and I'm not sure if I'm using it incorrectly for other custom resolvers.

  type Query @sqlmancer(dialect: POSTGRES, transformFieldNames: SNAKE_CASE, customScalars: { JSON: ["JSON", "JSONObject"], Date: ["DateTime"] }) {
    post(slug: String!): Post
    posts: [Post!]! @paginate
  }
  type Post @model(table: "posts", pk: "id") {
    id: ID!
    mobiledoc: String!
    slug: String!
    title: String!
    createdAt: String!
    publishedAt: String
    authors: [Author!]! @relate(
      through: "posts_authors",
      on: [{ from: "id", to: "post_id" }, { from: "author_id", to: "id" }]
    ) @paginate
  }
  type Author @model(table: "authors", pk: "id") {
    id: ID!
    name: String!
    email: String!
    posts: [Post!]! @relate(
      through: "posts_authors",
      on: [{ from: "id", to: "author_id" }, { from: "post_id", to: "id" }]
    )
  }

{
    Query: {
      async post(parent, args, ctx, info) {
        return client.models.Post.findOne()
          .where({ slug: { equal: args.slug } })
          .resolveInfo(info)
          .execute()
      },
      async posts(parent, args, ctx, info) {
        return client.models.Post.findMany()
          .orderBy([{ publishedAt: 'DESC' }])
          .resolveInfo(info)
          .execute()
      }
    },
    Post: {},
    Author: {},
  }

For both I'm seeing errors about results not returning a non-null field for <TypeName>Page.results. I'm not sure if maybe there's something I need to do in the resolvers or not to make this work but returning { results: <some data> }

{
  "errors": [
    {
      "message": "Cannot return null for non-nullable field PostPage.results.",
      "locations": [
        {
          "line": 3,
          "column": 5
        }
      ],
      "path": [
        "posts",
        "results"
      ],
      "extensions": {
        "code": "INTERNAL_SERVER_ERROR",
        "exception": {
          "stacktrace": [
            "Error: Cannot return null for non-nullable field PostPage.results.",
            "    at completeValue (/Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:560:13)",
            "    at completeValueCatchingError (/Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:495:19)",
            "    at resolveField (/Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:435:10)",
            "    at executeFields (/Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:275:18)",
            "    at collectAndExecuteSubfields (/Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:713:10)",
            "    at completeObjectValue (/Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:703:10)",
            "    at completeValue (/Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:591:12)",
            "    at completeValue (/Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:557:21)",
            "    at /Users/tristan/code/thedipp/api.thedipp.com/node_modules/graphql/execution/execute.js:492:16",
            "    at processTicksAndRejections (internal/process/task_queues.js:97:5)"
          ]
        }
      }
    }
  ],
  "data": null
}

[RFC] Changes to the existing API and workflow

There’s a number of issues with the library in its current form:

• Reliance on schema directives means the library is incompatible with code-first libraries like nexus, type-graphql or graphql-compose
• Transforming the schema, whether done through schema directives or some other mechanism (like annotations), makes it hard to work with other tools like GraphQL Code Generator or IDE plugins.
• Exposing model details in the type definitions tightly couples the schema with the underlying data sources and the library itself. Migrating away from Sqlmancer would require not only changing the resolvers but changing all the type definitions as well.
• Exposing model details in the type definitions means we’re forced to utilize code generation -- the client’s type cannot just be inferred by TypeScript
• A schema-first approach means that if additional data models needed for internal use, they have to be exposed first as GraphQL types that are later removed from the schema using the @private directive

What we could do differently:

• Remove all schema directives and use a single, standalone configuration object that includes all data model details.
• The data models defined inside the configuration object would resemble more traditional ORM data models and become the “source of truth” for the application. “Base” type definitions could be generated from the models as a convenience but type definitions could also be written by hand. Similarly, migration files could also be generated to keep the database in sync with the models.
• The data models could still include information specific to their respective GraphQL types, like “virtual” and paginated fields

The new workflow would look something like this:

• Write data models
• (Optionally) generate and run migrations based on the models
• (Optionally) generate type definitions from the models
• Write any remaining type definitions for the schema
• Instantiate client using only config object/data models and use it to write the resolvers
• Build the schema however you normally build your schema, with no extra steps

The API of the client itself would stay the same. When the data models change, the base type definitions can be regenerated and a new migration file can be created to sync the database.

In some ways, this workflow would be potentially more complicated than the existing one. Adding a where or orderBy argument to a field would require more steps than just sticking a directive on a field. On the other hand, utilizing a configuration object means we can leverage TypeScript for type checking and autocompletion, making it less error prone than stumbling through trying to add directives with the correct arguments.

Any "compliant" schema will work with the client, regardless of how it's created, opening up the possibility to use Sqlmancer with code-first schema libraries or even writing schemas using the core graphql module. The code generation feature could be expanded in the future to include generating code for these libraries without having to create additional, Sqlmancer-specific plugins for them.

Add CLI command for watching changes

In addition to just generating the client once, the CLI should support the ability to watch the type definitions for changes and rebuild the client when a change is detected.

Hitting some issues with one to many relations and retreiving specific fields

I have a model that has a one to many relationship. Franchises to Posts. Franchises can have many posts, but a post can only have on franchise. This works fine using just the schema directives, but I try to do anything to scope it down using a where clause a custom resolver I'm always returned one result and I can't retrieve any of it's fields beyond id even if I remove my custom where clause. I've tried removing pagination directives but that only works if I remove the custom resolver.

Here's my schema

  type Franchise @model(table: "franchises", pk: "id") {
    id: ID!
    name: String!
    slug: String!
    posts: [Post!]!
      @relate(
        on: [{ from: "id", to: "franchise_id" }]
        pagination: OFFSET
      )
      @many
      @paginate
  }
  type Post @model(table: "posts", pk: "id") {
    id: ID!
    slug: String!
    title: String!
    status: String!
    franchise: Franchise
      @relate(
        on: { from: "franchise_id", to: "id" }
      )
  }

Here's my resolver:

    Franchise: {
      async posts(parent, args, ctx, info) {
        const whereClause = {
          posts: {
            status: { equal: 'published' },
          }
        }
        return client.models.Franchise
          .paginate()
          .selectAll()
          .where(whereClause as any)
          .resolveInfo(info)
          .execute()
      },
    },

Examples link in Homepage leads to nowhere

https://github.com/danielrearden/sqlmancer/src/__tests__

Add aggregate queries and aggregate joins

Add ability to query aggregate values and do aggregate joins. In order to support querying aggregate values, a new aggregate query builder would need to be added and included in the database client. The findOne, findMany and findById query builders would need to support a new join method. Either the existing @join directive would need to be modified to support aggregate joins, or else a new directive would need to be added.

The resulting query might look something like this:

{
  modelsAggregate {
    count
    min {
      someField
    }
    avg {
      someOtherField
    }
  }
}

This mirrors existing implementations, like Hasura's, but results in a very nested structure. Alternatively, we could expose a single field with an input, for example:

{
  modelsCount: modelsAggregate(func: count)
  minSomeField: modelsAggregate(func: min, field: someField)
  avgSomeOtherField: modelsAggregate(func: avg, field: someOtherField)
}

The biggest downside is that using a single field like this would mean using Float even when Int would be appropriate (e.g. for count) but maybe we can live with that.

graphql-toolkit is now deprecated in favor of graphql-tools

According to npmjs.

Switching packages involves some migration tasks. Details here.

Missing directives (NestJS integration)

Following 'Getting Started' documentation, I'm getting error

(node:868) UnhandledPromiseRejectionWarning: Error: Unknown argument "dialect" on directive "@sqlmancer".

Unknown argument "table" on directive "@model".

Unknown argument "pk" on directive "@model".

import { schemaDirectives} from 'sqlmancer';
console.log('schemaDirectives', schemaDirectives);

Looks like it's not all from https://sqlmancer.netlify.app/directives
At least model, sqlmancer are missing.

[email protected]

Add support for type definition merging

Sqlmancer should be compatible with GraphQL Modules and any other library or project that relies on type definition merging rather than extension. The CLI and config should support an optional mergeTypeDefs flag.

Question - athorization

What would be the recommended approach to limit orders (order has customerId field) query only to users that are associated with these customers in a connected table customerUsers(userId, customerId) ?
resolver of orders

        async orders(_, __, { client, userId }, info) {
            return client.models.Order.paginate().count().selectAll().resolveInfo(info).execute()
        },

userId comes from the context

1. Inner join (using knex??, is it possible at all to use knex with sqlmancer client?) with customerUsers(where userId = contextUserId)
2. Somehow complement the request to be

query {
   orders {
      users(where: { userId: contextUserId})
   }
}

3. Something like graphql-shield (I don't know yet if it is feasible)
4. Permissions on postgres itself (have to investigate, I don't know yet anything about it)

Customizable argument and input field names

There's a number of naming conventions that could potentially be made configurable:

• Argument names like where, orderBy, limit and offset
• Logical operator field names like and, or and not
• Comparison operator field names like lessThan or equal
• Any generated type names

Add subscription helpers

It would be feasible to automatically publish an event when an insert, update or delete operation takes place, potentially reducing the boilerplate required to set up subscriptions. A PubSub implementation would need to be provided when creating the client instance.

Add support for multiple clients

The @sqlmancer directive could accept a list configuration objects instead of just one, allowing the generation of multiple database clients. This would be useful for projects that utilize more than one database. A name option would be necessary to distinguish between clients and identify which client (or clients) a particular model belongs to.

Add support for Oracle Database