Banking App (MySQL with Drizzle ORM)

This document outlines the MySQL database schema for a sample banking application. The schema is designed to be efficient, scalable, and easy to understand. We use Drizzle ORM for defining our data models with TypeScript.

The core of our application revolves around four main tables: User, Account, Transaction and Ledger.

Installation Guide

To add these schemas to your project, run:

npx servercn-cli add schema banking-app

If you install schema individually, the relationships between them won't be automatic—you'll need to implement them manually.

1. User Schema

The User schema stores essential information about the customer. This includes personal details and security credentials.

Path: src/drizzle/schemas/user.schema.ts

import {
  mysqlTable,
  varchar,
  boolean,
  timestamp,
  int,
  json,
  uniqueIndex,
  index,
  mysqlEnum
} from "drizzle-orm/mysql-core";
import { timestamps } from "./schema.helper";
 
export interface IAvatar {
  public_id?: string;
  url: string;
  size?: number;
}
 
export const users = mysqlTable(
  "users",
  {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 100 }).notNull(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    password: varchar("password", { length: 255 }),
    role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
 
    provider: mysqlEnum("provider", ["local", "google", "github"])
      .default("local")
      .notNull(),
    providerId: varchar("provider_id", { length: 255 }),
 
    avatar: json("avatar").$type<IAvatar>(),
 
    isEmailVerified: boolean("is_email_verified").default(false).notNull(),
    lastLoginAt: timestamp("last_login_at"),
    failedLoginAttempts: int("failed_login_attempts").default(0).notNull(),
    lockUntil: timestamp("lock_until"),
 
    isDeleted: boolean("is_deleted").default(false).notNull(),
    deletedAt: timestamp("deleted_at"),
    reActivateAvailableAt: timestamp("re_activate_available_at"),
 
    ...timestamps
  },
  table => [
    uniqueIndex("email_idx").on(table.email),
    index("role_idx").on(table.role),
    index("is_deleted_idx").on(table.isDeleted)
  ]
);
 
 
//? User type
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Installation

npx servercn-cli add schema banking-app/user

2. Account Schema

The Account schema stores user bank accounts (e.g., savings, current). A user can own multiple accounts with different types, currencies, and statuses.

Path: src/drizzle/schemas/account.schema.ts

import {
  mysqlTable,
  boolean,
  int,
  mysqlEnum,
  index
} from "drizzle-orm/mysql-core";
import { timestamps } from "./schema.helper";
import { users } from "./user.schema";
 
//? Account types
export const ACCOUNT_TYPES = ["savings", "current"] as const;
export type AccountType = (typeof ACCOUNT_TYPES)[number];
 
//? Account currencies
export const ACCOUNT_CURRENCIES = ["NPR", "INR", "USD"] as const;
export type AccountCurrency = (typeof ACCOUNT_CURRENCIES)[number];
 
//? Account status
export const ACCOUNT_STATUS = ["active", "frozen", "closed"] as const;
export type AccountStatus = (typeof ACCOUNT_STATUS)[number];
 
export const accounts = mysqlTable(
  "accounts",
  {
    id: int("id").primaryKey().autoincrement(),
    userId: int("user_id")
      .references(() => users.id)
      .notNull(),
    type: mysqlEnum("type", ACCOUNT_TYPES).notNull(),
    currency: mysqlEnum("currency", ACCOUNT_CURRENCIES)
      .default("NPR")
      .notNull(),
    status: mysqlEnum("status", ACCOUNT_STATUS).default("active").notNull(),
    systemAccount: boolean("system_account").default(false).notNull(),
    ...timestamps
  },
  table => [
    index("user_id_idx").on(table.userId),
    index("status_idx").on(table.status),
    index("type_idx").on(table.type)
  ]
);
 
// Account types
export type Account = typeof accounts.$inferSelect;
export type NewAccount = typeof accounts.$inferInsert;

Installation

npx servercn-cli add schema banking-app/account

3. Transaction Schema

The Transaction schema records money movements between accounts, including transfers, deposits, and withdrawals.

Path: src/drizzle/schemas/transaction.schema.ts

import {
  mysqlTable,
  varchar,
  int,
  mysqlEnum,
  index,
  uniqueIndex,
  decimal
} from "drizzle-orm/mysql-core";
import { timestamps } from "./schema.helper";
import { accounts } from "./account.schema";
 
// Transaction status
export const TRANSACTION_STATUS = [
  "pending",
  "completed",
  "failed",
  "reversed"
] as const;
export type TransactionStatus = (typeof TRANSACTION_STATUS)[number];
 
