rexxars / sql-to-graphql Goto Github PK

View Code? Open in Web Editor NEW

591.0 28.0 73.0 449 KB

Generate a GraphQL API based on your SQL database structure

License: MIT License

JavaScript 94.79% HTML 0.09% CSS 5.12%

sql-to-graphql's Introduction

sql-to-graphql

Generate GraphQL schemas and server based on SQL table structure.

⚠️ Unmaintained

If you want to help maintain or develop this, let me know!

What?

GraphQL is pretty awesome, but getting started can be difficult - especially if you are unfamiliar with the concepts it introduces.

sql-to-graphql is a command-line utility that can help you get started. You give it the credentials to an SQL database (MySQL, PostgreSQL and SQLite currently) and it will inspect the tables it finds and do the following:

Generate GraphQL-types for each table (including resolvers)
Generate an HTTP-server based on Hapi that accepts GraphQL queries
Sets up a basic web-frontend that lets you query the server

Disclaimer

This utility is intended to help people get started with GraphQL. It is NOT intended to be used in production.

Installation

npm install -g sql-to-graphql

Usage

sql2graphql [options]

Options:

--relay, -r - Generate Relay-style schema (boolean [default: false])
--output-dir, -o - Directory to use when generating app (string [required])
--es6 - Use ES6 for generated code (boolean [default: false])
--database, --db - Database name (string [required])
--db-filename - Database filename, used for SQLite (string)
--host, -h - Hostname of database server (string [default: "localhost"])
--port, -P - Port number of database server (number)
--user, -u - Username to use when connecting (string [default: "root"])
--password, -p - Password to use when connecting (string [default: ""])
--table, -t - Tables to generate type schemas for (array [default: "*"])
--backend, -b - Type of database (string [default: "mysql"])
--strip-suffix - Remove a suffix from table names when generating types (array)
--strip-prefix - Remove a prefix from table names when generating types (array)
--interactive, -i - Interactive mode (boolean [default: false])
--colors, -c - Colorize the code output (boolean [default: false])
--use-tabs - Use tabs for indentation (boolean [default: false])
--tab-width - Width of tabs (number [default: 2])
--quote - Quote style (single/double) (string [default: "single"])
--default-description - The description to use for columns without a comment (string [default: "@TODO DESCRIBE ME"])
--unaliased-primary-keys Disable aliasing of primary key fields to "id" for each type (boolean [default: false])
--help - Show help (boolean)

A note about connections

At the moment, sql-to-graphql tries to guess connections between tables based on naming conventions. This is far from fool-proof, but here's how it works:

Given you have a table called users (or user) and a table called posts (or post) and the posts table have a column called user_id, it will create a connection called user on the Post-type, giving you the User this post belongs to. It will also create a connection on the User-type named posts, which will return all the posts belonging to this User.

How these connections look depends on the options used (namely, --relay).

License

MIT-licensed. See LICENSE.

sql-to-graphql's People

Contributors

Stargazers

Watchers

sql-to-graphql's Issues

MySQL JSON column type throws error.

{ tableName: 'recipes',
  columnName: 'ingredients',
  ordinalPosition: 5,
  isNullable: 'NO',
  dataType: 'json',
  columnKey: '',
  columnType: 'json',
  columnComment: '' }
Unhandled rejection Error: Type "json" not recognized
    at getType (/usr/lib/node_modules/sql-to-graphql/steps/column-to-object.js:127:19)
    at columnToObject (/usr/lib/node_modules/sql-to-graphql/steps/column-to-object.js:16:8)
    at /usr/lib/node_modules/sql-to-graphql/steps/table-to-object.js:16:16
    at Array.map (native)
    at Object.tableToObject (/usr/lib/node_modules/sql-to-graphql/steps/table-to-object.js:15:32)
    at onTableDataCollected (/usr/lib/node_modules/sql-to-graphql/cli.js:127:23)
    at /usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:726:13
    at /usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:52:16
    at done (/usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:246:17)
    at /usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:44:16
    at /usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:723:17
    at /usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:167:37
    at /usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:726:13
    at /usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:52:16
    at done (/usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:246:17)
    at /usr/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:44:16

