Skip to content

Latest commit

 

History

History
221 lines (151 loc) · 6.37 KB

querying.md

File metadata and controls

221 lines (151 loc) · 6.37 KB
Raw

Querying

DBX allows several ways to make queries, from low level (crystal-db), to query builder and ORM (model query).

DBX provides a query builder that can be used directly (example: query.find(:users).where(:username, "foo")) or through models (example: User.find.where(:username, "foo")).

Crystal DB query

require "dbx"
require "pg" # <= Replaces by your database driver

# Connection URI / DSL https://www.postgresql.org/docs/current/libpq-connect.html#h5o-9
db = DBX.open("app", "postgres://...")

pp DBX.db?("app") ? "connected" : "not connected"

users = db.query_all(
  "select username, email from users",
  as: {name: String, email: String}
)

pp users

# Closes all connections of this DB connection pool and remove it.
DBX.destroy("app")

See Crystal DB API: QueryMethods

Just for example with a simple Crystal DB model:

class User
  include JSON::Serializable
  include DB::Serializable
  include DB::Serializable::NonStrict

  property lang : String

  @[JSON::Field(key: "firstName")]
  property first_name : String?
end

db = DBX.db("app")

user = User.from_rs(db.query("SELECT id, lang, first_name FROM users"))
pp user.to_json

user = User.from_json "{\"lang\":\"fr\",\"firstName\":\"Nico\"}"
pp user

For a more advanced model system, see ORM: Model.

Build query only

To build queries only, then get the SQL string and its array of arguments.

require "dbx"
require "dbx/adapter/pg" # or require "dbx/adapter/sqlite"
require "dbx/query_builder"

builder = DBX::QueryBuilder.new
sql, args = builder.table(:users).insert({username: "foo", email: "[email protected]"}).build

pp sql
pp args

# One
sql, args = builder.find(:users).where(:username, "foo").limit(1).build

pp sql
pp args

# All
sql, args = builder.find(:users).build

pp sql
pp args

See API: QueryBuilder

Query

To build and execute the queries.

Bootstrap:

require "dbx"
require "dbx/adapter/pg" # or require "dbx/adapter/sqlite"
require "dbx/query_builder"

DB_CONN = DBX.open("app", "postgres://...")
DB_ADAPTER = DBX::Adapter::PostgreSQL.new(DB_CONN)

def new_query
  DBX::Query.new(DB_ADAPTER)
end

Usage:

er = new_query.table(:users).insert({username: "foo", email: "[email protected]"}).exec!
puts er.rows_affected

# Find one
user = new_query.find(:users).where(:username, "foo").limit(1).to_o!
pp user

# Find all
users = new_query.find(:users).to_a
pp users

See API: Query

ORM - Model query

To build and execute the queries through a ORM model.

Bootstrap:

require "dbx"
require "dbx/adapter/pg" # or require "dbx/adapter/sqlite"
require "dbx/orm"

DBX.open("server2", "postgres://...")

class User < DBX::ORM::Model
  adapter :pg

  # table :users # <= automatically resolved from class name
  # connection "server2" # <= default is "app", but you can use another DB connection pool.

  # DB table schema
  class Schema < DBX::ORM::Schema
    field id : Int64?
    field username : String
    field email : String
  end

  # Custom (optional)
  class ModelQuery < DBX::ORM::ModelQuery(User)
    def select_custom
      self.select({:id, :username, :email})
    end
  end
end

Usage:

user = User.create!({username: "foo", email: "[email protected]"})
pp user

# Find one
user = User.find.where(:username, "foo").limit(1).to_o!
pp user

# Find all
users = User.find.to_a
pp users

To keep in mind

Remember:

💡 Drivers and crystal-db are the common modules that ultimately interact with specific databases.

💡 DBX adapters are the classes that link DBX to the databases drivers (and crystal-db).

require "dbx/adapter/pg" for PostgreSQL. require "dbx/adapter/sqlite" for SQLite.

💡 dbx/query_builder is the module that provides DBX::QueryBuilder and DBX::Query.

DBX::QueryBuilder only takes care of building the query intended to be executed to the database.

DBX::Query builds the query with DBX::QueryBuilder and executes it to the database.

💡 to_o is the same as query_one.

Mnemonic: to_o (query_one) - to one, to object. Performs the query (#query_one) and returns the data (one object). Returns DB::ResultSet, the response of a query performed on a DB::Database.

💡 to_a is the same as query_all.

Mnemonic: to_a (query_all) - to all, to array. Performs the query (#query_all) and returns the data (array of object). Returns DB::ResultSet, the response of a query performed on a DB::Database.

💡 scalar

Performs the query and returns a single scalar value. Returns a single scalar value (String or Int32 or Int64 or another Crystal type).

💡 exec

Execution that does not wait for data (scalar or object or array) from the database. Returns DB::ExecResult, result of a #exec statement.

Summary

To go further, see: