NSJ Multi-Database Library

Common errors

• KeyError: 'my_unregistered_db_label'

  cause Attempting to get a session for a database label that was not configured during `ConnectionPool.configure()`.
  fix Verify that the database label passed to `DatabaseManager.get_session()` exactly matches one of the keys in the `DATABASE_CONFIGS` dictionary used for `ConnectionPool.configure()`.

• AttributeError: 'NoneType' object has no attribute 'get_session'

  cause `ConnectionPool.configure()` was not called before `DatabaseManager.get_session()`, meaning the database manager was not properly initialized.
  fix Ensure `ConnectionPool.configure(DATABASE_CONFIGS)` is executed at the start of your application, typically once, before any database session is requested.

• sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: password authentication failed for user "wrong_user"

  cause Incorrect database connection string, credentials, host, or port specified in the `DATABASE_CONFIGS`.
  fix Double-check the `connection_string` in your `DATABASE_CONFIGS` for typos, correct usernames, passwords, hostnames, and port numbers. Test the connection string directly with `sqlalchemy.create_engine()` if necessary.

Warnings

• breaking The `ConnectionPool.configure()` method signature changed significantly in version 2.0.0. It now expects a dictionary where keys are database labels (strings) and values are configuration dictionaries, instead of a list of `DatabaseConnectionConfigDTO` objects.
  Fix: Update your `ConnectionPool.configure()` call to pass a dictionary of configurations. For example, `ConnectionPool.configure({'my_db': {'engine': 'pg', 'connection_string': '...'}})`.
• gotcha This library explicitly depends on `SQLAlchemy` versions less than 2.0.0 (e.g., `SQLAlchemy>=1.4.0,<2.0.0`). Attempting to use `SQLAlchemy 2.x` will lead to dependency conflicts or runtime errors, as its API has breaking changes.
  Fix: Ensure your project's `requirements.txt` or `pyproject.toml` pins `SQLAlchemy` to a `1.x` version (e.g., `SQLAlchemy~=1.4.0`). Do not install `SQLAlchemy 2.x` if you are using `nsj-multi-database-lib`.
• gotcha Failure to call `ConnectionPool.configure()` before attempting to retrieve a session via `DatabaseManager.get_session()` will result in an `AttributeError` or `TypeError`.
  Fix: Always initialize the connection pool at application startup using `ConnectionPool.configure(your_configs_dict)` before any database operations.

Install

• pip install nsj-multi-database-lib Install latest version

Imports

• ConnectionPool

  from nsj_multi_database_lib.connection.connection_pool import ConnectionPool

• DatabaseManager

  from nsj_multi_database_lib.connection.database_manager import DatabaseManager

• DaoBase
  wrong:

  from nsj_multi_database_lib.dao_base import DaoBase

  correct:

  from nsj_multi_database_lib.dao.dao_base import DaoBase

  DaoBase is within the 'dao' submodule, not directly under the root package.
• TransactionalDao

  from nsj_multi_database_lib.dao.transactional_dao import TransactionalDao

Quickstart

This quickstart demonstrates how to configure multiple in-memory SQLite databases using `nsj-multi-database-lib`. It shows how to initialize the `ConnectionPool` with a dictionary of database configurations, obtain sessions for different databases using `DatabaseManager.get_session()`, and perform basic SQL operations within a transactional context. Remember to replace the SQLite in-memory setup with your actual database connection strings and credentials.

import os
from nsj_multi_database_lib.connection.connection_pool import ConnectionPool
from nsj_multi_database_lib.connection.database_manager import DatabaseManager
from sqlalchemy import text

# Mock Database setup for example (using SQLite in-memory)
DB_NAME_DEFAULT = "default_db"
DB_NAME_AUDIT = "audit_db"

# NOTE: In a real application, connection_string would use actual DB credentials.
# Example for PostgreSQL: "postgresql://user:password@host:port/database"
DATABASE_CONFIGS = {
    DB_NAME_DEFAULT: {
        "engine": "sqlite",
        "db": ":memory:",
        "connection_string": "sqlite:///:memory:",
        "schema": None,
        "label": DB_NAME_DEFAULT
    },
    DB_NAME_AUDIT: {
        "engine": "sqlite",
        "db": ":memory:",
        "connection_string": "sqlite:///:memory:",
        "schema": None,
        "label": DB_NAME_AUDIT
    }
}

# 1. Initialize the connection pool with your database configurations
ConnectionPool.configure(DATABASE_CONFIGS)

try:
    print("\n--- Using Default Database ---")
    # 2. Get a database session using the context manager
    with DatabaseManager.get_session(DB_NAME_DEFAULT) as session:
        # Example operations with SQLAlchemy 1.x compatible syntax
        session.execute(text("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"))
        session.execute(text("INSERT INTO users (id, name) VALUES (1, 'Alice')"))
        session.execute(text("INSERT INTO users (id, name) VALUES (2, 'Bob')"))
        session.commit()
        result = session.execute(text("SELECT * FROM users")).fetchall()
        print(f"Retrieved from {DB_NAME_DEFAULT}: {result}")

    print("\n--- Using Audit Database ---")
    with DatabaseManager.get_session(DB_NAME_AUDIT) as session:
        session.execute(text("CREATE TABLE logs (id INTEGER PRIMARY KEY, event TEXT)"))
        session.execute(text("INSERT INTO logs (id, event) VALUES (1, 'User Alice created')"))
        session.commit()
        result = session.execute(text("SELECT * FROM logs")).fetchall()
        print(f"Retrieved from {DB_NAME_AUDIT}: {result}")

except Exception as e:
    print(f"An error occurred: {e}")
finally:
    # 3. Always close connections when done to release resources
    ConnectionPool.close_all_connections()
    print("\nAll database connections closed.")