Skip to content

R Database Integration Best Practices

Objective: Master senior-level R database integration patterns for production systems. When you need to connect R to databases, when you want to ensure data integrity and performance, when you need enterprise-grade database patterns—these best practices become your weapon of choice.

Core Principles

  • Connection Management: Efficiently manage database connections
  • Data Integrity: Ensure data consistency and reliability
  • Performance: Optimize database queries and operations
  • Security: Implement security best practices
  • Scalability: Design for horizontal and vertical scaling

Database Connections

Connection Pooling

# R/01-database-connections.R

#' Create database connection pool
#'
#' @param db_config Database configuration
#' @return Database connection pool
create_database_connection_pool <- function(db_config) {
  pool <- list(
    config = create_pool_config(db_config),
    connections = create_pool_connections(db_config),
    management = create_pool_management(db_config)
  )

  return(pool)
}

#' Create pool configuration
#'
#' @param db_config Database configuration
#' @return Pool configuration
create_pool_config <- function(db_config) {
  pool_config <- list(
    min_size = db_config$min_size %||% 5,
    max_size = db_config$max_size %||% 20,
    idle_timeout = db_config$idle_timeout %||% 3600,
    validation_query = db_config$validation_query %||% "SELECT 1"
  )

  return(pool_config)
}

#' Create pool connections
#'
#' @param db_config Database configuration
#' @return Pool connections
create_pool_connections <- function(db_config) {
  connections <- list(
    postgresql = create_postgresql_connections(db_config),
    mysql = create_mysql_connections(db_config),
    sqlite = create_sqlite_connections(db_config),
    oracle = create_oracle_connections(db_config)
  )

  return(connections)
}

#' Create PostgreSQL connections
#'
#' @param db_config Database configuration
#' @return PostgreSQL connections
create_postgresql_connections <- function(db_config) {
  postgresql_connections <- c(
    "# PostgreSQL Connections",
    "library(DBI)",
    "library(pool)",
    "library(RPostgreSQL)",
    "",
    "# Create connection pool",
    "pool <- dbPool(",
    "  drv = RPostgreSQL::PostgreSQL(),",
    "  dbname = \"rdatabase\",",
    "  host = \"localhost\",",
    "  port = 5432,",
    "  user = \"ruser\",",
    "  password = \"rpassword\",",
    "  minSize = 5,",
    "  maxSize = 20,",
    "  idleTimeout = 3600",
    ")",
    "",
    "# Get connection from pool",
    "conn <- poolCheckout(pool)",
    "",
    "# Use connection",
    "result <- dbGetQuery(conn, \"SELECT * FROM users\")",
    "",
    "# Return connection to pool",
    "poolReturn(conn)"
  )

  return(postgresql_connections)
}

#' Create MySQL connections
#'
#' @param db_config Database configuration
#' @return MySQL connections
create_mysql_connections <- function(db_config) {
  mysql_connections <- c(
    "# MySQL Connections",
    "library(DBI)",
    "library(pool)",
    "library(RMySQL)",
    "",
    "# Create connection pool",
    "pool <- dbPool(",
    "  drv = RMySQL::MySQL(),",
    "  dbname = \"rdatabase\",",
    "  host = \"localhost\",",
    "  port = 3306,",
    "  user = \"ruser\",",
    "  password = \"rpassword\",",
    "  minSize = 5,",
    "  maxSize = 20,",
    "  idleTimeout = 3600",
    ")",
    "",
    "# Get connection from pool",
    "conn <- poolCheckout(pool)",
    "",
    "# Use connection",
    "result <- dbGetQuery(conn, \"SELECT * FROM users\")",
    "",
    "# Return connection to pool",
    "poolReturn(conn)"
  )

  return(mysql_connections)
}

