module DB
Overview
The DB module is a unified interface for database access. Individual database systems are supported by specific database driver shards.
Available drivers include:
- crystal-lang/crystal-sqlite3 for SQLite
- crystal-lang/crystal-mysql for MySQL and MariaDB
- will/crystal-pg for PostgreSQL
- kaukas/crystal-cassandra for Cassandra
For basic instructions on implementing a new database driver, check Driver
and the existing drivers.
DB manages a connection pool. The connection pool can be configured by query parameters to the
connection URI
as described in Database
.
Usage
Assuming crystal-sqlite3
is included a SQLite3 database can be opened with #open
.
db = DB.open "sqlite3:./path/to/db/file.db"
db.close
If a block is given to #open
the database is closed automatically
DB.open "sqlite3:./file.db" do |db|
# work with db
end # db is closed
In the code above db
is a Database
. Methods available for querying it are described in QueryMethods
.
Three kind of statements can be performed:
Database#exec
waits no response from the database.Database#scalar
reads a single value of the response.Database#query
returns a ResultSet that allows iteration over the rows in the response and column information.
All of the above methods allows parametrised query. Either positional or named arguments.
Check a full working version:
The following example uses SQLite where ?
indicates the arguments. If PostgreSQL is used $1
, $2
, etc. should be used. crystal-db
does not interpret the statements.
require "db"
require "sqlite3"
DB.open "sqlite3:./file.db" do |db|
# When using the pg driver, use $1, $2, etc. instead of ?
db.exec "create table contacts (name text, age integer)"
db.exec "insert into contacts values (?, ?)", "John Doe", 30
args = [] of DB::Any
args << "Sarah"
args << 33
db.exec "insert into contacts values (?, ?)", args: args
puts "max age:"
puts db.scalar "select max(age) from contacts" # => 33
puts "contacts:"
db.query "select name, age from contacts order by age desc" do |rs|
puts "#{rs.column_name(0)} (#{rs.column_name(1)})"
# => name (age)
rs.each do
puts "#{rs.read(String)} (#{rs.read(Int32)})"
# => Sarah (33)
# => John Doe (30)
end
end
end
Defined in:
lib/db/src/db.crlib/db/src/db/begin_transaction.cr
lib/db/src/db/connection.cr
lib/db/src/db/connection_builder.cr
lib/db/src/db/connection_context.cr
lib/db/src/db/database.cr
lib/db/src/db/disposable.cr
lib/db/src/db/driver.cr
lib/db/src/db/enumerable_concat.cr
lib/db/src/db/error.cr
lib/db/src/db/mapping.cr
lib/db/src/db/pool.cr
lib/db/src/db/pool_prepared_statement.cr
lib/db/src/db/pool_statement.cr
lib/db/src/db/pool_unprepared_statement.cr
lib/db/src/db/query_methods.cr
lib/db/src/db/result_set.cr
lib/db/src/db/serializable.cr
lib/db/src/db/session_methods.cr
lib/db/src/db/statement.cr
lib/db/src/db/string_key_cache.cr
lib/db/src/db/transaction.cr
ext/db/connection.cr
ext/db/transaction.cr