Inside Database Management Tool

In this article, we’ll dive into the inner workings of the database management tool, exploring the architecture and technologies that power it. Understanding how the application functions behind the scenes will give you insight into its design, how it manages databases, tables, and how the API interacts with data.

We will discuss the core technologies used, including InterSystems IRIS as the primary database and Redis for caching. Additionally, we’ll break down the structure of the tables used and explain how the system handles data creation, retrieval, and manipulation through the REST API.

Web Application Overview

This application is built as a web-based tool, consisting of two main components: the backend and the frontend.

• Backend: The backend is developed using Java with the Spring Boot framework. Spring Boot simplifies the development process, offering a robust and scalable structure for handling server-side logic. For managing the connection to the databases, I used Spring Data, which integrates seamlessly with InterSystems IRIS (for the main database) and Redis (for caching JWT). This setup makes programming easier by handling much of the boilerplate code related to data access and storage.
• Frontend: The frontend is built using Angular 17, which provides a dynamic and responsive user interface for managing databases, tables, and interacting with data. Angular’s component-based architecture ensures that the UI is both maintainable and scalable as the application grows.

Database Structure and Workflow

The main InterSystems IRIS database contains four key tables that store crucial information about users, their databases, and the tables they create. Here's how it works:

• Users Table (users): When a new user registers, their information is saved in the users table. This table keeps track of all registered users, ensuring each user has a unique identifier for managing their resources.
• Databases Table (dbs): When a user creates a new database, the details of the database (such as its name) are stored in the dbs table. This allows the application to keep track of which user owns which database.
• Database Tokens Table (db_tokens): For each new database, a unique token is generated. This token is used to authenticate API requests to that specific database. The token's lifetime (which could be a day, week, month, or year) is stored in the db_tokens table, ensuring that access to the database is securely managed over time.
• Tables Table (tables): When a user creates a new table inside a database, the table’s name is saved in the tables table along with the associated database ID. This ensures that each table is linked to the correct database.

In addition to storing the table's metadata, the system creates a new SQL table within InterSystems IRIS for the actual user data. Each SQL table is named using a technical naming convention with the prefix table_ followed by the table’s ID as a suffix. This SQL table is where the user’s data for that particular table will be stored.

Redis Database

The Redis database plays a simple role in the application by managing user authentication. When users log in to the website, their JWT tokens (JSON Web Tokens) are generated for secure session management. These tokens are then stored in Redis for quick access and validation.

Redis acts as an in-memory cache, ensuring that user authentication is fast and efficient. When users make requests to the backend, the application checks the validity of the stored tokens to confirm the user's identity and grant access to the appropriate resources.

API for Table Operations

As mentioned in the first article, to interact with the data stored in the user’s tables, the application provides a simple yet powerful REST API. Currently, the API supports five basic operations, allowing users to manage their data efficiently:

1. Get All Records by Condition: Retrieve records from a table based on specific conditions or filters.
2. Get All Records: Fetch all records from a table without any filters.
3. Save: Add new records to a table.
4. Update: Modify existing records in a table.
5. Delete: Remove records from a table.

These API endpoints make it easy to perform CRUD (Create, Read, Update, Delete) operations on the table data. In the future, the API will be expanded to support custom queries, giving users more flexibility to execute complex data operations directly through the API.

Conclusion

In this article, we explored the inner workings of the database management tool, breaking down the backend and frontend technologies, and explaining how the InterSystems IRIS and Redis databases function within the system. We also looked at how data is managed through the API, with current support for basic CRUD operations.

While the application already offers a powerful set of features for managing databases, tables, and records, there’s still more to come. Future updates will introduce support for custom queries and other advanced features, making the tool even more versatile for users looking to manage their data seamlessly.