#' Create SQLite connections
#'
#' @param db_config Database configuration
#' @return SQLite connections
create_sqlite_connections <- function(db_config) {
  sqlite_connections <- c(
    "# SQLite Connections",
    "library(DBI)",
    "library(RSQLite)",
    "",
    "# Create connection",
    "conn <- dbConnect(RSQLite::SQLite(), \"rdatabase.db\")",
    "",
    "# Use connection",
    "result <- dbGetQuery(conn, \"SELECT * FROM users\")",
    "",
    "# Close connection",
    "dbDisconnect(conn)"
  )

  return(sqlite_connections)
}

#' Create Oracle connections
#'
#' @param db_config Database configuration
#' @return Oracle connections
create_oracle_connections <- function(db_config) {
  oracle_connections <- c(
    "# Oracle Connections",
    "library(DBI)",
    "library(ROracle)",
    "",
    "# Create connection",
    "conn <- dbConnect(",
    "  ROracle::Oracle(),",
    "  username = \"ruser\",",
    "  password = \"rpassword\",",
    "  dbname = \"rdatabase\"",
    ")",
    "",
    "# Use connection",
    "result <- dbGetQuery(conn, \"SELECT * FROM users\")",
    "",
    "# Close connection",
    "dbDisconnect(conn)"
  )

  return(oracle_connections)
}

Connection Management

# R/01-database-connections.R (continued)

#' Create connection management
#'
#' @param db_config Database configuration
#' @return Connection management
create_connection_management <- function(db_config) {
  management <- list(
    lifecycle = create_connection_lifecycle(db_config),
    monitoring = create_connection_monitoring(db_config),
    error_handling = create_connection_error_handling(db_config)
  )

  return(management)
}

#' Create connection lifecycle
#'
#' @param db_config Database configuration
#' @return Connection lifecycle
create_connection_lifecycle <- function(db_config) {
  lifecycle <- c(
    "# Connection Lifecycle",
    "library(DBI)",
    "library(pool)",
    "",
    "# Create connection pool",
    "pool <- dbPool(",
    "  drv = RPostgreSQL::PostgreSQL(),",
    "  dbname = \"rdatabase\",",
    "  host = \"localhost\",",
    "  port = 5432,",
    "  user = \"ruser\",",
    "  password = \"rpassword\",",
    "  minSize = 5,",
    "  maxSize = 20,",
    "  idleTimeout = 3600",
    ")",
    "",
    "# Get connection from pool",
    "conn <- poolCheckout(pool)",
    "",
    "# Use connection",
    "result <- dbGetQuery(conn, \"SELECT * FROM users\")",
    "",
    "# Return connection to pool",
    "poolReturn(conn)",
    "",
    "# Close pool when done",
    "poolClose(pool)"
  )

  return(lifecycle)
}

#' Create connection monitoring
#'
#' @param db_config Database configuration
#' @return Connection monitoring
create_connection_monitoring <- function(db_config) {
  monitoring <- c(
    "# Connection Monitoring",
    "library(DBI)",
    "library(pool)",
    "",
    "# Create connection pool with monitoring",
    "pool <- dbPool(",
    "  drv = RPostgreSQL::PostgreSQL(),",
    "  dbname = \"rdatabase\",",
    "  host = \"localhost\",",
    "  port = 5432,",
    "  user = \"ruser\",",
    "  password = \"rpassword\",",
    "  minSize = 5,",
    "  maxSize = 20,",
    "  idleTimeout = 3600",
    ")",
    "",
    "# Monitor pool status",
    "pool_status <- function() {",
    "  list(",
    "    total_connections = pool$total_connections,",
    "    active_connections = pool$active_connections,",
    "    idle_connections = pool$idle_connections",
    "  )",
    "}",
    "",
    "# Get pool status",
    "status <- pool_status()",
    "print(status)"
  )

  return(monitoring)
}

