Skip to main content

Database Configuration

AutoDeployBase uses Prisma ORM for database management with support for PostgreSQL, MongoDB, and SQLite.

Supported Databases​

DatabaseBest ForStatus
PostgreSQLProduction appsRecommended
MongoDBDocument storageStable
SQLiteDevelopment/testingStable

Configuration​

Project Creation​

npx autodeploybase init my-app --database postgresql

Configuration File​

autodeploy.config.json
{
  "database": "postgresql"
}

Environment Setup​

PostgreSQL​

DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

MongoDB​

DATABASE_URL="mongodb://localhost:27017/mydb"

SQLite​

DATABASE_URL="file:./dev.db"

Prisma Schema​

Generated Schema​

prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  password  String
  name      String?
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

enum Role {
  USER
  ADMIN
  SUPER_ADMIN
}

Custom Entities​

Define entities in config:

autodeploy.config.json
{
  "entities": [
    {
      "name": "Product",
      "fields": [
        { "name": "name", "type": "String", "required": true },
        { "name": "price", "type": "Decimal", "required": true },
        { "name": "description", "type": "String" },
        { "name": "inStock", "type": "Boolean", "default": true }
      ]
    }
  ]
}

Generates:

model Product {
  id          String   @id @default(cuid())
  name        String
  price       Decimal
  description String?
  inStock     Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
}

Database Client​

Using Prisma Client​

lib/db.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['query'] : []
  });

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

In API Routes​

import { prisma } from '@/lib/db';

export async function GET() {
  const users = await prisma.user.findMany();
  return Response.json(users);
}

export async function POST(request: Request) {
  const body = await request.json();

  const user = await prisma.user.create({
    data: body
  });

  return Response.json(user, { status: 201 });
}

Migrations​

Development​

# Create and apply migration
npx prisma migrate dev --name add_products

# Reset database
npx prisma migrate reset

# Generate client without migration
npx prisma generate

Production​

# Apply pending migrations
npx prisma migrate deploy

CI/CD​

- name: Run migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

Seeding​

Seed File​

prisma/seed.ts
import { PrismaClient } from '@prisma/client';
import { hash } from 'bcryptjs';

const prisma = new PrismaClient();

async function main() {
  // Create admin user
  const adminPassword = await hash('admin123', 12);

  await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: 'Admin User',
      password: adminPassword,
      role: 'ADMIN'
    }
  });

  // Create sample data
  await prisma.product.createMany({
    data: [
      { name: 'Product 1', price: 29.99 },
      { name: 'Product 2', price: 49.99 },
      { name: 'Product 3', price: 99.99 }
    ],
    skipDuplicates: true
  });

  console.log('Database seeded');
}

main()
  .catch(e => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Run Seed​

npx prisma db seed

Relations​

One-to-Many​

{
  "entities": [
    {
      "name": "Post",
      "fields": [
        { "name": "title", "type": "String", "required": true },
        { "name": "content", "type": "String" }
      ],
      "relations": [{ "name": "author", "type": "many-to-one", "target": "User" }]
    }
  ]
}

Many-to-Many​

{
  "entities": [
    {
      "name": "Post",
      "relations": [{ "name": "tags", "type": "many-to-many", "target": "Tag" }]
    },
    {
      "name": "Tag",
      "fields": [{ "name": "name", "type": "String", "required": true, "unique": true }]
    }
  ]
}

Queries​

Basic Queries​

// Find all
const users = await prisma.user.findMany();

// Find one
const user = await prisma.user.findUnique({
  where: { id: 'user-id' }
});

// Find with conditions
const activeUsers = await prisma.user.findMany({
  where: {
    role: 'USER',
    createdAt: { gte: new Date('2024-01-01') }
  }
});

Relations​

// Include relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: 'user-id' },
  include: { posts: true }
});

// Nested queries
const postsWithAuthors = await prisma.post.findMany({
  include: {
    author: {
      select: { name: true, email: true }
    }
  }
});

Pagination​

const page = 1;
const pageSize = 10;

const [users, total] = await Promise.all([
  prisma.user.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { createdAt: 'desc' }
  }),
  prisma.user.count()
]);

return {
  users,
  total,
  pages: Math.ceil(total / pageSize)
};

Performance​

Connection Pooling​

For serverless environments:

DATABASE_URL="postgresql://...?pgbouncer=true&connection_limit=1"

Query Optimization​

// Use select to limit fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true
  }
});

// Add indexes in schema
model User {
  // ...
  @@index([email])
  @@index([createdAt])
}

Batch Operations​

// Create many
await prisma.user.createMany({
  data: users,
  skipDuplicates: true
});

// Update many
await prisma.user.updateMany({
  where: { role: 'USER' },
  data: { verified: true }
});

// Transactions
await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.profile.create({ data: profileData })
]);