Core Database Functions
=======================

.. verified:: 2025-11-25
   :reviewer: Christof Buchbender

This page documents the core database connection and initialization API. These are the
entry points for working with ops-db programmatically.

Configuration
-------------

Database connection parameters are managed via `Dynaconf
<https://www.dynaconf.com/docs/>`_ settings. The configuration file is located at
`ccat_ops_db/config/settings.toml
<https://github.com/ccatobs/ops-db/blob/main/ccat_ops_db/config/settings.toml>`_.

Default settings::

   [default]
   database_type = "sqlite"
   database_sqlite_database = "/tmp/dev_ccat_ops_db.sqlite"

   database_postgresql_database = "ccat_ops_db"
   database_postgresql_user = "ccat"
   database_postgresql_password = "pw4ccat"
   database_postgresql_host = "localhost"
   database_postgresql_port = "5432"

Environment-specific settings (e.g., ``[production]``, ``[development]``) override
defaults.

Environment variables with prefix ``CCAT_OPS_DB_`` can override any setting::

   export CCAT_OPS_DB_DATABASE_TYPE=postgresql
   export CCAT_OPS_DB_DATABASE_POSTGRESQL_HOST=db.prod.example.com

The active environment is set via ``ENV_FOR_DYNACONF`` environment variable::

   export ENV_FOR_DYNACONF=production


Database Initialization
-----------------------

This function is a helper function for development and staging purposes. It  fills the
database with the starting seed that we used for production it will generate a
representative starting state with defined obs_units, sources, instruments, and the
like. A seeded database can be immediately used to simulate observing and data transfer
as well as data reduction and archive transfer in our development and staging
environments.

.. autofunction:: ccat_ops_db.init_ccat_ops_db

Usage Example::

   from ccat_ops_db import init_ccat_ops_db

   # Initialize with default settings (from environment)
   session, engine = init_ccat_ops_db()

   # Initialize with specific database
   session, engine = init_ccat_ops_db(
       database_type="postgresql",
       host="db.example.com",
       port="5432",
       user="myuser",
       password="mypass",
       db_name="ops_db"
   )

   # Initialize and drop existing tables (for testing)
   session, engine = init_ccat_ops_db(drop=True)

   # Use NullPool for short-lived connections
   session, engine = init_ccat_ops_db(null_pool=True)

Database URL Construction
-------------------------

.. autofunction:: ccat_ops_db.get_database_url

Usage Example::

   from ccat_ops_db import get_database_url

   # PostgreSQL URL
   url = get_database_url(
       database_type="postgresql",
       host="localhost",
       port="5432",
       user="ccat",
       password="secret",
       db_name="ccat_ops_db"
   )
   # Returns: postgresql://ccat:secret@localhost:5432/ccat_ops_db

   # Async PostgreSQL URL
   url = get_database_url(
       database_type="postgresql",
       host="localhost",
       db_name="ccat_ops_db",
       async_driver=True
   )
   # Returns: postgresql+asyncpg://...

   # SQLite URL
   url = get_database_url(
       database_type="sqlite",
       database="/tmp/test.db"
   )
   # Returns: sqlite:////tmp/test.db


Connection Pooling
------------------

By default, SQLAlchemy uses connection pooling. For short-lived scripts or testing, you
may want to disable pooling::

   session, engine = init_ccat_ops_db(null_pool=True)

This uses ``NullPool``, which creates a new connection for each operation and closes it
immediately.

For long-running applications (like the API server), use the default pooling behavior.

Working with the Session
------------------------

The returned session is a SQLAlchemy ``scoped_session``::

   from ccat_ops_db import init_ccat_ops_db
   from ccat_ops_db.models import Observatory

   session, engine = init_ccat_ops_db()

   # Query
   obs = session.query(Observatory).filter_by(name="CCAT").first()

   # Add
   new_obs = Observatory(name="Test", description="Test observatory")
   session.add(new_obs)
   session.commit()

   # Close when done
   session.close()

For context manager usage::

   from contextlib import contextmanager

   @contextmanager
   def get_db_session():
       session, engine = init_ccat_ops_db()
       try:
           yield session
           session.commit()
       except:
           session.rollback()
           raise
       finally:
           session.close()

   with get_db_session() as session:
       # Your database operations
       pass

Related Documentation
---------------------

* Complete models API: :doc:`models`
* Utilities: :doc:`utilities`
* Database concepts: :doc:`../concepts/overview`