Database Configuration

Overview

The Inmobiliaria API uses PostgreSQL as its database and Drizzle ORM for type-safe database access and migrations.

Requirements

PostgreSQL 12 or higher
Node.js 18 or higher
Database with public schema access

Quick Setup

Install PostgreSQL

Install PostgreSQL on your system:

brew install postgresql@15
brew services start postgresql@15

Create Database

Create a database for the application:

CREATE DATABASE inmobiliaria;

Or using psql:

psql -U postgres -c "CREATE DATABASE inmobiliaria;"

Set DATABASE_URL

Add the connection string to your .env file:

.env

DATABASE_URL=postgresql://postgres:password@localhost:5432/inmobiliaria

Format: postgresql://[user]:[password]@[host]:[port]/[database]

Run Migrations

Apply the database schema:

npm run db:push

Or use migration files:

npm run db:migrate:run

Seed Data (Optional)

Populate with sample data for development:

npm run db:seed

Drizzle Configuration

The database is configured in drizzle.config.ts:

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

// Ensure search_path is set for migrations
let dbUrl = process.env.DATABASE_URL!;
if (dbUrl && !dbUrl.includes("search_path")) {
  const separator = dbUrl.includes("?") ? "&" : "?";
  dbUrl += `${separator}schema=public`;
}

export default defineConfig({
  out: "./src/db/migrations",
  schema: "./src/db/schema/*",
  dialect: "postgresql",
  dbCredentials: {
    url: dbUrl,
  },
  schemaFilter: ["public"],
  verbose: true,
  strict: true,
});

Configuration Options

Option	Value	Description
`out`	`./src/db/migrations`	Directory for migration files
`schema`	`./src/db/schema/*`	Drizzle schema definition files
`dialect`	`postgresql`	Database type
`schemaFilter`	`["public"]`	Use PostgreSQL `public` schema
`verbose`	`true`	Show detailed migration logs
`strict`	`true`	Enable strict mode for schema validation

Database Schema

The application uses a comprehensive schema organized into several domains.

Authentication Tables

users

Core user accounts table (Better Auth compatible).Schema Definition: src/db/schema/users.ts

Column	Type	Description
`id`	text	Primary key (UUID)
`name`	text	User’s display name
`email`	text	Unique email address
`email_verified`	boolean	Email verification status
`image`	text	Profile image URL
`role`	user_role	`user` or `admin`
`created_at`	timestamp	Account creation time
`updated_at`	timestamp	Last update time

sessions

Active user sessions.

Column	Type	Description
`id`	text	Primary key
`user_id`	text	References `users.id`
`token`	text	Unique session token
`expires_at`	timestamp	Session expiration
`ip_address`	text	Client IP
`user_agent`	text	Browser/device info

accounts

OAuth provider accounts linked to users.

Column	Type	Description
`id`	text	Primary key
`user_id`	text	References `users.id`
`provider_id`	text	OAuth provider (google, etc.)
`account_id`	text	Provider’s user ID
`access_token`	text	OAuth access token
`refresh_token`	text	OAuth refresh token

verification

Email verification tokens.

Column	Type	Description
`id`	text	Primary key
`identifier`	text	Email or user identifier
`value`	text	Verification token
`expires_at`	timestamp	Token expiration

Property Management Tables

properties

Main properties listing table.Schema Definition: src/db/schema/properties.tsKey Columns:

Basic info: title, description, url_slug
Classification: property_type_id, property_subtype_id, operation_type_id
Pricing: price, original_price, currency_id, expenses
Size: surface_covered_m2, surface_uncovered_m2, lot_size_m2
Rooms: rooms, bedrooms, bathrooms
Features: has_balcony, has_terrace, has_garden, garage_spaces
Status: status_id, published_at, views_count
Ownership: user_id

Relationships:

References multiple metadata tables
One-to-one with property_locations
One-to-many with property_images

property_locations

Property address and geolocation.