#' Create connection error handling
#'
#' @param db_config Database configuration
#' @return Connection error handling
create_connection_error_handling <- function(db_config) {
  error_handling <- c(
    "# Connection Error Handling",
    "library(DBI)",
    "library(pool)",
    "",
    "# Create connection pool with error handling",
    "pool <- dbPool(",
    "  drv = RPostgreSQL::PostgreSQL(),",
    "  dbname = \"rdatabase\",",
    "  host = \"localhost\",",
    "  port = 5432,",
    "  user = \"ruser\",",
    "  password = \"rpassword\",",
    "  minSize = 5,",
    "  maxSize = 20,",
    "  idleTimeout = 3600",
    ")",
    "",
    "# Safe database operation",
    "safe_db_operation <- function(operation) {",
    "  tryCatch({",
    "    conn <- poolCheckout(pool)",
    "    result <- operation(conn)",
    "    poolReturn(conn)",
    "    return(result)",
    "  }, error = function(e) {",
    "    if (exists(\"conn\")) poolReturn(conn)",
    "    stop(paste(\"Database operation failed:\", e$message))",
    "  })",
    "}",
    "",
    "# Use safe operation",
    "result <- safe_db_operation(function(conn) {",
    "  dbGetQuery(conn, \"SELECT * FROM users\")",
    "})"
  )

  return(error_handling)
}

Query Optimization

Query Performance

# R/02-query-optimization.R

#' Create query optimization
#'
#' @param query_config Query configuration
#' @return Query optimization
create_query_optimization <- function(query_config) {
  optimization <- list(
    indexing = create_indexing_strategies(query_config),
    query_planning = create_query_planning(query_config),
    caching = create_query_caching(query_config)
  )

  return(optimization)
}

#' Create indexing strategies
#'
#' @param query_config Query configuration
#' @return Indexing strategies
create_indexing_strategies <- function(query_config) {
  indexing <- c(
    "# Indexing Strategies",
    "library(DBI)",
    "library(pool)",
    "",
    "# Create indexes for common queries",
    "create_indexes <- function(conn) {",
    "  # Create index on user_id",
    "  dbExecute(conn, \"CREATE INDEX IF NOT EXISTS idx_users_id ON users(id)\")",
    "  ",
    "  # Create index on email",
    "  dbExecute(conn, \"CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)\")",
    "  ",
    "  # Create composite index",
    "  dbExecute(conn, \"CREATE INDEX IF NOT EXISTS idx_users_status_created ON users(status, created_at)\")",
    "  ",
    "  # Create partial index",
    "  dbExecute(conn, \"CREATE INDEX IF NOT EXISTS idx_users_active ON users(id) WHERE status = 'active'\")",
    "}",
    "",
    "# Create indexes",
    "conn <- poolCheckout(pool)",
    "create_indexes(conn)",
    "poolReturn(conn)"
  )

  return(indexing)
}

#' Create query planning
#'
#' @param query_config Query configuration
#' @return Query planning
create_query_planning <- function(query_config) {
  query_planning <- c(
    "# Query Planning",
    "library(DBI)",
    "library(pool)",
    "",
    "# Analyze query performance",
    "analyze_query <- function(conn, query) {",
    "  # Get query plan",
    "  plan <- dbGetQuery(conn, paste(\"EXPLAIN ANALYZE\", query))",
    "  ",
    "  # Print plan",
    "  print(plan)",
    "  ",
    "  # Return plan",
    "  return(plan)",
    "}",
    "",
    "# Analyze query",
    "conn <- poolCheckout(pool)",
    "plan <- analyze_query(conn, \"SELECT * FROM users WHERE status = 'active'\")",
    "poolReturn(conn)"
  )

  return(query_planning)
}

#' Create query caching
#'
#' @param query_config Query configuration
#' @return Query caching
create_query_caching <- function(query_config) {
  query_caching <- c(
    "# Query Caching",
    "library(DBI)",
    "library(pool)",
    "library(memoise)",
    "",
    "# Create cached query function",
    "cached_query <- memoise(function(conn, query) {",
    "  dbGetQuery(conn, query)",
    "}, cache = cache_memory())",
    "",
    "# Use cached query",
    "conn <- poolCheckout(pool)",
    "result <- cached_query(conn, \"SELECT * FROM users\")",
    "poolReturn(conn)"
  )

  return(query_caching)
}

