systemprompt-database

Database abstraction layer for systemprompt.io supporting SQLite, PostgreSQL, and MySQL.

Overview

Part of the Infra layer in the systemprompt.io architecture. Infrastructure · Self-Hosted Deployment

Provides database abstraction via SQLx with repository patterns, transaction helpers, and administrative utilities.

Installation

Add to your Cargo.toml:

[dependencies]
systemprompt-database = "0.0.1"

Quick Example

use systemprompt_database::{DbPool, Repository, with_transaction};

async fn example(pool: &DbPool) -> anyhow::Result<()> {
    with_transaction(pool, |tx| Box::pin(async move {
        // Execute queries within transaction
        Ok(())
    })).await
}

Structure

database/
├── Cargo.toml
├── module.yml
├── README.md
├── status.md
├── schema/
│   └── functions.sql
└── src/
    ├── lib.rs
    ├── error.rs
    ├── admin/
    │   ├── mod.rs
    │   ├── introspection.rs
    │   └── query_executor.rs
    ├── lifecycle/
    │   ├── mod.rs
    │   ├── installation.rs
    │   └── validation.rs
    ├── models/
    │   ├── mod.rs
    │   ├── info.rs
    │   ├── query.rs
    │   └── transaction.rs
    ├── repository/
    │   ├── mod.rs
    │   ├── base.rs
    │   ├── cleanup.rs
    │   ├── entity.rs
    │   ├── info.rs
    │   ├── macros.rs
    │   └── service.rs
    └── services/
        ├── mod.rs
        ├── database.rs
        ├── display.rs
        ├── executor.rs
        ├── provider.rs
        ├── transaction.rs
        └── postgres/
            ├── mod.rs
            ├── conversion.rs
            ├── ext.rs
            ├── introspection.rs
            └── transaction.rs

Modules

`admin/`

Administrative database utilities for introspection and query execution.

File	Purpose
`introspection.rs`	`DatabaseAdminService` for listing tables, describing columns, getting indexes
`query_executor.rs`	`QueryExecutor` for safe SQL execution with read-only mode support

`lifecycle/`

Database setup and validation.

File	Purpose
`installation.rs`	Schema and seed installation for modules and extensions
`validation.rs`	Connection and schema validation functions

`models/`

Data structures for database operations.

File	Purpose
`info.rs`	`DatabaseInfo`, `TableInfo`, `ColumnInfo`, `IndexInfo`
`query.rs`	`DatabaseQuery`, `QuerySelector`, `FromDatabaseRow`, `QueryResult`
`transaction.rs`	`DatabaseTransaction` trait

`repository/`

Repository pattern implementations.

File	Purpose
`base.rs`	`Repository` trait, `PgDbPool` type alias, `PaginatedRepository`
`cleanup.rs`	Cleanup utilities for expired data
`entity.rs`	Generic `Entity` trait and `GenericRepository<E>`
`info.rs`	`DatabaseInfoRepository` for metadata queries
`macros.rs`	`impl_repository_new!`, `define_repository!`, `impl_repository_pool!`
`service.rs`	`ServiceRepository` for service process management

`services/`

Core database services and providers.

File	Purpose
`database.rs`	`Database` wrapper, `DbPool`, `DatabaseExt`
`display.rs`	`DatabaseCliDisplay` trait for CLI output
`executor.rs`	`SqlExecutor` for SQL statement parsing and execution
`provider.rs`	`DatabaseProvider`, `DatabaseProviderExt` traits
`transaction.rs`	`with_transaction`, `with_transaction_retry` helpers

`services/postgres/`

PostgreSQL-specific implementations.

File	Purpose
`mod.rs`	`PostgresProvider` implementation
`conversion.rs`	`row_to_json`, `bind_params`, `rows_to_result`
`ext.rs`	`DatabaseProviderExt` implementation
`introspection.rs`	`get_database_info` for schema introspection
`transaction.rs`	`PostgresTransaction` implementation

Public API

Types

Type	Source	Description
`Database`	`services/database.rs`	Main database wrapper
`DbPool`	`services/database.rs`	`Arc<Database>` alias
`PgDbPool`	`repository/base.rs`	`Arc<PgPool>` alias for repositories
`RepositoryError`	`error.rs`	Unified repository error type
`PostgresProvider`	`services/postgres/mod.rs`	PostgreSQL provider
`PostgresTransaction`	`services/postgres/transaction.rs`	Transaction handle
`DatabaseQuery`	`models/query.rs`	Static query wrapper
`QueryResult`	`models/query.rs`	Query execution result
`DatabaseInfo`	`models/info.rs`	Database metadata
`TableInfo`	`models/info.rs`	Table metadata
`ColumnInfo`	`models/info.rs`	Column metadata
`BoxFuture`	`services/transaction.rs`	Boxed future type for transactions

Traits

Trait	Source	Description
`Repository`	`repository/base.rs`	Base CRUD repository trait
`PaginatedRepository`	`repository/base.rs`	Pagination extension trait
`DatabaseProvider`	`services/provider.rs`	Core database operations
`DatabaseProviderExt`	`services/provider.rs`	Typed row fetching
`DatabaseTransaction`	`models/transaction.rs`	Transaction operations
`QuerySelector`	`models/query.rs`	Query abstraction
`FromDatabaseRow`	`models/query.rs`	Row-to-type conversion
`DatabaseExt`	`services/database.rs`	Database extraction
`DatabaseCliDisplay`	`services/display.rs`	CLI output formatting

Functions

Function	Source	Description
`with_transaction`	`services/transaction.rs`	Execute closure in transaction
`with_transaction_raw`	`services/transaction.rs`	Transaction with raw PgPool
`with_transaction_retry`	`services/transaction.rs`	Transaction with automatic retry

Macros

Macro	Source	Description
`impl_repository_new!`	`repository/macros.rs`	Generate `new()` constructor for repositories
`define_repository!`	`repository/macros.rs`	Define repository struct with pool field
`impl_repository_pool!`	`repository/macros.rs`	Generate pool accessor methods

Re-exports

From systemprompt-traits: DbValue, ToDbValue, FromDbValue, JsonRow, parse_database_datetime

From systemprompt-identifiers: UserId, TaskId, SessionId, ContextId, TraceId, ArtifactId, ExecutionStepId, SkillId, ContentId, FileId, ClientId, TokenId, LogId

From sqlx: PgPool, Pool, Postgres, Transaction, Json

Dependencies

systemprompt-traits - Core traits
systemprompt-identifiers - Typed identifiers
sqlx - PostgreSQL driver
tokio - Async runtime
serde / serde_json - Serialization
chrono - Timestamps
uuid - UUID support
rust_decimal - Decimal support
anyhow - Error handling
thiserror - Error derivation
async-trait - Async traits

Usage

use systemprompt_database::{Database, DbPool, with_transaction};

async fn count_users(pool: &DbPool) -> anyhow::Result<i64> {
    with_transaction(pool, |tx| Box::pin(async move {
        let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM users")
            .fetch_one(&mut **tx)
            .await?;
        Ok(row.0)
    }))
    .await
}

License

Business Source License 1.1 - See LICENSE for details.

systemprompt-database 0.2.1