Getting Started with QueryKit

Installation

Add the database driver you need:

gem install sqlite3      # For SQLite
gem install pg           # For PostgreSQL
gem install mysql2       # For MySQL

Install the gem.


gem install querykit

Configuration

Configure once at application startup:

require 'querykit'

# Block style
QueryKit.configure do |config|
  config.adapter = :sqlite
  config.connection_options = { database: 'db/app.db' }
end

# Shorthand
QueryKit.setup(:sqlite, database: 'db/app.db')

# Use anywhere
db = QueryKit.connection

Environment-Specific

case ENV['RACK_ENV']
when 'development'
  QueryKit.setup(:sqlite, database: 'db/development.db')
when 'test'
  QueryKit.setup(:sqlite, database: ':memory:')
when 'production'
  QueryKit.setup(:postgresql,
    host: ENV['DB_HOST'],
    dbname: ENV['DB_NAME'],
    user: ENV['DB_USER'],
    password: ENV['DB_PASSWORD']
  )
end

Direct Connection

For scripts or multiple databases:

db = QueryKit.connect(:sqlite, 'database.db')
db = QueryKit.connect(:postgresql, host: 'localhost', dbname: 'mydb', user: 'postgres', password: 'pass')
db = QueryKit.connect(:mysql, host: 'localhost', database: 'mydb', username: 'root', password: 'pass')

Basic Usage

SELECT

db = QueryKit.connection

# Build query
query = db.query('users')
  .select('id', 'name', 'email')
  .where('age', '>', 18)
  .order_by('name')
  .limit(10)

# Execute
users = db.get(query)           # Returns array of hashes
user = db.first(query)          # Returns first result or nil

INSERT

query = db.insert('users').values(
  name: 'John Doe',
  email: 'john@example.com',
  age: 30
)

id = db.execute_insert(query)   # Returns last_insert_id

UPDATE

query = db.update('users')
  .set(status: 'active')
  .where('id', 1)

affected = db.execute_update(query)  # Returns affected_rows

DELETE

query = db.delete('users')
  .where('status', 'banned')

affected = db.execute_delete(query)  # Returns affected_rows

Raw SQL

results = db.raw('SELECT * FROM users WHERE age > ?', 18)
db.raw('UPDATE users SET views = views + 1 WHERE id = ?', 5)

Transactions

db.transaction do
  db.execute_insert(...)
  db.execute_update(...)
  # Commits on success, rolls back on error
end

Example

See examples/demo.rb for a complete demonstration of all features.

Documentation