Relay: Implement Node-interface

See https://facebook.github.io/relay/docs/graphql-object-identification.html#content for more information.

TypeError: (db || "<not set>").toLowerCase is not a function

I am receiving this error, despite the fact that I am indeed supplying the database. I have tried calling as follows:

sql2graphql -uroot -pW%$$#$#$%@ -P3307 --db stats

and

sql2graphql -uroot -pW%$$#$#$%@ -P3307 --database stats

Relay: Connection types (edges/cursor)

See https://facebook.github.io/relay/docs/graphql-connections.html#content

Does it support to be used in a programmatic way not just command line?

support to oracle

oracle pagination is a little bit different

this is an example: https://github.com/oracle/node-oracledb/blob/master/examples/rowlimit.js#L64

Add support for Relay

This issue is for organizing the work that needs to happen before we are Relay-compatible.

Relay introduces some requirements that we need to incorporate if the generated apps are intended to be compatible with it:

~~the Node interface~~
~~identifiers as ID type, which should refer to which type it is of, unless the ID is globally unique (which it obviously is not in our SQL-context)~~
~~connection types for one-to-many relationships (ties in with #10)~~
~~cursor/pageinfo for connections~~
a predefined way to do mutations

Can Graphql Subscription capture change directly on Oracle Database like Hasura?

I used Hasura, not sure what tech does it use. Hasura can monitor the database directly.
That means changes don't have to be made by mutation. Changes From any app, or directly on the database will be captured by subscription.
But Hasura only works with Postgres.
According to the graphql official tutorial, subsciption event triggerred by mutation. Is there any way can subscription monitor the oracle database change directly?
Don't want write tons of mutation code.

Please update sqlite3 library version from 3.x to 4.x

Since I am encountering this unsolved issue for [email protected]: TryGhost/node-sqlite3#612

Respect code style settings for "templates"

Currently, only generated code adheres to the code style settings. The static files should also follow the settings provided.

Fails when not using the stripSuffix parameter

When calling the exectuable sql2graphql without the -s parameter, an error is given.

$ sql2graphql --db vglive -p root
[...]l/node_modules/mysql/lib/protocol/Parser.js:82
        throw err;
              ^
TypeError: Cannot read property 'forEach' of undefined
    at normalizeTableName ([...]/steps/table-to-object.js:32:10)

Add root-level queries for types

Hard to know which ones are going to be used, so I think it makes sense to just expose them all (at some point we'll get the interactive mode working properly and will use that to determine, I guess).

Enum options need to be quoted

It generated this for me:

timeFormat: {
    type: new GraphQLEnumType({
        name: 'TimeFormat',
        description: '@TODO DESCRIBE ME',

        values: {
            12_H: {
                value: '12H',
                description: '@TODO DESCRIBE ME'
            },

            24_H: {
                value: '24H',
                description: '@TODO DESCRIBE ME'
            }
        }
    }),

    description: '@TODO DESCRIBE ME'
},

12_H and 24_H aren't valid object keys unless they're quoted.

Getting error "Unhandled rejection Error" when using with mysql

I have a table called blogs in the database blogPost that has below information

sqlToGraphql$ sql2graphql -i
? What kind of database is it? mysql
? What is the hostname of your database server? localhost
? What is the name of the database? blogPost
? What is the database username? root
? What is the database password? ****
? What is the port number of the mysql database? 3306
? Output path of the application /Users/Projects/sqlToGraphql
? Do you want to use babel + ES6? No
? Do you want a Relay-style GraphQL schema? No
? Select tables to include blogs, users
Unhandled rejection Error: ER_BAD_FIELD_ERROR: Unknown column 'undefined' in 'field list'
at Query.Sequence._packetToError (/usr/local/lib/node_modules/sql-to-graphql/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
at Query.ErrorPacket (/usr/local/lib/node_modules/sql-to-graphql/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (/usr/local/lib/node_modules/sql-to-graphql/node_modules/mysql/lib/protocol/Protocol.js:280:23)
at Parser.write (/usr/local/lib/node_modules/sql-to-graphql/node_modules/mysql/lib/protocol/Parser.js:75:12)
at Protocol.write (/usr/local/lib/node_modules/sql-to-graphql/node_modules/mysql/lib/protocol/Protocol.js:39:16)
at Socket. (/usr/local/lib/node_modules/sql-to-graphql/node_modules/mysql/lib/Connection.js:103:28)
at emitOne (events.js:96:13)
at Socket.emit (events.js:189:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:551:20)
--------------------
at Protocol._enqueue (/usr/local/lib/node_modules/sql-to-graphql/node_modules/mysql/lib/protocol/Protocol.js:141:48)
at Connection.query (/usr/local/lib/node_modules/sql-to-graphql/node_modules/mysql/lib/Connection.js:208:25)
at /usr/local/lib/node_modules/sql-to-graphql/node_modules/knex/lib/dialects/mysql/index.js:92:18
at tryCatcher (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/util.js:26:23)
at Promise._resolveFromResolver (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/promise.js:483:31)
at new Promise (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/promise.js:71:37)
at Client._query (/usr/local/lib/node_modules/sql-to-graphql/node_modules/knex/lib/dialects/mysql/index.js:88:12)
at Client.query (/usr/local/lib/node_modules/sql-to-graphql/node_modules/knex/lib/client.js:127:24)
at Runner. (/usr/local/lib/node_modules/sql-to-graphql/node_modules/knex/lib/runner.js:118:24)
at Runner.tryCatcher (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/util.js:26:23)
at Runner.query (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/method.js:15:34)
at /usr/local/lib/node_modules/sql-to-graphql/node_modules/knex/lib/runner.js:44:21
at /usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/using.js:176:30
at tryCatcher (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/util.js:26:23)
at Promise._settlePromiseFromHandler (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/promise.js:510:31)
at Promise._settlePromiseAt (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/promise.js:584:18)

Join forces "taviroquai/db2graphql"

Hi,

Once this project in not maintained anymore is anyone interested in joining forces at
https://github.com/taviroquai/db2graphql ?

The aim is the same, generate a graphql schema and resolvers for an existing relational database.
You can also add your own resolvers.

Let me know what you think.

Question - GraphQL playground using generated schema

Awesome work!!!

Quick question, I used mysql sakila database to output Country and City tables, using following parameters (on Windows):
> sql2graphql --db sakila -r true -h localhost -u root -p test -t city -t country --output-dir C:\Development\Playground\app

Got this schema generated:

type City implements Node {
  id: ID!
  city: String!
  countryId: Int!
  country: Country
  lastUpdate: Int!
}

type Country implements Node {
  id: ID!
  country: String!
  lastUpdate: Int!
}

interface Node {
  id: ID!
}

type RootQueryType {
  node(id: ID!): Node
}

can't seem to query sever data on http//localhost:3000 ... tried many scenarious, but keep in running into Cannot query field "City" on "RootQueryType". error.

Do I need to modify schema.js to nest Country and City types under RootQueryType?

Support one-to-many relationships

We currently support one-to-one, but we should definitely also support one-to-many.

Improve Enum implementation

Enums in sql-to-graphql currently habe a weaknesses, in that they are not reused across types if they have equal enum values. For instance, a status enum with values active, pending and rejected could be reused across multiple types instead of redefining it in every type.

I imagine we might have to restructure the whole type building step (which might not be a bad idea anyway), so that we can first collect all the data for all types, then figure out if we've got duplicates (exact same enum values/descriptions). If so, extract them into a generic enum and reuse it.

Add Support Many-To-One (n:1) relationship mappings?

Example City to Country

Migrate to ES6

I'm willing to PR to migrate this repo to es6. Would that be a welcome change?

Implement limit/offset for connections

We are currently just fetching the first 25 items for a connection.
Obviously, the next step would be to implement limit and offset.

We also need to support relays connection system, but I think it would be nice with this simpler (if more naive) approach as well.

Add support for Postgres

Detect database foreign keys

Hi there,
I think your project is awesome.
I'd like to improve it to support foreign keys when generating the schema.
Could you give me some advice where to start ?

Support mutations

We should obviously also support mutations. Have not had time to look at the Relay standard for mutations, but if we can get away with one way of doing it regardless of relay or not, that would obviously be best.

Should *int(1) be mapped to boolean type?

Should we assume that int(1), tinyint(1) etc are meant to be booleans, and map them as such?

The "problem" here is that an tinyint(1) can actually still store 255 different values, so it might be an incorrect assumption. Perhaps a CLI-flag?

Postgres types are not recognized?

This is my column data

{ tableName: 'users',
  columnName: 'id',
  ordinalPosition: 1,
  isNullable: 'NO',
  dataType: 'character varying',
  udtName: 'varchar',
  columnKey: 'PRI',
  columnType: null }

And this is the error I get

nhandled rejection Error: Type "character varying" not recognized
    at getType (/home/playlyfer-4/.nvm/v0.10.40/lib/node_modules/sql-to-graphql/steps/column-to-object.js:127:19)

GraphQL version too old

GraphQL is now >0.9 and it's also the version used by GraphiQL

Give a more precise error message when the type generation fails

When trying to generate the GraphQL schema from a random database I'm getting an error from some part of the ast transformation.

$ sql2graphql print --db vglive --password root

/home/kribrabr/development/sql-to-graphql/node_modules/mysql/lib/protocol/Parser.js:82
        throw err;
              ^
AssertionError: [{kind: init, key: [object Object], value: [object Object], loc: null, type: Property, comments: null, method: false, shorthand: false, computed: false, decorators: null}, {kind: init, key: [object Object], value: [object Object], loc: null, type: Property, comments: null, method: false, shorthand: false, computed: false, decorators: null}, {kind: init, key: [object Object], value: [object Object], loc: null, type: Property, comments: null, method: false, shorthand: false, computed: false, decorators: null}, false] does not match field "properties": [Property | SpreadProperty] of type ObjectExpression
    at add (/home/kribrabr/development/sql-to-graphql/node_modules/ast-types/lib/types.js:544:28)
    at /home/kribrabr/development/sql-to-graphql/node_modules/ast-types/lib/types.js:558:17
    at Array.forEach (native)
    at Object.defineProperty.value [as objectExpression] (/home/kribrabr/development/sql-to-graphql/node_modules/ast-types/lib/types.js:557:30)
    at generateType (/home/kribrabr/development/sql-to-graphql/steps/generate-types.js:57:33)
    at Object.generateTypes (/home/kribrabr/development/sql-to-graphql/steps/generate-types.js:22:27)
    at onTableDataCollected (/home/kribrabr/development/sql-to-graphql/cli.js:100:24)
    at /home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:691:13
    at /home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:52:16
    at done (/home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:243:21)
    at /home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:44:16
    at /home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:688:17
    at /home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:167:37
    at /home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:691:13
    at /home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:52:16
    at done (/home/kribrabr/development/sql-to-graphql/node_modules/async/lib/async.js:243:21)

Error Pool2 - Error: connect ETIMEDOUT

I'm getting error with Postgres as well as mssql

sql2graphql --output-dir "c:\auto" --es6 --database "xxx" --host "xxx.xx.corp.xx.com" --user "xxx" --password "xxx"

Knex:warning - Pool2 - Error: Pool was destroyed
Knex:Error Pool2 - Error: connect ETIMEDOUT
Knex:Error Pool2 - Error: connect ETIMEDOUT

Add support for SQLite

Use Join Monster

http://join-monster.readthedocs.io

Add `definition` command

I thought I already had implemented this, but apparently not:

We need a definition command which prints the GraphQL schema definition in a plain-text variant, as opposed to the "code" version currently used by default.

Replace self-rolled playground with Graphiql?

Unhandled exception for mySQL database

I'm not really sure how to resolve this:

sql2graphql --database project_development --output-dir=.

Unhandled rejection TypeError: Cannot read property 'originalName' of undefined
    at generateListReferenceField (/usr/local/lib/node_modules/sql-to-graphql/steps/generate-types.js:154:105)
    at /usr/local/lib/node_modules/sql-to-graphql/steps/generate-types.js:59:25
    at Array.forEach (native)
    at generateType (/usr/local/lib/node_modules/sql-to-graphql/steps/generate-types.js:58:30)
    at Object.generateTypes (/usr/local/lib/node_modules/sql-to-graphql/steps/generate-types.js:32:27)
    at /usr/local/lib/node_modules/sql-to-graphql/cli.js:144:28
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:726:13
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:52:16
    at done (/usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:246:17)
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:44:16
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:723:17
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:167:37
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/lodash/function/after.js:43:19
    at /usr/local/lib/node_modules/sql-to-graphql/steps/find-one-to-many-rels.js:56:13
    at /usr/local/lib/node_modules/sql-to-graphql/backends/mysql.js:89:21
    at tryCatcher (/usr/local/lib/node_modules/sql-to-graphql/node_modules/bluebird/js/main/util.js:26:23)

The database user is root and there is no password.

Incorrect connection object for sqlite database

Currently in a generated app database connection object for SQLite database (in config/config.js) is generated like this:

connection: {
        'host': 'localhost',
        'user': 'root',
        'password': '',
        'database': undefined
}

And it should be;

connection: {
        filename: '/relative/path/to/db.sqlite'
}

I generated app using this command:
sql2graphql --db-filename "/path/to/db.sqlite" --backend "sqlite" --output-dir "/path/to/app"

Relay: Make ids of type ID + globally unique

See https://facebook.github.io/relay/docs/graphql-object-identification.html#content

Write a proper README

Can't expect people to use this without a readme, now can we?

Required fields

I hava a table described like this:
type City { id: Int! name: String! }

Well, if I want to query all cities I should do:
query test{ city { name } }

But I got this error:
Field "city" argument "id" of type "Int!" is required but not provided.

So I was thinking if is there a way to list all cities instead of only one.

Better GraphQL -> SQL

What about the reverse?

Back to the old ORM days, going DB->Object never worked 100%
However, going Object -> DB schema always does.

Split database structure lookup into separate module

There is a bunch of logic in here that basically builds up an object structure representing a database. Types, references etc. This doesn't really have much to do with GraphQL and could be reused in other ways (think a REST-API generator) and should therefore be split into a separate module.

Won't prioritize this right now, but leaving it as a reminder.

Error running the first stage

When i run by providing each paramerter manually i get

sql2graphql --db "dbname" -u "username" -p "password" -p 5432 -b postgres -o ./app
Unhandled rejection TypeError: undefined is not a function
    at /usr/local/lib/node_modules/sql-to-graphql/steps/find-one-to-many-rels.js:29:17
    at Array.forEach (native)
    at findRelationships (/usr/local/lib/node_modules/sql-to-graphql/steps/find-one-to-many-rels.js:27:22)
    at tasklist.(anonymous function) (/usr/local/lib/node_modules/sql-to-graphql/steps/find-one-to-many-rels.js:13:13)
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:689:13
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:239:13
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:119:13
    at _arrayEach (/usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:91:13)
    at _forEachOf (/usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:118:9)
    at _each (/usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:83:13)
    at async.forEachOf.async.eachOf (/usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:238:9)
    at _parallel (/usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:688:9)
    at Object.async.parallel (/usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:702:9)
    at Object.findOneToManyRelationships [as findOneToManyReferences] (/usr/local/lib/node_modules/sql-to-graphql/steps/find-one-to-many-rels.js:18:11)
    at onTableDataCollected (/usr/local/lib/node_modules/sql-to-graphql/cli.js:139:11)
    at /usr/local/lib/node_modules/sql-to-graphql/node_modules/async/lib/async.js:697:13

if i runin interactive mode, i get

Knex:Error Pool2 - error: database "undefined" does not exist
Knex:Error Pool2 - error: database "undefined" does not exist

i think i checked the user/pass and they seem to be correct
Thanks.