Database

    The following packages are compliant with crystal-db

    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

    During this guide crystal-lang/crystal-mysql will be used.

    1. require "db"
    2. require "mysql"
    4.   # ... use db to perform queries
    5. end

    Other connection uris are

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

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

    1. require "db"
    2. require "mysql"
    4. db = DB.open "mysql://root@localhost/test"
    5. begin
    6. ensure
    7.   db.close
    8. end

    To execute sql statements you can use Database#exec

    To avoid sql injection use parameters to submit data

    1. db.exec "insert into contacts values (?, ?)", "John", 30
    2. db.exec "insert into contacts values (?, ?)", "Sarah", 33

    Note: When using the pg driver, use $1, $2, etc. instead of ?

    To perform a query and get the result set use , arguments can be used as in Database#exec.

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

    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.

    There are many convenient query methods built on top of #query.

    You can read multiple columns at once:

      Or read a single row:

      1. name, age = db.query_one "select name, age from contacts order by age desc limit 1", as: { String, Int32 }

      Or read a scalar value without dealing explicitly with the ResultSet:

      All available methods to perform statements in a database are defined in .