Data Validation

# R/02-query-optimization.R (continued)

#' Create data validation
#'
#' @param validation_config Validation configuration
#' @return Data validation
create_data_validation <- function(validation_config) {
  validation <- list(
    schema_validation = create_schema_validation(validation_config),
    data_quality = create_data_quality_checks(validation_config),
    constraints = create_data_constraints(validation_config)
  )

  return(validation)
}

#' Create schema validation
#'
#' @param validation_config Validation configuration
#' @return Schema validation
create_schema_validation <- function(validation_config) {
  schema_validation <- c(
    "# Schema Validation",
    "library(DBI)",
    "library(pool)",
    "",
    "# Validate table schema",
    "validate_schema <- function(conn, table_name, expected_schema) {",
    "  # Get actual schema",
    "  actual_schema <- dbGetQuery(conn, paste(\"SELECT * FROM information_schema.columns WHERE table_name = '\", table_name, \"'\"))",
    "  ",
    "  # Compare schemas",
    "  if (nrow(actual_schema) != nrow(expected_schema)) {",
    "    stop(\"Schema mismatch: different number of columns\")",
    "  }",
    "  ",
    "  # Check column types",
    "  for (i in 1:nrow(expected_schema)) {",
    "    expected_col <- expected_schema[i, ]",
    "    actual_col <- actual_schema[actual_schema$column_name == expected_col$column_name, ]",
    "    ",
    "    if (nrow(actual_col) == 0) {",
    "      stop(paste(\"Column not found:\", expected_col$column_name))",
    "    }",
    "    ",
    "    if (actual_col$data_type != expected_col$data_type) {",
    "      stop(paste(\"Column type mismatch for\", expected_col$column_name, \": expected\", expected_col$data_type, \"got\", actual_col$data_type))",
    "    }",
    "  }",
    "  ",
    "  return(TRUE)",
    "}",
    "",
    "# Validate schema",
    "conn <- poolCheckout(pool)",
    "validate_schema(conn, \"users\", expected_schema)",
    "poolReturn(conn)"
  )

  return(schema_validation)
}

#' Create data quality checks
#'
#' @param validation_config Validation configuration
#' @return Data quality checks
create_data_quality_checks <- function(validation_config) {
  data_quality <- c(
    "# Data Quality Checks",
    "library(DBI)",
    "library(pool)",
    "",
    "# Check data quality",
    "check_data_quality <- function(conn, table_name) {",
    "  # Check for null values",
    "  null_counts <- dbGetQuery(conn, paste(\"SELECT COUNT(*) as null_count FROM\", table_name, \"WHERE column_name IS NULL\"))",
    "  ",
    "  # Check for duplicates",
    "  duplicate_counts <- dbGetQuery(conn, paste(\"SELECT COUNT(*) as duplicate_count FROM (SELECT column_name, COUNT(*) FROM\", table_name, \"GROUP BY column_name HAVING COUNT(*) > 1) as duplicates\"))",
    "  ",
    "  # Check for outliers",
    "  outliers <- dbGetQuery(conn, paste(\"SELECT * FROM\", table_name, \"WHERE column_name > (SELECT AVG(column_name) + 3 * STDDEV(column_name) FROM\", table_name, \")\"))",
    "  ",
    "  # Return quality report",
    "  list(",
    "    null_counts = null_counts,",
    "    duplicate_counts = duplicate_counts,",
    "    outliers = outliers",
    "  )",
    "}",
    "",
    "# Check data quality",
    "conn <- poolCheckout(pool)",
    "quality_report <- check_data_quality(conn, \"users\")",
    "poolReturn(conn)"
  )

  return(data_quality)
}

