Database

This guide covers the database creation, initialization, migration and connection to the 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.

Create a database

Recommended to use hosted PostgreSQL database services, they provide easy setup and management, and often include free tiers sufficient for getting started.

Neon (Recommended)

Neon is a serverless PostgreSQL service with an excellent developer experience and is the default choice for this project. It's a great option for small projects and is free to get started.

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"

You can find more information about how to setup Neon in this guide.

Supabase

Supabase provides PostgreSQL databases with additional features like authentication and storage. It's a great option for medium to large projects and is free to get started.

Setup Steps:

1. Create an account at supabase.com
2. Create a new project
3. Click on the Connect button
4. Get your connection string in Transaction pooler section
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"

You can find more information about how to setup Supabase in this guide, or check out the Connect Guide on Supabase.

Initialize the database

MkSaaS uses Drizzle ORM to interact with the database. You can initialize the database by running the following command:

pnpm run db:generate # Generate the Drizzle client
pnpm run db:migrate # Migrate the database

npm run db:generate # Generate the Drizzle client
npm run db:migrate # Migrate the database

yarn db:generate # Generate the Drizzle client
yarn db:migrate # Migrate the database

bun run db:generate # Generate the Drizzle client
bun run db:migrate # Migrate the database

This will generate the Drizzle client, migrate the database and push the database schema to initialize the database.

Create a database (other options)

Other Hosted Services

You can find more information about other database options in the Drizzle ORM documentation.

• Vercel Postgres
• Prisma Postgres
• 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 drizzle-postgres -e POSTGRES_PASSWORD=mypassword -d -p 5432:5432 postgres

Then use this connection string:

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

You can find more information about how to setup PostgreSQL locally in this guide.

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://your-username:your-password@localhost:5432/database-name"

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 postgres-js driver for better compatibility with different databases and serverless environments:

import { drizzle } from 'drizzle-orm/postgres-js';

import postgres from 'postgres';
import * as schema from './schema';

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

export async function getDb() {
  if (db) return db;
  const connectionString = process.env.DATABASE_URL!;
  const client = postgres(connectionString, { prepare: false });
  db = drizzle(client, { schema });
  return 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

You can see the schema in the image below:

Using Other Database Providers

Drizzle ORM supports multiple database providers. 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.

Connect to the database

You can run pnpm db:studio to open the database studio to view and manage the database data in development environment.

This command connects to the corresponding database according to the configuration in the drizzle.config.ts file.

FAQ

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.

Database Cost Issues

If you are using Neon database service, you may need to set up AutoScaling to avoid excessive cost bills.

References

• Neon
• Supabase
• Drizzle ORM
• Supabase | Connecting with Drizzle
• Database connection with Drizzle
• Drizzle with Neon Postgres
• Drizzle with Supabase Postgres
• Drizzle with Vercel Edge Functions
• Drizzle | How to setup PostgreSQL locally
• Use Neon with Cloudflare Workers

Video Tutorial

Next Steps

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