DEV Community: Thad Guidry

How to connect and query multiple databases with a single REST API

Thad Guidry — Fri, 18 Oct 2024 03:14:32 +0000

Intro

As a web developer or database admin, creating a single REST API for querying your databases together should be easy, but it is often quite difficult.

What if you could skip using an ORM, and use technology that auto-creates a REST API to query your databases together?

Open-source DB2Rest can allow your frontend to access your multiple separate databases where DB2Rest automatically exposes a safe and secure REST API to easily query, join, or push data records to store into your databases.

Config

We can use a JSON structure (and provide the config as a large environment variable string) or use a YAML file to provide the configuration for DB2Rest to work with multiple databases.

Here's an example of a YAML file which you can type or generate by whichever template mechanism you desire (for extra security, any values such as passwords can be replaced at instantiation time or after DB2Rest deployment). Each database needs to be given a unique id of your choosing.


app:
  databases:
    - id: DB1
      type: POSTGRESQL
      url: jdbc:postgresql://localhost:5432/homidb
      username: root
      password: "@Kolkata84"

    - id: DB2
      type: MYSQL
      url: jdbc:mysql://localhost:3306/sakila
      username: root
      password: "@Kolkata84"

After saving configuration, or setting the environment variable string for config, we can start DB2Rest with a single command:

$ java -jar db2rest-1.2.3.jar

Making REST API calls to multiple databases

Once DB2Rest is running, we can make REST API calls from our application or frontend code to query, or insert data into our databases. Let's see how to insert a record via curl into our DB1 database's employee table.

Notice the url endpoint that DB2Rest created and exposes for you by automatically inspecting all schema & tables of your configured databases upon its startup:

POST Request

curl --request POST \
--url http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB1/employee \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '{
"first_name" : "Salman",
"last_name" : "Khan",
"email" : "sk@skfilms.com",
"created_on" : "2015-04-14T11:07:36.639Z"
}'

HTTP Response from DB2Rest after successful write

{
    "row": 1,
    "keys": {
        "id": 1
    }
}

To store data records to DB2 we simply change the url like so:


curl --request POST \
--url http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB2/employee \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '{
"first_name" : "Salman",
"last_name" : "Khan",
"email" : "sk@skfilms.com",
"created_on" : "2015-04-14T11:07:36.639Z"
}'

Querying data

Let's see how querying the table employee from our DB2 looks like after we inserted the record previously. Instead of curl, let's use httpie:

HTTPie Request

http GET http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB2/employee \
User-Agent:insomnia/8.6.1

HTTP Response

[
    {
        "id": 1,
        "first_name": "Salman",
        "last_name": "Khan",
        "email": "sk@skfilms.com",
        "created_on": "2015-04-14T11:07:36.639+00:00"
    }
]

Restricting Schema for multiple databases

You can even restrict schema that will be exposed by DB2Rest's auto REST API. This is done by using a schemas add rule into the configuration of DB2Rest.

Below, we want to limit the accessible schema (objects, tables) to only the public and hrms schemas that DB2Rest will automatically create URL endpoints for us (no other schema will be reachable, and DB2Rest will immediately return an error response):


app:
  databases:
    - id: DB1
      type: POSTGRESQL
      url: jdbc:postgresql://localhost:5432/homidb
      username: root
      password: "@Kolkata84"
      schemas:
       - public
       - hrms

    - id: DB2
      type: MYSQL
      url: jdbc:mysql://localhost:3306/sakila
      username: root
      password: "@Kolkata84"

POST Request

http GET http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB1/admin \
User-Agent:insomnia/8.6.1

HTTP Error Response

[
    {
        "errorCategory": "Data-access-error",
        "timestamp:": "2015-04-14T11:07:36.639+00:00"
    }
]

Advanced Querying

DB2Rest also supports easy to use advanced querying and filtering, not only querying entire tables or inserting rows!

http GET 'http://localhost:8080/v1/rdbms/DB1/employee?filter=last_name==Green' \
User-Agent:insomnia/8.6.1

even multiple filters can be combined, using operators such as AND ; (the official docs have many more examples of powerful expression syntax available)

http GET 'http://localhost:8080/v1/rdbms/DB1/employee?filter=last_name==Green;first_name==David' \
User-Agent:insomnia/8.6.1

For more information see the official docs at https://db2rest.com/docs/intro or join the DB2Rest Discord channel

No-Code REST API for Databases adds Table Joins

Thad Guidry — Thu, 15 Aug 2024 00:29:14 +0000

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	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

Instant API for databases adds automatic TSID primary keys

Thad Guidry — Sun, 05 May 2024 06:49:47 +0000

In a previous post, we saw how we can read and write data through an instant API provided by DB2Rest. It would be nice to have a way to automatically create chronological sequences of our written data so we do not have to code anything extra in our application when writing data out through our API.

Benefits of TSIDs

DB2Rest has a built-in ability to automatically create Time-Sorted Unique Identifiers (TSID) that offer benefits such as the following:

Chronologically sorted like integers.
Compact storage (14 bytes).
Efficient indexing due to sequential nature.

TSIDs combine the benefits of integers, UUIDs, and ULIDs and are a recommended alternative for primary key values. They borrow some structure from Snowflake IDs and Instagram IDs.

One of the additional benefits are that we get automatic date timestamping that we can use for things like sorting and data analysis later on. This can save us from adding an additional Date column in our schema for our database tables. We get a timestamp, a node (shard) id, and a sequence number, all in one! TSIDs come in handy when writing data about temporal entities or anything that is event-based, like comments, posts, transactions, etc. But they can also be used as a primary key for non-temporal data in your tables.

