lucidql
npm install @srtech/lucidql
About lucidql
lucidql allows you to query an AdonisJS Lucid backed database from a client using structured JSON in a post request. The principle is very simple and similar to GraphQL which is where my inspiration for lucidql came from
What is the benefit of lucidql
Data Structure
No need to setup and maintain your data structure, LUCID already knows all about this. Lucid also knows all about your table relationships, so getting related data is very simple.
Only get the data you need
lucidql allows you to select only the fields you want to see in both the main table and the related tables
Faimilar Knex syntact for filtering
If your already familier with KnexJS then wirting client queries for lucidql will be a breeze.
Easy to use with a simple POST request
Accessing your data from the client is as easy as making a POST request and passing some JSON to the server.
Getting Started
install lucidql
npm i @srtech/lucidql
setup your Adonis application and create models and a controller
- Setup your models and relationships as you would normally. Nothing special here
- Create a new controller, you can call it anything you want and set it up like in the example below, but referencing your own models
You controller might look something like this.
//App/Controllers/Http/LucidQlController.js
const lucidql = require('@srtech/lucidql')
const PurchaseOrder = use('App/Models/PurchaseOrder')
const PurchaseOrderLines = use('App/Models/PurchaseOrderLine')
const classes = {
__proto__: null, // to avoid people being able to pass something like `toString`
PurchaseOrder,
PurchaseOrderLines
};
class LucidQlController {
async query({ request }) {
var { baseTable, query } = request.all()
return lucidql.run(classes[baseTable], query)
}
}
module.exports = LucidQlController
Note: Only Models that are imported and included in the classes object will be visible to lucidql as base tables for queries.
setup a new route in App/start/routes.js
Route.post('/api/lucidql', 'LucidQlController.query')
Querying data
Simply point your client i.e Axios or Postman etc at your route and send a structured JSON object to request your data.
The structure of the JSON object you send needs to be in the following format
{
"baseTable": "PurchaseOrder",
"query": {
"where": [
{
"field": "PurchaseOrder",
"value":"POC001460",
"op":"=="
}
],
"fields": ["PurchaseOrder", "ShipFromAddress", "Currency"],
"with": [
{
"table": "lines",
"fields": ["PurchaseOrder", "Position", "Item", "Price"],
"where": [
{
"field": "Position",
"value": [5, 8],
"op": "between"
}
]
}
]
}
}
The above query is the same as running the following LUCID query in your controller.
return await PurchaseOrder.query().where('PurchaseOrder', "POC001460")
.with('lines', (builder)=>{
builder.whereBetween('Position', [5, 9]).select(["PurchaseOrder", "Position", "Item", "Price"])
})
.fetch()
baseTable
Accepts a String This should match of the models you specified in the classes object you created in your controller
query
Accepts a JSON object
Although the query property is required, it can be left empty. For example, this will return all records from the PurchaseOrder table.
{
"baseTable": "PurchaseOrder",
"query": {}
}
The query property accepts these child properties
- fields
- where
- order
- paginate
- aggregate
- with
- withCount
- limit
fields
Accepts an Array of Strings
This is the same as using .select(...) in a normal LUCID query. It is the fields that you want to include from your baseTable.
important In order for relations to work using the with property (see below) you must include the fields that form the foreign key relationship, otherwise LUCID will not be able to make the relationship.
where
Accepts an Array of Objects
The where property that is a direct child of "query" will filter the records from your base table, the above example will restrict the base table to returning one record where PurchaseOrder == 'POC001460'
the structure of each condition is setup as follows
{
"field": <string>,
"value": <string, int, float, object, array>,
"op": (many options that mostly map to Knex operators. See below for more details)
}
You can add multiple conditions by simply adding more objects to the array.
order
Accepts an Array of Objects
The order property allows you to order your query. It is an array of objects, each object containing the properties
- field
- direction
If direction is omitted it defaults to ASC
"order": [
{
"field": <string>,
"direction": <"ASC":"DESC">
}
]
paginate
Accepts an object
"paginate": {
"page": <integer>,
"perPage": <integer>
}
Using page will return a different format of results as it includes details about the pagination. The object that is returned looks like this
{
total: '',
perPage: '',
lastPage: '',
page: '',
data: [{...}]
}
Your data is included in the data property, but the other properties give you useful information that allow you to easily build your pagination logic.
aggregate
Accepts an Object
"aggregate": {
"field": <String>,
"function": <String>
}
The function property should be any one of those supported by Lucid and documented here https://adonisjs.com/docs/4.1/query-builder#_aggregate_helpers
Examples of aggregate functions you can use are
- getCount
- getCountDistinct
- getMin
- getMax
- getSum
- getSumDistinct
- getAvg
- getAvgDistinct
with
Accepts an array of Objects
Just as if you were using LUCID directly in your controller, with allows you to join relations to your baseTable
The structure of with is an array of objects, with each object describing filters and fields to include.
"with": [
{
"table": "lines",
"fields": ["PurchaseOrder", "Position", "Item", "Price"],
"where": [
{
"field": "Position",
"value": [5, 8],
"op": "between"
}
]
}
]
table Accepts a String this must match the name of the relation you have setup in your model. i.e.
lines() {
return this.hasMany('App/Models/PurchaseOrderLine', 'PurchaseOrder', 'PurchaseOrder')
}
in the case above you can see that lines is the name of our relation in the model and it is what was used to name the relation in the JSON object.
You can add multiple relationships by simplying adding more objects to the array.
fields - Optional Accepts an Array of Strings
Works identically to the how the fields property is described above for the baseTable, but will limit the fields returned from your relationship, Important You must include in the list the field that forms part of the foreign key relationship
If you ommit the fields property, all fields will be returned for this relationship
order Just as with the baseQuery you can order the results of the related data. It is described in more detail above.
where
Accepts an Array of Objects
Each object in this array is used to filter the results from related table. It works in exactly the same way as described above for the where property for the baseTable.
where op
As mentioned above a where property consists of an Array of Objects, each object describing a where condition, each of these objects must have an op property. Below is a list of the supported op's
==
"where": [
{
"field": "Position",
"value": 8,
"op": "=="
}
]
Would return rows where Position is equal to 8
<> / !=
"where": [
{
"field": "Position",
"value": 8,
"op": "!="
}
]
Would return rows where Position is not equal to 8
<
"where": [
{
"field": "Position",
"value": 8,
"op": "<"
}
]
Would return rows where Position is less than 8
>
"where": [
{
"field": "Position",
"value": 8,
"op": ">"
}
]
Would return rows where Position is greater than 8
between
"where": [
{
"field": "Position",
"value": [4, 8],
"op": "between"
}
]
Would return rows where Position between 4 and 8
notBetween
"where": [
{
"field": "Position",
"value": [4, 8],
"op": "notBetween"
}
]
Would return rows where Position is not between 4 and 8
like
"like": [
{
"field": "Company",
"value": "%widgets",
"op": "like"
}
]
Would return any rows where the Company field ends in widgets
in
"where": [
{
"field": "Position",
"value": [4, 5, 6, 7, 8],
"op": "in"
}
]
Would return rows where Position is one of 4 or 5 or 6 or 7 or 8
notIn
"where": [
{
"field": "Position",
"value": [4, 5, 6, 7, 8],
"op": "notIn"
}
]
Would return rows where Position is NOT one of 4 or 5 or 6 or 7 or 8
not
Using not the field property can be omitted
"where": [
{
"value": {
"Position": 8,
"Currency": "USD"
},
"op": "not"
}
]
Would return rows where Position is not 8 and Currency is not "USD"
null
Using null the value property can be omitted
"where": [
{
"field": "Position",
"op": "null"
}
]
Would return rows where Position is null
notNull
Using notNull the value property can be omitted
"where": [
{
"field": "Position",
"op": "notNull"
}
]
Would return rows where Position is not null
withCount
Accepts an Array of Objects Lucid's own documentation for this feature can be found here https://adonisjs.com/docs/4.0/relationships#_counts
"withCount": [
{
"table": <String>
}
]The data returned will include a __meta__ property with a count of the records that are related to the baseTable
//The following
"withCount": [
{
"table": "tags"
}
]
//Would produce
{
...
"__meta__": {
"tags_count": 1
}
}limit
Accepts an Object
limit will limit the number of records that Lucid returns to you.
The object should contain only a single property qty with a numeric value
The code below would tell Lucid to limit the number of records returned from baseTable to 3.
limit: { qty: 3}