Column	Type	Description
`property_id`	integer	References `properties.id` (unique)
`street`	varchar	Street name
`street_number`	varchar	Street number
`neighborhood`	varchar	Neighborhood/barrio
`city`	varchar	City name
`province`	varchar	Province/state
`latitude`	decimal(10,7)	GPS latitude
`longitude`	decimal(10,7)	GPS longitude
`show_exact_address`	boolean	Privacy control

property_images

Property photos with variants.

Column	Type	Description
`property_id`	integer	References `properties.id`
`image_key`	text	Storage key (main variant)
`image_url`	text	Public URL
`order_index`	integer	Display order
`is_main`	boolean	Featured image flag
`rotation_degrees`	integer	0, 90, 180, or 270

Images are stored with variants: -lg.webp (large) and -thumb.webp (thumbnail).

Metadata Tables

property_types

Property categories (Casa, Departamento, etc.).Pre-populated with: 14 types including Casa, Departamento, PH, Terreno, Local comercial, etc.

Column	Type	Description
`id`	serial	Primary key
`name`	varchar	Display name
`slug`	varchar	URL-friendly identifier

property_subtypes

Subcategories within property types.Examples: Casa en country, Monoambiente, PH con jardín

Column	Type	Description
`id`	serial	Primary key
`type_id`	integer	References `property_types.id`
`name`	varchar	Display name
`slug`	varchar	URL-friendly identifier

operation_types

Transaction types: Venta, Alquiler, Alquiler temporal, Permuta.

property_statuses

Status: Activo, Pendiente, Vendido, Alquilado, Pausado.

property_conditions

Condition: A estrenar, Bueno, Muy bueno, Excelente, Reciclado, Para refaccionar.

currencies

Supported currencies: ARS (Peso Argentino), USD (Dólar), EUR (Euro).

Property Characteristics

property_characteristics_all

Unified characteristics table supporting both predefined and custom features.

Column	Type	Description
`id`	uuid	Primary key
`property_id`	integer	References `properties.id`
`label`	varchar(150)	Characteristic name
`created_at`	timestamp	When added

Constraints:

Unique per property (case-insensitive, trimmed)
Label length: 1-150 characters

Examples: Parrilla, Pileta, Aire acondicionado, SUM, Gimnasio, Seguridad 24hs

Contact & Engagement

contact_requests

Property valuation and contact form submissions.Schema Definition: src/db/schema/contact.ts

Column	Type	Description
`id`	uuid	Primary key
`name`	varchar	Contact name
`email`	varchar	Contact email
`phone`	varchar	Contact phone
`available_hours`	varchar	Preferred contact time
`operation_type`	varchar	Venta/Alquiler
`property_type`	varchar	Type of property
`location`	text	Property location
`description`	text	Details
`status`	enum	pending/contacted/completed
`user_id`	text	Optional linked user
`is_deleted`	boolean	Soft delete flag

user_favorites

User saved properties.

Column	Type	Description
`id`	serial	Primary key
`user_id`	text	References `users.id`
`property_id`	integer	References `properties.id`
`created_at`	timestamp	When favorited

Constraint: Unique per user-property pair

Database Scripts

Available npm scripts for database management:

package.json

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio",
    "db:seed": "tsx src/db/seed.ts",
    "db:migrate:run": "tsx src/db/migrate.ts",
    "db:setup": "node scripts/setup-database.js"
  }
}

Command Reference

# Generate SQL migration files from schema changes
npm run db:generate

# Output: src/db/migrations/*.sql

Connection Configuration

The database connection is configured in src/db/index.ts:

src/db/index.ts

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // SSL configuration for production
  ssl: process.env.DATABASE_URL?.includes("sslmode=require")
    ? { rejectUnauthorized: false }
    : false,
});

export const db = drizzle(pool, { schema });

Connection Pooling

The application uses pg connection pooling with defaults:

Max connections: 10 (pg default)
Idle timeout: 10 seconds
Connection timeout: 0 (no timeout)

