Database Management Tool

Introduction

Managing databases and performing CRUD operations are fundamental tasks for developers building data-driven applications. While many database management systems (DBMS) exist, they can be complex and cumbersome to interact with, especially when it comes to creating databases and tables, handling constraints, and performing real-time data operations through an API.

This web-based Database Management Tool simplifies the entire process, offering an intuitive UI for managing databases and tables, alongside a powerful REST API for interacting with the data. Whether you’re a developer building a backend for your app or a data engineer needing to manage multiple databases efficiently, this tool provides a seamless and easy-to-use interface to create, update, and delete databases and tables. Additionally, it supports secure access via JWT tokens, ensuring that all data operations are performed safely.

The frontend is built with Angular 17 to provide a dynamic and responsive user experience, while the backend uses Java 21 with Spring Boot 3, ensuring high performance and scalability. The tool leverages InterSystems IRIS as the main database and Redis for caching, making data management both efficient and fast.

In this article, we will dive deep into the features of the tool and walk you through how to work with it, from setting up databases to utilizing the API for CRUD operations.

Creating Your First Database

Before you can start managing databases and tables, you'll need to create an account or log in with an existing one. This step ensures secure access to the system and enables you to manage your databases privately.

Once you are logged in, the main dashboard will give you access to all database management features. To create your first database, follow these steps:

• Click on "Create New Database": This will open a form where you can enter the database details.
• Enter the Database Name: Provide a unique name for your database. This name will be used to identify and manage the database.
• Select Token Lifetime: Every database you create generates a special API token that allows you to interact with the database’s tables via REST API. You’ll need to select a lifetime for this token, choosing from one of the following options: day, week, month, year.

Creating a Table for Your Database

Once you've created your first database, the next step is to define the structure of your data by creating tables. Each table holds the data for your database, and you can customize the columns and constraints to fit your needs.

Open the Database

To start, navigate to the list of databases on your dashboard. Find the database in which you want to create a new table and click on it. This will open up the details page for the selected database.

Create a New Table: 

1. Click on "Create Table": Inside the database details page, you’ll see a "Create Table" button. Clicking this will open a new form where you can define your table.
2. Enter the Table Name: In the form, provide a unique and descriptive name for your table. This name will be used to reference the table in both the UI and API.
3. Define Table Columns: Click "Add Column": Each table consists of multiple columns, and you can add as many as needed for your data. For each column:
   1. Enter a Column Name: This will be the identifier for the column within the table.
   2. Select a Column Type: Choose from a variety of data types (e.g., String, Integer, Date, etc.) to match the kind of data that column will hold.
   3. Add Constraints: You can apply constraints such as NOT NULL, UNIQUE, or PRIMARY KEY to enforce rules on the column data.
4. Submit the Table: Once you’ve added all your columns and set the appropriate constraints, click the "Submit" button to finalize the table creation. The new table will now appear in the list of tables for the database, ready for data entry or API operations. 

Using the API to Interact with Your Table

After creating your tables, you can start working with your data through the API, which allows you to perform CRUD (Create, Read, Update, Delete) operations on the tables. Each database has its own unique API token, which you will use to authenticate your requests to that specific database.

Access API Request Examples

Once your table is created, navigate to the Table Information Page by selecting the table from the list of tables within your database. On this page, you will find examples of the API requests you can make to interact with the table, including: get by field, get all, create, update, delete.  

Retrieve the Database API Token

To perform API operations on your table, you need to authenticate your requests with a special API token that was generated when you created the database. Here’s how to get the token:

1. Navigate to the Database Information Page: Go back to the page for the database that contains your table.
2. Copy the API Token: You’ll see a section with the token information. Copy this token, as it will be needed in the headers of every request you make to the API for that database.

Making a "Create" Request

Now that you have the token and have reviewed the API examples, let's add some records to your new table.

1. Find the "Create" Request: On the Table Information Page, locate the "Create" request example. This will include the API endpoint URL and an example of the request body. 
     
2. Send the Request: Using an API client (such as Postman, cURL, or any other tool), send your POST request to the API. The server will process the request and add the new record to the table.

Getting All Records from the Table

Now that we’ve added some data to our table, let’s retrieve all the records to verify that our entries were saved correctly. The process for retrieving data is similar to creating records, but we’ll use a different API endpoint.  

Conclusion

That’s all for now! In this article, I’ve walked you through the main functionalities of this database management tool: from creating databases and tables, to performing basic CRUD operations through the REST API. However, this is just the beginning of what the application can do.

There are a variety of other features that make the tool powerful and versatile, such as:

• Creating, updating, and deleting databases: Manage multiple databases effortlessly.
• Customizing tables and columns: Add, modify, or remove columns, with support for various data types and constraints.
• Extensive API access: Beyond simple CRUD operations, you can fully manage your database structures programmatically.

This tool aims to streamline database management, making it easy to organize your data and access it securely through the API. As development continues, more advanced features like custom queries, enhanced constraints, and additional column types will be added, expanding its possibilities even further.

Thank you for exploring this tool!