Introduction

GraphQL is a relatively new open-source technology, introduced by Facebook. GraphQL is a data query and manipulation language for APIs, and a runtime for fulfilling queries with existing data.
While the relationship between GraphQL and REST enabled SQL may not be immediately obvious, I believe there are many similarities and plan to describe them in this post.
What is GraphQL?
Essentially, GraphQL allows a front-end developer to generate a dynamic data query without needing to know the underlying database technology. Much like REST services therefore, GraphQL abstracts the developer from the underlying database. Because of its dynamic nature, however, GraphQL removes the need to build a REST end-point to cover every resource and data element. Instead, you build one back end end-point to handle all requests. You then dynamically build your query on the front end (using a special JSON format) and submit your query to the server for execution. This avoids the overhead of calling multiple REST resources and then consolidating the responses.
GraphQL is not a database, it is a query language that allows you to inquire on data. It requires a backend (think middleware server) that handles the query and dispatches the query to the appropriate database(s) to get the actual data. These databases could be MySQL, Oracle, Postgress etc. An example of a GraphQL API server is an open source product called Prisma.
GraphQL is strongly typed and each type describes a set of available fields. This not only facilitates descriptive error messages but also allows you to inquire on the types that are supported by the database.
So, what does a GraphQL query look like?
This all sounds great but the GraphQL server does not come for free. It obviously needs to understand everything about the underlying databases. Forgetting the infrastructure requirements, the work of mapping all of that data can be a big hurdle.
So how can Oracle RAD stack developers achieve some of the benefits of GraphQL using the software they already own? ORDS and REST enabled SQL to the rescue.
What is REST Enabled SQL?
These statements can include:
- SELECT, UPDATE, DELETE
- PL/SQL Blocks
- SQL*Plus and SQLCL Commands
- The client can inquire on what tables and views are available in the database by inquiring on the data dictionary.
- The client can inquire on the structure of specific tables and views and receive detailed information on each column (i.e. columnName, jsonColumnName, type, precision, scale and isNullable)
- The client can request exactly what data it wants by defining it in the SQL statement.
- REST enabled SQL uses a single end-point “https://<HOST>/ords/<SchemaAlias>/_/sql“ for all inquiries (not multiple separate REST services).
- Because it is using the Oracle database, the data is strongly typed.
- You can join multiple tables (an or use views) to consolidate data into one request that would otherwise have required you to call multiple REST resources and then merge the data together.
An interesting point to note here is that much like with GraphQL, a ‘smart’ client can navigate an inquire on a database with no pre-programmed knowledge of the structure of the database.
Other attributes of REST Enabled SQL:
- Queries can be expressed as a simple select statement ‘SELECT SYSDATE FROM DUAL’ or by passing a structured JSON document.
- REST enabled SQL can be secured by:
- Schema Authentication (the database schema user name and password).
- OAuth 2 Client Credentials (client gets a token and then uses that token for subsequent calls)
- First Party Authentication (Basic Authentication). Utilize a user and password created in Oracle REST Data Services with the SQL Developer role.
Probably the biggest mismatch between GraphQL and REST Enabled SQL is that REST enabled SQL assumes the client/developer knows the Oracle database. While this may sound like heresy to the open source world, in the RAD stack world this is not a bad thing. In fact, embracing the power of the Oracle database is something every RAD stack developer should be looking to do.
Configuring REST Enabled SQL
<entry key="restEnabledSql.active">true</entry>
In order to use REST enabled SQL for a specific schema, you also need to REST enable the schema, just like you would to allow creation of any ORDS services in a schema.
- Connect to the Schema
- Run the command: exec ords.enable_schema;
Important Note: As mentioned in the documentation “Enabling the REST- Enabled SQL service enables authentication against the Oracle REST Data Service enabled database schemas. This makes the database schemas accessible over HTTPS, using the database password. Oracle highly recommends that you provide strong secure database passwords”.
Securing REST Enabled SQL
If a third party needs to access your database using REST enabled SQL, setup OAUTH2 Client Credentials Authentication by performing the following steps:
- Create a new schema e.g. SYSTEMX
- Login as SYSTEMX and run ords.enable_schema to enable ORDS in the schema
- Create the appropriate grants to the schema SYSTEMX for the tables and views you want to provide access to.
- Create an OAUTH client and assign the role ‘SQL Developer’
- Provide the client with the client_id and client_secret values from the table ORDS_METADATA.OAUTH_CLIENTS.
- The client would then use the client id and secret to get a token and use that and would never know the schema password.
A REST Enabled SQL Example
I have created a table called atx_ev_charging_stations which contains a list of the electric vehicle charging stations in Austin. The first think I need to do is get a definition of the table. REST enabled SQL will always return a metadata array in the response, this describes the columns in the SELECT statement. All I need to do is pass a SQL statement that I know will return no rows and I can get the metadata e.g. 'select * from atx_ev_charging_stations where 1=2'
Now I understand the table structure I can form a query to get the charging station name and first line of the address for charging stations at apartments in the zip code 78704.
Conclusion
Author
Jon Dixon, Co-Founder JMJ Cloud