<!-- Markdown mirror of https://justscale.sh/docs/postgres/overview -->

# PostgreSQL Overview

PostgreSQL adapter for persistent storage in JustScale

The PostgreSQL adapter provides a production-ready storage backend for JustScale applications. It wraps domain models with PostgreSQL-specific configuration, separating domain concerns from storage implementation details.

## Installation

Install the PostgreSQL adapter package:

Bash

```bash
pnpm add @justscale/postgres
```

## Core Concepts

### Domain Models vs Storage Models

JustScale separates domain models (pure business logic) from storage models (database-specific configuration):

- Domain Model - Defined with defineModel, contains field types and validation, no storage details
- Storage Model - Created with createPgModel, wraps the domain model with table name, indexes, column overrides
- Repository - Created with createPgRepository, provides query methods and integrates with DI

## Quick Start

Here is a complete example showing all three layers:

user-model.tsTypeScript

```typescript
import { defineModel, field } from '@justscale/core/models';
import { createPgModel, createPgRepository } from '@justscale/postgres';

// 1. Define domain model (pure, no storage details)
class User extends defineModel({
  email: field.string().max(255).unique(),
  name: field.string().max(100),
  status: field.enum('UserStatus', ['active', 'inactive', 'banned'] as const).default('active'),
  balance: field.decimal(10, 2).default('0.00'),
}) {}

// 2. Create storage model with PostgreSQL config
const PgUser = createPgModel(User, {
  table: 'users',  // Optional: defaults to snake_case + 's'
  indexes: [
    { fields: ['email'], unique: true },
    { fields: ['status'] },
  ],
});

// 3. Create repository service
const UserRepository = createPgRepository(PgUser);

export { User, UserRepository };
```

## Storage Modes

The PostgreSQL adapter supports two storage modes:

### Columnar Mode (Default)

Each field maps to a database column. Provides best query performance and full SQL capabilities.

columnar-mode.tsTypeScript

```typescript
import { createPgModel } from '@justscale/postgres';
import { User } from './user-model';

const PgUser = createPgModel(User, {
  table: 'users',
  storageMode: 'columnar',  // Default, can be omitted
});

// Creates table: users (id, email, name, status, balance, created_at, updated_at, version)
```

### JSONB Mode

Fields are stored in a JSONB column. Useful for schema flexibility and rapid iteration. System fields (id, createdAt, updatedAt, version) remain as columns.

jsonb-mode.tsTypeScript

```typescript
import { createPgModel } from '@justscale/postgres';
import { Product } from './product-model';

const PgProduct = createPgModel(Product, {
  table: 'products',
  storageMode: 'jsonb',
  dataColumn: 'data',  // Optional: defaults to 'data'
});

// Creates table: products (id, data, created_at, updated_at, version)
// All domain fields stored in the 'data' JSONB column
```

## Table Naming Conventions

By default, table names are automatically generated from model names using snake_case and pluralization:

table-naming.tsTypeScript

```typescript
import { defineModel, field } from '@justscale/core/models';
import { createPgModel } from '@justscale/postgres';

class User extends defineModel({ name: field.string() }) {}
const PgUser = createPgModel(User);
// Table: 'users'

class BlogPost extends defineModel({ title: field.string() }) {}
const PgBlogPost = createPgModel(BlogPost);
// Table: 'blog_posts'

class Category extends defineModel({ name: field.string() }) {}
const PgCategory = createPgModel(Category, { table: 'categories' });
// Table: 'categories' (explicit override)
```

## Column Overrides

Override inferred PostgreSQL types or add constraints for specific fields:

column-overrides.tsTypeScript

```typescript
import { defineModel, field } from '@justscale/core/models';
import { createPgModel } from '@justscale/postgres';

class User extends defineModel({
  email: field.string().max(255),
  tags: field.array(field.string()),
  metadata: field.jsonb(),
}) {}

const PgUser = createPgModel(User, {
  table: 'users',
  overrides: {
    email: { type: 'CITEXT', unique: true },  // Case-insensitive text
    tags: { type: 'TEXT[]' },  // Native PostgreSQL array
  },
});
```

## Connecting to PostgreSQL

Supply the connection string through a secret provider and add `PostgresFeature` — it provides `AbstractPostgresClient` for DI. The channel and lock features layer on LISTEN/NOTIFY pub/sub and distributed advisory locks over the same connection:

database-setup.tsTypeScript

```typescript
import JustScale, { createSecretProvider } from '@justscale/core';
import {
  PostgresFeature,
  PostgresChannelFeature,
  PostgresLockFeature,
  PostgresSecrets,
} from '@justscale/postgres';

const Secrets = createSecretProvider({
  provides: [PostgresSecrets],
  factory: () => ({
    [PostgresSecrets.key]: { connectionString: process.env.DATABASE_URL! },
  }),
});

const app = JustScale()
  .add(Secrets)
  .add(PostgresFeature)         // provides AbstractPostgresClient
  .add(PostgresChannelFeature)  // provides AbstractChannelBackend (LISTEN/NOTIFY)
  .add(PostgresLockFeature)     // distributed advisory locks
  .build();
```

Tune the connection pool with a `PostgresClientConfig` partial (`max`, `idleTimeout`, `connectTimeout`) — or adjust it at runtime with the config CLI:

pool-config.tsTypeScript

```typescript
import { createConfig } from '@justscale/core';
import { PostgresClientConfig } from '@justscale/postgres';

const PoolConfig = createConfig({
  provides: [PostgresClientConfig],
  factory: () => ({
    [PostgresClientConfig.key]: {
      max: 25,           // Connection pool size
      idleTimeout: 20,   // Seconds
      connectTimeout: 10, // Seconds
    },
  }),
});

// Or at runtime:  just config set postgres:client max 25
```

Need a custom secret shape, multiple databases, or hand-built wiring? The low-level `createPostgresClient` / `createPostgresChannelBackend` factories live in `@justscale/postgres/advanced`.

## Type Safety

Repositories are fully typed based on your domain models:

type-safety.tsTypeScript

```typescript
import { User, UserRepository } from './user-model';

class UserService extends defineService({
  inject: { users: UserRepository },
  factory: ({ users }) => ({
    async findActive() {
      // Type-safe field references
      return users.find({
        where: User.fields.status.eq('active'),
        orderBy: { createdAt: 'desc' },
      });
      // Returns: Persistent<User>[]
      // Each entity has: id, email, name, status, balance, createdAt, updatedAt, version
    },
  }),
}) {}
```

## Best Practices

- Separate domain from storage - Define models with defineModel, then wrap with createPgModel
- Use columnar mode for production - Better performance for queries, indexes, and constraints
- Use JSONB mode for rapid iteration - Schema changes without migrations during development
- Leverage type safety - Use field expressions like User.fields.email.eq('...') for compile-time validation
- Configure indexes - Add indexes for frequently queried fields to improve performance

## What's Next

Now that you understand the basics, learn about repository methods, transactions, and advanced features:

## Next Steps

- PostgreSQL Repositories
- Queries Overview
- Models Overview