Skip to content

Database

The 2KRIKA platform uses a dual-database architecture combining PostgreSQL (via Sequelize ORM) for relational data and MongoDB (via Mongoose) for chat messages. This hybrid approach optimizes for both transactional integrity and flexible document storage.

Overview

  • PostgreSQL (Primary) – Relational data with ACID transactions
  • MongoDB (Secondary) – Chat messages and real-time data
  • Sequelize ORM – SQL query builder and migrations
  • Mongoose ODM – MongoDB schema and validation

Database Architecture

PostgreSQL (Sequelize)

Used for: Core business entities requiring relational integrity and transactions

  • Users, Roles, Permissions
  • Services, Categories, Options
  • Orders, Payments, Refunds
  • Accounts, Withdrawals, Transactions
  • Identity/KYC records
  • Upload metadata

MongoDB (Mongoose)

Used for: High-volume, flexible document storage

  • Chat messages with attachments
  • Real-time communication logs

PostgreSQL Setup

Connection Configuration

// adapters/shared/db.ts
import { Sequelize } from 'sequelize';

const sequelize = new Sequelize(process.env.SQL_DATABASE_URL!, {
  benchmark: true,
  pool: {
    max: 10,        // Maximum connections
    min: 0,         // Minimum connections
    acquire: 30000, // Max time to acquire connection (ms)
    idle: 10000,    // Max idle time before releasing
  },
  logging: (sql, timing) => {
    logger.query(sql, timing);
  },
});

Environment Variables

# PostgreSQL
SQL_DATABASE_URL=postgresql://user:password@localhost:5432/2krika
SQL_DATABASE_URL_TEST=postgresql://user:password@localhost:5432/2krika_test

# Connection pool
DB_POOL_MAX=10
DB_POOL_MIN=0
DB_POOL_ACQUIRE=30000
DB_POOL_IDLE=10000

Models

Sequelize models are auto-loaded from adapters/shared/sql/models/:

// adapters/shared/sql/models/user.model.ts
import { DataTypes, Model, Sequelize } from 'sequelize';

export default (sequelize: Sequelize) => {
  class User extends Model {
    declare id: string;
    declare fullName: string;
    declare email: string;
    declare passwordHash: string;
    declare isActive: boolean;
    // ... more fields
  }

  User.init(
    {
      id: {
        type: DataTypes.UUID,
        primaryKey: true,
      },
      fullName: {
        type: DataTypes.STRING(100),
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING(255),
        allowNull: false,
        unique: true,
      },
      passwordHash: {
        type: DataTypes.STRING(255),
        allowNull: false,
      },
      isActive: {
        type: DataTypes.BOOLEAN,
        defaultValue: false,
      },
      // ... more fields
    },
    {
      sequelize,
      modelName: 'User',
      tableName: 'users',
      timestamps: true,
      underscored: true,
    }
  );

  return User;
};

Associations

Model relationships defined in associations.ts:

// adapters/shared/sql/models/associations.ts
export function associate(db: any) {
  // User - Service (one-to-many)
  db.User.hasMany(db.Service, {
    foreignKey: 'ownerId',
    as: 'services',
  });
  db.Service.belongsTo(db.User, {
    foreignKey: 'ownerId',
    as: 'owner',
  });

  // User - Order (as customer)
  db.User.hasMany(db.Order, {
    foreignKey: 'customerId',
    as: 'orders',
  });

  // User - Order (as seller)
  db.User.hasMany(db.Order, {
    foreignKey: 'sellerId',
    as: 'sales',
  });

  // Service - Category
  db.Service.belongsTo(db.Category, {
    foreignKey: 'categoryId',
    as: 'category',
  });

  // Order - Service
  db.Order.belongsTo(db.Service, {
    foreignKey: 'serviceId',
    as: 'service',
  });

  // ... more associations
}

MongoDB Setup

Connection Configuration

// adapters/shared/db.ts
import mongoose from 'mongoose';

mongoose.connect(process.env.MONGO_DATABASE_URL!, {
  dbName: process.env.MONGO_DATABASE_NAME || '2krika',
});

mongoose.connection.on('connected', () => {
  logger.info('MongoDB connected');
});

mongoose.connection.on('error', (err) => {
  logger.error(`MongoDB connection error: ${err}`);
});

Environment Variables

# MongoDB
MONGO_DATABASE_URL=mongodb://localhost:27017
MONGO_DATABASE_NAME=2krika
MONGO_DATABASE_NAME_TEST=2krika_test

Mongoose Models

// adapters/shared/mongo/models/message.model.ts
import mongoose, { Schema, Document } from 'mongoose';

export interface IMessage extends Document {
  chatId: string;
  senderId: string;
  content: string;
  timestamp: Date;
  attachments: Array<{
    path: string;
    type: string;
    size: number;
  }>;
}

const MessageSchema = new Schema<IMessage>({
  chatId: {
    type: String,
    required: true,
    index: true,
  },
  senderId: {
    type: String,
    required: true,
  },
  content: {
    type: String,
    required: true,
  },
  timestamp: {
    type: Date,
    default: Date.now,
    index: true,
  },
  attachments: [{
    path: String,
    type: String,
    size: Number,
  }],
});

// Compound index for efficient queries
MessageSchema.index({ chatId: 1, timestamp: -1 });

export default mongoose.model<IMessage>('Message', MessageSchema);