Click for structure of TSID

42-bit timestamp: Calculated using milliseconds from a reference date (e.g., 2020-01-01).
10-bit machine ID: Unique identifier for the machine generating the TSID.
12-bit sequence number: Ensures uniqueness within the same millisecond.

When stored as a long TSIDs look like this:
38352658567418872
And when written as Text or a String they look like this:
01226N0640J7Q

Auto Generating TSIDs

DB2Rest can automatically generate TSID primary keys as it inserts data through its instant API into your database tables. It does this by auto detecting an existing primary key column in the table you are writing to and will use the datatype category (Integer-based or Text/String based) to generate the appropriate TSID format (Long/Number or Text).

Let's use a DB2Rest API endpoint to write to our /actor/ table with bulk data and let DB2Rest worry about automatically creating our tables' primary keys for each row inserted.

REQUEST:

curl --request POST \
--url http://localhost:8080/actor/bulk \
--header 'Content-Type: application/json'
--data '[
    {

        "first_name" : "Salman",
        "last_name" : "Khan"
    },
    {

        "first_name" : "Hrithik",
        "last_name" : "Roshan"
    },
    {

        "first_name" : "Tom",
        "last_name" : "Cruise"
    }
]

RESULT:
Table: actor

id	first_name	last_name
0012	John	Travolta
0036	George	Clooney
01226N0640J7P	Salman	Khan
01226N0640J7Q	Hrithik	Roshan
01226N0640J7R	Tom	Cruise

Notice that we had 2 existing rows in our actor table with id's 0012 and 0036 with famous actor names?

DB2Rest detected the data type of our existing id column and determined to use the String format for automatic TSID generation when writing the 3 new rows of data from our API POST request. We didn't have to write any special application code ourselves to do this or even setup a database stored procedure!

Summary

What's really amazing here is that DB2Rest automatically creates an API for our actor table (securely and safely) for fast frontend application development. We didn't have to code an API data access layer for our database tables at all! DB2Rest does this for us without using an ORM (Object Relational Mapper) or using any code generation that slows things down!

Read more about DB2Rest benefits in my previous post:

Easy no-code REST API for your databases

Thad Guidry ・ Apr 11

#restapi #sql #database #opensource

In summary, TSIDs strike a balance between integers and UUIDs, offering chronological sorting, compactness, and efficient indexing. If you plan to store UUID values in a primary key column, TSIDs are a recommended alternative.

You can save 100's of hours of coding data access layers by quickly enabling an instant API for your database by using DB2Rest , an APACHE 2 licensed open source middleware.

Easy no-code REST API for your databases

Thad Guidry — Thu, 11 Apr 2024 04:11:39 +0000

One of the complex parts of application development is to first write code to connect to your database and trying to leverage an Object Relational Mapping (ORM) framework to help write your data access layer.

But what would things look like if you didn't need an ORM? Or didn't need to write data access code to access your database? How would you then expose the data AND access it safely for your frontend application development?

REST API's provide a standardized interface to use HTTP requests to return data to users or applications in the form of JSON, HTML, XML, etc.

We can use some middleware to sit between our database and our application. We can then use RESTful queries in our application to ask the middleware to give us the specific data filtered to our liking as needed for our applications' operations. But which middleware? And wouldn't something in the middle slow things down?

DB2Rest is open source middleware that offers a no-code way to safely expose data from your database for your applications to consume. It's runs as a service that you can host locally or in the cloud. It even has an easy to use Docker deployment.

Simple

Let's see a simple example of a query with DB2Rest that shows how to filter on our database table of movies.

QUERY:

http GET 'http://example.com/movies?filter=title=="Titanic";year=gt=1950' \
User-Agent:insomnia/8.6.1

RESULT:

HTTP/1.1 200
Content-Type: application/json
Transfer-Encoding: chunked

[
    {
        "title": "Titanic",
        "year": 1953,
        "distributor": "20th Century Fox",
        "budget": "$1,805,000",
        "director": "Jean Negulesco"

    },
    {
        "title": "Titanic",
        "year": 1997,
        "distributor": "Paramount Pictures",
        "budget": "$200,000,000",
        "director": "James Cameron"
    }
]

Notice the QUERY where we didn't even need to use a full SQL query!?! We simply asked to filter the year greater than (=gt=) 1950 and it easily returned results! DB2Rest uses simple syntax (based on RQL) for querying and even joining tables.

Fast

DB2Rest is blazing fast even as middleware and reported by users since it doesn't need to use an ORM, but instead uses industry proven data access libraries and drivers for the most common databases. The queries are even cached for greater speed. DB2Rest translates your queries (without code generation!) into SQL so you don't have to, forwards to the database, and returns paginated results in JSON. If you already know SQL, DB2Rest can also just forward any custom SQL expressions to your database.

This is really great, because with DB2Rest you can even expose legacy databases to your applications without all the pain of coding, and quickly take advantage of that older data, or even skip database migrations and just use DB2Rest to give data access to your legacy data!

Secure

What is even better, I think, is that DB2Rest can serve as a gateway of sorts by being middleware and not directly exposing your database to your application, but instead only the data it needs. There's not even a possibility of SQL Injection attacks because of this. Security concerns are further minimized by configuring DB2Rest to use a DB user account that only has access to the schema and tables you wish to give access to. All data access security is thus handled directly by your database user access roles (and not DB2Rest) to conform with best practices and allow database administrators (DBAs) to continue to maintain security access roles as they need, even in an enterprise setting.

Visit DB2Rest.com to learn more and see all the databases it currently supports and other examples of advanced filtering, updating, deleting, and joining data from tables.