Skip to main content

​
Database

SPN uses PostgreSQL with Alembic for migrations.

​
Migrations

​
Generate Migration

cd backend
PYTHONPATH=. alembic revision --autogenerate -m "description"

​
Apply Migrations

cd backend
PYTHONPATH=. alembic upgrade head

​
Rollback

cd backend
PYTHONPATH=. alembic downgrade -1

​
View History

cd backend
PYTHONPATH=. alembic history --verbose

​
Schema

​
Core Tables

TableDescription
companiesCompany profiles
usersUser accounts
catalog_productsProduct catalog
contactsContact information
enquiriesLeads/enquiries

​
Full-Text Search

The companies table includes a search_vector column for fast search: 
SELECT * FROM companies
WHERE search_vector @@ plainto_tsquery('english', 'search term')
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'search term')) DESC;

​
Connecting to Database

​
From Host

docker exec -it spn_postgres psql -U postgres -d spn_db

​
From Container

docker exec -it spn_backend-1 psql -U postgres -d spn_db

​
Common Tasks

​
Add a Column

  1. Update model in backend/app/models/domain.py
  2. Generate migration: alembic revision --autogenerate
  3. Review the migration file
  4. Apply: alembic upgrade head

​
Reset Database

docker-compose down -v
docker-compose up -d
alembic upgrade head
Warning: This deletes all data!