Database - Inventario

Inventario uses Django’s ORM with support for PostgreSQL in production and SQLite for development. Database configuration is managed through environment variables.

Database Configuration

PostgreSQL with dj-database-url

The database is configured using dj-database-url for easy environment-based setup:

import dj_database_url

DATABASES = {
    'default': dj_database_url.config(
        default=f'sqlite:///{BASE_DIR / "db.sqlite3"}',
        conn_max_age=600
    )
}

Configuration:

Environment variable: DATABASE_URL
Connection pooling: conn_max_age=600 (10 minutes)
Fallback: SQLite at db.sqlite3 if DATABASE_URL not set

Database URL Format

PostgreSQL connection string format:

DATABASE_URL=postgresql://username:password@host:port/database_name

Example:

DATABASE_URL=postgresql://inventario_user:mypassword@db.railway.app:5432/inventario_prod

Components:

postgresql:// - Database engine
username:password - Credentials
host:port - Database server location
database_name - Database name

SQLite Fallback (Development)

When DATABASE_URL is not set, Inventario uses SQLite:

default=f'sqlite:///{BASE_DIR / "db.sqlite3"}'

Location: db.sqlite3 in project root

SQLite is suitable for development only. Always use PostgreSQL for production:

SQLite lacks concurrent write support
No user management or access control
Limited performance under load
File-based storage not suitable for containerized deployments

Database Setup

Development Setup

For local development with SQLite:

# No DATABASE_URL needed
python manage.py migrate
python manage.py createsuperuser
python manage.py runserver

SQLite database is created automatically on first migration.

Production Setup (PostgreSQL)

Provision PostgreSQL database On Railway:

# Railway provides DATABASE_URL automatically when you add PostgreSQL

On Render:

# Add PostgreSQL service in Render dashboard
# Copy DATABASE_URL from database info

Manual setup:

CREATE DATABASE inventario;
CREATE USER inventario_user WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE inventario TO inventario_user;

Set environment variable

DATABASE_URL=postgresql://inventario_user:secure_password@localhost:5432/inventario

Run migrations
python manage.py migrate

Migrations

Django migrations track database schema changes.

Running Migrations

Apply pending migrations:

python manage.py migrate

The build.sh script automatically runs migrations on deployment:

echo "🗄️ Applying migrations..."
python manage.py migrate

Creating Migrations

After changing models, create migrations:

python manage.py makemigrations

This generates migration files in each app’s migrations/ directory.

Always commit migration files to version control. They ensure database schema stays in sync across environments.

Checking Migration Status

View applied and pending migrations:

python manage.py showmigrations

View SQL for a migration:

python manage.py sqlmigrate <app_name> <migration_number>

Rolling Back Migrations

Rollback to a specific migration:

python manage.py migrate <app_name> <migration_name>

Rollback all migrations for an app:

python manage.py migrate <app_name> zero

Rolling back migrations in production can cause data loss. Always backup the database first.

Database Applications

Inventario includes these database-backed applications:

applications.usuarios - User management
applications.cuentas - Account management with custom user model
applications.proveedores - Supplier records
applications.productos - Product inventory
applications.clientes - Customer records
applications.ventas - Sales transactions
applications.reportes - Report data
applications.compras - Purchase orders
applications.configuracion - System configuration
applications.devoluciones - Return/refund records

Each application has its own migration directory and models.

Custom User Model

Inventario uses a custom user model:

AUTH_USER_MODEL = 'cuentas.Usuario'

This model is defined in applications.cuentas.models and extends Django’s authentication.

Changing AUTH_USER_MODEL after initial migrations is extremely difficult. This must be set before first migration.

Connection Pooling

conn_max_age=600

Keeps database connections open for 10 minutes:

Reduces connection overhead
Improves response times
Prevents connection exhaustion

Adjust based on:

Database connection limits
Application server count
Expected concurrent users

Formula: max_connections / (workers * servers) ≈ 600 seconds

Database Backups

PostgreSQL Backups

Create a backup:

pg_dump -h host -U username -d database_name -F c -f backup.dump

With DATABASE_URL:

pg_dump $DATABASE_URL -F c -f backup_$(date +%Y%m%d_%H%M%S).dump

Restore from Backup

pg_restore -h host -U username -d database_name -c backup.dump

Restore with -c (clean) drops existing objects. Test on a separate database first.

Automated Backups

On Railway:

Configure automatic backups in Railway dashboard
Backups are taken daily by default

On Render:

Enable automatic backups in database settings
Choose retention period

Manual automation:

#!/bin/bash
# backup.sh - Daily PostgreSQL backup
BACKUP_DIR="/backups"
DATABASE_URL="$DATABASE_URL"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

pg_dump $DATABASE_URL -F c -f "$BACKUP_DIR/backup_$TIMESTAMP.dump"

# Keep only last 7 days
find $BACKUP_DIR -name "backup_*.dump" -mtime +7 -delete

Run daily with cron:

0 2 * * * /path/to/backup.sh

Database Management

Django Database Shell

Open PostgreSQL shell through Django:

