Dynamically Managing Database Schemas with NestJS and Knex.js

In the world of backend development, managing database schemas can be a challenge. If you’re building complex applications that require changes to the database structure over time, you might find yourself manually creating and updating database tables. This article presents a more efficient approach.

Let’s take a deep dive into a code snippet that leverages the power of NestJS, a progressive Node.js framework for building efficient and scalable server-side applications, and Knex.js, a SQL query builder that is designed to be flexible, portable, and fun to use.

The provided solution allows us to create or update database tables dynamically, driven by request payloads. This will greatly enhance our ability to manage our database schema in a more automated and flexible manner. Let’s break down the code into smaller snippets and understand what each part does.

import { Body, Controller, Post } from '@nestjs/common'; 
import { KnexSchemaBuilderService } from './knex.service'; 
 
@Controller('knex') 
export class KnexController { 
  constructor(private readonly knexService: KnexSchemaBuilderService) {} 
 
  @Post() 
  async createOrUpdateTable(@Body() body): Promise<string> { 
    const { tableName, schema } = body; 
 
    if (!tableName || !schema) { 
      throw new Error('Both tableName and schema are required'); 
    } 
 
    await this.knexService.createOrUpdateTable(tableName, schema); 
 
    return `Table ${tableName} created or updated successfully`; 
  } 
}

The KnexController class handles the incoming HTTP POST requests. The createOrUpdateTable() method is decorated with the @Post() decorator and is responsible for creating or updating the database table. The method takes the incoming request’s body as an argument, where we expect to receive the tableName and schema. If any of these are missing, it will throw an error. Upon receiving valid data, it calls the createOrUpdateTable() method from the KnexSchemaBuilderService.

import { Module } from '@nestjs/common';
import { KnexController } from './knex.controller';
import { KnexSchemaBuilderService } from './knex.service';

@Module({
  imports: [],
  controllers: [KnexController],
  providers: [KnexSchemaBuilderService],
})
export class KnexSchemaModule {}

Here, we define the KnexSchemaModule that encapsulates the KnexController and KnexSchemaBuilderService for dependency injection by NestJS.

import { Injectable } from '@nestjs/common';
import { Knex, knex } from 'knex';

@Injectable()
export class KnexSchemaBuilderService {
  private knex: Knex;
  constructor() {
    this.knex = knex({
      client: 'mssql',
      connection: {
        host: 'localhost',
        user: 'xxxxx',
        password: 'xxxx',
        database: 'xxxxx',
      },
    });
  }
  // rest of the class
}

The KnexSchemaBuilderService is the core of our code. This service creates an instance of Knex, configured for an MSSQL database with the provided connection parameters.

async createOrUpdateTable(tableName: string, schema: any): Promise<void> {
  if (await this.knex.schema.hasTable(tableName)) {
    await this.updateTable(tableName, schema);
  } else {
    await this.createTable(tableName, schema);
  }
}

This method is the service’s main entry point, checking if the table exists in the database. If it does, it will update it, and if not, it will create it.

private async createTable(tableName: string, schema: any): Promise<void> {
  await this.knex.schema.createTable(tableName, (table) => {
    table.increments('id').primary();

    for (const key in schema) {
      const type = schema[key];
      this.addColumn(table, key, type);
    }
  });
}

This method will create a new table based on the provided schema. It automatically adds an ‘id’ column as the primary key.

private async updateTable(tableName: string, schema: any): Promise<void> {
  const existingColumns = await this.knex(tableName).columnInfo();

  await this.knex.schema.alterTable(tableName, (table) => {
    for (const key in schema) {
      const type = schema[key];
      if (!existingColumns[key]) {
        this.addColumn(table, key, type);
      }
    }

    for (const key in existingColumns) {
      if (!schema[key] && key !== 'id') {
        table.dropColumn(key);
      }
    }
  });
}

The updateTable() method will check the existing columns in the table and alter the table according to the provided schema. It will add any new columns from the schema that are not present in the table. It will also remove any columns that exist in the table but are not present in the new schema, with the exception of the ‘id’ column.

In conclusion, this NestJS module combined with Knex.js service allows us to create and update SQL database schemas dynamically, providing a powerful tool for managing evolving database structures. Remember to always handle schema changes with care, as they can result in data loss if not managed properly.