Database

    The following packages are compliant with crystal-db

    And several .

    This guide presents the api of crystal-db, the sql commands might need to be adapted for the concrete driver due to differences between postgres, mysql and sqlite.

    Also some drivers may offer additional functionality like postgres /NOTIFY.

    Choose the appropriate driver from the list above and add it as any shard to your application’s shard.yml

    There is no need to explicitly require crystal-lang/crystal-db

    Open database

    DB.open will allow you to easily connect to a database using a connection uri. The schema of the uri determines the expected driver. The following sample connects to a local mysql database named test with user root and password blank.

    1. require "db"
    2. require "mysql"
    4. DB.open "mysql://root@localhost/test" do |db|
    5.   # ... use db to perform queries

    Other connection uris are

    • sqlite3:///path/to/data.db
    • mysql://user:password@server:port/database
    • postgres://user:password@server:port/database

    Alternatively you can use a non yielding DB.open method as long as Database#close is called at the end.

    1. require "db"
    4. db = DB.open "mysql://root@localhost/test"
    5. begin
    6.   # ... use db to perform queries
    7. ensure
    8.   db.close
    9. end

    To execute sql statements you can use Database#exec

    1. db.exec "insert into contacts (name, age) values ('abc', 30)"

    Values can be provided as query parameters, see below.

    Query

    To perform a query and get the result set use Database#query.

    1. db.query "select name, age from contacts order by age desc" do |rs|
    2.     # ... perform for each row in the ResultSet
    4. end

    Values can be provided as query parameters, see below.

    To avoid SQL injection values can be provided as query parameters. The syntax for using query parameters depends on the database driver because they are typically just passed through to the database. MySQL uses ? for parameter expansion and assignment is based on argument order. PostgreSQL uses $n where n is the ordinal number of the argument (starting with 1).

    Reading Query Results

    When reading values from the database there is no type information during compile time that crystal can use. You will need to call rs.read(T) with the type T you expect to get from the database.

    1. db.query "select name, age from contacts order by age desc" do |rs|
    2.   rs.each do
    3.     name = rs.read(String)
    4.     age = rs.read(Int32)
    5.     puts "#{name} (#{age})"
    6.     # => Sarah (33)
    7.     # => John Doe (30)
    8.   end
    9. end

    There are many convenient query methods built on top of #query to make this easier.

    You can read multiple columns at once:

    1. name, age = rs.read(String, Int32)

    Or read a single row:

    There are many other helper methods to query with types, query column names with types, etc. All available methods to perform statements in a database are defined in DB::QueryMethods.