class

Ralph::Database::PostgresBackend

Inherits Ralph::Database::Backend / Reference / Object

PostgreSQL database backend implementation

Provides PostgreSQL-specific database operations for Ralph ORM. Uses the crystal-pg shard for database connectivity.

Example

# Standard connection
backend = Ralph::Database::PostgresBackend.new("postgres://user:pass@localhost:5432/mydb")

# Unix socket connection
backend = Ralph::Database::PostgresBackend.new("postgres://user@localhost/mydb?host=/var/run/postgresql")

Connection String Format

PostgreSQL connection strings follow the format: postgres://user:password@host:port/database?options

Common options:

  • host=/path/to/socket - Unix socket path
  • sslmode=require - Require SSL connection

Connection Pooling

Connection pooling is configured automatically from Ralph.settings:

Ralph.configure do |config|
  config.initial_pool_size = 5
  config.max_pool_size = 25
  config.max_idle_pool_size = 10
  config.checkout_timeout = 5.0
  config.retry_attempts = 3
  config.retry_delay = 0.2
end

Prepared Statement Caching

This backend supports prepared statement caching for improved query performance. Enable and configure via Ralph.settings:

Ralph.configure do |config|
  config.enable_prepared_statements = true
  config.prepared_statement_cache_size = 100
end

Placeholder Conversion

This backend automatically converts ? placeholders to PostgreSQL's $1, $2, ... format, so you can write queries the same way as SQLite.

INSERT Behavior

PostgreSQL uses INSERT ... RETURNING id to get the last inserted ID, which is handled automatically by the insert method.

Constructors

new(connection_string : String, apply_pool_settings : Bool = true)

Creates a new PostgreSQL backend with the given connection string

Parameters

  • connection_string: PostgreSQL connection URI
  • apply_pool_settings: Whether to apply pool settings from Ralph.settings (default: true)

Example

# Basic usage
backend = Ralph::Database::PostgresBackend.new("postgres://localhost/mydb")

# Skip pool settings (useful for CLI tools)
backend = Ralph::Database::PostgresBackend.new("postgres://localhost/mydb", apply_pool_settings: false)
Source

Instance methods

available_text_search_configs

Get all available text search configurations

Returns a list of available text search configuration names that can be used with full-text search functions like to_tsvector() and to_tsquery().

Example

backend = Ralph::Database::PostgresBackend.new(url)
configs = backend.available_text_search_configs
# => ["arabic", "danish", "dutch", "english", "finnish", "french", "german", ...]

Common Configurations

  • simple: No stemming, just lowercasing and removing stop words
  • english: English language with stemming and stop words
  • french: French language configuration
  • german: German language configuration
  • spanish: Spanish language configuration
  • russian: Russian language configuration
  • And many more...
Source
begin_transaction_sql

SQL to begin a transaction

Source
clear_statement_cache

Clear all cached prepared statements

Source
close

Close the database connection

Source
closed?

Check if the connection is open

Source
commit_sql

SQL to commit a transaction

Source
connection_string

Get the original connection string (without pool params)

Source
create_extension(name : String, if_not_exists : Bool = true)

Install a PostgreSQL extension

Example

backend.create_extension("pg_trgm")
Source
create_text_search_config(name : String, copy_from : String = "english")

Create a custom text search configuration

Creates a new text search configuration by copying from an existing one.

Example

# Create a custom config based on English
backend.create_text_search_config("my_english", copy_from: "english")
Source
dialect

Returns the dialect identifier for this backend Used by migrations and schema generation

Source
drop_extension(name : String, if_exists : Bool = true, cascade : Bool = false)

Uninstall a PostgreSQL extension

Source
drop_text_search_config(name : String, if_exists : Bool = true)

Drop a custom text search configuration

Example

backend.drop_text_search_config("my_english")
Source
enable_statement_cache=(enabled : Bool)

Enable or disable statement caching at runtime

Source
execute(query : String, args : Array(DB::Any) = [] of DB::Any)

Execute a write query (INSERT, UPDATE, DELETE, DDL) Uses prepared statement cache when enabled

Source
extension_available?(name : String) : Bool

Check if a PostgreSQL extension is available

Example

backend.extension_available?("pg_trgm") # => true
backend.extension_available?("postgis") # => false (if not installed)
Source
extension_installed?(name : String) : Bool

Check if a PostgreSQL extension is installed

Source
insert(query : String, args : Array(DB::Any) = [] of DB::Any) : Int64

Insert a record and return the inserted ID (for auto-increment PKs) For non-Int PKs (like UUID), this returns 0 but the insert still succeeds Uses RETURNING clause for PostgreSQL

Source
insert_returning_id(query : String, args : Array(DB::Any) = [] of DB::Any) : Int64

Insert a record and return the auto-generated Int64 ID Only use this for tables with serial/bigserial primary keys

Source
introspect_columns(table : String) : Array(Schema::DatabaseColumn)

Get column information for a specific table

Source
introspect_foreign_keys(table : String) : Array(Schema::DatabaseForeignKey)

Get foreign key constraints FROM a table (outgoing FKs)

Source
introspect_foreign_keys_referencing(table : String) : Array(Schema::DatabaseForeignKey)

Get foreign key constraints TO a table (incoming FKs)

Source
introspect_indexes(table : String) : Array(Schema::DatabaseIndex)

Get index information for a specific table

Source
postgres_version

Get PostgreSQL version

Returns the PostgreSQL server version as a string.

Example

backend.postgres_version
# => "15.4"
Source
query_all(query : String, args : Array(DB::Any) = [] of DB::Any) : DB::ResultSet

Query for multiple rows Uses prepared statement cache when enabled

Source
query_one(query : String, args : Array(DB::Any) = [] of DB::Any) : DB::ResultSet | Nil

Query for a single row, returns nil if no results Uses prepared statement cache when enabled

Source
raw_connection

Get the underlying DB::Database connection for advanced operations

Source
release_savepoint_sql(name : String) : String

SQL to release a savepoint

Source
rollback_sql

SQL to rollback a transaction

Source
rollback_to_savepoint_sql(name : String) : String

SQL to rollback to a savepoint

Source
savepoint_sql(name : String) : String

SQL to create a savepoint

Source
scalar(query : String, args : Array(DB::Any) = [] of DB::Any) : DB::Any | Nil

Run a scalar query and return a single value Uses prepared statement cache when enabled

Source
statement_cache_enabled?

Check if statement caching is enabled

Source
statement_cache_stats

Get statement cache statistics

Source
table_names

Get all user table names (excluding system tables)

Source
text_search_config_exists?(config_name : String) : Bool

Check if a text search configuration exists

Source
text_search_config_info(config_name : String) : Hash(String, String)

Get text search configuration details

Returns information about a specific text search configuration.

Example

backend.text_search_config_info("english")
# => {name: "english", parser: "default", dictionaries: [...]}
Source
transaction

Begin a transaction

Source