X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Demo: GraphQL with Oracle Database and node-oracledb

Christopher Jones
Senior Principal Product Manager

Some of our node-oracledb users recently commented they have moved from REST to GraphQL so I thought I'd take a look at what it is all about.

I can requote the GraphQL talking points with the best of them, but things like "Declarative Data Fetching" and "a schema with a defined type system is the contract between client and server" are easier to undstand with examples.

In brief, GraphQL:

  • Provides a single endpoint that responds to queries. No need to create multiple endpoints to satisfy varying client requirements.

  • Has more flexibility and efficiency than REST. Being a query language, you can adjust which fields are returned by queries, so less data needs to be transfered. You can parameterize the queries, for example to alter the number of records returned - all without changing the API or needing new endpoints.

Let's look at the payload of a GraphQL query. This query with the root field 'blog' asks for the blog with id of 2. Specifically it asks for the id, the title and the content of that blog to be returned:

{
  blog(id: 2) {
    id
    title
    content
  }
}

The response from the server would contain the three request fields, for example:

{
  "data": {
    "blog": {
      "id": 2,
      "title": "Blog Title 2",
      "content": "This is blog 2"
    }
  }
}

Compare that result with this query that does not ask for the title:

{
  blog(id: 2) {
    id
    content
  }
}

With the same data, this would give:

{
  "data": {
    "blog": {
      "id": 2,
      "content": "This is blog 2"
    }
  }
}

So, unlike REST, we can choose what data needs to be transferred. This makes clients more flexible to develop.

Let's looks at some code. I came across this nice intro blog post today which shows a basic GraphQL server in Node.js. For simplicity its data store is an in-memory JavaScript object. I changed it to use an Oracle Database backend.

The heart of GraphQL is the type system. For the blog example, a type 'Blog' is created in our Node.js application with three obvious values and types:

type Blog {
  id: Int!,
  title: String!,
  content: String!
}

The exclamation mark means a field is required.

The part of the GraphQL Schema to query a blog post by id is specified in the root type 'Query':

type Query {
  blog(id: Int): Blog
}

This defines a capability to query a single blog post and return the Blog type we defined above.

We may also want to get all blog posts, so we add a "blogs" field to the Query type:

type Query {
  blog(id: Int): Blog
  blogs: [Blog],
}

The square brackets indicates a list of Blogs is returned.

A query to get all blogs would be like:

{
  blogs {
    id
    title
    content
  }
}

You can see that the queries include the 'blog' or 'blogs' field. We can pass all queries to the one endpoint and that endpoint will determine how to handle each. There is no need for multiple endpoints.

To manipulate data requires some 'mutations', typically making up the CUD of CRUD:

input BlogEntry {
  title: String!,
  content: String!
}
type Mutation {
  createBlog(input: BlogEntry): Blog!,
  updateBlog(id: Int, input: BlogEntry): Blog!,
  deleteBlog(id: Int): Blog!
}

To start with, the "input" type allows us to define input parameters that will be supplied by a client. Here a BlogEntry contains just a title and content. There is no id, since that will be automatically created when a new blog post is inserted into the database.

In the mutations, you can see a BlogEntry type is in the argument lists for the createBlog and updateBlog fields. The deleteBlog field just needs to know the id to delete. The mutations all return a Blog. An example of using createBlog is shown later.

Combined, we represent the schema in Node.js like:

const typeDefs = `
type Blog {
  id: Int!,
  title: String!,
  content: String!
}
type Query {
  blogs: [Blog],
  blog(id: Int): Blog
}
input BlogEntry {
  title: String!,
  content: String!
}
type Mutation {
  createBlog(input: BlogEntry): Blog!,
  updateBlog(id: Int, input: BlogEntry): Blog!,
  deleteBlog(id: Int): Blog!
}`;

This is the contract, defining the data types and available operations.

In the backend, I decided to use Oracle Database 12c's JSON features. There's no need to say that using JSON gives developers power to modify and improve the schema during the life of an application:

CREATE TABLE blogtable (blog CLOB CHECK (blog IS JSON));

INSERT INTO blogtable VALUES (
   '{"id": 1, "title": "Blog Title 1", "content": "This is blog 1"}');
INSERT INTO blogtable VALUES (
    '{"id": 2, "title": "Blog Title 2", "content": "This is blog 2"}');
COMMIT;