#' Create data constraints
#'
#' @param validation_config Validation configuration
#' @return Data constraints
create_data_constraints <- function(validation_config) {
  data_constraints <- c(
    "# Data Constraints",
    "library(DBI)",
    "library(pool)",
    "",
    "# Create data constraints",
    "create_constraints <- function(conn) {",
    "  # Create check constraint",
    "  dbExecute(conn, \"ALTER TABLE users ADD CONSTRAINT check_status CHECK (status IN ('active', 'inactive', 'pending'))\")",
    "  ",
    "  # Create unique constraint",
    "  dbExecute(conn, \"ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email)\")",
    "  ",
    "  # Create foreign key constraint",
    "  dbExecute(conn, \"ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id)\")",
    "  ",
    "  # Create not null constraint",
    "  dbExecute(conn, \"ALTER TABLE users ALTER COLUMN email SET NOT NULL\")",
    "}",
    "",
    "# Create constraints",
    "conn <- poolCheckout(pool)",
    "create_constraints(conn)",
    "poolReturn(conn)"
  )

  return(data_constraints)
}

Data Pipelines

ETL Processes

# R/03-data-pipelines.R

#' Create ETL processes
#'
#' @param etl_config ETL configuration
#' @return ETL processes
create_etl_processes <- function(etl_config) {
  etl <- list(
    extraction = create_data_extraction(etl_config),
    transformation = create_data_transformation(etl_config),
    loading = create_data_loading(etl_config)
  )

  return(etl)
}

#' Create data extraction
#'
#' @param etl_config ETL configuration
#' @return Data extraction
create_data_extraction <- function(etl_config) {
  extraction <- c(
    "# Data Extraction",
    "library(DBI)",
    "library(pool)",
    "library(dplyr)",
    "",
    "# Extract data from source",
    "extract_data <- function(conn, query) {",
    "  # Get data from database",
    "  data <- dbGetQuery(conn, query)",
    "  ",
    "  # Convert to tibble",
    "  data <- as_tibble(data)",
    "  ",
    "  # Add metadata",
    "  attr(data, \"extracted_at\") <- Sys.time()",
    "  attr(data, \"source\") <- \"database\"",
    "  ",
    "  return(data)",
    "}",
    "",
    "# Extract data",
    "conn <- poolCheckout(pool)",
    "data <- extract_data(conn, \"SELECT * FROM users\")",
    "poolReturn(conn)"
  )

  return(extraction)
}

#' Create data transformation
#'
#' @param etl_config ETL configuration
#' @return Data transformation
create_data_transformation <- function(etl_config) {
  transformation <- c(
    "# Data Transformation",
    "library(DBI)",
    "library(pool)",
    "library(dplyr)",
    "library(lubridate)",
    "",
    "# Transform data",
    "transform_data <- function(data) {",
    "  # Clean data",
    "  data <- data %>%",
    "    filter(!is.na(email)) %>%",
    "    mutate(email = tolower(email)) %>%",
    "    mutate(created_at = as.Date(created_at))",
    "  ",
    "  # Add derived columns",
    "  data <- data %>%",
    "    mutate(age = year(Sys.Date()) - year(birth_date)) %>%",
    "    mutate(is_active = status == 'active')",
    "  ",
    "  # Add metadata",
    "  attr(data, \"transformed_at\") <- Sys.time()",
    "  ",
    "  return(data)",
    "}",
    "",
    "# Transform data",
    "transformed_data <- transform_data(data)"
  )

  return(transformation)
}

