a simple SQLite key/val store in Ruby

motivation:

  • I just like having a place to store blobs of data on my local machine

environment/requirements:

  • Mac os x 10.5.8
  • ruby 1.8.6 (2008-08-11 patchlevel 287) [universal-darwin9.0]
  • SQLite3
  • sqlite3-ruby (1.2.1) gem

code:

require 'rubygems'
require 'sqlite3'
class Storage
  def self.init
    db = SQLite3::Database.new( 'sqlite' )
    db.execute( %{
      CREATE TABLE foo 
      (key varchar(100) PRIMARY KEY, 
      value varchar(1000), 
      modified timestamp(20))
    } )
  end
  def self.get(key)
    db = SQLite3::Database.new( 'sqlite' )
    db.get_first_row( %{
      SELECT * FROM foo WHERE key='#{key}'
    } )
  end
  def self.set(key, val)
    db = SQLite3::Database.new( 'sqlite' )
    result = db.execute( %{
      REPLACE INTO foo
      (key, value, modified) 
      VALUES ('%s', '%s', %d)
    } % [key, val, Time.now.to_i] )
  end
end

usage:

require 'rubygems'
require 'sqlite3'
require 'json'
...
# init table once (anyone have a table detection query?)
Storage.init
...
# put data in
value = {:foo => 'bar'}
Storage.set( '123', value.to_json )
...
# get data out
row = Storage.get( '123' )
...

5 thoughts on “a simple SQLite key/val store in Ruby”

  1. Pingback: updated Ruby utility for simple SQLite3 key/val storage « Erik Eldridge

  2. This seems to be just what I need – but how do you recover the value after the row has been selected? If instead I use a “SELECT value” command and then try to JSON.parse it, I get a “can’t convert Array into String” error . .

    1. Hi Philip.

      Please have a look at my follow-up post titled “updated Ruby utility for simple SQLite3 key/val storage”:
      https://erikeldridge.wordpress.com/2010/02/20/updated-ruby-utility-for-sqlite3/

      If you want to use the code in this post, however, note that it selects all the fields from a given row, so it returns an array of elements. You can see this by printing the “row” variable:
      puts row –> [“123”, “{\”foo\”:\”bar\”}”, 1300429278]

      You would need to parse the second element in the array, which corresponds to the “value” field:
      data = JSON.parse(row[1])

      Good luck!

  3. Erik,

    I was just about to post that I had worked that even when you select for value rather than “*” – an array is still returned so I found the array element trick. I will have a look at your updated post though.

    Thanks!

    Phil.

Comments are closed.