基于Rack的PG网页客户端：非SQLite的连接持久化方案咨询

针对你用Ruby+Rack构建网页PG客户端的需求，这里提供几个不用SQLite的高效方案，可根据服务器架构和并发场景选择：

方案1：单进程场景 - 内存线程安全存储+Rack Session #

如果你的Rack服务器是单进程模式（比如Puma只开1个worker），直接用内存存储连接是最高效的方式，配合Rack Session绑定用户会话：

实现代码 #

require 'rack'
require 'pg'
require 'json'
require 'concurrent-ruby'

# 线程安全的全局连接存储，多线程环境下可安全读写
$connections = Concurrent::Hash.new

# 配置Rack Session，用Cookie存储会话ID（生产环境要替换为安全密钥）
use Rack::Session::Cookie, secret: 'your-strong-secret-key'

class ConnectionEndpoint
  def call(env)
    req = Rack::Request.new(env)
    return [405, {}, ['Method Not Allowed']] unless req.post?

    begin
      req_body = JSON.parse(req.body.read)
      connection_string = req_body["connection_string"].to_s
      # 建立并验证连接有效性
      conn = PG::Connection.new(connection_string)
      conn.exec('SELECT 1')

      # 将连接与当前会话绑定，存入全局存储
      session_id = req.session.id
      $connections[session_id] = conn

      [200, {'Content-Type' => 'application/json'}, ['{"status":"ok"}']]
    rescue PG::ConnectionBad, JSON::ParserError => e
      [400, {'Content-Type' => 'application/json'}, ["{\"error\":\"#{e.message}\"}"]]
    end
  end
end

class QueryEndpoint
  def call(env)
    req = Rack::Request.new(env)
    return [405, {}, ['Method Not Allowed']] unless req.post?

    session_id = req.session.id
    conn = $connections[session_id]

    unless conn
      return [401, {'Content-Type' => 'application/json'}, ['{"error":"No active connection found"}']]
    end

    begin
      req_body = JSON.parse(req.body.read)
      query = req_body["query"].to_s
      result = conn.exec(query)
      [200, {'Content-Type' => 'application/json'}, [result.to_a.to_json]]
    rescue PG::Error, JSON::ParserError => e
      [400, {'Content-Type' => 'application/json'}, ["{\"error\":\"#{e.message}\"}"]]
    end
  end
end

# 定时清理失效连接，避免内存泄漏（每小时检查一次）
Thread.new do
  loop do
    sleep 3600
    $connections.each do |session_id, conn|
      begin
        conn.exec('SELECT 1')
      rescue PG::Error
        conn.close
        $connections.delete(session_id)
      end
    end
  end
end

# 路由配置
map '/connection' do
  run ConnectionEndpoint.new
end

map '/query' do
  run QueryEndpoint.new
end

优缺点 #

• ✅ 完全无需额外存储，内存操作性能拉满
• ✅ 线程安全，适配多线程服务器（比如Puma的threads配置）
• ❌ 不支持多进程模式（多个worker进程内存不共享，会话连接无法跨进程访问）

方案2：多进程/高并发场景 - Redis+连接池 #

如果你的服务器是多进程模式，或者需要高并发支持，用Redis存储会话对应的连接字符串，配合连接池复用连接，兼顾分布式和性能：

实现代码 #

require 'rack'
require 'pg'
require 'json'
require 'redis'
require 'connection_pool'

# 初始化Redis客户端（根据你的Redis配置调整）
$redis = Redis.new(host: 'localhost', port: 6379)

# 线程安全的连接池存储，每个连接字符串对应一个连接池
$connection_pools = Concurrent::Hash.new do |h, conn_str|
  h[conn_str] = ConnectionPool.new(size: 5, timeout: 5) do
    PG::Connection.new(conn_str)
  end
end

use Rack::Session::Cookie, secret: 'your-strong-secret-key'

