db2graphql
v0.13.3
Published
Generate Graphql schema based on existing relational database
Downloads
39
Readme
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:
- getPage
- getFirst
- 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?