python manage.py dbshell

This uses the configured DATABASE_URL automatically.

Inspecting the Database

View all tables:

\dt

Describe a table:

\d+ tablename

Query data:

SELECT * FROM cuentas_usuario LIMIT 10;

Django Shell

Interact with models in Python:

python manage.py shell

from applications.cuentas.models import Usuario
from applications.productos.models import Producto

# Query users
users = Usuario.objects.all()
print(f"Total users: {users.count()}")

# Query products
products = Producto.objects.filter(activo=True)
print(f"Active products: {products.count()}")

Database Optimization

Indexing

Django automatically creates indexes for:

Primary keys
Foreign keys
Fields with unique=True
Fields with db_index=True

Add custom indexes in models:

class Meta:
    indexes = [
        models.Index(fields=['campo1', 'campo2']),
    ]

Query Optimization

Use select_related() for foreign keys:

# Instead of:
products = Producto.objects.all()
for product in products:
    print(product.proveedor.nombre)  # N+1 queries

# Use:
products = Producto.objects.select_related('proveedor')
for product in products:
    print(product.proveedor.nombre)  # 1 query

Use prefetch_related() for many-to-many:

ventas = Venta.objects.prefetch_related('productos')

Database Monitoring

Monitor these metrics:

Connection count
Query execution time
Slow queries
Database size
Index usage

Enable query logging in development:

# settings.py (development only)
if DEBUG:
    LOGGING = {
        'version': 1,
        'handlers': {
            'console': {
                'class': 'logging.StreamHandler',
            },
        },
        'loggers': {
            'django.db.backends': {
                'handlers': ['console'],
                'level': 'DEBUG',
            },
        },
    }

Troubleshooting

”No such table” Error

Migrations haven’t been applied:

python manage.py migrate

Connection Refused

Check DATABASE_URL is correct:

echo $DATABASE_URL

Test PostgreSQL connection:

psql $DATABASE_URL

Authentication Failed

Verify credentials in DATABASE_URL:

Username and password are correct
User has access to the database
Database exists

Too Many Connections

Reduce conn_max_age or increase PostgreSQL max_connections:

DATABASES = {
    'default': dj_database_url.config(
        default=f'sqlite:///{BASE_DIR / "db.sqlite3"}',
        conn_max_age=300  # Reduced from 600
    )
}

Migration Conflicts

If multiple developers create migrations simultaneously:

python manage.py makemigrations --merge

Database Locked (SQLite)

SQLite locks the entire database for writes. Switch to PostgreSQL for production:

DATABASE_URL=postgresql://...
python manage.py migrate

Data Import/Export

Export Data (JSON)

python manage.py dumpdata > data.json

Export specific app:

python manage.py dumpdata productos > productos.json

Import Data

python manage.py loaddata data.json

loaddata can create duplicate data. Use with caution in production.

Migration from SQLite to PostgreSQL

To migrate from development SQLite to production PostgreSQL:

Export data from SQLite

python manage.py dumpdata --natural-foreign --natural-primary \
  -e contenttypes -e auth.Permission > data.json

Configure PostgreSQL

export DATABASE_URL=postgresql://user:pass@host:5432/dbname

Run migrations
python manage.py migrate
Import data
python manage.py loaddata data.json

Verify data

python manage.py shell
>>> from applications.cuentas.models import Usuario
>>> Usuario.objects.count()

​Database Configuration

​PostgreSQL with dj-database-url

​Database URL Format

​SQLite Fallback (Development)

​Database Setup

​Development Setup

​Production Setup (PostgreSQL)

​Migrations

​Running Migrations

​Creating Migrations

​Checking Migration Status

​Rolling Back Migrations

​Database Applications

​Custom User Model

​Connection Pooling

​Database Backups

​PostgreSQL Backups

​Restore from Backup

​Automated Backups

​Database Management

​Django Database Shell

​Inspecting the Database

​Django Shell

​Database Optimization

​Indexing

​Query Optimization

​Database Monitoring

​Troubleshooting

​”No such table” Error

​Connection Refused

​Authentication Failed

​Too Many Connections

​Migration Conflicts

​Database Locked (SQLite)

​Data Import/Export

​Export Data (JSON)

​Import Data

​Migration from SQLite to PostgreSQL

Database Configuration

PostgreSQL with dj-database-url

Database URL Format

SQLite Fallback (Development)

Database Setup

Development Setup

Production Setup (PostgreSQL)

Migrations

Running Migrations

Creating Migrations

Checking Migration Status

Rolling Back Migrations

Database Applications

Custom User Model

Connection Pooling

Database Backups

PostgreSQL Backups

Restore from Backup

Automated Backups

Database Management

Django Database Shell

Inspecting the Database

Django Shell

Database Optimization

Indexing

Query Optimization

Database Monitoring

Troubleshooting

”No such table” Error

Connection Refused

Authentication Failed

Too Many Connections

Migration Conflicts

Database Locked (SQLite)

Data Import/Export

Export Data (JSON)

Import Data

Migration from SQLite to PostgreSQL