Initialize the workspace structure and instal required dependencies. Create source directories and bootstrap the Node.js project.

mkdir typeorm-db-demo && cd typeorm-db-demo
mkdir src && npm init -y
npm install reflect-metadata bcryptjs dotenv typeorm typeorm-naming-strategies ts-node ts-jest jest @types/jest --save-dev

Generate a TypeScript compiler configuration file and customize it to support decorators and metadata emission:

{
  "compilerOptions": {
    "target": "ES2018",
    "module": "commonjs",
    "lib": ["es2017", "esnext"],
    "outDir": "./dist",
    "rootDir": "./src",
    "strict": true,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "declaration": true,
    "resolveJsonModule": true,
    "sourceMap": true
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist"]
}

Configure the ORM and testing environments. The ORM configuration loads connection parameters from system variables and applies a custom naming convention. The test configuration points to the TypeScript Jest preset.

// ormconfig.js
const { SnakeNamingStrategy } = require('typeorm-naming-strategies');

class LowerCaseNamingStrategy extends SnakeNamingStrategy {
  primaryKeyCreation(tableName, columnNames) {
    const baseName = tableName instanceof Object ? tableName.name : tableName;
    const snakeCols = columnNames.join('_').toLowerCase();
    return `pk_${baseName}_${snakeCols}`.toLowerCase();
  }
}

module.exports = {
  type: process.env.DRIVER_TYPE || 'mssql',
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '1433'),
  username: process.env.USERNAME,
  password: process.env.PASSWORD,
  database: process.env.DATABASE_NAME,
  synchronize: false,
  logging: true,
  entities: ['src/modules/**/*.entity.{ts,js}'],
  migrations: ['src/migrations/*.ts'],
  namingStrategy: new LowerCaseNamingStrategy(),
  cli: {
    entitiesDir: 'src/modules',
    migrationsDir: 'src/migrations'
  }
};

// jest.config.js
module.exports = {
  preset: 'ts-jest',
  testEnvironment: 'node',
  transform: {
    '^.+\\.ts$': 'ts-jest'
  }
};

Define environment variables for local development:

# .env.local
APP_ENV=development
DRIVER_TYPE=mssql
DB_HOST=localhost
DB_PORT=1433
USERNAME=sa
PASSWORD=SecurePass123!
DATABASE_NAME=app_development
HASH_ROUNDS=10

Exclude runtime artifacts and IDE-specific files from version control:

# .gitignore
node_modules/
dist/
.env*
*.log
.vscode/
.idea/

Structuring Data base Models

Organize database models within the src/modules directory. Establish relationships and apply validation constraints.

// src/modules/account-role.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, OneToMany, CreateDateColumn, UpdateDateColumn, DeleteDateColumn } from 'typeorm';
import { AppUserProfile } from './app-user-profile.entity';

@Entity({ name: 'account_roles' })
export class AccountRole {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', length: 100 })
  roleName: string;

  @Column({ type: 'tinyint', default: 1 })
  isActive: boolean;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @DeleteDateColumn()
  deletedAt?: Date;

  @OneToMany(() => AppUserProfile, (profile) => profile.role)
  associatedProfiles: AppUserProfile[];
}

// src/modules/app-user-profile.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, JoinColumn, Unique, CreateDateColumn, UpdateDateColumn, DeleteDateColumn } from 'typeorm';
import { AccountRole } from './account-role.entity';

@Unique('UQ_profile_email', ['emailAddress'])
@Unique('UQ_profile_username', ['username'])
@Entity({ name: 'app_user_profiles' })
export class AppUserProfile {
  @PrimaryGeneratedColumn()
  profileId: number;

  @Column({ type: 'varchar', length: 255 })
  username: string;

  @Column({ type: 'varchar', length: 255 })
  emailAddress: string;

  @Column({ type: 'varchar', length: 512, select: false })
  credentialHash: string;

