The Crystal Programming Language Forum

Using parametrized queries with a database

https://crystal-lang.org/reference/database/ does a poor job demonstrating how to actually do anything with parametrized queries.

STATEMENT = "SELECT id, handle FROM users WHERE users.id = ? LIMIT 1"

struct User
  getter id
  getter username

  def initialize(@id : UInt64,
                 @username : String)
  end
end

def get_user(db, user_id)
  id, username =
    db.query_one(STATEMENT, [user_id])  as : {UInt64, String}

  User.new(id, username)
end

# or this
def get_user(db, user_id)
  User.new(db.query_one(STATEMENT, [user_id]))
end

# or this
def get_user(db, user_id)
  db.query_one(STATEMENT, [user_id]) as: User
end

I can’t figure out the syntax to make one of these work.

1 Like

Hi!

Please open an issue in crystal-db. This is a docs issue there was recently a breaking change.

Just pass user_id without bracket (without it being inside an array).

This should do:

db.query_one(STATEMENT, user_id, as: {UInt64, String})

Thanks, I got it working. Here’s the final working code for future reference:

STATEMENT = "SELECT id, handle, created_at, updated_at, deleted_at FROM users WHERE users.id = ?"
"SELECT id, handle, created_at, updated_at, deleted_at FROM users WHERE users.id = ?"

struct User
  getter id
  getter username
  getter created_at
  getter updated_at
  getter deleted_at

  def initialize(@id : UInt64,
                 @username : String,
                 @created_at : String,
                 @updated_at : String,
                 @deleted_at : String)
  end
end

def get_user(db, user_id)
  id, handle, created_at, updated_at, deleted_at = db.query_one(STATEMENT, user_id, as: {UInt64, String, String, String, String})
  user = db.query_one(STATEMENT, user_id, as: User)

  # return user
  user
end