#' Create data loading
#'
#' @param etl_config ETL configuration
#' @return Data loading
create_data_loading <- function(etl_config) {
  loading <- c(
    "# Data Loading",
    "library(DBI)",
    "library(pool)",
    "",
    "# Load data to destination",
    "load_data <- function(conn, data, table_name) {",
    "  # Create table if not exists",
    "  if (!dbExistsTable(conn, table_name)) {",
    "    dbCreateTable(conn, table_name, data)",
    "  }",
    "  ",
    "  # Insert data",
    "  dbWriteTable(conn, table_name, data, append = TRUE)",
    "  ",
    "  # Add metadata",
    "  attr(data, \"loaded_at\") <- Sys.time()",
    "  ",
    "  return(TRUE)",
    "}",
    "",
    "# Load data",
    "conn <- poolCheckout(pool)",
    "load_data(conn, transformed_data, \"users_processed\")",
    "poolReturn(conn)"
  )

  return(loading)
}

Data Synchronization

# R/03-data-pipelines.R (continued)

#' Create data synchronization
#'
#' @param sync_config Sync configuration
#' @return Data synchronization
create_data_synchronization <- function(sync_config) {
  sync <- list(
    incremental_sync = create_incremental_sync(sync_config),
    full_sync = create_full_sync(sync_config),
    conflict_resolution = create_conflict_resolution(sync_config)
  )

  return(sync)
}

#' Create incremental sync
#'
#' @param sync_config Sync configuration
#' @return Incremental sync
create_incremental_sync <- function(sync_config) {
  incremental_sync <- c(
    "# Incremental Sync",
    "library(DBI)",
    "library(pool)",
    "library(dplyr)",
    "",
    "# Incremental sync",
    "incremental_sync <- function(source_conn, target_conn, table_name, last_sync) {",
    "  # Get changes since last sync",
    "  changes <- dbGetQuery(source_conn, paste(\"SELECT * FROM\", table_name, \"WHERE updated_at > '\", last_sync, \"'\"))",
    "  ",
    "  # Apply changes to target",
    "  for (i in 1:nrow(changes)) {",
    "    row <- changes[i, ]",
    "    ",
    "    # Check if record exists",
    "    exists <- dbGetQuery(target_conn, paste(\"SELECT COUNT(*) FROM\", table_name, \"WHERE id = \", row$id))",
    "    ",
    "    if (exists > 0) {",
    "      # Update existing record",
    "      dbExecute(target_conn, paste(\"UPDATE\", table_name, \"SET column_name = '\", row$column_name, \"' WHERE id = \", row$id))",
    "    } else {",
    "      # Insert new record",
    "      dbExecute(target_conn, paste(\"INSERT INTO\", table_name, \"(id, column_name) VALUES (\", row$id, \", '\", row$column_name, \"')\"))",
    "    }",
    "  }",
    "  ",
    "  return(TRUE)",
    "}",
    "",
    "# Incremental sync",
    "source_conn <- poolCheckout(source_pool)",
    "target_conn <- poolCheckout(target_pool)",
    "incremental_sync(source_conn, target_conn, \"users\", last_sync)",
    "poolReturn(source_conn)",
    "poolReturn(target_conn)"
  )

  return(incremental_sync)
}

#' Create full sync
#'
#' @param sync_config Sync configuration
#' @return Full sync
create_full_sync <- function(sync_config) {
  full_sync <- c(
    "# Full Sync",
    "library(DBI)",
    "library(pool)",
    "",
    "# Full sync",
    "full_sync <- function(source_conn, target_conn, table_name) {",
    "  # Get all data from source",
    "  data <- dbGetQuery(source_conn, paste(\"SELECT * FROM\", table_name))",
    "  ",
    "  # Clear target table",
    "  dbExecute(target_conn, paste(\"TRUNCATE TABLE\", table_name))",
    "  ",
    "  # Insert all data",
    "  dbWriteTable(target_conn, table_name, data, append = TRUE)",
    "  ",
    "  return(TRUE)",
    "}",
    "",
    "# Full sync",
    "source_conn <- poolCheckout(source_pool)",
    "target_conn <- poolCheckout(target_pool)",
    "full_sync(source_conn, target_conn, \"users\")",
    "poolReturn(source_conn)",
    "poolReturn(target_conn)"
  )

  return(full_sync)
}

