No-Code REST API for Databases adds Table Joins

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	email	phone
1	1	Jack	Wolf	[e-mail]	600075764216
	2
	4
	6
5	7	Chris	Jones	[e-mail]	[phone]

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": "[e-mail]",
    "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