Skip to main content
This guide covers setting up and configuring the PostgreSQL database for the POS Nest API.

​
Prerequisites

  • PostgreSQL installed (see Environment Setup)
  • Environment variables configured
  • Node.js and npm installed

​
PostgreSQL Installation and Configuration

​
Local PostgreSQL Setup

1

Verify PostgreSQL is running

# Check if PostgreSQL service is running
# macOS (Homebrew)
brew services list | grep postgresql

# Linux (systemd)
sudo systemctl status postgresql

# Windows
# Check Services app for "postgresql-x64-15"
2

Access PostgreSQL

# Connect to PostgreSQL as the default user
psql -U postgres

# If you need to specify host and port
psql -h localhost -p 5432 -U postgres
3

Set or verify the postgres user password

If you need to set a password for the postgres user:
ALTER USER postgres WITH PASSWORD 'your_password';

​
Using Supabase Database

Supabase provides a managed PostgreSQL database. If you’re using Supabase for both authentication and database, you can use the database credentials from your Supabase project.
1

Get database credentials

In your Supabase dashboard:
  • Go to Project Settings > Database
  • Find the “Connection string” section
  • Use the “URI” or individual connection parameters
2

Update environment variables

DATABASE_HOST=db.xxxxxxxxxxxxx.supabase.co
DATABASE_PORT=5432
DATABASE_USER=postgres
DATABASE_PASS=your_supabase_db_password
DATABASE_NAME=postgres

​
Database Creation

1

Connect to PostgreSQL

psql -U postgres -h localhost
2

Create the database

CREATE DATABASE pos_nest_db;
For a development and test environment setup:
-- Development database
CREATE DATABASE pos_nest_dev;

-- Test database
CREATE DATABASE pos_nest_test;
3

Verify database creation

\l
-- or
\list
You should see your newly created database in the list.
4

Connect to the database

\c pos_nest_db
If you’re using Supabase, the database is already created. You’ll connect to the default postgres database or create a new one through the Supabase SQL Editor.

​
Database Schema and Migrations

​
Automatic Schema Synchronization

The POS Nest API uses TypeORM with synchronize: true enabled in development: 
// src/config/typeorm.config.ts
export const typeOrmConfig = (configService: ConfigService): TypeOrmModuleOptions => ({
  type: 'postgres',
  host: configService.get('DATABASE_HOST'),
  port: configService.get('DATABASE_PORT'),
  username: configService.get('DATABASE_USER'),
  password: configService.get('DATABASE_PASS'),
  database: configService.get('DATABASE_NAME'),
  ssl: {
    rejectUnauthorized: false,
  },
  logging: false,
  entities: [join(__dirname, '..', '**', '*.entity.{ts,js}')],
  synchronize: true, // Auto-creates tables from entities
});
The synchronize: true option automatically creates database tables based on your entity definitions. This is convenient for development but should NEVER be used in production as it can cause data loss.

​
Schema Creation on First Run

1

Ensure database exists and is accessible

Verify your .env file has the correct database credentials.
2

Start the application

npm run start:dev
TypeORM will automatically:
  • Connect to your PostgreSQL database
  • Scan for all *.entity.ts files
  • Create tables based on your entity definitions
  • Create relationships and indexes
3

Verify tables were created

Connect to your database and list tables:
psql -U postgres -d pos_nest_db

\dt
You should see tables for:
  • categories
  • products
  • transactions
  • coupons
  • And other entities defined in the application

​
Production Migrations

For production deployments, disable synchronize and use TypeORM migrations: 
// Production configuration
synchronize: false,
migrationsRun: true,
migrations: [join(__dirname, '..', 'migrations', '*.{ts,js}')],
To generate and run migrations: 
# Generate migration from entity changes
npm run typeorm migration:generate -- -n MigrationName

# Run migrations
npm run typeorm migration:run