#' Create conflict resolution
#'
#' @param sync_config Sync configuration
#' @return Conflict resolution
create_conflict_resolution <- function(sync_config) {
  conflict_resolution <- c(
    "# Conflict Resolution",
    "library(DBI)",
    "library(pool)",
    "",
    "# Resolve conflicts",
    "resolve_conflicts <- function(source_conn, target_conn, table_name) {",
    "  # Get conflicts",
    "  conflicts <- dbGetQuery(source_conn, paste(\"SELECT * FROM\", table_name, \"WHERE updated_at > (SELECT updated_at FROM\", table_name, \"WHERE id = \", table_name, \".id)\"))",
    "  ",
    "  # Resolve conflicts using last-write-wins",
    "  for (i in 1:nrow(conflicts)) {",
    "    row <- conflicts[i, ]",
    "    ",
    "    # Update target with source data",
    "    dbExecute(target_conn, paste(\"UPDATE\", table_name, \"SET column_name = '\", row$column_name, \"' WHERE id = \", row$id))",
    "  }",
    "  ",
    "  return(TRUE)",
    "}",
    "",
    "# Resolve conflicts",
    "source_conn <- poolCheckout(source_pool)",
    "target_conn <- poolCheckout(target_pool)",
    "resolve_conflicts(source_conn, target_conn, \"users\")",
    "poolReturn(source_conn)",
    "poolReturn(target_conn)"
  )

  return(conflict_resolution)
}

Security and Authentication

Database Security

# R/04-security-authentication.R

#' Create database security
#'
#' @param security_config Security configuration
#' @return Database security
create_database_security <- function(security_config) {
  security <- list(
    authentication = create_authentication(security_config),
    authorization = create_authorization(security_config),
    encryption = create_encryption(security_config)
  )

  return(security)
}

#' Create authentication
#'
#' @param security_config Security configuration
#' @return Authentication
create_authentication <- function(security_config) {
  authentication <- c(
    "# Database Authentication",
    "library(DBI)",
    "library(pool)",
    "library(httr)",
    "",
    "# Authenticate user",
    "authenticate_user <- function(username, password) {",
    "  # Validate credentials",
    "  if (validate_credentials(username, password)) {",
    "    # Generate JWT token",
    "    token <- generate_jwt_token(username)",
    "    ",
    "    # Return token",
    "    return(token)",
    "  } else {",
    "    stop(\"Invalid credentials\")",
    "  }",
    "}",
    "",
    "# Validate credentials",
    "validate_credentials <- function(username, password) {",
    "  # Check against database",
    "  conn <- poolCheckout(pool)",
    "  result <- dbGetQuery(conn, paste(\"SELECT COUNT(*) FROM users WHERE username = '\", username, \"' AND password = '\", password, \"'\"))",
    "  poolReturn(conn)",
    "  ",
    "  return(result > 0)",
    "}",
    "",
    "# Generate JWT token",
    "generate_jwt_token <- function(username) {",
    "  # Create JWT payload",
    "  payload <- list(",
    "    username = username,",
    "    exp = as.numeric(Sys.time()) + 3600",
    "  )",
    "  ",
    "  # Sign token",
    "  token <- jwt_sign(payload, secret_key)",
    "  ",
    "  return(token)",
    "}"
  )

  return(authentication)
}

