Automate Database Creation and Schema Setup for Infrastructure

[rohit-sourcefuse]

Overview:
As part of our project's infrastructure setup, it's essential to automate the process of creating databases and setting up schemas for our microservices. This automation will help in several ways, including ensuring that when running lerna-db:migrate or similar commands, databases and schemas already exist, reducing the likelihood of issues related to missing databases or schemas. Additionally, it will streamline the deployment of new infrastructure with fewer manual configurations.

Issue Details:
Currently, our infrastructure setup process may require manual database creation and schema setup, leading to potential errors and inefficiencies. To improve this process, we propose the following steps:

Proposed Solution:

Automated Database Creation:

Develop scripts or use infrastructure as code (IaC) tools like Terraform or Docker Compose to automate the creation of databases required for our microservices.
Ensure that the scripts or IaC templates are versioned and maintainable.

Automated Schema Setup:

Develop scripts or use database migration tools like Knex.js, Sequelize, or Flyway to automate schema setup within the databases.
These scripts should be able to create tables, indexes, and other schema components required for each microservice.

Documentation:

Document the automated setup process, including the steps and tools used.
Provide clear instructions for developers and operators to execute the setup process.

Integration with CI/CD:

Integrate the automated setup process into our CI/CD pipeline so that it's executed automatically during deployments.

Expected Benefits:

Reduced risk of missing databases or schemas when running commands like lerna-db:migrate.
Streamlined and efficient infrastructure setup for new deployments.
Improved developer experience by reducing manual configuration steps.

Additional Information:

This enhancement is essential for maintaining a smooth development and deployment process.
The choice of tools and scripts for automation should be based on project requirements and best practices.

Environment:

Project: ISBC
Infrastructure: ARC backend

[rohit-sourcefuse]

Suggested Solution: Using Liquibase with Node.js

To automate database creation and schema setup using Liquibase with Node.js in your existing project, follow these steps:

Step 1: Set Up Liquibase Change Scripts

Ensure you have Liquibase change scripts for each microservice that define the initial database schema. If you already have these scripts, you can use them.

Step 2: Create a Node.js Application

If you don't already have a Node.js application, create one in your existing project to manage the database creation process.

Step 3: Write a Node.js Script

Here's a Node.js script that reads environment variables to determine required databases and runs Liquibase to create them. This script includes proper error handling and is suitable for an existing project:

const liquibase = require('liquibase');
const dotenv = require('dotenv');
const path = require('path');

/**
 * Automated Database Creation and Schema Setup
 *
 * This script automates the creation of PostgreSQL databases and schema setup using Liquibase.
 * It attempts to create the database if it doesn't exist.
 *
 * IMPORTANT: This code is provided as a hypothetical solution and should be thoroughly tested in your environment.
 */

// Load environment variables from .env file
dotenv.config();

// Define the directory where your Liquibase change scripts are located
const changeLogDirectory = path.join(__dirname, 'changelogs');

// Read the list of databases from your .env file
const databases = [
  'USER-SERVICE_DB_DATABASE',
  'AUTH-SERVICE_DB_DATABASE',
  'AUDIT-LOG-SERVICE_DB_DATABASE',
  'DOCUMENT-SERVICE_DB_DATABASE'
];

// Loop through each database and run Liquibase
(async () => {
  for (const databaseVar of databases) {
    const databaseName = process.env[databaseVar];

    const liquibaseOptions = {
      changeLogFile: path.join(changeLogDirectory, `${databaseName}-changelog.xml`),
      // Use a modified JDBC URL with 'createDatabaseIfNotExist=true' to attempt creation
      url: `jdbc:postgresql://localhost:5432/${databaseName}?createDatabaseIfNotExist=true`,
      username: process.env[`${databaseVar}_DB_USER`],
      password: process.env[`${databaseVar}_DB_PASSWORD`],
      classpath: 'liquibase.jar' // Path to Liquibase JAR file
    };

    try {
      await liquibase.run(liquibaseOptions);
      console.log(`Database ${databaseName} created or updated successfully.`);
    } catch (error) {
      console.error(`Error creating or updating database ${databaseName}:`, error);
    }
  }
})();

This updated code will attempt to create the database if it doesn't exist, as you mentioned. Please ensure thorough testing in your environment before using it, as it's a hypothetical solution.

Step 4: Add Liquibase as a Dev-Dependency

Ensure Liquibase is added as a dev-dependency in your package.json:

"devDependencies": {
  "liquibase": "^4.20.0" // Update to the appropriate version
}

Step 5: Modify package.json

To add a step in your package.json that runs the Node.js script before running lerna-db:migrate, you can define a custom script in the scripts section of your package.json. Here's how to do it:

1. Open your package.json file.

2. Modify the scripts section to include a custom prelerna-db:migrate script. This script will run automatically before lerna-db:migrate, thanks to the pre-naming convention:

"scripts": {
  "prelerna-db:migrate": "node create-databases.js",
  "lerna-db:migrate": "lerna-db:migrate", // Your existing script
  // ... other scripts ...
},

In the above example:

1. "prelerna-db:migrate" is the name of the custom script with the pre prefix.
2. "node create-databases.js" runs the Node.js script you've created earlier (assuming your script is named create-databases.js).

Save your package.json file.

Now, the custom script prelerna-db:migrate will automatically execute before the lerna-db:migrate script when you run npm run lerna-db:migrate. You don't need to explicitly call the custom script; npm will take care of the execution order.

Please ensure thorough testing in your environment before using it, as it's a hypothetical solution, not tested on my local machine.