# Revert last migration
npm run typeorm migration:revert
You’ll need to add these scripts to package.json if you plan to use migrations:
"typeorm": "typeorm-ts-node-commonjs",
"migration:generate": "npm run typeorm -- migration:generate",
"migration:run": "npm run typeorm -- migration:run",
"migration:revert": "npm run typeorm -- migration:revert"

​
Seeding Data

The application includes a seeder to populate your database with initial data.

​
Running the Seeder

1

Ensure database is set up

Make sure your database exists and the application has created the schema (run npm run start:dev at least once).
2

Run the seed command

npm run seed
This executes the seeder script at src/seeder.ts which:
  • Creates a NestJS application context with the SeederModule
  • Runs the SeederService.seed() method
  • Populates the database with initial data
  • Closes the application context
3

Verify seeded data

You can verify the data was seeded by:
-- Connect to database
psql -U postgres -d pos_nest_db

-- Check categories
SELECT * FROM categories;

-- Check products
SELECT * FROM products;

-- Check other tables
SELECT * FROM transactions;
SELECT * FROM coupons;
The seeder is idempotent-safe depending on its implementation. Check src/seeder/seeder.service.ts to see if it clears existing data or skips already-seeded records.

​
Database Connection Testing

​
Test Connection on Application Start

1

Start the application in development mode

npm run start:dev
2

Watch for connection messages

You should see logs indicating successful database connection. If there are connection errors, they’ll appear in the console.
3

Test API health check

curl http://localhost:3000
If the application starts successfully, the database connection is working.

​
Manual Connection Test

Test your database connection directly: 
# Using psql
psql -h localhost -p 5432 -U postgres -d pos_nest_db

# Using connection string format
psql postgresql://postgres:password@localhost:5432/pos_nest_db

​
Troubleshooting Connection Issues

  • Verify PostgreSQL is running: sudo systemctl status postgresql (Linux) or brew services list (macOS)
  • Check if PostgreSQL is listening on the correct port: sudo netstat -plnt | grep 5432
  • Ensure firewall isn’t blocking port 5432
  • Verify username and password in .env are correct
  • Check PostgreSQL’s pg_hba.conf allows password authentication
  • Try connecting with psql directly to confirm credentials
  • Verify database name in .env matches the created database
  • Create the database if it doesn’t exist: CREATE DATABASE pos_nest_db;
  • Check for typos in DATABASE_NAME environment variable
The application is configured with ssl: { rejectUnauthorized: false }. If you need strict SSL:
ssl: {
  rejectUnauthorized: true,
  ca: fs.readFileSync('/path/to/ca-certificate.crt').toString(),
}

​
Database Configuration Reference

The database configuration is defined in src/config/typeorm.config.ts: 
import { ConfigService } from '@nestjs/config';
import type { TypeOrmModuleOptions } from '@nestjs/typeorm';
import { join } from 'path';

export const typeOrmConfig = (
  configService: ConfigService,
): TypeOrmModuleOptions => ({
  type: 'postgres',
  host: configService.get('DATABASE_HOST'),
  port: configService.get('DATABASE_PORT'),
  username: configService.get('DATABASE_USER'),
  password: configService.get('DATABASE_PASS'),
  database: configService.get('DATABASE_NAME'),
  ssl: {
    rejectUnauthorized: false,
  },
  logging: false,
  entities: [join(__dirname, '..', '**', '*.entity.{ts,js}')],
  synchronize: true,
});

​
Configuration Options

OptionDescriptionDevelopmentProduction
synchronizeAuto-create tables from entitiestruefalse
loggingEnable SQL query loggingfalse or true for debuggingfalse
sslSSL connection settingsOptionalRecommended
entitiesPath to entity filesAuto-discoveredAuto-discovered

​
Next Steps

Deployment

Learn how to deploy your API to production

API Reference

Start using the API endpoints

Build docs developers (and LLMs) love

Get started for freeTalk to us