Download the PHP package bitsmind/graphsql without Composer

GraphSql

Overview

GraphSql is a Graphql like syntactical method to read data from SQL databases with an ease. It's built on top of Laravel Eloquent ORM.

We typically face a dilemma while building api routes. We need to build multiple api for different purposes but the data is from same database table. Let's say for products table with 10 columns, We have 2 lists in our frontend app. List 1 shows justname and image. List 2 shows name,description. For this case, we may build 2 apis to return specific fields only or a single api to return all fields. It takes longer to build 2 apis. If we build a single api, look we need just two fields, but we are returning all 10 fields. This issue just scales up with our application grows.

Imagine you have a tool, you can ask backend for fields you need from frontend like {name,image}. The api will return list of products with name and image fields, or {name,description} to get name and description only just with a single product list api.

This is what GraphSql is.

Is GraphSql just limited to single table?

Hahah, here we go, we can ask for additional data from related tables too. Imagine, we need product list with category name of each product. Then we ask {name,image,category{name}}. The api will return a list of product with each product having its category with field name only. \ Or, list of products with its variations (table: product_variations), variations{*} returns all fields. \ Or, list of products with its variations_count (table: product_variations), variations.count.

We may add conditions in a node graphString, like, variations(status=1,color=Blue){*} returns variations of status 1 and color Blue. Allowed operators: =, !=, >=, <=, >, <. \ Or, variations(status=1,color=Blue).count returns variations_count of status 1 and color Blue for individual product. Or, variations(status=1,color=Blue).sum.sale returns variations_sum_sale of status 1 and color Blue for individual product.

Examples

Api:

Data from response:

Don't worry about the category_id, id in the output. We will discuss it later.

Api:

Data from response:

Api:

Data from response:

Api:

Data from response:

What the hack is going on here?

Few questions arising in our minds. Like, how category table data is there? or how asking variations{*} node, is getting data from product_variations table?

Single line answer: GraphSql uses Eloquent Relationship for that

app/Models/Product.php

It's making sense now? That's where they come.

Installation

Pre-requisites

• Laravel Application minimum version 8
• Models: User, Category, Product, ProductVariation, CartItem etc
• Proper Eloquent Relationship defined

Note

• Graph String: {name,image,category{name},variations(status=1,color=Blue){*}}
• Nodes: category{name}, variations(status=1,color=Blue){*}
• Node Title: category, variations
• Node Properties: *, name, etc
• Node Conditions: status=1, color=Blue
• Node Titles are method names defined in the models for a related table
• Node Properties/Props are the column names
• Special Node Props: * indicates all columns, _timestamps indicates created_at, update_at columns

1. Create routes in routes/api.php

2. Add methods in app/Http/Controllers/ProductController.php

3. Add methods in app/Http/Services/ProductService.php

Let's check what we get now

Api:

Response:

Let's implement GraphSql.

1. Install GraphSql

2. Migrate new table graph_sql_keys to database. We shall discuss it later.

3. Update app/Http/Services/ProductService.php

Let's check what we get now

Api:

Returns identical content as before.

Now play with the graph string and check what you get in return

• {*}
• {name,image}
• {id,name,image,_timestamps}
• {name,image,category{*}}
• {name,image,category{name}}
• {name,image,category{name},variations{*}}
• {name,image,category{name},variations.count}
• {name,image,category{name},variations.sum.sale}
• {name,image,category{name},variations(status=1).count}
• Your imagination is the limit here

Let's receive the string from api query params

1. update app/Http/Controllers/ProductController.php

2. Update app/Http/Services/ProductService.php

Let's check what we get now with query params

Api:

Returns identical content.

Now play with the graph string and check what you get in return

• {*}
• {name,image}
• {id,name,image,_timestamps}
• {name,image,category{*}}
• {name,image,category{name}}
• {name,image,category{name},variations{*}}
• {name,image,category{name},variations.count}
• {name,image,category{name},variations.sum.sale}
• {name,image,category{name},variations(status=1).count}
• Your imagination is the limit here

Try the same for the product/{id} api.

Few example apis

Additional Methods

GraphSql comes with few shorthands for traditional queries

Here we have optional pagination, status and category_id columns filter, multi-option filter for brand column, sort by any column

Let's see typical implementation first

app/Http/Services/ProductService.php

GraphSql Shorthand

app/Http/Services/ProductService.php

Nice Hah!

Attention

If you have sensitive data then allowing direct graph string is a bit risky.

How?

Imagine a system with authentication. So, any user is not allowed to access other user data. But look at the api call below

This api will return product data with every order of for the product whether order is from this user or other user.

What is the solution then?

GraphSql provides out of the box solutions for that:

Solution 1: Graphsql Key Mapping \ Solution 2: Graphsql String Encryption

Instead of open graph string, we may map all strings and then use their map keys or use encrypted strings.

1. GraphSql Key Mapping

Remember the table graph_sql_keys we migrated during installation? We will save our graph strings in that table and set a key on behalf of a string: customer_product_list and {name,image,category{name}} in key and string column respectively. We shall use graph_key instead of graph query params in apis.

table: graph_sql_keys

Let's set up a crud for the graph keys

1. Create routes in routes/api.php. The apis are recommended to be private.

2. Add Controller app/Http/Controllers/GraphSqlKeyController.php

3. Add Service in app/Http/Services/GraphSqlKeyService.php

Use QueryAssist::queryGraphSQLByKey instead of QueryAssist::queryGraphSQL.

Now the api call

2. GraphSql String Encryption

The graph string can be encrypted and send as query params. Remember, encryption is expensive.

1. Use this encryption function in frontend to encrypt the string first

2. Set up e secret key in .env. The secret consist of alphanumeric strings seperated by . . Ex: cipher.scramble1st.scramble2nd.scramble3rd The first part generates cipher of the string and successive parts scramble the ciphered string. It's recommended to use secret of 2 parts. Adding more parts will generate more complex encryption. Use the secret also in frontend during encryption

3. Use QueryAssist::queryGraphSQLEncrypted instead of QueryAssist::queryGraphSQL in app/Http/Services/ProductService.php.

Now the api call

For development environment, we usually do not require encryption. In this case, we may use GRAPHSQL_SECRET=0. Then the encrypted string we be identical to the origin graph string. This will help us to debug easily in dev environment.

Using key-map requires managing a crud operation or manually updating a table. Using encryption is a bit expensive from cpu perspective.

So, choose which one fits the best for you.