To customize:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // max connections
  idleTimeoutMillis: 30000, // 30 seconds
  connectionTimeoutMillis: 2000, // 2 seconds
});

Health Checks

The API includes database health check endpoints:

GET /

Response:
{
  "status": "ok",
  "message": "Inmobiliaria API Server",
  "timestamp": "2026-03-03T10:30:00.000Z",
  "database": {
    "status": "healthy",
    "latency": "5ms"
  }
}

Production Considerations

SSL Connections

Enable SSL for production databases:

DATABASE_URL=postgresql://user:pass@host:5432/db?sslmode=require

The application automatically detects sslmode=require and configures SSL.

Connection Limits

Monitor active connections
Configure max pool size based on load
Use connection pooling services (PgBouncer) for high traffic
Most cloud providers (Heroku, Render) include connection pooling

Migrations

Development:

npm run db:push  # Quick schema updates

Production:

npm run db:generate  # Generate migration SQL
npm run db:migrate:run  # Apply migrations

Always use migrations in production
Review generated SQL before applying
Backup database before major migrations

Backups

Regular backup strategies:

# Full backup
pg_dump -h host -U user -d inmobiliaria > backup.sql

# Schema only
pg_dump -h host -U user -d inmobiliaria --schema-only > schema.sql

# Data only
pg_dump -h host -U user -d inmobiliaria --data-only > data.sql

Automated: Use your hosting provider’s backup features

Indexes

The schema includes performance indexes:

idx_sessions_token - Fast session lookups
idx_sessions_user_id - User’s sessions
idx_properties_type - Filter by property type
idx_properties_operation - Filter by operation
idx_locations_city - Location-based search
idx_properties_search - Full-text search (GIN index)

Monitor slow queries and add indexes as needed.

Troubleshooting

Connection Refused

Error: ECONNREFUSED or “Connection refused”Solutions:

Verify PostgreSQL is running: pg_isready
Check host/port in DATABASE_URL
Verify firewall allows connections
For Docker: check container is running

Authentication Failed

Error: “password authentication failed”Solutions:

Verify username and password in DATABASE_URL
Check pg_hba.conf authentication method
Reset password: ALTER USER postgres PASSWORD 'newpassword';

Database Does Not Exist

Error: ‘database “inmobiliaria” does not exist’Solution:

psql -U postgres -c "CREATE DATABASE inmobiliaria;"

Schema/Table Not Found

Error: ‘relation “users” does not exist’Solutions:

Run migrations: npm run db:push or npm run db:migrate:run
Verify schemaFilter: ["public"] in drizzle.config.ts
Check search_path: SHOW search_path;

Too Many Connections

Error: “sorry, too many clients already”Solutions:

Reduce pool max connections
Close idle connections
Increase PostgreSQL max_connections
Use connection pooling service (PgBouncer)

Get Started

Authentication

Configuration

Deployment

Overview

Requirements

Quick Setup

Drizzle Configuration

Configuration Options

Database Schema

Authentication Tables

Property Management Tables

Metadata Tables

Property Characteristics

Contact & Engagement

Database Scripts

Command Reference

Connection Configuration

Connection Pooling

Health Checks

Production Considerations

Troubleshooting

See Also

Build docs developers (and LLMs) love

Get Started

Authentication

Configuration

Deployment

​Overview

​Requirements

​Quick Setup

​Drizzle Configuration

​Configuration Options

​Database Schema

​Authentication Tables

​Property Management Tables

​Metadata Tables

​Property Characteristics

​Contact & Engagement

​Database Scripts

​Command Reference

​Connection Configuration

​Connection Pooling

​Health Checks

​Production Considerations

​Troubleshooting

​See Also

Build docs developers (and LLMs) love

Overview

Requirements

Quick Setup

Drizzle Configuration

Configuration Options

Database Schema

Authentication Tables

Property Management Tables

Metadata Tables

Property Characteristics

Contact & Engagement

Database Scripts

Command Reference

Connection Configuration

Connection Pooling

Health Checks

Production Considerations

Troubleshooting

See Also