export const transactions = mysqlTable(
  "transactions",
  {
    id: int("id").primaryKey().autoincrement(),
    fromAccountId: int("from_account_id")
      .references(() => accounts.id)
      .notNull(),
    toAccountId: int("to_account_id")
      .references(() => accounts.id)
      .notNull(),
    amount: decimal("amount", { precision: 15, scale: 2 }).notNull(),
    status: mysqlEnum("status", TRANSACTION_STATUS)
      .default("pending")
      .notNull(),
    idempotencyKey: varchar("idempotency_key", { length: 255 })
      .notNull()
      .unique(),
    ...timestamps
  },
  table => [
    index("from_account_id_idx").on(table.fromAccountId),
    index("to_account_id_idx").on(table.toAccountId),
    index("status_idx").on(table.status),
    uniqueIndex("idempotency_key_idx").on(table.idempotencyKey)
  ]
);
 
// Transaction types
export type Transaction = typeof transactions.$inferSelect;
export type NewTransaction = typeof transactions.$inferInsert;

Installation

npx servercn-cli add schema banking-app/transaction

4. Ledger Schema

The Ledger schema records all financial movements such as deposits, withdrawals, and transfers. Each entry represents a finalized financial event.

Path: src/drizzle/schemas/ledger.schema.ts

Ledger entries are immutable in business logic. Application layer should prevent updates/deletes.

import {
  mysqlTable,
  int,
  mysqlEnum,
  index,
  decimal
} from "drizzle-orm/mysql-core";
import { timestamps } from "./schema.helper";
import { accounts } from "./account.schema";
import { transactions } from "./transaction.schema";
 
// Ledger entry types
export const LEDGER_ENTRY_TYPES = ["debit", "credit"] as const;
export type LedgerEntryType = (typeof LEDGER_ENTRY_TYPES)[number];
 
export const ledgers = mysqlTable(
  "ledgers",
  {
    id: int("id").primaryKey().autoincrement(),
    accountId: int("account_id")
      .references(() => accounts.id)
      .notNull(),
    transactionId: int("transaction_id")
      .references(() => transactions.id)
      .notNull(),
    entryType: mysqlEnum("entry_type", LEDGER_ENTRY_TYPES).notNull(),
    amount: decimal("amount", { precision: 15, scale: 2 }).notNull(),
    ...timestamps
  },
  table => [
    index("account_id_idx").on(table.accountId),
    index("transaction_id_idx").on(table.transactionId),
    index("entry_type_idx").on(table.entryType)
  ]
);
 
// Ledger types
export type Ledger = typeof ledgers.$inferSelect;
export type NewLedger = typeof ledgers.$inferInsert;

Installation

npx servercn-cli add schema banking-app/ledger

5. Relations

Defines relationships between database tables for seamless data fetching with Drizzle ORM.

Path: src/drizzle/schemas/relations.ts

import { relations } from "drizzle-orm";
import { accounts } from "./account.schema";
import { transactions } from "./transaction.schema";
import { ledgers } from "./ledger.schema";
import { users } from "./user.schema";
 
// User relations
export const usersRelations = relations(users, ({ many }) => ({
  accounts: many(accounts)
}));
 
// Account relations
export const accountsRelations = relations(accounts, ({ one, many }) => ({
  user: one(users, {
    fields: [accounts.userId],
    references: [users.id]
  }),
  ledgers: many(ledgers)
}));
 
// Transaction relations
export const transactionsRelations = relations(transactions, ({ many }) => ({
  ledgers: many(ledgers)
}));
 
// Ledger relations
export const ledgersRelations = relations(ledgers, ({ one }) => ({
  account: one(accounts, {
    fields: [ledgers.accountId],
    references: [accounts.id]
  }),
  transaction: one(transactions, {
    fields: [ledgers.transactionId],
    references: [transactions.id]
  })
}));

6. Export all schemas

All database schemas must be re-exported from a single entry point. This allows Drizzle to load every table definition from one file during migration generation.

File Path: src/drizzle/index.ts

export * from "./schemas/user.schema";
export * from "./schemas/ledger.schema";
export * from "./schemas/transaction.schema";
export * from "./schemas/account.schema";

7. drizzle.config.ts

Create a drizzle.config.ts file at the project root. This file defines how Drizzle connects to the database and where it should read schemas and write migrations.

File Path: drizzle.config.ts

import { Config, defineConfig } from "drizzle-kit";
import env from "./src/configs/env";
 
export default defineConfig({
  out: "./src/drizzle/migrations", // Directory where migrations are generated
  schema: "./src/drizzle/index.ts", // Central schema entry point
  dialect: "mysql", // Database dialect
  dbCredentials: {
    url: env.DATABASE_URL!
  },
  verbose: true, // Enable detailed logs
  strict: true // Enforce strict schema validation
}) satisfies Config;

File & Folder Structure

ServerCN

Select a file to view its contents

Installation

npx servercn-cli add schema banking-app