  @ManyToOne(() => AccountRole, (role) => role.associatedProfiles, { eager: true })
  @JoinColumn({ name: 'role_id' })
  role: AccountRole;

  @Column({ type: 'tinyint', default: 0 })
  lockStatus: number;

  @Column({ type: 'varchar', length: 255 })
  authToken: string;

  @CreateDateColumn()
  registeredOn: Date;

  @UpdateDateColumn()
  lastModified: Date;

  @DeleteDateColumn({ nullable: true })
  softDeletedOn?: Date;
}

Aggregate entity exports in an index file to simplify imports throughout the applicasion:

// src/modules/index.ts
export { AccountRole } from './account-role.entity';
export { AppUserProfile } from './app-user-profile.entity';

Automating Schema Translations

Run the initialization script to compile dependencies, then trigger the TypeORM CLI to inspect entity differences and produce incremental migration scripts.

npm install
npx dotenv -f .env.local -- npx typeorm-ts-node-esm migration:generate -d ormconfig.js src/migrations/CreateInitialSchema

Review the generated SQL operations inside the migration file, execute the batch to apply structural changes, and verify table creation.

npx dotenv -f .env.local -- npx typeorm-ts-node-esm migration:run -d ormconfig.js

Populating Foundational Data

Create a dedicated folder for data population logic. Define seed classes that interact directly with the repository layer.

// src/seeds/create-admin-data.seed.ts
import { Seeder, Factory } from 'typeorm-seeding';
import { Connection } from 'typeorm';
import { AccountRole } from '../modules/account-role.entity';
import { AppUserProfile } from '../modules/app-user-profile.entity';
import bcrypt from 'bcryptjs';

export default class InitialAdminSeeder implements Seeder {
  public async run(factory: Factory, conn: Connection): Promise<void> {
    try {
      const roleRepo = conn.getRepository(AccountRole);
      const userRepo = conn.getRepository(AppUserProfile);

      await roleRepo.query('TRUNCATE TABLE account_roles;');
      await userRepo.query('TRUNCATE TABLE app_user_profiles;');

      const defaultRole = { name: 'SuperAdmin', isActive: true };
      const adminPayload = { 
        username: 'root_operator', 
        emailAddress: 'admin@example.com',
        credentialHash: '' 
      };

      const roleInstance = roleRepo.create(defaultRole);
      const userInstance = userRepo.create(adminPayload);
      
      userInstance.credentialHash = await bcrypt.hash('DefaultP@ssw0rd!', parseInt(process.env.HASH_ROUNDS || '10'));

      roleInstance.associatedProfiles = [userInstance];
      
      await roleRepo.save(roleInstance);
      console.log('✅ Foundation data injection successful.');
    } catch (err) {
      console.error('❌ Seed execution failed:', err.message);
    }
  }
}

Orchestrate the seed runner outside the main application lifecycle. This script boots the connection pool, executes registered factories, and terminates gracefully.

// src/scripts/bootstrap-database.ts
import 'reflect-metadata';
import { createConnection, getConnectionOptions } from 'typeorm';
import { Seeder, factory } from 'typeorm-seeding';
import InitialAdminSeeder from '../seeds/create-admin-data.seed';

async function executeSeeding() {
  try {
    const config = await getConnectionOptions();
    const dbConn = await createConnection(config);

    const registeredFactories: Seeder[] = [new InitialAdminSeeder()];

    for (const factoryInstance of registeredFactories) {
      await factoryInstance.run(factory, dbConn);
    }

    await dbConn.destroy();
    console.log('🌱 Database preparation finished.');
  } catch (runError) {
    console.error('💥 Runtime failure during seeding:', runError);
  }
}

executeSeeding();

Register the execution pathway in package.json to streamline the workflow:

{
  "scripts": {
    "bootstrap": "ts-node src/scripts/bootstrap-database.ts"
  }
}

Trigger the command within your terminal to populate the target schema with predefined administrative credentials.