postgres-simple - No instance for (ToRow Int) arising from a use of ‘query’

Question

I am new to haskell and have honestly hard times with it. But it expands my thinking, so here we go. I am trying to run a really simple Webserver that queries a Postgres DB and should return the result as JSON.

The query is absolutely simple: "Select id,data from MYTABLE where id = 1"

But the type system of haskell is killing me right now and the final type of my actions doesn't match up. I am using Spock and PostgreSQL-Simple as a Combo.

Most tutorials are either to simple for what I want to do or to difficult. I am somewhere inbetween and miss a lot of Haskell understanding, a lot of my previous problems I have already solved by simple copy and pasting and got a simple version working.

But as soon as I try to pass on a route variable I am failing. Here is my working version. My Database Table is here called "Envelope", The important call is where it says get "json" :

{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE FlexibleInstances #-}

module Main where

import Web.Spock
import Web.Spock.Config
import Database.PostgreSQL.Simple
import Data.Pool
import Data.Aeson (ToJSON(toJSON), object, (.=),Value)
import  Database.PostgreSQL.Simple.FromRow

type AppAction a = SpockActionCtx () Connection AppSession AppState a

data AppState = EmptyState
data AppSession = EmptySession

data Envelope = Envelope { envId :: Int, envData :: Value } deriving Show

instance FromRow Envelope where
  fromRow = Envelope <$> field <*> field

instance ToJSON Envelope where
   toJSON (Envelope envA envB) = object [ "id" .= envA, "data" .= envB ]

main :: IO ()
main =
  do pool<-createPool (connect (ConnectInfo "localhost" 5432 "" "" "envelopes") ) close 1 10 10
     spockCfg <- defaultSpockCfg EmptySession (PCPool pool) EmptyState
     runSpock 8080 (spock spockCfg app)

app :: SpockM Connection AppSession AppState ()
app = do 
    get root $
      text "Hello World!"
    get "json" $ do
      xs<-runQuery $ \conn -> 
        query_ conn  "select id,data from envelope where id = 1"
      json (xs::[Envelope])

Then I try to pass on the Envelope ID with a lambda function, for that i also need to change PostgreSQL-Simple query_ to query:

    get ( "json" <//> var  ) $ \eid -> do
      xs<-runQuery $ \conn -> 
        query conn  "select id,data from envelope where id = ?" (eid :: Int)
      json (xs::[Envelope])

The error I get says:

 No instance for (ToRow Int) arising from a use of ‘query’
   In the expression:
      query conn "select id,data from envelope where id = ?" (eid :: Int)
    In the second argument of ‘($)’, namely
      ‘\ conn
         -> query
              conn "select id,data from envelope where id = ?" (eid :: Int)’
    In a stmt of a 'do' block:
      xs <- runQuery
            $ \ conn
                -> query
                     conn "select id,data from envelope where id = ?" (eid :: Int)

I also have problem to return just the first item from the query, even without the lambda function.

Full Source can be found on bitbucket

I hope someone has the time to help me out here. Thanks for reading already.

score 5 · Accepted Answer · answered Feb 23 '17 at 19:31

5

What the error basically says is that you can't pass an Int to query as the third argument. query expects something that has an instance of the typeclass ToRow and Int is not one. What you might want to do in your case considering you only want to pass one value to query is to use Only. So that line becomes:

query conn  "select id,data from envelope where id = ?" (Only (eid :: Int))

answered Feb 23 '17 at 19:31

soupi

1,013
6
6

What, thats it? After all that thinking headaches ? Thank you so much, that works. It is very confusing for me why with two parameters you can simply do ( parA:Int,parB:Int), but with oneparam you have to do the `Only` notation. But after you pointed me towards it I found some comments on that in the Postgres-Simple Docs, so I guess I will understand it later. – Björn Grambow Feb 24 '17 at 08:17
In this context you can think of types like `Int` as a column and of a tuple as a row with two columns and of `Only` as a row with one column. `query` only deals with rows, so you need to place your column inside a row. This is a bit similar to using `values` in an insert statement in sql. You can't just pass 1, you need `values (1)` or `select 1`. – soupi Feb 24 '17 at 08:36
Makes more sense now. Thanks. – Björn Grambow Feb 24 '17 at 09:25

score 1 · Answer 2 · answered Feb 24 '17 at 08:28

Based on soupis help I also find the corresponding part in the Postgres- Simple documentation. Just for completion, the docs mention an alternative Syntax, using a singleton list. So instead of using normal brackets you can use simply square brackets and it works too:

query conn "select id,data from envelope where id = ?" [eid :: Int]

Additionally, in particular in my example, it makes more sense to return only the first row from the result, which then turned out to be simply straight forward with the head function. For anyone else who needs it, here is how you do it:

Spock and Prelude both include a head function. To avoid conflicts I decided to hide Spocks function since I am not using it anyway.

Add to your script at the top:

import Web.Spock hiding(head)

Then change the get part to:

get ( "json" <//> var  ) $ \eid -> do
      xs<-runQuery $ \conn -> 
        query conn  "select id,data from envelope where id = ?" [eid :: Int]
      json $ head (xs::[Envelope])

Done.

postgres-simple - No instance for (ToRow Int) arising from a use of ‘query’

2 Answers2