Database

Database Integration

Overview

The NextAI starter kit uses PostgreSQL with Prisma ORM for database management. This combination provides a robust, type-safe, and scalable solution for storing and retrieving data in your AI application.

Features

  • Type-safe database queries with Prisma
  • Automatic schema migrations
  • Efficient data retrieval and storage
  • Support for complex relationships
  • Built-in connection pooling

Setup

Environment Variables

To set up the database connection, configure the following environment variables in your .env file:

POSTGRES_URL="postgresql://username:password@hostname:port/database"
POSTGRES_DIRECT_URL="postgresql://username:password@hostname:port/database"

You can obtain these connection strings from your PostgreSQL provider (e.g., Supabase, Neon, or a self-hosted instance).

Database Schema

The starter kit includes a predefined schema in prisma/schema.prisma that supports all the core features:

  • User data
  • Chat sessions
  • Messages
  • Artifacts (documents, images, etc.)
  • Votes and feedback

Database Operations

Queries

The starter kit provides a set of predefined database queries in lib/db/queries.ts for common operations:

  • Retrieving chat history
  • Saving messages
  • Managing user data
  • Handling artifacts

Example of retrieving chat messages:

const messagesFromDb = await getMessagesByChatId({
  id,
});

Migrations

The starter kit includes scripts for managing database migrations:

# Generate migrations based on schema changes
pnpm db:generate
 
# Apply migrations to the database
pnpm db:migrate
 
# View database in Prisma Studio
pnpm db:studio

Customization

Adding New Models

To add new database models:

  1. Update the prisma/schema.prisma file with your new model definition
  2. Generate migrations using pnpm db:generate
  3. Apply the migrations using pnpm db:migrate
  4. Create query functions in lib/db/queries.ts for interacting with your new model

Example of adding a new model:

model CustomData {
  id        String   @id @default(cuid())
  userId    String
  name      String
  value     String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Optimizing Queries

For performance optimization:

  1. Use selective field retrieval to minimize data transfer
  2. Implement pagination for large result sets
  3. Create indexes for frequently queried fields
  4. Use transactions for operations that require atomicity

Best Practices

  1. Always use parameterized queries to prevent SQL injection
  2. Implement proper error handling for database operations
  3. Use transactions for operations that modify multiple records
  4. Regularly back up your database
  5. Monitor database performance and optimize as needed

Security Considerations

  • Store database credentials securely in environment variables
  • Implement proper access control for database operations
  • Sanitize user input before using it in database queries
  • Use the principle of least privilege for database users

For more detailed information about database operations and advanced usage, refer to the Prisma documentation (opens in a new tab).

AuthenticationPayments