Creating Local Databases

In this section, we’ll delve into the significance of local databases and their role within our backend environment. Local databases are a fundamental component of our system, serving as a vital bridge between the development, testing, and production phases. We’ll explore why we need local databases and provide instructions on how to create them using both graphical user interfaces (GUI) and command-line interfaces (CLI). Populating the database and getting connections to the actual BigLedger database schema will come in later sections. Additionally, we’ll introduce DBeaver, a tool for managing databases, and provide a link to a dedicated section on creating local databases using DBeaver.

Why We Need Local Databases

Local databases play a crucial role in our backend development process for several reasons:

  1. Development and Testing Isolation - Local databases provide a dedicated environment for developers to work on new features, bug fixes, and enhancements without affecting the production system. Each developer can have their own local database instance, allowing them to work in isolation.

  2. Data Integrity and Testing - Local databases allow us to test changes and updates to our application against real data without the risk of compromising production data. This ensures data integrity and helps identify potential issues early in the development process.

  3. Rapid Iteration - Local databases enable rapid development and iteration. Developers can quickly test changes and see their impact on the database, making the development process more efficient.

  4. Development-Testing-Production Workflow - Local databases are an integral part of our development-testing-production workflow. Data and schema changes can be tested thoroughly in a local environment before being deployed to staging and, eventually, production databases.

Master Vs Tenant Database

Master-Tenant Database Structure

In our company’s ERP (Enterprise Resource Planning) solutions, we adopt a master-tenant database structure to efficiently manage data for multiple customers or organizations. This architecture allows us to maintain a single master database while accommodating separate tenant databases for each customer. In this section, we’ll explore the key concepts and benefits of the master-tenant database structure.

Understanding the Master-Tenant Architecture

Master Database: The master database serves as the core database that contains shared resources and configurations. It typically stores system-level data, common functionalities, and configurations that are applicable to all tenants. This includes user management, global settings, and application-level features.

Tenant Databases: Each tenant has its dedicated database, often referred to as a "tenant database" or "customer database." These databases are isolated from each other, ensuring data privacy and security for each customer. Tenant databases store customer-specific data, such as transactions, records, and configurations unique to that organization.

Throughout the documentation, you might encounter terms like master (sometimes "root") database and tenant database, and this is what they refer to. Each environment, like development, staging, and production, has one master database and multiple tenant databases connected to that master database. Here is a article link explaining the master tenant database architecture.

Multi-tenant Architecture
Figure 1. Multi-tenant Architecture

Creating Local Databases

Through pgAdmin Graphical User Interface (EASY)

  • Open pgAdmin, the graphical user interface for PostgreSQ (Currently the application is pgAdmin 4).

  • In the Object Explorer section on the right there is a Servers drop down. Click on it. You will be prompted for a password that you setup earlier.

  • After keying the password, you will see everything under the servers dropdown.

  • Right click the "Databases" drop down and click "Create" and then "Database…​".

Creating local database through pgAdmin
Figure 2. Creating local database through pgAdmin

  • In the "Database" field, enter the database name. For example if you are creating a master database you can name it "local_master" or "local_akaun_master" and "local_tenant" or"local_akaun_tenant" for a tenant database respectively.

  • Under owner select "akaun". It is the super user you created earlier.

Fill up local database details
Figure 3. Fill up local database details

  • Click "Save" to create the new local database.

Through Dbeaver Graphical User Interface (EASY)

It is also possible to create local databases through the dbeaver GUI. This is covered in the dbeaver section over here.

Through Command-Line Interface (HARD)

  • Use these commands in command prompt:

    1. psql –U postgres (hit enter and enter your postgres password)

    2. CREATE DATABASE <database name>; (here my db name is localdb2)

    3. You can follow the appropriate naming convention as mentioned earlier as in the future you might have many local databases to reflect various use cases.

  • MasterPage2