如何用多小型SQLite数据库存储IoT设备测量数据并通过ActiveRecord管理？

结论 #

可以通过ActiveRecord动态管理连接的方式，为主数据源（PostgreSQL）中的每个设备对象创建、查询和删除独立的SQLite数据库。

具体实现步骤 #

1. 动态创建设备专属SQLite库及数据表 #

编写方法，根据设备ID生成对应SQLite文件路径，并自动初始化数据表结构：

def setup_device_database(device)
  db_path = Rails.root.join("db", "device_measurements_#{device.id}.sqlite3").to_s
  
  # 切换到目标SQLite连接
  ActiveRecord::Base.establish_connection(
    adapter: "sqlite3",
    database: db_path
  )

  # 仅当表不存在时创建测量数据表
  unless ActiveRecord::Base.connection.table_exists?(:measurements)
    ActiveRecord::Base.connection.create_table :measurements do |t|
      t.float :value, null: false
      t.datetime :measured_at, null: false
      t.timestamps
    end
    # 为查询常用字段创建索引
    ActiveRecord::Base.connection.add_index :measurements, :measured_at
  end

  # 切回主PostgreSQL连接
  ActiveRecord::Base.establish_connection(Rails.env.to_sym)
end

2. 动态切换连接查询/写入数据 #

通过动态生成模型类的方式，针对指定设备的SQLite库进行操作：

def device_measurement_model(device)
  db_path = Rails.root.join("db", "device_measurements_#{device.id}.sqlite3").to_s
  
  # 动态定义专属模型类，确保连接指向当前设备的SQLite库
  Class.new(ActiveRecord::Base) do
    self.table_name = "measurements"
    establish_connection(
      adapter: "sqlite3",
      database: db_path
    )

    # 可添加常用查询scope
    scope :recent, ->(limit = 100) { order(measured_at: :desc).limit(limit) }
  end
end

# 使用示例
device = Device.find(params[:id])
measurement_model = device_measurement_model(device)

# 查询最近数据用于生成线性图表
chart_data = measurement_model.recent(200).pluck(:measured_at, :value)

# 写入新的测量数据
measurement_model.create!(value: 25.6, measured_at: Time.current)

3. 删除设备对应的SQLite数据库 #

直接删除文件即可，注意先断开可能的连接：

def delete_device_database(device)
  db_path = Rails.root.join("db", "device_measurements_#{device.id}.sqlite3").to_s
  
  # 断开该数据库的连接（如果存在）
  if ActiveRecord::Base.connection_config[:database] == db_path
    ActiveRecord::Base.connection_pool.disconnect!
  end

  # 删除数据库文件
  File.delete(db_path) if File.exist?(db_path)
end

关键注意事项 #

• 连接资源管理：频繁切换连接可能导致连接池资源耗尽，建议操作完成后立即切回主连接，或通过连接池限制并发数。
• 数据表结构维护：后续若需修改测量表结构，需遍历所有设备的SQLite库执行迁移脚本，可编写批量处理任务完成。
• 性能瓶颈：SQLite单文件读写在高并发写入场景下存在锁竞争问题，如果设备数据写入频率高，更推荐使用时序数据库（如InfluxDB）或PostgreSQL的按设备哈希分表方案，这类方案对IoT时序数据的处理效率更高。
• 备份策略：需单独备份每个SQLite文件，可定时将文件归档至云存储服务，避免数据丢失。

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