Database

This guide covers the database options available for your project, how to set up and connect to your database, and how to work with Drizzle ORM.

Setup

The project uses PostgreSQL as the database system and Drizzle ORM for database access and management. The setup is designed to be flexible, allowing you to use various PostgreSQL hosting options.

Hosted PostgreSQL Services

Hosted PostgreSQL services are the recommended approach for both development and production environments. They provide easy setup, management, and often include free tiers sufficient for getting started.

Neon

Neon is a serverless PostgreSQL service with an excellent developer experience and is the default choice for this project.

Key Features:

• Serverless architecture
• Database branching for development/staging
• Automatic scaling
• Free tier with 500MB storage

Setup Steps:

1. Create an account at neon.tech
2. Create a new project
3. Create a database
4. Get your connection string from the dashboard
5. Add the connection string to your .env file as DATABASE_URL

# Example Neon connection string
DATABASE_URL="postgres://user:password@ep-something.us-east-2.aws.neon.tech/database?sslmode=require"

Supabase

Supabase provides PostgreSQL databases with additional features like authentication and storage.

Key Features:

• Full PostgreSQL support
• Additional services (auth, storage, etc.)
• Free tier with 500MB storage and 5GB data transfer
• Simple dashboard

Setup Steps:

1. Create an account at supabase.com
2. Create a new project
3. Navigate to the SQL editor or Database settings
4. Get your connection string
5. Add the connection string to your .env file as DATABASE_URL

# Example Supabase connection string
DATABASE_URL="postgres://postgres:your-password@db.something.supabase.co:6543/postgres"

Other Options

• AWS RDS (PostgreSQL)
• Google Cloud SQL (PostgreSQL)
• Azure Database for PostgreSQL

Self-Hosted Options

Docker

You can run PostgreSQL in a Docker container for local development:

docker run --name my-postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres

Then use this connection string:

DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres"

Local Installation

Install PostgreSQL directly on your machine:

1. Download from postgres.org or using package managers
2. Install and set up a password
3. Create a database

Then use this connection string:

DATABASE_URL="postgres://postgres:yourpassword@localhost:5432/mydatabase"

Using Drizzle ORM

This project uses Drizzle ORM, a TypeScript ORM with a focus on type safety and developer experience. Drizzle is compatible with various database systems and providers.

Current Configuration

Currently, the project is configured to use Neon's HTTP driver for better compatibility with Next.js and serverless environments:

import { drizzle } from 'drizzle-orm/neon-http';
 
// Using the browser-compatible Neon HTTP driver
const db = drizzle(process.env.DATABASE_URL!);
 
export default db;

Database Schema

The database schema is defined in src/db/schema.ts using Drizzle's schema definition syntax:

import { pgTable, text, timestamp } from "drizzle-orm/pg-core";
 
export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  // ... other fields
});
 
// ... other tables

Using Other Database Providers

Drizzle ORM supports multiple database systems. If you want to use a different provider:

1. Install the appropriate Drizzle adapter package
2. Update src/db/index.ts to use the correct driver
3. Adjust the schema in src/db/schema.ts if necessary

Example: Using MySQL

// 1. Install: npm install drizzle-orm mysql2
// 2. Update src/db/index.ts
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
 
const connection = await mysql.createConnection(process.env.DATABASE_URL!);
const db = drizzle(connection);
 
export default db;

Example: Using SQLite

// 1. Install: npm install drizzle-orm better-sqlite3
// 2. Update src/db/index.ts
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
 
const sqlite = new Database('sqlite.db');
const db = drizzle(sqlite);
 
export default db;

For more information about Drizzle ORM and its adapters, refer to the Drizzle ORM documentation.

Troubleshooting

Connection Issues

If you're having trouble connecting to your database:

1. Check that your DATABASE_URL is correctly formatted
2. Ensure your IP is allowed in the database's firewall settings
3. Verify that the database user has the correct permissions
4. Check for any network restrictions

Schema Migration Issues

If you're experiencing issues with database migrations:

1. Check your schema definitions for errors
2. Ensure your migration scripts are correctly formatted
3. Try running migrations manually to see detailed errors

For more detailed help, refer to the Drizzle ORM documentation.

Next Steps

Now that you understand how to set up a database in MkSaaS, you might want to explore these related features: