#
Database
QuickDapp uses DrizzleORM with PostgreSQL to provide type-safe, high-performance database access. DrizzleORM offers the best of both worlds: the type safety of an ORM with the performance of raw SQL.
#
Why DrizzleORM?
- Type Safety - Full TypeScript integration with compile-time checks
- Performance - Generates efficient SQL queries, no N+1 problems
- Developer Experience - Intuitive API that feels like writing SQL
- Migrations - Schema migrations with version control
- Raw SQL Support - Drop down to raw SQL when needed
#
Database Schema
The database schema is defined in TypeScript using Drizzle's schema builder:
// src/server/db/schema.ts
import {
boolean,
integer,
json,
pgTable,
serial,
text,
timestamp,
} from "drizzle-orm/pg-core"
// Settings table for application configuration
export const settings = pgTable("settings", {
id: serial("id").primaryKey(),
key: text("key").unique().notNull(),
value: text("value").notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
})
// Users table for authentication and user management
export const users = pgTable("users", {
id: serial("id").primaryKey(),
wallet: text("wallet").unique().notNull(),
settings: json("settings"),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
})
// Notifications table for user notifications
export const notifications = pgTable("notifications", {
id: serial("id").primaryKey(),
userId: integer("user_id")
.references(() => users.id)
.notNull(),
data: json("data").notNull(),
read: boolean("read").default(false).notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
})
// Worker jobs table for background task management
export const workerJobs = pgTable("worker_jobs", {
id: serial("id").primaryKey(),
type: text("type").notNull(),
userId: integer("user_id").notNull(),
data: json("data").notNull(),
due: timestamp("due").notNull(),
started: timestamp("started"),
finished: timestamp("finished"),
removeAt: timestamp("remove_at").notNull(),
success: boolean("success"),
result: json("result"),
cronSchedule: text("cron_schedule"),
autoRescheduleOnFailure: boolean("auto_reschedule_on_failure")
.default(false)
.notNull(),
persistent: boolean("persistent").default(false).notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
})
#
Database Operations
#
Basic Queries
import { eq, and, or, like, desc } from 'drizzle-orm'
import { users, notifications, workerJobs, settings } from './schema'
// Select all users
const allUsers = await serverApp.db.select().from(users)
// Select specific fields
const userWallets = await serverApp.db
.select({ wallet: users.wallet })
.from(users)
// Find user by wallet address
const user = await serverApp.db
.select()
.from(users)
.where(eq(users.wallet, '0x1234...'))
.then(rows => rows[0])
// Find notifications for a user
const userNotifications = await serverApp.db
.select()
.from(notifications)
.where(eq(notifications.userId, 1))
.orderBy(desc(notifications.createdAt))
.limit(10)
.offset(20)
#
Complex Queries with Joins
// Join notifications with their users
const notificationsWithUsers = await serverApp.db
.select({
notification: notifications,
user: users
})
.from(notifications)
.leftJoin(users, eq(notifications.userId, users.id))
// Find unread notifications for a specific wallet
const unreadNotifications = await serverApp.db
.select()
.from(notifications)
.innerJoin(users, eq(notifications.userId, users.id))
.where(and(
eq(users.wallet, walletAddress),
eq(notifications.read, false)
))
#
Insert Operations
// Insert single user
const [user] = await serverApp.db
.insert(users)
.values({
wallet: '0x1234567890123456789012345678901234567890',
settings: { theme: 'dark', notifications: true }
})
.returning()
// Insert multiple notifications
const newNotifications = await serverApp.db
.insert(notifications)
.values([
{
userId: 1,
data: { type: 'token_deployed', tokenName: 'MyToken' },
read: false
},
{
userId: 1,
data: { type: 'transaction_confirmed', txHash: '0xabc...' },
read: false
},
])
.returning()
// Insert with conflict handling (upsert user)
const [user] = await serverApp.db
.insert(users)
.values({
wallet: '0x1234567890123456789012345678901234567890',
settings: { theme: 'light' }
})
.onConflictDoUpdate({
target: users.wallet,
set: {
settings: excluded(users.settings),
updatedAt: new Date()
}
})
.returning()
#
Update Operations
// Update single record
await serverApp.db
.update(userTable)
.set({
nonce: newNonce,
updatedAt: new Date()
})
.where(eq(userTable.id, userId))
// Update with conditions
await serverApp.db
.update(tokenTable)
.set({ name: 'New Token Name' })
.where(and(
eq(tokenTable.id, tokenId),
eq(tokenTable.ownerId, userId)
))
#
Delete Operations
// Delete single record
await serverApp.db
.delete(tokenTable)
.where(eq(tokenTable.id, tokenId))
// Delete with conditions
await serverApp.db
.delete(userTable)
.where(and(
eq(userTable.address, address),
eq(userTable.isAdmin, false)
))
#
Transactions
DrizzleORM provides transaction support for atomic operations:
// Basic transaction
await serverApp.db.transaction(async (tx) => {
// Create user
const [user] = await tx
.insert(userTable)
.values({ address: '0x1234...', nonce: 'abc123' })
.returning()
// Create token for user
await tx
.insert(tokenTable)
.values({
name: 'User Token',
symbol: 'UTK',
ownerId: user.id
})
// If anything fails, entire transaction is rolled back
})
// Transaction with return value
const result = await serverApp.db.transaction(async (tx) => {
const [user] = await tx
.insert(userTable)
.values(userData)
.returning()
const [token] = await tx
.insert(tokenTable)
.values({ ...tokenData, ownerId: user.id })
.returning()
return { user, token }
})
#
Schema Migrations
#
Generating Migrations
When you modify the schema, generate a migration:
# Generate migration from schema changes
bun run db generate
# This creates a new migration file in src/server/db/migrations/
#
Running Migrations
# Development: Push schema directly (destructive)
bun run db push
# Production: Run migrations safely
bun run db migrate
#
Migration Files
Generated migration files look like this:
-- src/server/db/migrations/0001_add_token_table.sql
CREATE TABLE IF NOT EXISTS "tokens" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"address" text NOT NULL,
"name" text NOT NULL,
"symbol" text NOT NULL,
"owner_id" uuid NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "tokens_address_unique" UNIQUE("address")
);
ALTER TABLE "tokens" ADD CONSTRAINT "tokens_owner_id_users_id_fk"
FOREIGN KEY ("owner_id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;
#
Advanced Patterns
#
Custom SQL
When you need complex queries, drop down to raw SQL:
import { sql } from 'drizzle-orm'
// Raw SQL with parameters
const result = await serverApp.db.execute(
sql`
SELECT t.*, u.address as owner_address
FROM tokens t
JOIN users u ON t.owner_id = u.id
WHERE t.created_at > ${fromDate}
ORDER BY t.created_at DESC
`
)
// Raw SQL in select
const tokensWithCounts = await serverApp.db
.select({
user: userTable,
tokenCount: sql<number>`cast(count(${tokenTable.id}) as int)`
})
.from(userTable)
.leftJoin(tokenTable, eq(userTable.id, tokenTable.ownerId))
.groupBy(userTable.id)
#
Database Views
Create views for complex queries:
// Create a view in migration
const userStatsView = pgView('user_stats', {
userId: uuid('user_id'),
address: text('address'),
tokenCount: integer('token_count'),
}).as((qb) =>
qb.select({
userId: userTable.id,
address: userTable.address,
tokenCount: count(tokenTable.id),
})
.from(userTable)
.leftJoin(tokenTable, eq(userTable.id, tokenTable.ownerId))
.groupBy(userTable.id, userTable.address)
)
// Use view in queries
const userStats = await serverApp.db
.select()
.from(userStatsView)
.where(gt(userStatsView.tokenCount, 0))
#
Connection Management
The database connection is managed centrally:
// src/server/db/connection.ts
class DatabaseManager {
private connection?: PostgresJsDatabase
async connect(): Promise<PostgresJsDatabase> {
if (this.connection) return this.connection
const client = postgres(serverConfig.DATABASE_URL, {
max: 10, // Connection pool size
idle_timeout: 20,
connect_timeout: 10,
})
this.connection = drizzle(client)
return this.connection
}
async disconnect(): Promise<void> {
if (this.connection) {
await this.connection.end()
this.connection = undefined
}
}
}
export const dbManager = new DatabaseManager()
#
Testing Patterns
#
Test Database Setup
// tests/helpers/database.ts
export async function setupTestDatabase(db: PostgresJsDatabase) {
// Clear all tables
await db.delete(tokenTable)
await db.delete(userTable)
// Reset sequences
await db.execute(sql`ALTER SEQUENCE IF EXISTS users_id_seq RESTART WITH 1`)
await db.execute(sql`ALTER SEQUENCE IF EXISTS tokens_id_seq RESTART WITH 1`)
}
export async function createTestUser(db: PostgresJsDatabase) {
const [user] = await db
.insert(userTable)
.values({
address: '0x' + Math.random().toString(16).slice(2, 42),
nonce: Math.random().toString()
})
.returning()
return user
}
#
Database Tests
describe('User Database Operations', () => {
let serverApp: ServerApp
beforeEach(async () => {
serverApp = await createTestServerApp()
await setupTestDatabase(serverApp.db)
})
it('creates users correctly', async () => {
const user = await serverApp.db
.insert(userTable)
.values({
address: '0x1234567890123456789012345678901234567890',
nonce: 'test-nonce'
})
.returning()
.then(rows => rows[0])
expect(user.id).toBeDefined()
expect(user.address).toBe('0x1234567890123456789012345678901234567890')
})
})
DrizzleORM provides a powerful, type-safe way to interact with your PostgreSQL database while maintaining excellent performance and developer experience.