# Using parametrized queries with a database **URL:** \ **Category:** Help & Support\ **Created:** [October 13, 2019, 7:41pm UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235 "2019-10-13T19:41:17Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![carcinocron](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/carcinocron/32/1022_2.png) [@carcinocron](https://forum.crystal-lang.org/u/carcinocron)\ **Post date:** [October 13, 2019, 7:41pm UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235/1 "2019-10-13T19:41:17Z")
[https://crystal-lang.org/reference/database/](https://crystal-lang.org/reference/database/) does a poor job demonstrating how to actually do anything with parametrized queries. ```auto 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. ---
**Author:** ![asterite](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/asterite/32/60_2.png) [@asterite](https://forum.crystal-lang.org/u/asterite)\ **Post date:** [October 13, 2019, 8:34pm UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235/2 "2019-10-13T20:34:50Z")
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). ---
**Author:** ![straight-shoota](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/straight-shoota/32/36_2.png) [@straight-shoota](https://forum.crystal-lang.org/u/straight-shoota)\ **Post date:** [October 13, 2019, 9:17pm UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235/3 "2019-10-13T21:17:00Z")
This should do: ```auto db.query_one(STATEMENT, user_id, as: {UInt64, String}) ``` ---
**Author:** ![carcinocron](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/carcinocron/32/1022_2.png) [@carcinocron](https://forum.crystal-lang.org/u/carcinocron)\ **Post date:** [October 20, 2019, 2:54am UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235/4 "2019-10-20T02:54:28Z")
Thanks, I got it working. Here’s the final working code for future reference: ```auto 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 ``` ---
**Author:** ![carcinocron](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/carcinocron/32/1022_2.png) [@carcinocron](https://forum.crystal-lang.org/u/carcinocron)\ **Post date:** [January 30, 2020, 6:34pm UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235/5 "2020-01-30T18:34:25Z")
Ok, same question, except how would I get this to work? ```auto 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: ```auto 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 ``` ---
**Author:** ![Blacksmoke16](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/blacksmoke16/32/1241_2.png) [@Blacksmoke16](https://forum.crystal-lang.org/u/Blacksmoke16)\ **Post date:** [January 30, 2020, 7:15pm UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235/6 "2020-01-30T19:15:44Z")
I don’t think that’s possible by default. Could use an ORM or maybe look into the new [DB::Serializable](http://crystal-lang.github.io/crystal-db/api/0.8.0/DB/Serializable.html) feature of the `crystal-db` shard. ---
**Author:** ![straight-shoota](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/straight-shoota/32/36_2.png) [@straight-shoota](https://forum.crystal-lang.org/u/straight-shoota)\ **Post date:** [January 31, 2020, 12:11am UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235/7 "2020-01-31T00:11:09Z")
You should probably use `as: User`. `query_all` returns `Array(User)`, but you must not specify the container type in the argument. ---
**Author:** ![carcinocron](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/carcinocron/32/1022_2.png) [@carcinocron](https://forum.crystal-lang.org/u/carcinocron)\ **Post date:** [January 31, 2020, 12:41am UTC](https://forum.crystal-lang.org/t/using-parametrized-queries-with-a-database/1235/8 "2020-01-31T00:41:54Z")
```auto users = env.db.query_all("SELECT id, name, description FROM users OFFSET 0 LIMIT 15", as: User) ``` gives me: ```auto 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' ```