CREATE UNIQUE INDEX blog_idx ON blogtable b (b.blog.id);

CREATE SEQUENCE blog_seq START WITH 3;

Each field of the JSON strings corresponds to the values of the GraphQL Blog type. (The 'dotted' notation syntax I'm using in this post requires Oracle DB 12.2, but can be rewritten for 12.1.0.2.)

The Node.js ecosystem has some powerful modules for GraphQL. The package.json is:

{
  "name": "graphql-oracle",
  "version": "1.0.0",
  "description": "Basic demo of GraphQL with Oracle DB",
  "main": "graphql_oracle.js",
  "keywords": [],
  "author": "christopher.jones@oracle.com",
  "license": "MIT",
  "dependencies": {
    "oracledb": "^2.3.0",
    "express": "^4.16.3",
    "express-graphql": "^0.6.12",
    "graphql": "^0.13.2",
    "graphql-tools": "^3.0.2"
  }
}

If you want to see the full graphql_oracle.js file it is here.

Digging into it, the application has some 'Resolvers' to handle the client calls. From Dhaval Nagar's demo, I modified these resolvers to invoke new helper functions that I created:

const resolvers = {
  Query: {
    blogs(root, args, context, info) {
      return getAllBlogsHelper();
    },
    blog(root, {id}, context, info) {
      return getOneBlogHelper(id);
    }
  },

  [ . . . ]
};

To conclude the GraphQL part of the sample, the GraphQL and Express modules hook up the schema type definition from above with the resolvers, and start an Express app:

const schema = graphqlTools.makeExecutableSchema({typeDefs, resolvers});

app.use('/graphql', graphql({
  graphiql: true,
  schema
}));

app.listen(port, function() {
  console.log('Listening on http://localhost:' + port + '/graphql');
})

On the Oracle side, we want to use a connection pool, so the first thing the app does is start one:

await oracledb.createPool(dbConfig);

The helper functions can get a connection from the pool. For example, the helper to get one blog is:

async function getOneBlogHelper(id) {
  let sql = 'SELECT b.blog FROM blogtable b WHERE b.blog.id = :id';
  let binds = [id];
  let conn = await oracledb.getConnection();
  let result = await conn.execute(sql, binds);
  await conn.close();
  return JSON.parse(result.rows[0][0]);
}

The JSON.parse() call nicely converts the JSON string that is stored in the database into the JavaScript object to be returned.

Starting the app and loading the endpoint in a browser gives a GraphiQL IDE. After entering the query on the left and clicking the 'play' button, the middle pane shows the returned data. The right hand pane gives the API documentation:

To insert a new blog, the createBlog mutation can be used:

If you want to play around more, I've put the full set of demo-quality files for you to hack on here. [Update: There is also a more fully explored demo here from Stephen Black]. You may want to look at the GraphQL introductory videos, such as this comparison with REST.

To finish, GraphQL has the concept of real time updates with subscriptions, something that ties in well with the Continous Query Notification feature of node-oracledb 2.3. Yay - something else to play with! But that will have to wait for another day. Let me know if you beat me to it.

Update: an implementation using SODA instead of SQL is discussed in a more recent demonstration blog post.

Join the discussion

Comments ( 7 )
  • Steven Black Saturday, July 21, 2018
    That's cool man! I should have thought to try JSON in the database!
  • Matt Muro Tuesday, September 18, 2018
    Excellent GraphQL Oracle demo, Chris! Thanks for making the files available and making Oracle "cool" :)
  • Christopher Jones Tuesday, September 18, 2018
    You're welcome!
  • Edward Prislac Friday, December 21, 2018
    Tried this out with my work db, complete success, except when trying to convert 1/0 to boolean, GraphQLBoolean without a resolver produces an error, with a resolver, it always comes out false
  • Arpan Kayastha Saturday, April 13, 2019
    Hey, I need some guidance on this,
    I am new to Oracle & GraphQL. can you provide some demo on GraphQL with Oracle 11g based database
  • Krish Tuesday, May 21, 2019
    I am not able to connect to the database and would love to see how are you making the connections work.

    Can you please share the github or source code for your demo project here.
  • Aki Wednesday, May 22, 2019
    Hello,
    Can you provide some example with dataloader for oracle 11g & GraphQL. There are tons of demos available for other DBs. I have been trying to do some with Dataloader, couldnt find any.

    Regards
    Aki
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.