Drizzle ORM Setup

Relevant source files

The following files were used as context for generating this wiki page:

• drizzle.config.ts
• server/db.ts
• drizzle/schema.ts
• package.json
• drizzle/0001_sturdy_santa_claus.sql
• README.md

The Drizzle ORM setup serves as the data access layer for the Driveway Estimator Pro application. It provides a type-safe interface for interacting with a MySQL/TiDB database, managing everything from schema definitions and migrations to complex application-level queries.

The architecture relies on a centralized schema file that defines the database structure in TypeScript, which is then used to generate SQL migrations and provide end-to-end type safety across the backend services and tRPC routers.
Sources: README.md:28-30, drizzle/schema.ts:1-12

Core Configuration and Environment

The system is configured to use the MySQL dialect, with connection details managed via environment variables. The configuration is primarily handled by drizzle-kit, which facilitates schema synchronization and migration generation.

Database Connection

Connections are established using the mysql2 driver. The database instance is created lazily to allow local development tools to run even when a live database connection is unavailable.

let _db: ReturnType<typeof drizzle> | null = null;

export async function getDb() {
  if (!_db && process.env.DATABASE_URL) {
    try {
      _db = drizzle(process.env.DATABASE_URL);
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  return _db;
}

Sources: server/db.ts:16-29, drizzle.config.ts:1-15

Configuration Parameters

The drizzle.config.ts file defines how Drizzle manages the database lifecycle:

Parameter	Value	Description
dialect	mysql	Target database engine.
schema	./drizzle/schema.ts	Path to the TypeScript schema definition.
out	./drizzle	Directory for generated migration files.
dbCredentials.url	DATABASE_URL	Environment variable for the connection string.

Sources: drizzle.config.ts:8-14

Data Modeling and Schema

The schema is defined using drizzle-orm/mysql-core. It includes four primary tables that support user authentication, project tracking, public sharing, and regional material pricing.

Entity Relationship Diagram

The following diagram illustrates the relationships between the core database entities:

The diagram shows the relationship between users and their projects, as well as projects and their public share tokens.
Sources: drizzle/schema.ts:13-112

Schema Table Definitions

Table	Purpose	Key Fields
users	Auth and profile management.	openId, role, lastSignedIn
projects	Core driveway estimate data.	squareFeet, cornerPoints, totalCost, photoUrl
projectShares	Tokens for contractor access.	shareToken, viewCount, expiresAt
materialPrices	Cached regional pricing data.	zipCode, material, pricePerTon

Sources: drizzle/schema.ts:13-112, drizzle/0001_sturdy_santa_claus.sql:1-45

Database Operations and Helpers

The application uses specific helper functions in server/db.ts to perform CRUD operations. These functions leverage Drizzle's query builder to ensure type safety and prevent SQL injection.

Data Access Flow

The following sequence diagram shows how the application retrieves and caches material prices:

This flow ensures that regional pricing is efficiently cached and served.
Sources: server/services/pricing.ts:40-80, server/db.ts:121-140

Key Functions

• upsertUser: Synchronizes user data from OAuth providers, assigning roles based on OWNER_OPEN_ID.
• getProjectById: Retrieves project details including measurement data for calculation.
• getProjectShareByToken: Facilitates public access to estimates without requiring user authentication.
• upsertMaterialPrice: Updates the regional pricing cache with a 24-hour expiration policy.

Sources: server/db.ts:31-155, drizzle/schema.ts:104-112

Workflow and Tooling

Development and maintenance of the database are managed via specific scripts in package.json.

• Migration Generation: drizzle-kit generate reads schema.ts and creates .sql files in the /drizzle folder.
• Migration Execution: drizzle-kit migrate applies pending SQL changes to the target database.
• Type Generation: Drizzle automatically infers types like InsertProject and Project from the schema definitions to be used in services and frontend components.

"scripts": {
  "db:push": "drizzle-kit generate && drizzle-kit migrate"
}

Sources: package.json:14, drizzle/schema.ts:47-48

Summary

The Drizzle ORM setup provides a robust, type-safe foundation for the Driveway Estimator Pro's data persistence. By centralizing the schema and using automated migration tooling, the project ensures consistency between the application logic and the underlying MySQL storage, facilitating features like AI-driven measurement storage and regional price caching.