Database

Set up databases, run migrations, and manage your data layer

Supported Databases

Choose your preferred database during project initialization

PostgreSQL

Production-ready, highly reliable relational database

servcraft init my-app --prisma postgres

MySQL

Popular open-source relational database

servcraft init my-app --prisma mysql

SQLite

Lightweight, file-based database for development

servcraft init my-app --prisma sqlite

MongoDB

NoSQL document database for flexible schemas

servcraft init my-app --prisma mongodb

Database Commands

Manage your database with these commands

servcraft db push

Push schema changes to database

npm run db:push

servcraft db migrate

Run Prisma migrations

npm run db:migrate

servcraft db generate

Generate Prisma client

npm run db:generate

servcraft db seed

Run database seeders

npm run db:seed

servcraft db reset

Reset database (dangerous!)

npm run db:reset

servcraft db studio

Open Prisma Studio UI

npm run db:studio

servcraft db status

Show migration status

npm run db:status

Prisma Schema

Define your database models in Prisma schema

prisma/schema.prisma

1// prisma/schema.prisma
2
3generator client {
4  provider = "prisma-client-js"
5}
6
7datasource db {
8  provider = "postgresql"
9  url      = env("DATABASE_URL")
10}
11
12model User {
13  id        String   @id @default(cuid())
14  email     String   @unique
15  name      String?
16  password  String
17  role      Role     @default(USER)
18  createdAt DateTime @default(now())
19  updatedAt DateTime @updatedAt
20
21  @@map("users")
22}
23
24model Post {
25  id        String   @id @default(cuid())
26  title     String
27  content   String
28  published Boolean  @default(false)
29  authorId  String
30  author    User     @relation(fields: [authorId], references: [id])
31  createdAt DateTime @default(now())
32  updatedAt DateTime @updatedAt
33
34  @@map("posts")
35}
36
37enum Role {
38  USER
39  ADMIN
40}

Environment Variables

.env

1# Database Connection
2DATABASE_URL="postgresql://user:password@localhost:5432/servcraft?schema=public"
3
4# For MySQL
5# DATABASE_URL="mysql://user:password@localhost:3306/servcraft"
6
7# For SQLite
8# DATABASE_URL="file:./dev.db"
9
10# For MongoDB
11# DATABASE_URL="mongodb://user:password@localhost:27017/servcraft"

Best Practices

Follow these guidelines for optimal database usage

Use migrations for schema changes

Never edit schema directly in production

Keep models focused

Each model should have a single responsibility

Use relations wisely

Define proper foreign keys and indexes

Seed your database

Use seed files for test data

Connection Pooling

Optimize database connections for high-traffic applications

Connection Pool Configuration

1// For high-traffic applications, configure connection pooling
2// in prisma/schema.prisma:
3
4generator client {
5  provider = "prisma-client-js"
6  previewFeatures = ["driverAdapters"]
7}
8
9// In your app startup (src/app.ts):
10import { PrismaClient } from '@prisma/client';
11
12const prisma = new PrismaClient({
13  datasources: {
14    db: {
15      url: process.env.DATABASE_URL,
16    },
17  },
18  // Connection pool settings
19  __internal: {
20    engine: {
21      adapter: undefined, // Use adapter for better pooling
22    },
23  },
24});
25
26// Or use PgBouncer for PostgreSQL
27// DATABASE_URL="postgresql://.../servcraft?pool_timeout=10&connection_limit=10"

Database Migrations

Manage schema changes safely across environments

Working with Migrations

1// Creating a migration
2npm run db:migrate
3
4// This creates files in prisma/migrations/
5// 20231201120000_add_user_profile/
6//   └── migration.sql
7
8// Sample migration.sql:
9-- Add profile fields to User model
10ALTER TABLE "users" ADD COLUMN "bio" TEXT;
11ALTER TABLE "users" ADD COLUMN "avatar_url" VARCHAR(255);
12ALTER TABLE "users" ADD COLUMN "updated_at" TIMESTAMP;
13
14-- Create index for faster queries
15CREATE INDEX "idx_users_email" ON "users"("email");
16
17// To reset database (development only!)
18npm run db:reset
19
20// To view migration status
21npm run db:status