Skip to content

Latest commit

 

History

History
233 lines (169 loc) · 4.61 KB

crud.md

File metadata and controls

233 lines (169 loc) · 4.61 KB

CRUD

CRUD examples with the query builder (and executor), see ORM: CRUD for the same using the DBX ORM.

Note: With the ORM, the queries are simpler and more productive.

This doc assumes that you have initialized DBX, for example like this:

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

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

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

Create

Simple insert:

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

Insert returning the new record:

user = new_query
  .insert(:users, {username: "foo", email: "[email protected]"})
  .returning
  .to_o({id: Int32, username: String, email: String})

if user
  pp user
end

# raises when no records found
user = new_query
  .insert(:users, {username: "foo", email: "[email protected]"})
  .returning
  .to_o!({id: Int32, username: String, email: String})

pp user

Note: to_o! is an alias of query_one!, see querying for more details.

Or create! method (helper), insert one, returning the new record:

user = new_query.table(:users).create!(
  {username: "foo", email: "[email protected]"},
  as: {id: Int32, username: String, email: String}
)

pp user

Read

Find one:

user = new_query
  .find(:users, :id, 42)
  .to_o({id: String, username: String, email: String})

if user
  puts user.email
end

# raises when no records found
user = new_query
  .find(:users, :id, 42)
  .to_o!({id: String, username: String, email: String})

puts user.email

another example:

user = new_query
  .find(:users)
  .where(:job, "dev")
  .limit(1)
  .to_o({id: String, username: String, email: String})

if user
  puts user.email
end

# raises when no records found
user = new_query
  .find(:users)
  .where(:job, "dev")
  .limit(1)
  .to_o!({id: String, username: String, email: String})

puts user.email

Find all:

users = new_query
  .find(:users)
  .to_a({id: String, username: String, email: String})

Find and get a scalar value:

id = new_query
  .find(:users)
  .select(:id)
  .where(:username, "foo")
  .scalar!
  .as(Int64)

With count:

total_users = new_query
  .find(:users)
  .count
  .scalar!
  .as(Int64)

Update

Update one:

er = new_query
  .table(:users)
  .update(:id, 42, {username: "bar", email: "[email protected]"})
  .exec

puts er.rows_affected if er

# raises when no records found
er = new_query
  .table(:users)
  .update(:id, 42, {username: "bar", email: "[email protected]"})
  .exec!

puts er.rows_affected

Update all:

er = new_query
  .update(:users, {job: "dev", bio: "Happy!"})
  .exec

puts er.rows_affected if er

# raises when no records found
er = new_query
  .update(:users, {job: "dev", bio: "Happy!"})
  .exec!

puts er.rows_affected

With pg (PostgreSQL) adapter, it is possible to get the updated rows:

updated_users = new_query
  .update(:users, {job: "dev", bio: "Happy!"})
  .returning(:id, :job, :bio)
  .to_a({id: Int64, job: String, bio: String})

pp updated_users

Note: the returning support, will also be implemented in the SQLite adapter.

Delete

Delete one:

er = new_query.table(:users).delete(:id, 42).exec
puts er.rows_affected if er

# raises when no records found
er = new_query.table(:users).delete(:id, 42).exec!
puts er.rows_affected

Delete all:

er = new_query.delete(:users).exec
puts er.rows_affected if er

# raises when no records found
er = new_query.delete(:users).exec!
puts er.rows_affected

With pg (PostgreSQL) adapter, it is possible to get the deleted rows:

deleted_users = new_query
  .delete(:users)
  .returning(:id, :job, :bio)
  .to_a({id: Int64, job: String, bio: String})

pp deleted_users

Note: the returning support, will also be implemented in the SQLite adapter.

Summary

We have explored some examples of CRUD, to go further see also: