Fix: Strapi Database Auto-Creation

Problem

Strapi CMS fails to start with error:

error: database "strapi_qual" does not exist

This occurs when the PostgreSQL database for Strapi hasn't been created yet.

Root Cause

The init-multiple-databases.sh script only runs when PostgreSQL is first initialized (when the data volume is empty). If: - PostgreSQL was already initialized - The database was deleted - The POSTGRES_MULTIPLE_DATABASES environment variable wasn't set during initialization

Then the strapi_qual database won't exist, causing Strapi to fail on startup.

Solution

Added auto-creation logic to Strapi's bootstrap function, similar to what was implemented for notification-service and user-management. The database is now automatically created if it doesn't exist when Strapi starts.

Implementation

Modified File

services/strapi-cms/src/index.ts

Changes

1. Added ensureDatabase() function:
2. Parses DATABASE_URL to extract database name
3. Attempts to connect to the database
4. If database doesn't exist (error code 3D000), creates it

5. Connects to default postgres database to create the target database

6. Updated bootstrap() function:

7. Calls ensureDatabase() before Strapi connects to the database
8. Only runs if DATABASE_CLIENT is postgres or DATABASE_URL is set
9. Handles errors gracefully (doesn't prevent Strapi from starting)

Code Example

async function ensureDatabase() {
  const databaseUrl = process.env.DATABASE_URL;

  if (!databaseUrl) {
    console.warn('DATABASE_URL not set. Skipping database auto-creation.');
    return;
  }

  // Parse database URL to extract database name
  const url = new URL(databaseUrl);
  const databaseName = url.pathname.slice(1);

  // Try to connect to the database
  try {
    const testPool = new Pool({
      connectionString: databaseUrl,
      ssl: false as any,
    });
    const testClient = await testPool.connect();
    testClient.release();
    await testPool.end();
    console.log(`Database '${databaseName}' exists and is accessible.`);
  } catch (error: any) {
    // If database doesn't exist, create it
    if (error.code === '3D000' || error.message?.includes('does not exist')) {
      console.log(`Database '${databaseName}' does not exist. Attempting to create it...`);

      // Connect to default 'postgres' database to create the target database
      const adminUrl = new URL(databaseUrl);
      adminUrl.pathname = '/postgres';

      const adminPool = new Pool({
        connectionString: adminUrl.toString(),
        ssl: false as any,
      });

      const client = await adminPool.connect();
      try {
        // Check if database already exists
        const checkResult = await client.query(
          'SELECT 1 FROM pg_database WHERE datname = $1',
          [databaseName]
        );

        if (checkResult.rows.length === 0) {
          // Escape database name to prevent SQL injection
          const escapedDbName = `"${databaseName.replace(/"/g, '""')}"`;
          await client.query(`CREATE DATABASE ${escapedDbName}`);
          console.log(`✅ Database '${databaseName}' created successfully`);
        } else {
          console.log(`Database '${databaseName}' already exists`);
        }
      } catch (createError: any) {
        console.error(`Failed to create database '${databaseName}':`, createError.message);
        throw createError;
      } finally {
        client.release();
        await adminPool.end();
      }
    } else {
      console.error('Failed to connect to database:', error.message);
      throw error;
    }
  }
}

Verification

After deploying the fix, Strapi should:

1. On first startup:
2. Detect that strapi_qual doesn't exist
3. Create the database automatically
4. Log: ✅ Database 'strapi_qual' created successfully

5. Continue with normal startup

6. On subsequent startups:

7. Detect that database exists
8. Log: Database 'strapi_qual' exists and is accessible.
9. Continue with normal startup

Manual Database Creation (Alternative)

If you need to create the database manually:

# Connect to PostgreSQL container
docker exec -it po-postgres psql -U postgres

# Create database
CREATE DATABASE strapi_qual;

# Grant permissions (if needed)
GRANT ALL PRIVILEGES ON DATABASE strapi_qual TO postgres;

# Exit
\q

Related Services

This same auto-creation pattern is used in: - notification-service (services/notification-service/src/common/database/database.service.ts) - user-management (services/user-management/src/db.ts)

Dependencies

• pg package (already in package.json)
• PostgreSQL connection credentials from DATABASE_URL environment variable

Notes

• The auto-creation only works if the PostgreSQL user has CREATEDB privilege (which postgres user has by default)
• Database name is extracted from DATABASE_URL environment variable
• The function gracefully handles errors and doesn't prevent Strapi from starting if database creation fails