baza

A database abstraction layer for Ruby. Also supports JRuby.

Installation

Is fairly painless.

gem install baza

Or in your Gemfile:

gem 'baza'

Connection to a database.

MySQL

db = Baza::Db.new(type: :mysql2, host: "localhost", user: "my_user", pass: "my_password", port: 3306, db: "my_database")

PostgreSQL

db = Baza::Db.new(type: :pg, host: "localhost", user: "my_user", pass: "my_password", db: "my_database")

SQLite3

db = Baza::Db.new(type: :sqlite3, path: "/path/to/file.sqlite3")

Queries

Select

db.select(:users, {name: "Kasper"}, {orderby: "age"}) do |row|
  puts "Row: #{row}"
end

name = "Kasper"
db.q("SELECT * FROM users WHERE name = '#{db.esc(name)}' ORDER BY age") do |row|
  puts "Row: #{row}"
end

Inserting

db.insert(:users, {name: "Kasper", age: 27})
id = db.last_id

It can also return the ID at the same time

id = db.insert(:users, {name: "Kasper", age: 27}, return_id: true)

Inserting multiple rows in one query is also fairly painless:

db.insert_multi(:users, [
  {name: "Kasper", age: 27},
  {name: "Christina", age: 25},
  {name: "Charlotte", age: 23}
])

Update

db.update(:users, {name: "Kasper Johansen"}, {name: "Kasper"})

Delete

db.delete(:users, name: "Kasper")

Upsert

The following example handels a row that will be inserted with {name: “Kasper”, age: 27} if it doesnt exist or rows with {name: “Kasper”} will have their their age updated to 27.

db.upsert(:users, {name: "Kasper"}, {age: 27})

Structure

Database creation

db.databases.create(name: "test-db")

Database renaming

database = db.databases["test-db"]
database.name = "new-name"
database.save!

Listing databases

db.databases.list.each do |database|
  puts "Database: #{database.name}"
end

Dropping a database

database = db.databases["test-db"]
database.drop

Listing tables on non-used-database

database = db.database["test-db"]
database.tables.each do |table|
  puts "TableName: #{table.name}"
  puts "Columns: #{table.columns.map(&:name)}"
end

Table creation

db.tables.create(:users, {
  columns: [
    {name: :id, type: :int, autoincr: true, primarykey: true},
    {name: :name, type: :varchar}
  ],
  indexes: [
    :name
  ]
})

Table dropping

table = db.tables[:users]
table.drop

Table listing

array_of_tables = db.tables.list

Or you can use blocks:

db.tables.list do |table|
  puts "Table-name: #{table.name}"
end

Table renaming

table = db.tables[:users]
table.rename(:new_table_name)

Table optimizing

table.optimize

Table rows counting

table.rows_count

Column listing

table = db.tables["users"]
cols = table.columns

Or a specific column:

column = table.column(:id)
puts "Column: #{column.name} #{column.type}(#{column.maxlength})"
puts "Default: #{column.default}"

Column altering

column.change(name: "newname", type: :varchar, default: "")

Drop column

column.drop

Get an index by name

index = table.index("index_name")

Rename index

index.rename("new name")

Dropping an index

index.drop

Getting various data from an index

puts "Unique: #{index.unique?}"
puts "Primary: #{index.primary?}"
puts "Autoincr: #{index.autoincr?}"
puts "Table: #{index.table}"

Copying databases

db_mysql = Baza::Db.new(type: :mysql, ...)
db_mysql2 = Baza::Db.new(type: :mysql2, ...)
db_sqlite = Baza::Db.new(type: :sqlite3, path: ...)

db_mysql.copy_to(db_sqlite)

Dumping SQL to an IO

db = Baza::Db.new(...)
dump = Baza::Dump.new(db: db)
str_io = StringIO.new
dump.dump(str_io)

Transactions

db.transaction do
  1000.times do
    db.insert(:users, name: "Kasper")
  end
end

Users

Listing users

db.users.list do |user|
  puts "User found: #{user.name}"
end
root_user = db.users.find_by_name("root")
root_user.name #=> "root"

Dropping users

user.drop

Creating users

user = db.users.create(name: "myuser", host: "localhost")

Query Buffer

In order to speed things up, but without using transactions directly, you can use a query buffer. This stores various instructions in memory and flushes them every now and then through transactions or intelligent queries (like multi-insertion). The drawback is that it will not be possible to test the queries for errors before a flush is executed and it wont be possible to read results from any of the queries.

It is fairly simple do:

db.q_buffer do |buffer|
  100_000.times do |count|
    buffer.insert(:table_name, name: "Kasper #{count}")

    buffer.query("UPDATE table SET ...")
    buffer.query("DELETE FROM table WHERE ...")
  end
end

Contributing to baza

Copyright © 2013 Kasper Johansen. See LICENSE.txt for further details.