It's not uncommon for organizations to rely on multiple types of databases to meet various needs. From MongoDB's JSON-like documents to 's transactional SQL tables, each database has its own unique strengths. But what happens when you need to merge data from these different databases, say, for reporting purposes? The conventional route involves querying each database separately and merging the results programmatically. However, this method can be cumbersome and inefficient. In this article, we will explore a more streamlined approach: Using an intelligent database proxy, we'll demonstrate how you can perform complex SQL joins between a MariaDB and a MongoDB database, all while keeping your application's architecture clean and efficient. MariaDB Combining data programmatically Suppose you have a Node.js app connected to a MongoDB database for storing book ratings and another app in Java linked to a MariaDB database for managing book details. You're tasked with generating a report combining titles and ratings for each book. Since the MongoDB database lacks information on book titles and the MariaDB database lacks information on ratings, you'll need to merge data from two very different databases. Typically, you'd query each database separately, then use a common identifier like ISBN to match and combine the data. This can be achieved in any programming language that can connect to both databases, such as , as shown in the following figure: Java, TypeScript, C#, or Python While manually works, databases are designed for such operations, making them more efficient. This approach also turns your SQL application into a complex, harder-to-maintain database polyglot. By using a database proxy like , you can handle the data-joining task at the database level using , keeping your application single-faceted. Though this adds another component to your infrastructure, you gain benefits like , , and features. MaxScale is a versatile proxy compatible with both SQL and NoSQL databases and even . This allows you to integrate your NoSQL app with a relational database, benefiting from compliance. joining data MaxScale SQL automatic failover data masking security Kafka ACID To follow the rest of this article, make sure you have , the new tool, and the tool installed on your computer. Docker MariaDB-shell mongosh Setting up the environment with Docker Compose To set up the database environment, use the following file: docker-compose.yml version: "3.9"
services:
  mariadb:
    image: alejandrodu/mariadb
    environment:
      - MARIADB_CREATE_DATABASE=demo
      - MARIADB_CREATE_USER=user:Password123!
      - MARIADB_CREATE_MAXSCALE_USER=maxscale_user:MaxScalePassword123!

  maxscale:
    image: alejandrodu/mariadb-maxscale
    command: --admin_host 0.0.0.0 --admin_secure_gui false
    ports:
      - "3306:4000"
      - "27017:27017"
      - "8989:8989"
    environment:
      - MAXSCALE_USER=maxscale_user:MaxScalePassword123!
      - MARIADB_HOST_1=mariadb 3306
      - MAXSCALE_CREATE_NOSQL_LISTENER=user:Password123! The previous Docker Compose file sets up two services for local deployment: a MariaDB database server and a MaxScale database proxy. The MariaDB server is configured with a database and a user named with the password , granted relevant privileges. Additionally, a with the password is created for MaxScale's use. demo user Password123! maxscale_user MaxScalePassword123! The proxy’s is configured to disable HTTPS—something not advisable in a production environment. The file also specifies various ports to expose and the details of the database user as well as the MariaDB server location. Typically, you would use an IP address for this location, but here, the container name suffices when using Docker Compose. Finally, a is established for MongoDB client connections using the default MongoDB port 27017. web UI NoSQL listener To launch the services via the command line, navigate to the folder where your Docker Compose file is stored and execute the following command: docker compose up -d * While these Docker images are for testing and , their source code is available on . For other purposes, check the . Note: not for production GitHub MariaDB's official images on Docker Hub Configuring the SQL database Connect to the MariaDB (relational) database by running the following command in your terminal: mariadb-shell --dsn mariadb://user:'Password123!'@127.0.0.1 Switch to the demo database: use demo; Create the following SQL table: CREATE TABLE books(
  isbn VARCHAR(20) PRIMARY KEY,
  title VARCHAR(256),
  year INT
); Insert a few rows into the table (sorry, I couldn’t resist the cliché of using ): books my own books INSERT INTO books(title, isbn, year)
VALUES
  ("Vaadin 7 UI Design By Example", "978-1-78216-226-1", 2013),
  ("Data-Centric Applications with Vaadin 8", "978-1-78328-884-7", 2018),
  ("Practical Vaadin", "978-1-4842-7178-0", 2021); Confirm that the data is stored correctly: SELECT * FROM books; Creating a NoSQL collection Even without installing a MongoDB server, we can use a MongoDB client to create collections and documents in MariaDB. The difference is that the data resides in a robust, fully ACID-compliant, and scalable relational database. Let's give it a try! In the terminal, use the MongoDB shell tool to connect to what appears to be a MongoDB server but actually is a MariaDB database! Just execute the following command: mongosh When parameters are omitted, the tool connects to a MongoDB server (which is MariaDB in our case) on your local machine (127.0.0.1) via the default port (20017). If all goes as planned, executing the following command should show the database in the list: demo show databases Switch to that database: use demo We've connected to a relational database using a non-relational client! Now, let's create the collection and populate it with some example data: ratings db.ratings.insertMany([
	{
		"isbn": "978-1-78216-226-1",
		"starts": 5,
		"comment": "A good resource for beginners who want to learn Vaadin"
	},
	{
		"isbn": "978-1-78328-884-7",
		"starts": 4,
		"comment": "Explains Vaadin in the context of other Java technologies"
	},
	{
		"isbn": "978-1-4842-7178-0",
		"starts": 5,
		"comment": "The best resource to learn web development with Java and Vaadin"
	}
]) Verify that the ratings have been saved in the database: db.ratings.find() Understanding NoSQL collections in MariaDB Now, we have a unified database that appears as both a NoSQL (MongoDB) and a relational (MariaDB) database externally. We can interact with this single database using both MongoDB and SQL clients. All data resides in MariaDB, allowing us to use SQL to merge data created by MongoDB and MariaDB clients or apps. Let's delve into how utilizes MariaDB to house MongoDB data, including collections and documents. MaxScale Use the client to connect to the database and display the tables within the schema (check the for more information on this new CLI client): mariadb-shell demo documentation show tables in demo; You should see tables for both books and ratings data. The table, originally a MongoDB collection, was created via MaxScale's translation of MongoDB commands into a SQL table format. Let's examine this table's structure: ratings describe demo.ratings; The table has two columns: ratings :  the object identifier id : the JSON-formatted document doc All rating data is stored in the column as JSON. Run the following query: doc SELECT doc FROM demo.ratings \G Mixing SQL and NoSQL data in a single query Revisiting our main objective of pairing book titles with ratings, let's hypothetically consider that the table has conventional columns for and . If this were the case, merging it with the table would be a simple task. ratings stars comment books /* this doesn't work */
SELECT b.title, r.stars, r.comment
FROM ratings r
JOIN books b USING(isbn) To include the column from the table in the query, we'll need to transform it into a relational expression that can act like a new table in the SQL join. For example: doc ratings /* this still doesn't work */
SELECT b.title, r.stars, r.comment
FROM ratings rt
JOIN ...something to convert rt.doc to a table... AS r
JOIN books b USING(isbn) The solution to this is the . MariaDB offers a wide range of for handling JSON data. We'll employ to transform the column into a relational structure suitable for SQL joins. Here's the general syntax: JSON_TABLE function JSON functions JSON_TABLE doc JSON_TABLE(json_document, context_path COLUMNS (
    column_definition_1,
    column_definition_2,
    ...
  )
) [AS] the_new_relational_table Where: is an expression with the JSON text json_document ` is a JSON Path expression that defines the nodes to be used as the source of the rows Context_path The syntax for column definitions is as follows: new_column_name sql_type PATH path_in_the_json_doc [on_empty] [on_error] Using all this, our SQL query would be structured as follows: SELECT b.title, r.stars, r.comment
FROM ratings rt
JOIN JSON_TABLE(rt.doc, '$' COLUMNS(
    isbn VARCHAR(20) PATH '$.isbn',
    stars INT PATH '$.starts',
    comment TEXT PATH '$.comment'
  )
) AS r
JOIN books b USING(isbn); You could use the ISBN as the MongoDB ObjectID, making it the column in the table. I'll leave that for you to try (hint: use instead of when inserting data through the MongoDB client or app). id ratings _id isbn Is this scalable? The common belief that relational databases can't scale horizontally—by adding more nodes—as NoSQL databases can is misleading. In fact, while still maintaining their ACID compliance. Take MariaDB as an example: it offers a range of specialized storage engines suited to various tasks. Using the storage engine, you can implement data sharding to effectively scale your MariaDB database. Additionally, MariaDB allows you to select different for different tables, depending on the workload. What's more, it even supports cross-engine joins within a single SQL query. relational databases can scale Spider storage engines An even more powerful option for scaling is to use for [ ]. This approach provides a unified, logical view of a relational database to applications, made possible through “automatic sharding”. Utilizing a shared-nothing architecture, MariaDB Xpand is capable of scaling both read and write operations almost linearly. MariaDB Xpand distributed SQL Conclusion We've accomplished our goal! Now, our systems offer a scalable, ACID-compliant, unified view of your data, regardless of its SQL or NoSQL origin. This reduces the need to transition apps from NoSQL to SQL or diversify the databases they connect to. For more on MaxScale's features, check out or consult the . this video documentation

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

2022 - HackerNoon Contributor of the Year - Analytics

2022 - HackerNoon Contributor of the Year - Database

2022 - HackerNoon Contributor of the Year - Docker

2022 - HackerNoon Contributor of the Year - Git

2022 - HackerNoon Contributor of the Year - Java

2022 - HackerNoon Contributor of the Year - Sql

Visit my personal Blog!

Nominated for 2022 - HackerNoon Contributor of the Year - Database

Nominated for 2022 - HackerNoon Contributor of the Year - Git

Nominated for 2022 - HackerNoon Contributor of the Year - Java

Nominated for 2022 - HackerNoon Contributor of the Year - Docker

Nominated for 2022 - HackerNoon Contributor of the Year - Sql

Nominated for 2022 - HackerNoon Contributor of the Year - Analytics

Too Long; Didn't Read

Using MariaDB as a MongoDB NoSQL Database

Using MariaDB as a MongoDB NoSQL Database

Too Long; Didn't Read

@alejandroduarte

Credibility

RELATED STORIES