Migrations

Migrations managed with Sequelize CLI:

Running Migrations

# Run all pending migrations
pnpm run migrate

# Rollback last migration
pnpm run migrate:undo

# Create new migration
npx sequelize-cli migration:generate --name add-kyc-status-to-users

Migration Example

// migrations/20240101000000-create-users-table.js
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('users', {
      id: {
        type: Sequelize.UUID,
        primaryKey: true,
        defaultValue: Sequelize.UUIDV4,
      },
      full_name: {
        type: Sequelize.STRING(100),
        allowNull: false,
      },
      email: {
        type: Sequelize.STRING(255),
        allowNull: false,
        unique: true,
      },
      password_hash: {
        type: Sequelize.STRING(255),
        allowNull: false,
      },
      is_active: {
        type: Sequelize.BOOLEAN,
        defaultValue: false,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
      },
    });

    // Add indexes
    await queryInterface.addIndex('users', ['email']);
    await queryInterface.addIndex('users', ['is_active']);
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('users');
  },
};

Database Transactions

Sequelize Transactions

// Example: Order creation with transaction
async function createOrder(orderData: any) {
  const transaction = await sequelize.transaction();

  try {
    // Create order
    const order = await Order.create(orderData, { transaction });

    // Update service sales count
    await Service.increment('salesCount', {
      where: { id: orderData.serviceId },
      transaction,
    });

    // Create chat
    await Chat.create({
      orderId: order.id,
      members: [order.customerId, order.sellerId],
    }, { transaction });

    // Commit transaction
    await transaction.commit();
    return order;
  } catch (error) {
    // Rollback on error
    await transaction.rollback();
    throw error;
  }
}

Manual Transaction Management

// Using repository pattern
const transaction = await this.orderRepository.begin();

try {
  await this.orderRepository.add(order, { transaction });
  await this.accountRepository.credit(sellerId, amount, { transaction });
  await transaction.commit();
} catch (error) {
  await transaction.rollback();
  throw error;
}

Query Optimization

Indexes

// Add index in migration
await queryInterface.addIndex('orders', ['state', 'created_at']);
await queryInterface.addIndex('services', ['status', 'category_id']);
await queryInterface.addIndex('transactions', ['user_id', 'created_at']);

Eager Loading

// Load related entities efficiently
const service = await Service.findByPk(serviceId, {
  include: [
    {
      model: User,
      as: 'owner',
      attributes: ['id', 'fullName', 'username'],
    },
    {
      model: Category,
      as: 'category',
    },
    {
      model: Option,
      as: 'options',
    },
  ],
});

Pagination

const { page = 1, limit = 20 } = query;
const offset = (page - 1) * limit;

const { count, rows } = await Service.findAndCountAll({
  where: { status: 'active' },
  limit,
  offset,
  order: [['createdAt', 'DESC']],
});

return {
  results: rows,
  count,
  page,
  limit,
  totalPages: Math.ceil(count / limit),
};

Database Best Practices

✅ Do

  • Use transactions for multi-step operations
  • Add indexes on frequently queried columns
  • Use eager loading to avoid N+1 queries
  • Validate data before database operations
  • Use migrations for schema changes
  • Back up regularly (automated backups)
  • Monitor slow queries and optimize
  • Use connection pooling effectively

❌ Don't

  • Don't skip migrations – Always use migrations for schema changes
  • Don't expose raw SQL – Use Sequelize query builder
  • Don't forget transactions – Use for multi-table operations
  • Don't over-index – Indexes have write overhead
  • Don't store large files – Use file storage instead
  • **Don't use SELECT *** – Specify needed columns
  • Don't hard-delete – Use soft deletes where appropriate

Backup and Restore

PostgreSQL Backup

# Backup database
pg_dump -U user -d 2krika -F c -f backup_$(date +%Y%m%d).dump

# Restore database
pg_restore -U user -d 2krika -c backup_20240101.dump

MongoDB Backup

# Backup MongoDB
mongodump --uri="mongodb://localhost:27017/2krika" --out=backup_$(date +%Y%m%d)

# Restore MongoDB
mongorestore --uri="mongodb://localhost:27017/2krika" backup_20240101/2krika

Testing

Test Database

// Use separate test database
const dbUrl = process.env.NODE_ENV === 'test'
  ? process.env.SQL_DATABASE_URL_TEST
  : process.env.SQL_DATABASE_URL;

const sequelize = new Sequelize(dbUrl);

Database Factories

// tests/factories.ts
export async function createUser(overrides = {}) {
  return await User.create({
    id: generateId(),
    fullName: 'Test User',
    email: 'test@example.com',
    passwordHash: await hasher.hash('password'),
    isActive: true,
    ...overrides,
  });
}

export async function createService(overrides = {}) {
  const owner = await createUser();
  return await Service.create({
    id: generateId(),
    title: 'Test Service',
    ownerId: owner.id,
    basicPrice: 10000,
    ...overrides,
  });
}

Summary

The database infrastructure provides:

  • Dual-database architecture (PostgreSQL + MongoDB)
  • Sequelize ORM for SQL operations
  • Mongoose ODM for MongoDB
  • Migration management for schema evolution
  • Transaction support for data consistency
  • Connection pooling for performance
  • Query optimization with indexes and eager loading

This architecture balances relational integrity with flexible document storage for optimal performance.