just.another.template

Databases

This template includes a fully configured database setup with Drizzle ORM, PostgreSQL, and automated migrations.

Overview

The database system in this template uses Drizzle ORM for type-safe database operations with PostgreSQL. The setup includes migrations, schema validation, and a Docker-based development environment for easy local development.

This approach provides a robust foundation for data management with minimal configuration, while maintaining type safety throughout your application.

Features

Type-safe Schema

Define your database schema in TypeScript with full type safety and autocompletion.

Migrations

Automatically generate and apply database migrations based on schema changes.

Query Builder

Type-safe SQL query builder with intuitive syntax and full TypeScript support.

PostgreSQL

Robust, feature-rich PostgreSQL database with JSON, full-text search, and more.

Docker Integration

Pre-configured Docker setup for local development with persistence.

Relations

Define and query relations between tables with ease and type safety.

Implementation

The database layer is implemented in the src/database directory and consists of these main components:

Schema Definitions: Type-safe table schema definitions using Drizzle ORM.
Migrations: Automated migrations based on schema changes.
Database Client: Configured client for connecting to PostgreSQL.
Query Utilities: Helper functions for common database operations.
Docker Configuration: Ready-to-use Docker setup for PostgreSQL.

Schema Example

Here's an example of how the database schema is defined:

// Example schema definition with Drizzle
import { pgTable, serial, text, timestamp, boolean } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

// Define the users table
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name"),
  password: text("password"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
  emailVerified: boolean("email_verified").default(false),
});

// Define the posts table
export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  authorId: serial("author_id").references(() => users.id),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Define relations
export const userRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

Using the Database

To query the database in your server components or API routes:

// Example: Querying the database in a server component
import { db } from "@/database";
import { users } from "@/database/schema";
import { eq } from "drizzle-orm";

// In a server component or API route
export async function getUser(userId: number) {
  // Query a single user by ID
  const user = await db.query.users.findFirst({
    where: eq(users.id, userId),
    with: {
      posts: true, // Include relations
    },
  });
  
  return user;
}

// Example: Creating a new record
export async function createUser(data: { email: string; name: string; password: string }) {
  const result = await db.insert(users).values(data).returning();
  return result[0];
}

Running Migrations

The template includes scripts to help manage database migrations:

# Generate migrations based on schema changes
npm run db:generate

# Apply pending migrations
npm run db:migrate

# Start the development database with Docker
docker-compose up -d

Configuration

Database settings can be configured in the .env file and in drizzle.config.ts. The following environment variables are used:

DATABASE_URL: Connection string for your PostgreSQL database
DATABASE_HOST: Database host (default: localhost)
DATABASE_PORT: Database port (default: 5432)
DATABASE_NAME: Database name
DATABASE_USER: Database user
DATABASE_PASSWORD: Database password