nc-db 0.1.7

Simple document-oriented library for PostgresSql and Pydantic. High performance w/ support for large jsonb objects and fast querying using native b-tree indexes.

nc-db

The nc-db package provides an easy way to use the document/object oriented capabilites of Postgres while maintaining good performance with large object by using B-tree indexes instead of GINs.

Serialization and deserialization is handled for you with pydantic.

Prerequisites

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

How To Use

1. Install the package

   • poetry install nc-db
     

2. Import the necessary classes

   • from nc_db.decorators import schema_version
     from nc_db.identifiable import Identifiable
     from nc_db.db_settings import DbSettings        
     # Import index types as needed (IntDbIndex is just one example)
     from nc_db.metadata.db_column import IntDbIndex
     

3. Define your Postgres connection settings in a .env file (see example.env) or in the code by passing options to the DbSettings class.

4. Create an instance of the Db class in your service somewhere near startup

   • my_db = Db(my_settings)
     

5. Specify the schema version for the class you want to save in the database

   •   @schema_version(1)
       class Foo(Identifiable):
     

   • NOTE: If you make changes to your class (e.g attributes, attribute types, etc.) you should update the schema version number. This includes changes to classes that are referenced as attributes on your class. nc-db will raise an exception at run-time if anything changes and you forget to update the schema version number. You can suppress this with @schema_varsion(1,True) but it's not recommended as it may lead to hard-to-find bugs.
   • NOTE: This is only needed on the top-level class. If your class references other classes as attributes, they don't need a schema version but you'll need to update the schema version on your top-level class if anything changes on other classses it references.

6. Define any custom indexes on attributes you wish to query (don't worry about the id attribute -- it's automatically added when you inherit from Identifiable):

   •   class Foo(Identifiable):
           my_var: IntDbIndex
     

   • OR use annotations for more customized indexes:

     class Foo(Identifiable):
         name: Annotated[str, DbColumn('VARCHAR NOT NULL', True, False)]
     

7. Perform CRUD as frequently and quickly as you like (basic use cases)

   • # Save a new instance of an object to the database
     await db.insert(my_class_instance)
     # Get one or more instances from the databse by id
     await db.get(MyClass,[1])
     # Search for all objects where an attribute equals a value
     # NOTE: The my_attribute must be annotated as an index on 'MyClass'
     await db.search(MyClass,'my_attribute',23)
     # Search for all objects that meet arbitrary criteria
     # NOTE: my_attribute must be annotated as an index on 'MyClass'
     await db.search(MyClass,t"my_attribute < 23 AND my_attribute > 2")
     

8. Perform advanced custom querying

   • # Generate columns in your SQL (e.g. pull out statistics, counts, calculate or concatenate values, etc.)
     # and map the query results to an object.
     # NOTE: Columns will be mapped based upon the class attributes. All class attibutes must map to a column
     # from the select statement or an exception will be raised.
     await db.select(MyQueryResult,t"SELECT COUNT(COL1) as count, (COL2 + COL3) as total FROM my_class WHERE COL1 LIKE 'TEST_%';")
     

Migrations and Backwards Compatibility

Option 1

1. Keep a reference to your previous classes (e.g. MyClassV1)
2. Create your new classes (e.g. MyClassV2)
3. Define a function that moves data from old class instances and puts them in new class instances
4. Call the db.migrate function

Option 2

1. Write your own code to update the DB tables or migrate.
   • NOTE: For this to work your table name must match the table name generated by nc-db.

Guidelines

The Db class provides easy document/object-oriented CRUD capabilities for Pydantic classes.

• Connects to a Postgres DB.
• Supports concurrent connections across multiple processes / services
• Uses b-trees for indexes
• To improve performance with large objects:
  1. each object type has a dedicated table in the database.
  2. indexes are always maintained in dedicated columns (B-trees, no GIN indexes)
  3. The Db class handles all of this for you but you MUST apply the 'index' attribute to fields you want to search on.
• The Db instance should share a lifecycle with your service or application. Recommended practice is to treat it like a singleton for each database your service connects to.

How it Works

graph TB

    subgraph API            
        db["Db class<hr>API for performing CRUD"]
        db_index["DbIndex<hr>Annotation that defines indexes on user classes"]
        identifiable["Identifiable<hr>Base class all user classes must derive from"]
    end
    subgraph Handlers
        command_handler["CommandHandler<hr>SQL for inserts, updates, etc."]
        query_handler["QueryHandler<hr>SQL for get, search, etc."]
        common["common.py<hr>small reusable code for handlers"]
    end
    subgraph Metadata
        class_metadata["ClassMetadata<hr>Structural information about the user's class"]
        db_column["DbColumn<hr>Maps attributes to user-defined indexes"]
        nc_config["nc_config.py<hr>Very small global config constants"]
        column_mappers["ColumnMapper<hr>Maps data from the DB into a column"]
    end
    user["User App"]
    user_db_class["User Db Classes"]
    db--->|Execute DB CRUD|Handlers
    db--->|Track information about classes|Metadata
    user--->|Initiate CRUD actions|db
    user_db_class--->|Annotate attributes that should be indexed for searches|db_index
    user--->user_db_class
    user_db_class--->|Inherits from|identifiable

GitHub Repo Overview

Contents

nc_db/            # Python source: Db, handlers, queries, metadata classes, etc.
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)

Run Tests

cd nc_db poetry run pytest

Publish

python -m build
python -m twine upload dist/*