db2graphql

Logo by @Caneco

db2graphql

Generates a Graphql schema and resolvers from an existing relational database

Features

• Fully compatible with express, koa, hapi and Apollo Server
• Converts an existing relational database (only PostgreSQL, MySQL and MSSql for now) schema to a JSON schema
• Generates a Graphql SDL schema with convenient types, queries and mutations
• Implements a generic Graphql resolver ready for API prototyping
• Load related records based on foreign keys
• Allows to add/override resolvers

Demo

Query example

query {
  getPageUsers(
    filter: "id#1,2,3"
    pagination: "limit=2;orderby=username desc"
    _debug: true
  ) {
    items {
      id
      username
      fullname(foo: "hello ")
      password
      posts(filter: "publish=true", _cache: false) {
        total
        items {
          title
          publish
          categories {
            title
          }
        }
      }
    }
  }
}

Limitations/TODO

• Only PostgreSQL, MySQLand MSSql supported
• Better database types handling
• Better database queries optimization
• Create tests
• Create an NPM module
• Move to TypeScript
• Add more and improve convenient API methods. Currently, only:
  1. getPage
  2. getFirst
  3. putItem

Example

CREATE TABLE foo (
  id serial,
  name BOOLEAN
);
CREATE TABLE bar (
  id serial,
  foo_id integer
);
ALTER TABLE bar ADD CONSTRAINT fk_bar_foo_1 FOREIGN KEY (foo_id) REFERENCES foo (id) ON UPDATE CASCADE ON DELETE CASCADE;

Generated Graphql Schema
NOTE: "Foo" and "Bar" is the converted tablenames to CamelCase

type Query {

  getPageBar(
    filter: String
    pagination: String
    where: Condition
    _debug: Boolean
    _cache: Boolean
  ): PageBar

  getFirstBar(
    filter: String
    pagination: String
    where: Condition
    _debug: Boolean
    _cache: Boolean
  ): Bar

  getPageFoo(
    filter: String
    pagination: String
    where: Condition
    _debug: Boolean
    _cache: Boolean
  ): PageFoo

  getFirstFoo(
    filter: String
    pagination: String
    where: Condition
    _debug: Boolean
    _cache: Boolean
  ): Foo

}

type Mutation {

  putItemBar(
    input: Bar!
    _debug: Boolean
  ): Bar

  putItemFoo(
    input: Foo!
    _debug: Boolean
  ): Foo

}

type Condition {
  sql: String!
  val: [String!]
}

type PageBar {
  total: Int
  items: [Bar]
}

type PageFoo {
  total: Int
  items: [Foo]
}

type Bar {
  id: Int
  foo_id: Int
  foo_id_foo: Foo
}

type Foo {
  id: Int
  name: String
}

Filter Examples

Graphql

{ getPageFoo(filter: "field1[op1]value1;field2[op2]value2") }

SQL

WHERE foo.field1 [op1] value1 AND foo.field2 [op2] value2 

Where [op] matches /<=>|>=|<=|=|>|<|~|#/

Graphql

{ getPageFoo(filter: "id#1,2,3") }

SQL

WHERE foo.name IN (1,2,3)

Graphql

{ getPageFoo(filter: "name~my name is foo") }

SQL

WHERE foo.name ilike "my name is foo"

Where Example

Graphql

query getPageFoo($where: Condition) {
  getPageFoo(where: $where): PageFoo
}

Variables

{
  "where": {
    "sql": "tablename.field1 IN (?,?) AND tablename.field2 > (SELECT field FROM tablename WHERE id > ?)",
    "val": ["1","2","3"]
  }
}

Pagination Example

Graphql

query getPageFoo($pagination: String) {
  getPageFoo(pagination: $pagination): PageFoo
} }

Variables

{
  "pagination": "limit=10;offset=2;order by=title desc"
}

SQL

ORDER BY title desc LIMIT 10 OFFSET 2

Usage

Generate a Graphql schema from an existing relational database

const knex = require('knex');
const db2g = require('db2graphql');
const conn = knex(require('./connection.json'));
const api = new db2g('demo', conn);
api.connect().then(() => {
  const schema = api.getSchema();
  console.log(schema);
  conn.destroy();
});

Connect to Mysql database, please supply database name in connect method

api.connect('database_name').then(() => {
  const schema = api.getSchema();
  console.log(schema);
  conn.destroy();
});

Example of file connection.json

{
  "client": "pg",
  "version": "10.6",
  "debug": false,
  "connection": {
    "host" : "127.0.0.1",
    "user" : "postgres",
    "password" : "postgres",
    "database" : "db2graphql"
  },
  "exclude": []
}

Complete example with Apollo Server

const knex = require('knex');
const db2g = require('db2graphql');
const { ApolloServer } = require('@apollo/server');
const { startStandaloneServer } = require('@apollo/server/standalone');

const start = async (cb) => {

  /**************************************/
  const api = new db2g('demo', knex(require('./connection.json')));
  await api.connect(); // Connects to database and extracts database schema

  // Set authorization hook example
  const validator = async (type, field, parent, args, context) => {
    return true; // Should return true/ false
  }
  const denied = async (type, field, parent, args, context) => {
    throw new Error('Access Denied'); // Denied callback
  }
  api.isAuthorized(validator, denied);

  // Example of adding extra field
  api.addField('Users.fullname', 'String', (parent, args, context) => {
    return String(args.foo + parent.username);
  }, { foo: 'String' });

  // Example of overiding existing schema
  api.addField('Users.password', 'String', () => '');

  // Get generated schema and resolvers
  const schema = api.getSchema();
  const resolvers = api.getResolvers();
  /**************************************/

  // Create Apollo Server and start
  if (!schema) throw new Error('Error: empty schema');
  console.log(schema);
  const server = new ApolloServer({
    typeDefs: schema,
    resolvers,
  });
  startStandaloneServer(server).then(({ url }) => {
    console.log(`🚀 Server ready at ${url}`);
  });
}

start();

Example without database connection

const db2g = require('db2graphql');
const api = new db2g('demo');

// Add a query and resolver
api.addField('Query.getFoo', 'Boolean', async (root, args, context) => {
  return true;
}, { param: 'String!' });

// Ready to generate schema
const schema = api.getSchema();
const resolvers = api.getResolvers();

Run de demo

$ git clone https://github.com/taviroquai/db2graphql.git
$ cd db2graphql
$ npm install
$ psql -h localhost -U postgres -c "CREATE DATABASE db2graphql"
$ psql -h localhost -U postgres -f demo/database.sql db2graphql
$ cp demo/connection.example.json demo/connection.json
# Edit demo/connection.json
$ npm run start

Open browser on http://localhost:4000 and see your Graphql API ready!

Credits

• Created by Marco Afonso
• Logo by Caneco

Contributions

Anyone is free to contribute!
If you need an easy-to-use environment, please run the following docker containers:

docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=test' -p 1433:1433 --name azuresqledge -d mcr.microsoft.com/azure-sql-edge
docker run --platform=linux/amd64 --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD= -d mysql:5.7
docker run --name some-postgres -p 5432:5432 -e POSTGRES_PASSWORD= -d postgres:10.6

Please consider the azure-sql-edge container is because M1 Macs are not supported by the official mssql container.

License

MIT, what else?