class ConnectionEndpoint
  def call(env)
    req = Rack::Request.new(env)
    return [405, {}, ['Method Not Allowed']] unless req.post?

    begin
      req_body = JSON.parse(req.body.read)
      connection_string = req_body["connection_string"].to_s
      # 验证连接有效性
      PG::Connection.new(connection_string).exec('SELECT 1')

      # 将连接字符串存入Redis，绑定会话ID并设置1小时过期
      session_id = req.session.id
      $redis.setex("pg_conn:#{session_id}", 3600, connection_string)

      [200, {'Content-Type' => 'application/json'}, ['{"status":"ok"}']]
    rescue PG::ConnectionBad, JSON::ParserError => e
      [400, {'Content-Type' => 'application/json'}, ["{\"error\":\"#{e.message}\"}"]]
    end
  end
end

class QueryEndpoint
  def call(env)
    req = Rack::Request.new(env)
    return [405, {}, ['Method Not Allowed']] unless req.post?

    session_id = req.session.id
    connection_string = $redis.get("pg_conn:#{session_id}")

    unless connection_string
      return [401, {'Content-Type' => 'application/json'}, ['{"error":"No active connection found"}']]
    end

    begin
      req_body = JSON.parse(req.body.read)
      query = req_body["query"].to_s

      # 从连接池获取连接执行查询，自动回收连接
      $connection_pools[connection_string].with do |conn|
        result = conn.exec(query)
        [200, {'Content-Type' => 'application/json'}, [result.to_a.to_json]]
      end
    rescue PG::Error, JSON::ParserError, ConnectionPool::TimeoutError => e
      [400, {'Content-Type' => 'application/json'}, ["{\"error\":\"#{e.message}\"}"]]
    end
  end
end

map '/connection' do
  run ConnectionEndpoint.new
end

map '/query' do
  run QueryEndpoint.new
end

优缺点 #

• ✅ 支持多进程/分布式场景，Redis跨进程共享连接信息
• ✅ 连接池复用连接，减少重复建立连接的开销
• ✅ 自动过期连接字符串，避免无效存储
• ❌ 需要额外部署Redis服务（但性能比SQLite更高）

方案3：轻量低并发场景 - 直接存储连接字符串到Session #

如果你的客户端用户量小、并发低，直接把连接字符串存在Rack Session里，每次查询时临时建立连接，实现最简单：

实现代码 #

require 'rack'
require 'pg'
require 'json'

use Rack::Session::Cookie, secret: 'your-strong-secret-key'

class ConnectionEndpoint
  def call(env)
    req = Rack::Request.new(env)
    return [405, {}, ['Method Not Allowed']] unless req.post?

    begin
      req_body = JSON.parse(req.body.read)
      connection_string = req_body["connection_string"].to_s
      # 验证连接
      PG::Connection.new(connection_string).exec('SELECT 1')

      # 直接把连接字符串存在当前会话中
      req.session[:pg_connection_string] = connection_string

      [200, {'Content-Type' => 'application/json'}, ['{"status":"ok"}']]
    rescue PG::ConnectionBad, JSON::ParserError => e
      [400, {'Content-Type' => 'application/json'}, ["{\"error\":\"#{e.message}\"}"]]
    end
  end
end

class QueryEndpoint
  def call(env)
    req = Rack::Request.new(env)
    return [405, {}, ['Method Not Allowed']] unless req.post?

    connection_string = req.session[:pg_connection_string]
    unless connection_string
      return [401, {'Content-Type' => 'application/json'}, ['{"error":"No active connection found"}']]
    end

    begin
      req_body = JSON.parse(req.body.read)
      query = req_body["query"].to_s

      # 每次查询建立新连接，用完自动关闭
      PG::Connection.new(connection_string) do |conn|
        result = conn.exec(query)
        [200, {'Content-Type' => 'application/json'}, [result.to_a.to_json]]
      end
    rescue PG::Error, JSON::ParserError => e
      [400, {'Content-Type' => 'application/json'}, ["{\"error\":\"#{e.message}\"}"]]
    end
  end
end

map '/connection' do
  run ConnectionEndpoint.new
end

map '/query' do
  run QueryEndpoint.new
end

优缺点 #

• ✅ 零额外依赖，实现最简单
• ❌ 每次查询都要建立新连接，性能开销大，不适合高并发
• ❌ 连接字符串存在Cookie Session中（默认加密，但仍需注意安全风险）

内容的提问来源于stack exchange，提问作者Dedy Puji