#' Create authorization
#'
#' @param security_config Security configuration
#' @return Authorization
create_authorization <- function(security_config) {
  authorization <- c(
    "# Database Authorization",
    "library(DBI)",
    "library(pool)",
    "",
    "# Check user permissions",
    "check_permissions <- function(username, resource, action) {",
    "  # Get user permissions",
    "  conn <- poolCheckout(pool)",
    "  permissions <- dbGetQuery(conn, paste(\"SELECT * FROM user_permissions WHERE username = '\", username, \"' AND resource = '\", resource, \"' AND action = '\", action, \"'\"))",
    "  poolReturn(conn)",
    "  ",
    "  return(nrow(permissions) > 0)",
    "}",
    "",
    "# Authorize database operation",
    "authorize_operation <- function(username, operation) {",
    "  # Check if user can perform operation",
    "  if (check_permissions(username, \"database\", operation)) {",
    "    return(TRUE)",
    "  } else {",
    "    stop(\"Unauthorized operation\")",
    "  }",
    "}",
    "",
    "# Use authorization",
    "username <- \"user1\"",
    "if (authorize_operation(username, \"read\")) {",
    "  # Perform read operation",
    "  result <- dbGetQuery(conn, \"SELECT * FROM users\")",
    "}"
  )

  return(authorization)
}

#' Create encryption
#'
#' @param security_config Security configuration
#' @return Encryption
create_encryption <- function(security_config) {
  encryption <- c(
    "# Database Encryption",
    "library(DBI)",
    "library(pool)",
    "library(openssl)",
    "",
    "# Encrypt sensitive data",
    "encrypt_data <- function(data, key) {",
    "  # Encrypt data",
    "  encrypted <- aes_encrypt(data, key)",
    "  ",
    "  return(encrypted)",
    "}",
    "",
    "# Decrypt sensitive data",
    "decrypt_data <- function(encrypted_data, key) {",
    "  # Decrypt data",
    "  decrypted <- aes_decrypt(encrypted_data, key)",
    "  ",
    "  return(decrypted)",
    "}",
    "",
    "# Store encrypted data",
    "store_encrypted_data <- function(conn, table_name, data, key) {",
    "  # Encrypt sensitive columns",
    "  data$password <- encrypt_data(data$password, key)",
    "  data$ssn <- encrypt_data(data$ssn, key)",
    "  ",
    "  # Store in database",
    "  dbWriteTable(conn, table_name, data, append = TRUE)",
    "  ",
    "  return(TRUE)",
    "}",
    "",
    "# Retrieve and decrypt data",
    "retrieve_encrypted_data <- function(conn, table_name, key) {",
    "  # Get data from database",
    "  data <- dbGetQuery(conn, paste(\"SELECT * FROM\", table_name))",
    "  ",
    "  # Decrypt sensitive columns",
    "  data$password <- decrypt_data(data$password, key)",
    "  data$ssn <- decrypt_data(data$ssn, key)",
    "  ",
    "  return(data)",
    "}"
  )

  return(encryption)
}

TL;DR Runbook

Quick Start

# 1. Create database connection pool
pool <- create_database_connection_pool(db_config)

# 2. Create query optimization
optimization <- create_query_optimization(query_config)

# 3. Create data validation
validation <- create_data_validation(validation_config)

# 4. Create ETL processes
etl <- create_etl_processes(etl_config)

# 5. Create data synchronization
sync <- create_data_synchronization(sync_config)

# 6. Create database security
security <- create_database_security(security_config)

Essential Patterns

# Complete database integration
create_database_integration <- function(integration_config) {
  # Create connection pool
  pool <- create_database_connection_pool(integration_config$db_config)

  # Create query optimization
  optimization <- create_query_optimization(integration_config$query_config)

  # Create data validation
  validation <- create_data_validation(integration_config$validation_config)

  # Create ETL processes
  etl <- create_etl_processes(integration_config$etl_config)

  # Create data synchronization
  sync <- create_data_synchronization(integration_config$sync_config)

  # Create database security
  security <- create_database_security(integration_config$security_config)

  return(list(
    pool = pool,
    optimization = optimization,
    validation = validation,
    etl = etl,
    sync = sync,
    security = security
  ))
}

This guide provides the complete machinery for implementing database integration for R applications. Each pattern includes implementation examples, optimization strategies, and real-world usage patterns for enterprise database systems.