Database Configuration¶

This page documents how to configure and connect to different database backends supported by asset-db. It covers connection string formats, database type identifiers, and connection parameters for PostgreSQL, SQLite, and Neo4j.

For information about database schema initialization and migrations, see Database Migrations. For details about the repository abstraction that these configurations support, see Repository Pattern.

Supported Database Types¶

The asset-db system supports three database backend types, identified by string constants defined in the repository implementations:

Database Type Resolution¶

The following diagram shows how the assetdb.New() function resolves database types to their respective implementations:

graph TB
    NewFunc["assetdb.New(dbtype, dsn)"]
    TypeSwitch{"Database Type"}

    SQLiteMemCheck["dbtype == sqlrepo.SQLiteMemory"]
    GenerateDSN["Generate random in-memory DSN:<br/>file:memN?mode=memory&cache=shared"]

    RepoNew["repository.New(dbtype, dsn)"]
    Migrate["migrateDatabase(dbtype, dsn)"]

    SQLiteMigrate["sqlMigrate('sqlite3', ...)"]
    PostgresMigrate["sqlMigrate('postgres', ...)"]
    NeoMigrate["neoMigrate(dsn)"]

    SQLRepo["sqlRepository"]
    NeoRepo["neoRepository"]

    NewFunc --> SQLiteMemCheck
    SQLiteMemCheck -->|"Yes"| GenerateDSN
    GenerateDSN --> RepoNew
    SQLiteMemCheck -->|"No"| RepoNew

    RepoNew --> TypeSwitch
    TypeSwitch -->|"sqlrepo.Postgres<br/>sqlrepo.SQLite<br/>sqlrepo.SQLiteMemory"| SQLRepo
    TypeSwitch -->|"neo4j.Neo4j"| NeoRepo

    NewFunc --> Migrate
    Migrate --> TypeSwitch
    TypeSwitch -->|"sqlrepo.SQLite<br/>sqlrepo.SQLiteMemory"| SQLiteMigrate
    TypeSwitch -->|"sqlrepo.Postgres"| PostgresMigrate
    TypeSwitch -->|"neo4j.Neo4j"| NeoMigrate

Connection String Formats¶

PostgreSQL DSN¶

PostgreSQL uses a standard connection string format supported by the pgx driver:

host=localhost user=myuser password=mypass dbname=assetdb port=5432 sslmode=disable

Common parameters: - host - Database server hostname or IP - port - Database server port (default: 5432) - user - Database username - password - Database password - dbname - Database name - sslmode - SSL mode (disable, require, verify-ca, verify-full)

The DSN is passed directly to gorm.Open(postgres.Open(dsn), ...) for connection.

SQLite DSN¶

SQLite uses a file path as the DSN:

/path/to/database.db

Or with URI parameters:

file:/path/to/database.db?_journal_mode=WAL

The DSN is passed to gorm.Open(sqlite.Open(dsn), ...) using the glebarez/sqlite driver, which is a pure Go SQLite implementation.

SQLite In-Memory DSN¶

For SQLite in-memory databases, the DSN is automatically generated by assetdb.New() when the database type is sqlrepo.SQLiteMemory:

file:mem{random}?mode=memory&cache=shared

The random number (0-999) is appended to create unique in-memory database instances. The cache=shared parameter allows multiple connections to access the same in-memory database.

Neo4j DSN¶

Neo4j uses a URL-based connection string format:

neo4j://localhost:7687/databasename
bolt://localhost:7687/databasename
neo4j+s://localhost:7687/databasename

With authentication:

neo4j://username:password@localhost:7687/databasename

Components: - Scheme: neo4j, bolt, neo4j+s, neo4j+ssc, bolt+s, bolt+ssc - Username/Password: Optional, extracted from URL user info - Host/Port: Neo4j server location - Path: Database name (extracted and used separately)

The Neo4j implementation parses the DSN to extract authentication credentials and the database name, then reconstructs a connection URL without the path component.

Creating a Repository¶

The assetdb.New() function is the main entry point for creating a repository with a configured database:

func New(dbtype, dsn string) (repository.Repository, error)

This function: 1. Handles special DSN generation for sqlrepo.SQLiteMemory 2. Calls repository.New(dbtype, dsn) to create the repository implementation 3. Calls migrateDatabase(dbtype, dsn) to initialize/update the database schema 4. Returns a repository.Repository interface

Example usage patterns:

Connection Parameters and Pooling¶

Each database backend configures connection pooling and timeout parameters differently:

PostgreSQL Connection Parameters¶

graph LR
    subgraph PostgresConfig["postgresDatabase()"]
        GORMOpen["gorm.Open(postgres.Open(dsn), ...)"]
        GetDB["db.DB()"]
        PoolConfig["Connection Pool Configuration"]
    end

    subgraph PoolSettings["Pool Settings"]
        MaxIdle["SetMaxIdleConns(5)"]
        MaxOpen["SetMaxOpenConns(10)"]
        MaxLifetime["SetConnMaxLifetime(1 hour)"]
        MaxIdleTime["SetConnMaxIdleTime(10 minutes)"]
    end

    GORMOpen --> GetDB
    GetDB --> PoolConfig
    PoolConfig --> MaxIdle
    PoolConfig --> MaxOpen
    PoolConfig --> MaxLifetime
    PoolConfig --> MaxIdleTime

SQLite Connection Parameters¶

SQLite connection pools are configured differently based on the database mode:

Both modes share the same lifetime parameters: - ConnMaxLifetime: 1 hour - ConnMaxIdleTime: 10 minutes

The higher connection limits for in-memory databases enable better concurrent access for testing scenarios.

Neo4j Connection Parameters¶

Neo4j driver configuration is set during driver creation:

graph TB
    subgraph DriverConfig["neo4jdb.NewDriverWithContext()"]
        DSNParse["Parse DSN for auth & dbname"]
        Auth["Setup Authentication"]
        ConfigFunc["Configure driver"]
    end

    subgraph ConfigSettings["Driver Configuration"]
        MaxPool["MaxConnectionPoolSize: 20"]
        MaxLife["MaxConnectionLifetime: 1 hour"]
        Liveness["ConnectionLivenessCheckTimeout: 10 minutes"]
    end

    subgraph Verification["Connection Verification"]
        VerifyCtx["context.WithTimeout(5 seconds)"]
        VerifyConn["driver.VerifyConnectivity(ctx)"]
    end

    DSNParse --> Auth
    Auth --> ConfigFunc
    ConfigFunc --> MaxPool
    ConfigFunc --> MaxLife
    ConfigFunc --> Liveness
    ConfigFunc --> Verification
    Verification --> VerifyCtx
    VerifyCtx --> VerifyConn

Authentication modes: - No authentication: Used when DSN has no user info - Basic authentication: Username and password extracted from DSN user info

Configuration Summary¶

The following table summarizes the complete configuration landscape for all supported database backends: