Db2Query
A Rails 7 (Ruby v3.1.0) plugin for connecting Db2 with Rails appplication by using ODBC connection. Db2Query execute plain SQL instead of using query builder
Note: Tested at Rails 7.1.2 on Ruby v3.1.0
1. Installation
Add this line to your application's Gemfile:
gem 'db2_query'
And then execute:
$ bundle
...
Or install it yourself as:
$ gem install db2_query
...
2. Initialization
Execute db2query:init task at the app root to create database configurations and initializer file.
$ rake db2query:init
create config/db2query.yml
create config/initializers/db2query.rb
Complete the configurations by editing the files according to your application requirement.
Database Configuration
File config/db2query.yml consist of DSN/database name and connection pool config:
development:
dsn: LIBDEV
idle: 5
pool: 5
timeout: 5
test:
dsn: LIBTEST
idle: 5
pool: 5
timeout: 5
production:
dsn: LIBPROD
idle: 5
pool: 5
timeout: 5
Key idle is a client idle maximum limit value (in minutes) to avoid the client being disconnected by the host server. Setting this value to zero will lead to an "ODBC driver Communication Link Failure. Comm rc 10054 . CWBCO1047" error after your application idle in a certain period of time.
Ensure that unixodbc has been installed and test your connection first by using isql commands.
Initializer File
This file is used by Db2Query::Base to load field types configurations and establish a connection instance.
# app_root/config/initializers/db2query.rb
require "db2_query"
Db2Query::Base.initiation do |base|
base.set_field_types # or base.set_field_types(CUSTOM_FIELD_TYPES) if you have CUSTOM TYPES
base.establish_connection
end
Custom Field Type
FieldTypes are classes that are used by Db2Query to format the data before sending it to the database by using serialize method and deserialize the returned query result data by converting the query result before consumed by your Rails application. Both serialize and deserialize operations are only applied when you provide QueryDefinitions on your query.
By default, there are ten field types that can be used in your query definitions :
DEFAULT_FIELD_TYPES = {
binary: Db2Query::Type::Binary,
boolean: Db2Query::Type::Boolean,
string: Db2Query::Type::String,
varchar: Db2Query::Type::String,
longvarchar: Db2Query::Type::String,
decimal: Db2Query::Type::Decimal,
integer: Db2Query::Type::Integer,
date: Db2Query::Type::Date,
time: Db2Query::Type::Time,
timestamp: Db2Query::Type::Timestamp
}
You can use your own Field type class by extending Db2Query::Type::Value class. For example:
class CustomTypeClass < Db2Query::Type::Value
# Method to convert data from ruby type value into data that is understood by Db2
def serialize(value)
# Your logic
end
# Method to convert Db2 database output data type that is recognized by your rails app
def deserialize(value)
# Your logic
end
end
Then put the classes into a field types hash constant and load it into the Db2Query::Base by using set_field_types method in the initializer file.
# app_root/config/initializers/db2query.rb
require "db2_query"
CUSTOM_FIELD_TYPES = {
binary: CustomBinaryTypeClass
integer: CustomIntegerTypeClass
string: CustomStringTypeClass
...
}
Db2Query::Base.initiation do |base|
base.set_field_types(CUSTOM_FIELD_TYPES)
base.establish_connection
end
3. Usage
Once you completely do the Installation & Initialization steps, basically you has been ready to use Db2Query::Base. There are three additional rules that help Db2Query run properly: SQL Convention, Field Type Convention, and Argument Key Convention.
SQL Convention:
A colon : is used as the prefix of all column names of provided Parameterized Query SQL string. It is used in determining query arguments key and value binding process. We have to provide it manually in the SQL string of each Parameterized Query. Here, Parameterized Query is used to minimize SQL injection risks.
# SQL Convention Examples
# Example of Parameterized Query SQL usage
Db2Query::Base.query("SELECT * FROM USERS WHERE email = :email", "[email protected]")
# Example of Normal SQL usage
Db2Query::Base.query("SELECT * FROM USERS WHERE email = '[email protected]'")
Field Type Convention:
Query definition's field_name written in query_definition block must be in downcased format.
# Field Type Convention Example
module Definitions
class UsersQueryDefinitions < Db2Query::Definitions
def describe
query_definition :all do |c|
c.id :integer
c.first_name :varchar
c.last_name :varchar
c.email :varchar
end
end
end
end
Argument Key Convention:
The letter case of a Named Argument key that passed into a query, has to follow its parameter letter case format that is written in the SQL. The argument key is case-sensitive. If the parameter in your SQL is written in downcase format, then your argument key has to be in downcase format too, and vice versa.
# Argument Key Convention Example
class MyQuery < Db2Query::Base
...
query :find_by, <<-SQL
SELECT * FROM USERS WHERE id = :id
SQL
...
end
MyQuery.find_user_by_id id: 10000
3.1 Basic Usage
Base Class Query Methods
#query(sql, args)
A raw query to perform a connection.run(sql, args) operation and returns an array of hashes representing each row record being executed.
Db2Query::Base.query("SELECT * FROM USERS WHERE id < :id", 10003)
=> [{:id=>10000, :first_name=>"Taisha", :last_name=>"Kutch", :email=>"[email protected]"}, {:id=>10001, :first_name=>"Setsuko", :last_name=>"Kutch", :email=>"[email protected]"}, {:id=>10002, :first_name=>"Trina", :last_name=>"Mayer", :email=>"[email protected]"}]
#query_rows(sql)
Execute the SELECT Statement SQL and returns collections of arrays consisting of row values.
Db2Query::Base.query_rows("SELECT * FROM USERS WHERE id < 10003")
=> [[10000, "Taisha", "Kutch", "[email protected]"], [10001, "Setsuko", "Kutch", "[email protected]"], [10002, "Trina", "Mayer", "[email protected]"]]
#query_value(sql)
Execute the SELECT Statement SQL and returns the first value of the query results first row.
Db2Query::Base.query_value("SELECT * FROM USERS WHERE id < 10003")
=> 10000
#query_values(sql)
Execute the SELECT Statement SQL and returns a collection of the first value of each query result rows.
Db2Query::Base.query_values("SELECT * FROM USERS WHERE id < 10003")
=> [10000, 10001, 10002]
#execute(sql, args)
A method to execute DUI Statement SQL by using connection.do(sql, args)
Db2Query::Base.execute("DELETE FROM users WHERE id = :id", 10000)
=> -1
3.2 QueryDefinitions
3.2.1 Query Field Definitions
QueryDefinitions is helpful when you need formatter methods that serialize the data before it being sent to the database and deserialize database output data before being consumed by Rails application. The real examples are Binary and Boolean field types.
At Db2Query::Type::Binary, the data unpacked by serialize method before sending to the database and do deserialize operation to pack the database returned data.
QueryDefinition can be used as Query Schema where the field types of a query are outlined. The field-type written in QueryDefinition has to follow the Field Type Convention.
A QueryDefinitions reside in app_root/app/queries/definitions directory. It is automatically created when you create your query by running rails g query query_name generator command. The QueryDefinitions class can be defined as follow:
# app_root/app/queries/definitions/your_query_definitions.rb
module Definitions
class YourQueryDefinitions < Db2Query::Definitions
def describe # method that is used by Db2Query to describe your query definition
query_definition :your_first_query_name do |c|
c.field_name :field_type, options
...
end
query_definition :your_next_query_name do |c|
c.field_name :field_type, options
...
end
end
end
end
For Example:
# app_root/app/queries/definitions/users_query_definitions.rb
module Definitions
class UsersQueryDefinitions < Db2Query::Definitions
def describe
query_definition :all do |c|
c.id :integer
c.first_name :varchar
c.last_name :varchar
c.email :varchar
end
query_definition :insert do |c|
c.id :integer
c.first_name :varchar
c.last_name :varchar
c.email :varchar
end
end
end
end
3.2.2 Query Argument Types
Sometimes, the query arguments do not exist in query definitions fields. In such a case, a Db2Query::QueryArgumentError will be raised. So, we have to provide query argument types at the Query class.
module NameSpace
class QueryName < Db2Query::Base
query_arguments :user_by_email, { email: :string, trim: true }
def user_by_email_sql
"SELECT id, first_name, last_name FROM USERS WHERE email = :email"
end
end
end
3.3 Generator
Create query class by using rails g query NAME commands. For example:
$ rails g query NameSpace::Name --defines=first_query --queries=next_query --lambdas=last_query
create app/queries/name_space/name_query.rb
create app/queries/definitions/name_space/name_query_definitions.rb
create test/queries/name_space/name_query_test.rb
This will create app/queries/name_space/name_query.rb file in app/queries directory.
module NameSpace
class Name < Db2Query::Base
def first_query_sql
end
query :next_query, <<-SQL
SQL
query :last_query, -> {
}
end
end
# app_root/app/queries/definitions/name_space/name_query.rb
module Definitions
module NameSpace
class NameQueryDefinition < Db2Query::Definitions
def describe # method that is used by Db2Query to describe your query definition
query_definition :first_query do |c|
end
query_definition :next_query do |c|
end
query_definition :last_query do |c|
end
end
end
end
end
Please run rails g query --help to get more information on how to use the file generator.
3.4 Queries Methods
In a Query class that extends Db2Query::Base class, there are 3 ways of query implementation:
class MyQuery < Db2Query::Base
# 1. Plain Query (--defines)
def query_name_sql
"YOUR AMAZING SQL STATEMENT STRING"
end
# 2. String Query (--queries)
query :query_name, <<-SQL
YOUR AMAZING SQL STATEMENT
SQL
# 3. Lambda Query (--lambdas)
query :query_name, -> args {
# implement fetch, fetch_list, and exec_query
fetch("YOUR AMAZING SQL", args)
}
end
3.4.1 Plain Query (--defines)
Query implementation that uses the plain method. The method name must have a _sql suffix and return SQL statement string.
Example:
class MyQuery < Db2Query::Base
def all_users_sql
"SELECT * FROM USERS"
end
def find_user_by_id_sql
"SELECT * FROM USERS WHERE id = :id"
end
end
3.4.2 String Query (--queries)
Query implementation that uses the built-in query method. The input arguments consist of query_name symbol and SQL statement
Example:
class MyQuery < Db2Query::Base
query :all_users, <<-SQL
SELECT * FROM USERS
SQL
query :find_user_by_id, <<-SQL
SELECT * FROM USERS WHERE id = :id
SQL
end
3.4.3 Lambda Query (--lambdas)
Query implementation that uses the built-in query method. The input arguments consist of the query_name symbol and a lambda function. We have to pass args as the arguments of a lambda function. Do not change the args with let's say -> id, email { ... }. Just leave it written as args. The args is used by Db2Query::Base to store query_name and the other arg inputs.
Example:
class MyQuery < Db2Query::Base
query :all_users, -> args {
fetch("SELECT * FROM USERS", args)
}
query :find_user_by_id, -> args {
fetch("SELECT * FROM USERS WHERE id = :id", args)
}
end
Then you can call all three example with the same methods:
irb(main):001:0> MyQuery.all_users
SQL (2.7ms) SELECT * FROM USERS
=> #<Db2Query::Result [#<Record id: 10000, first_name: Yohanes, ...]>
irb(main):001:0> MyQuery.find_user_by_id 10000
SQL (3.0ms) SELECT * FROM USERS WHERE id = ? [["id", 10000]]
=> #<Db2Query::Result [#<Record id: 10004, first_name: Yohanes, ...]>
If you pass a key-value argument into query, the key has to follow Argument Key Convention
irb(main):001:0> MyQuery.find_user_by_id(id: 10000)
SQL (3.0ms) SELECT * FROM USERS WHERE id = ? [["id", 10000]]
=> #<Db2Query::Result [#<Record id: 10004, first_name: Yohanes, ...]>
And use it at your application
users = MyQuery.all
user_records = users.records
user_1 = user_records.first
user_1.id # => 10000
user_1.first_name # => "Yohanes"
user_1.last_name # => "Lumentut"
user_1.email # => "[email protected]"
user_1 == users.record # => true
user = MyQuery.find_user_by_id id: 10000
user.id # => 10000
user.first_name # => "Yohanes"
user.last_name # => "Lumentut"
user.email # => "[email protected]"
3.5 SQL extension (@extension)
For the sake of reusable SQL string, we can reuse the most commonly used SQL part by implementing sql_with_extension methods with an SQL string argument contain @extension pointer at SQL statement.
class MyQuery < Db2Query::Base
# reusable SQL
_SQL = -> extension {
sql_with_extension("SELECT * FROM USERS WHERE @extension", extension)
}
# implementation
query :user_by_email, _SQL.("email = :email")
end
irb(main):001:0> MyQuery.user_by_email email: "[email protected]"
SQL (2.7ms) SELECT * FROM USERS email = ? [["email", "[email protected]"]]
=> #<Db2Query::Result [#<Record id: 10000, first_name: Yohanes, ...]>
user = MyQuery.user_by_email "[email protected]"
user.id # => 10000
user.first_name # => "Yohanes"
user.last_name # => "Lumentut"
user.email # => "[email protected]"
3.6 List input (@list)
For an array consist list of inputs, we can use fetch_list method and @list pointer at the SQL statement.
class MyQuery < Db2Query::Base
query :user_by_ids, -> args {
fetch_list("SELECT * FROM USERS WHERE ID IN (@list)", args)
}
end
irb(main):007:0> MyQuery.user_by_ids [10000,10001,10002]
SQL (2.8ms) SELECT * FROM USERS WHERE ID IN ('10000', '10001', '10002')
=> #<Db2Query::Result [#<Record id: 10000, name: "Carol", last_name: "Danvers", email: "[email protected]">, #<Record id: 10001, first_name: "Natasha", last_name: "Romanova", email: "[email protected]">, #<Record id: 10002, first_name: "Wanda", last_name: "Maximoff", email: "[email protected]">]>
users = MyQuery.user_by_ids [10000,10001,10002]
user = users.first
user == users.record # => true
user.id # => 10000
user.first_name # => "Carol"
user.last_name # => "Danvers"
user.email # => "[email protected]"
3.7 Formatter
For the latest version of Db2Query, there is no more Db2Query::Formatter class. We can implement our formater into deserialize method of our QueryDefinitions.
If you upgrade from the previous version, you have to run rake db2query:init again to override the initializer. Please create a backup of your Formatter classes before you do this operation. Then you can implement your Formatter methods into your QueryDefinitions.
4. Available Result Object methods
Db2Query::Result inherit all ActiveRecord::Result methods with additional custom methods:
recordsto convert query result into an array of Result query's Record objects.recordto get the first Record Object of Result query.to_hto convert query result into an array of hashes with symbolized keys.
5. ActiveRecord Combination
Create an abstract class that inherits from ActiveRecord::Base. We have to implement splat operator correctly at the arguments to make it works.
class Db2Record < ActiveRecord::Base
self.abstract_class = true
def self.query(sql, args)
Db2Query::Base.query(sql, *args)
end
end
Utilize the goodness of rails model scope
class User < Db2Record
scope :by_name, -> *args {
query("SELECT * FROM USERS WHERE first_name = :first_name AND last_name = :last_name", args)
}
end
User.by_name first_name: "Strange", last_name: "Stephen"
SQL Load (3.28ms) SELECT * FROM USERS WHERE first_name = ? AND last_name = ? [["first_name", Strange], ["last_name", Stephen]]
=> [{:id=> 10000, :first_name=> "Strange", :last_name=> "Stephen", :email=> "[email protected]"}]
Another example:
class User < Db2Record
scope :age_gt, -> *args {
query("SELECT * FROM USERS WHERE age > ?", args)
}
end
User.age_gt 500
SQL Load (3.28ms) SELECT * FROM USERS WHERE age > 500
=> [{:id=> 99999, :first_name=> "Ancient", :last_name=> "One", :email=> "[email protected]"}]
6. Examples
For complete examples please see the basic examples here. Please see Db2Session for REST and GraphQL implementation of multi-user on the remote server.
7. Test
To run a development test:
$ bin/test test
Db2 Version : DB2 v11.5.8.0
...
8. License
The gem is available as open-source under the terms of the MIT License.