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

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

import { defineModel, field } from '@justscale/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

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

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

import { defineModel, field } from '@justscale/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

import { defineModel, field } from '@justscale/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

Create a PostgreSQL client and register it with your cluster:

database-setup.tsTypeScript

import { createPostgresClient } from '@justscale/postgres';

// Using connection string
const client = createPostgresClient({
  connectionString: process.env.DATABASE_URL,
});

// Or with individual options
const client = createPostgresClient({
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  username: 'user',
  password: 'pass',
  max: 10,  // Connection pool size
  idleTimeout: 20,  // Seconds
  connectTimeout: 10,  // Seconds
});

Type Safety

Repositories are fully typed based on your domain models:

type-safety.tsTypeScript

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

const UserService = createService({
  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: