This content originally appeared on DEV Community and was authored by Thad Guidry
Wouldn't it be great if you could easily have an API automatically create itself and without coding to ask a database to combine content from multiple tables and just give you a single JSON response that you can work with in your frontend application? Without code generation that slows things down, or having to worry about mapping with an ORM, or even knowing much SQL at all?
DB2Rest builds on its RSQL core to make it very easy to combine data from one table with another table and fetch the single result of data through DB2Rest's automatically provided REST API. With DB2Rest, it is also very easy to retrieve rows and apply filters. Let's dive in!
Types of Join
For real world applications data is often stored in multiple tables. In order to retrieve data from multiple tables, a join SQL command is typically used. But DB2Rest simplifies things for you!
DB2Rest supports the following SQL JOIN types:
- INNER JOIN (also known as a ‘simple’ JOIN). This is the most common type of JOIN.
- LEFT JOIN (or LEFT OUTER JOIN)
- RIGHT JOIN (or RIGHT OUTER JOIN)
- FULL JOIN (or FULL OUTER JOIN)NOT SUPPORTED
- SELF JOIN
- CROSS JOIN
Inner Join
An Inner Join is the most commonly used, so we'll explain how to do this using DB2Rest.
INNER selects records that have matching values in both tables (A) and (B).
For example, using DB2Rest we can expand (join) a review table (A) with data from a film table (B) to retrieve all the reviews for each film.
HTTPie POST
$ echo '[ \
{"table":"film", \
"fields":["title","release_year"], \
"type":"INNER","on":["film_id==film_id"] \
}]' | http POST 'http://localhost:8080/v1/rdbms/db/review/_expand' \
'Content-Type:application/json' \
'Accept:application/json'
Notice in the above http POST syntax using HTTPie test client that we specified our first table using /db/review/_expand which says that we want the review table expanded with data from the film table. We further also added some expand (join) conditions table, fields, type prior to the http POST in a JSON Array payload.
DB2Rest uses easy to use RSQL expression syntax such as == or year=gt=2003 or year>2003.
Combining filter conditions is painless using operators AND or semicolon ; such as in this example: http://localhost:8080/v1/rdbms/db/actor?filter=last_name==Roshan;actor_id==206
The response from the HTTPie POST query is shown below:
RESPONSE
HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 174
[
{
"review_id":"ABC123",
"message":"Awesome movie",
"rating":4,
"film_id":1,
"title":"ACADEMY DINOSAUR",
"release_year":"2006-01-01"
},
{
"review_id":"XYZ321",
"message":"Fantastic Movie!",
"rating":5,
"film_id":1,
"title":"ACADEMY DINOSAUR",
"release_year":"2006-01-01"
}
]
Left Join
The LEFT keyword returns all records/rows from the left table (A), and the matching records from the right table (B). The result is 0 records from the right side, if there is no match.
Let's see the 2 tables we'll use in our next example:
users (A):
| auid | isActive | username | password | createdate |
|---|---|---|---|---|
| 1 | true | admin | pswrd123 | 2024-03-10T00:00:00 |
| 2 | true | admin1 | pass506 | 2024-03-10T00:00:00 |
| 3 | true | toyota9 | pass12 | 2024-03-10T00:00:00 |
| 4 | true | fox12 | 45@jgo0 | 2024-03-10T00:00:00 |
| 5 | true | dartaB | 79take19 | 2024-03-10T00:00:00 |
| 6 | true | lexus1267 | 98hnfRT6 | 2024-03-10T00:00:00 |
userprofile (B):
| apid | auid | firstname | lastname | phone | |
|---|---|---|---|---|---|
| 1 | 1 | Jack | Wolf | bettestroom@gmail.com | 600075764216 |
| 2 | |||||
| 4 | |||||
| 6 | |||||
| 5 | 7 | Chris | Jones | jchris2@dottns.org | +15552836716 |
We want the users and their userprofile data only when the users ids match. So let's ask for that using DB2Rest's automatically created API endpoint for our database.
In the below query, we ask to _expand our users table (A) data with additional fields and their data from the userprofile table (B)
only when the users table auid field value matches the auid values in our userprofile table.
$ echo '[ \
{"table":"userprofile", \
"fields":["auid","apid","firstname","lastname","email","phone"], \
"type":"LEFT","on":["auid==auid"] \
}]' | http POST 'http://localhost:8080/v1/rdbms/db/users/_expand' \
'Content-Type:application/json' \
'Accept:application/json'
This will return the following result:
HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 730
[
{
"auid": 1,
"username": "admin",
"password": "pswrd123",
"createdate": "2024-03-10T00:00:00",
"isActive": true,
"apid": 1,
"firstname": "Jack",
"lastname": "Wolf",
"email": "bettestroom@gmail.com",
"phone": "600075764216"
},
{
"auid": 2,
"username": "admin1",
"password": "pass506",
"createdate": "2024-03-10T00:00:00",
"isActive": true,
"apid": null,
"firstname": null,
"lastname": null,
"email": null,
"phone": null
},
{
"auid": 4,
"username": "fox12",
"password": "45@jgo0",
"createdate": "2024-03-10T00:00:00",
"isActive": true,
"apid": null,
"firstname": null,
"lastname": null,
"email": null,
"phone": null
},
{
"auid": 6,
"username": "lexus1267",
"password": "98hnfRT6",
"createdate": "2024-03-10T00:00:00",
"isActive": true,
"apid": null,
"firstname": null,
"lastname": null,
"email": null,
"phone": null
}
]
Notice we did not return the userprofile for Chris Jones because the auid values for that user from both tables did not match.
SUMMARY
DB2Rest allows you to spin up an automatically created API for your databases. There is no ORM column mapping needed or generated code produced that you have to hack further (saving 100's of hours sometimes!). Instead DB2Rest uses simplified familiar JSON and URL query parameter syntax (not SQL) to get results.
DB2Rest has much more documentation such as:
- how to count rows
- how to delete records/rows from a table
- how to add records/rows to a table
- how to use table aliases
- how to use pagination for large result sets
- how to restrict schemas
- how to connect multiple databases
- and more!
Visit DB2Rest official website: https://db2rest.com
This content originally appeared on DEV Community and was authored by Thad Guidry
Thad Guidry | Sciencx (2024-08-15T00:29:14+00:00) No-Code REST API for Databases adds Table Joins. Retrieved from https://www.scien.cx/2024/08/15/no-code-rest-api-for-databases-adds-table-joins/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.