nc-db 0.1.3

A PostgresSql Database project with a fully generic infrastructure and design.

nc_db

PostgreSQL schema, lightweight Python DB client, tests, and Kubernetes manifests for the Neural Context project.

This repository provides:

• A thin, typed Python client for JSONB-first tables (insert/update/select/delete) under a configurable schema.
• Test suite that spins up isolated schemas to validate behavior and edge cases.
• Kubernetes manifests (Kustomize) to run Postgres locally or in-cluster, plus a safe reset script.

Contents

src/db/           # Python client: Db, DbConfig, SQL helpers
src/tests/        # Pytest suite and fixtures (isolated schemas)
k8s/              # K8s StatefulSet, init SQL, and ops scripts
pyproject.toml    # Poetry + pytest configuration
.github/          # Issue and PR templates
README.md         # This file (schema diagram below)

Prerequisites

• Python 3.12+
• PostgreSQL 15+ available locally, or run via the provided K8s manifests

Default development credentials used in examples/tests: host=localhost, port=5432, db=nc-data, user=nc_admin, password=bob.

Quick Start

1. Install dependencies with Poetry:

poetry install

2. Ensure Postgres is reachable using the defaults above. You can run it via Kubernetes (see below) or your local Postgres.

3. Smoke test the Python client:

poetry run python -c "from src.db.db import Db; print(Db().get('scg', ids=[]))"

Running Tests

kubectl apply -k k8s/postgres/overlays/ephemeral
kubectl -n nc_db wait --for=condition=ready pod -l app=nc-data --timeout=90s
kubectl -n nc_db port-forward svc/postgres 5432:5432

poetry run pytest -q

• To run a single test: poetry run pytest -k test_update_merge -q
• Tests create per-test schemas and tables; they do not require pre-existing app tables.

Python Client Overview

The client favors tables shaped as (id BIGSERIAL PRIMARY KEY, body JSONB NOT NULL) and enforces body.id == id on writes/reads.

Basic usage:

from nc_db.db.db import Db
from nc_db.db.DbConfig import DbConfig

db = Db(schema="nc", config=DbConfig(
    user="my_user", password="my_password", host="localhost", port=5432, dbname="my_database"
))

# Insert with or without explicit id
id1 = db.insert("scg", {"body": {"name": "alpha"}})
id2 = db.insert("scg", {"body": {"id": 42, "name": "explicit"}})

# Update (merge JSON into existing body or replace entirely)
db.update("scg", ids=[id1], items=[{"body": {"name": "alpha-2"}}], mode="merge")

# Read (optionally map rows to models)
rows = db.get("scg")  # [{"id": 1, "body": {...}}, ...]

# Delete
db.delete("scg", [id2])

Key features:

• Insert trusts a provided id if present; otherwise uses the table sequence and normalizes body.id.
• Update supports mode="merge" (default) and mode="replace", always ensuring body.id matches the row id.
• Get returns rows as dictionaries or via a mapper function to your own model types.
• Delete single or all rows, plus a helper to TRUNCATE ... RESTART IDENTITY.

Configuration

Use DbConfig or environment variables to control connection pooling and DSN; defaults are suitable for local development.

from nc_db.db.DbConfig import DbConfig

config = DbConfig(
    user="your_username",
    password="your_password",
    host="localhost",
    port=5432,
    dbname="your_database",
)

OR

export PGUSER="your_username"
export PGPASSWORD="your_password"
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE="youd_database"

Kubernetes (Optional)

Run a local Postgres via Kustomize overlays.

Ephemeral (emptyDir) instance:

kubectl apply -k k8s/postgres/overlays/ephemeral
kubectl -n nc_db wait --for=condition=ready pod -l app=nc-data --timeout=90s
kubectl -n nc_db port-forward svc/postgres 5432:5432

Persistent storage overlay is also available under k8s/postgres/overlays/persistent.

To safely reset a StatefulSet (DANGEROUS — wipes data), use:

python k8s/scripts/full_pod_reset.py --yes

Review the script before use; it scales down, deletes the PVC (and optionally PV), and scales back up.

Development Conventions

• Python 3.12, type hints, PEP 8; keep imports sorted and code minimal.
• Modules: snake_case; Classes: CamelCase; variables/functions: snake_case.
• Tests live in src/tests; name files/functions test_* and focus on behavior.
• See .github/PULL_REQUEST_TEMPLATE.md for PR expectations.

Schema Diagram

Note: the init SQL currently provisions scg and healthcheck

Security & License

• Do not commit real secrets. The defaults in code and K8s manifests are for local development only.
• Use DbConfig or environment variables to specify connection details.
• License: Proprietary (see pyproject.toml).