@unidev-hub/database

A standardized database package for TypeScript microservices using PostgreSQL and Drizzle ORM.

Features

• 🔌 Connection management with pooling
• 📁 Repository pattern implementation
• 📋 Pagination utilities
• 🔄 Transaction helpers
• 🚀 Migration runner
• 🔍 Type-safe queries using Drizzle ORM

Installation

npm install @unidev-hub/database

Usage

Connection Management

import { createDatabaseConnection } from '@unidev-hub/database';
import { createLogger } from '@unidev-hub/logger';

const logger = createLogger({ serviceName: 'user-service' });

const db = createDatabaseConnection({
  connectionString: 'postgres://user:password@localhost:5432/mydb',
  maxConnections: 10,
  logger,
});

// Initialize the connection
await db.initialize();

// Use the database
const result = await db.db.execute(sql`SELECT * FROM users`);

// Close when done
await db.close();

Defining Schema with Drizzle

// schema.ts
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  password: text('password').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Implementing Repository Pattern

// user-repository.ts
import { 
  BaseRepository, 
  createDatabaseConnection, 
  eq 
} from '@unidev-hub/database';
import { users, User, NewUser } from './schema';

type UserUpdateDTO = Partial<Omit<NewUser, 'id'>>;

export class UserRepository extends BaseRepository<User, NewUser, UserUpdateDTO, typeof users> {
  constructor(connection: DatabaseConnection, logger?: Logger) {
    super(connection, users, users.id, { logger });
  }
  
  // Custom method example
  async findByEmail(email: string): Promise<User | null> {
    const result = await this.db
      .select()
      .from(this.table)
      .where(eq(users.email, email))
      .limit(1);
      
    return result.length ? result[0] : null;
  }
}

// Usage
const dbConnection = createDatabaseConnection({ 
  connectionString: 'postgres://user:password@localhost:5432/mydb' 
});
await dbConnection.initialize();

const userRepo = new UserRepository(dbConnection, logger);

// Use the repository
const user = await userRepo.findById(1);
const newUser = await userRepo.create({ 
  name: 'John Doe', 
  email: '[email protected]',
  password: 'hashed_password'
});

Pagination

// Get paginated users
const paginatedUsers = await userRepo.findPaginated(
  { page: 2, limit: 20 },
  { condition: eq(users.active, true) },
  { field: 'created_at', direction: 'desc' }
);

console.log(`Page ${paginatedUsers.pagination.page} of ${paginatedUsers.pagination.pages}`);
console.log(`Total users: ${paginatedUsers.pagination.total}`);
console.log(`Users per page: ${paginatedUsers.pagination.limit}`);
console.log('User data:', paginatedUsers.data);

Transactions

// Execute multiple operations in a transaction
const transferResult = await userRepo.withTransaction(async (tx) => {
  // All operations in this callback use the same transaction
  await tx.update(accounts)
    .set({ balance: sql`balance - ${amount}` })
    .where(eq(accounts.id, fromAccountId));
    
  await tx.update(accounts)
    .set({ balance: sql`balance + ${amount}` })
    .where(eq(accounts.id, toAccountId));
    
  // Return any data needed from the transaction
  return { success: true };
});

Running Migrations

import { runMigrations } from '@unidev-hub/database';

async function migrate() {
  await runMigrations({
    connectionString: 'postgres://user:password@localhost:5432/mydb',
    migrationsFolder: './drizzle',
    logger,
  });
}

migrate().catch(console.error);

Best Practices

1. Repository Pattern

   • Extend BaseRepository for each entity
   • Add custom methods for entity-specific queries
   • Use repository methods instead of raw queries when possible

2. Transactions

   • Use withTransaction for operations that need to be atomic
   • Keep transaction blocks as short as possible

3. Connection Management

   • Create one connection per service
   • Close connections when shutting down

4. Migrations

   • Run migrations during deployment/startup
   • Use Drizzle Kit for generating migrations

License

MIT