Skip to main content
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:[email protected]: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)

  1. 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;
  2. Set environment variable 
    DATABASE_URL=postgresql://inventario_user:secure_password@localhost:5432/inventario
  3. 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:
  1. Export data from SQLite 
    python manage.py dumpdata --natural-foreign --natural-primary \
  -e contenttypes -e auth.Permission > data.json
  2. Configure PostgreSQL 
    export DATABASE_URL=postgresql://user:pass@host:5432/dbname
  3. Run migrations 
    python manage.py migrate
  4. Import data 
    python manage.py loaddata data.json
  5. Verify data 
    python manage.py shell
>>> from applications.cuentas.models import Usuario
>>> Usuario.objects.count()

Build docs developers (and LLMs) love

Get started for freeTalk to us