Using parametrized queries with a database

Help & Support
1

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
2

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).

3

This should do:

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

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 = ?"

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
5

Ok, same question, except how would I get this to work?

users = db.query_all("SELECT id, handle, created_at, updated_at, deleted_at FROM users"
, as: Array(User))

The docs are sparse and I’m getting this:

 103 | Decoders.decode_array(io, col_bytesize, T)
                ^-----------
Error: instantiating 'PG::Decoders:Module#decode_array(IO::Sized, Int32, Array(ChannelRow).class)'

In lib/pg/src/pg/decoders/array_decoder.cr:72:17

 72 | decoder = array_decoder(T)
                ^------------
Error: no overload matches 'PG::Decoders.array_decoder' with type User.class
6

I don’t think that’s possible by default. Could use an ORM or maybe look into the new DB::Serializable feature of the crystal-db shard.

7

You should probably use as: User. query_all returns Array(User), but you must not specify the container type in the argument.

8 
  users = env.db.query_all("SELECT id, name, description FROM users OFFSET 0 LIMIT 15", as: User)

gives me:

Exception: PG::ResultSet#read returned a Int64. A User was expected. (Exception)
  from lib/db/src/db/result_set.cr:79:7 in 'read'
  from lib/db/src/db/query_methods.cr:249:9 in 'query_all:as'