Database Module#

The Database module handles all PostgreSQL database interactions, including connection management, table setup, and query execution.

Note: The Database module now uses PostgreSQL's default socket authentication for local connections, which doesn't require username and password settings on most development setups.

Database Connection#

Functions for creating, managing, and using database connections:

`GeoIDs.DB.get_connection`

source

get_connection() -> Union{LibPQ.Connection, Nothing}

Returns a connection to the database specified by environment variables. By default connects to "tiger" database on localhost:5432. Uses the default socket authentication method.

Environment variables:

GEOIDSDBNAME: Database name (defaults to "tiger")
GEOIDSDBHOST: Database host (defaults to "localhost")
GEOIDSDBPORT: Database port (defaults to "5432")

Returns a LibPQ.Connection object or throws an informative error.

`GeoIDs.DB.with_connection`

source

with_connection(f::Function) -> Any

Execute function f with a database connection, ensuring the connection is closed after use. Provides graceful error handling for database operations.

Arguments

f::Function: Function that takes a connection as its argument and performs database operations

Returns

Returns the result of the function f

Example

with_connection() do conn
    execute(conn, "SELECT * FROM my_table")
end

`GeoIDs.DB.get_connection_string`

source

get_connection_string()

Build a PostgreSQL connection string from environment variables or defaults. Uses default socket authentication when no username/password provided.

Environment Variables

GEOIDS_DB_NAME: Database name (default: "tiger")
GEOIDS_DB_HOST: Database host (default: "localhost")
GEOIDS_DB_PORT: Database port (default: 5432)

`GeoIDs.DB.get_db_name`

source

get_db_name() -> String

Get the database name from the GEOIDSDBNAME environment variable, falling back to "tiger" if not set.

`GeoIDs.DB.get_db_params`

source

get_db_params() -> Dict{String, String}

Get database connection parameters from environment variables with fallbacks:

GEOIDSDBNAME: Database name (defaults to "tiger")
GEOIDSDBHOST: Database host (defaults to "localhost")
GEOIDSDBPORT: Database port (defaults to "5432")

Connection String Format#

The connection string is now formatted as:

postgresql://host:port/dbname

For example:

postgresql://localhost:5432/tiger

This format uses PostgreSQL's default authentication mechanism, which is socket authentication on most development setups.

Query Execution#

Functions for executing SQL queries and commands:

`GeoIDs.DB.execute`

source

execute(conn::LibPQ.Connection, query::String, params::Vector=[]) -> LibPQ.Result

Execute a SQL query with parameters and return the result.

`GeoIDs.DB.execute_query`

source

execute_query(query::String, params::Vector=[]) -> DataFrame

Execute a query with parameters and return the result as a DataFrame. Ensures proper type handling and provides informative error messages.

Arguments

query::String: SQL query to execute
params::Vector: Parameters to substitute into the query (optional)

Returns

DataFrame: Results of the query as a DataFrame
Empty DataFrame with appropriate column names on empty result

Throws

ErrorException: If the query fails to execute with an informative message

Schema Management#

Functions for setting up and managing the database schema:

`GeoIDs.DB.setup_tables`

source

setup_tables()

Create the necessary database tables for storing GEOID sets with versioning.