db2graphql

Features

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

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;

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

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

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

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

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

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

WHERE foo.name ilike "my name is foo"

Where Example

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

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

Pagination Example

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

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

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();
});

{
 "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

Credits

Contributions

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

License