[Haskell-cafe] Using Template Haskell to make type-safe database access

[Mads Lindstrøm]

Hi Wouter

Wouter Swierstra wrote:
> Here's a concrete example. Suppose you have a query q that, when  
> performed, will return a table storing integers. I can see how you can  
> ask the SQL server for the type of the query, parse the response, and  
> compute the Haskell type "[Int]". I'm not sure how to sum the integers  
> returned by the query *in Haskell* (I know SQL can sum numbers too,  
> but this is a simple example). What would happen when you apply  
> Haskell's sum function to the result of the query? Does TH do enough  
> compile time execution to see that the result is well-typed?

Not only pictures, but also code can say more than a thousands words.
Therefore, I have been implementing a proof of concept. The code is
attached in two files - SqlExpr.hs and UseSqlExpr.hs. The former
contains two SQL expressions + Haskell code. The latter is the Template
Haskell (TH) code that makes it possible to type-safely access the
database.

UseSqlExpr.hs is a lot easier to understand than SqlExpr.hs. So if you
only have time to look at one of them, look at UseSqlExpr.hs. The reason
SqlExpr.hs is harder to understand is not just because it is longer, but
also because TH is difficult. At least TH was difficult for me. It might
just be because I have never worked with anything like TH before (have
not learned Lisp yet :( ). It remained me of going from OO to FP. You
have to change how you think.

Your example of fetching a [Int] and take there sum is shown in
UseSqlExpr.hs.

The output from running UseSqlExpr.hs (on my computer) is:

[1,2,3,4]
[(1,"WikiSysop",""),(2,"Mads","Mads Lindstr\195\184m"),(3,"Madstest","Bob"),(4,"Test2","Test 2")]
Sum is: 10

> 
> Having the SQL server compute types for you does have other drawbacks,  
> I think. For example, suppose your query projects out a field that  
> does not exist. An error like that will only get caught once you ask  
> the server for the type of your SQL expression. If you keep track of  
> the types in Haskell, you can catch these errors earlier; Haskell's  
> type system can pinpoint which part of the query is accessing the  
> wrong field. I feel that if you really care about the type of your  
> queries, you should guarantee type correctness by construction, rather  
> than check it as an afterthought.

But the SQL database will output a meaningful error message. And TH is
asking the server at compile time. Thus, the user can also get the error
message at compile time. TH is used as part of the compilation process.
I _think_ it would be fair to say it occurs concurrently with type
checking (or maybe intermittently). Thus the user do not get the error
message later than with a type based approach.

If you, with the currently implemented proof of concept, name a
non-existing field in your SQL you get:

<compile time output>
UseSqlExpr.hs:22:6:
    Exception when trying to run compile-time code:
      Exception when trying "executing prepared statement" : execute execute: ["1054: [MySQL][ODBC 3.51 Driver][mysqld-5.0.32-Debian_7etch5-log]Unknown column 'duser_id' in 'field list'"]
      Code: compileSql
              "DSN=MySQL_DSN;USER=StocksDaemon;" "SELECT duser_id FROM user;"
    In the expression:
        $[splice](compileSql
                    "DSN=MySQL_DSN;USER=StocksDaemon;" "SELECT duser_id FROM user;")
          c
    In the definition of `selectIntegerList':
        selectIntegerList c = $[splice](compileSql
                                          "DSN=MySQL_DSN;USER=StocksDaemon;"
                                          "SELECT duser_id FROM user;")
                                c
make: *** [all] Fejl 1
</compile time output>

ok, there is some noise. But at the end of line three it says "Unknown
column 'duser_id'". Also with a little more work I could properly
improve the output.

> Perhaps I should explain my own thoughts on the subject a bit better.  
> I got interested in this problem because I think it makes a nice  
> example of dependent types "in the real world" - you really want to  

But won't you end up implementing all the functionality of an SQL
parser? While possible, it does seem like a huge job. With a TH solution
you will safe a lot of work.

Also, almost every software developer already knows SQL. And the few
that do not, will likely have to learn SQL if they are to do substantial
work with databases. Whereas if you implement a type based solution a
developer will have to learn how to use your library. A library that
will be a lot more complex to learn than what I am proposing (assuming
the developer already knows SQL).

> compute the *type* of a table based on the *value* of an SQL DESCRIBE.  
> Nicolas Oury and I have written a draft paper describing some of our  
> ideas:
> 
> http://www.cs.nott.ac.uk/~wss/Publications/ThePowerOfPi.pdf
> 

I have not read the paper yet, as I have been busy coding. Plus I have a
day job. But I did read the first couple of pages and so far the paper
seems very interesting. When time permits I will read the rest.
Hopefully this weekend.

> Any comments are very welcome! Our proposal is not as nice as it could  
> be (we would really like to have quotient types), but I hope it hints  
> at what is possible.


Greetings,

Mads Lindstørm

-------------- next part --------------
A non-text attachment was scrubbed...
Name: SqlExpr.hs
Type: text/x-haskell
Size: 2119 bytes
Desc: not available
Url : http://www.haskell.org/pipermail/haskell-cafe/attachments/20080507/6d89c7cf/SqlExpr-0001.bin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: UseSqlExpr.hs
Type: text/x-haskell
Size: 797 bytes
Desc: not available
Url : http://www.haskell.org/pipermail/haskell-cafe/attachments/20080507/6d89c7cf/UseSqlExpr-0001.bin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: makefile
Type: text/x-makefile
Size: 99 bytes
Desc: not available
Url : http://www.haskell.org/pipermail/haskell-cafe/attachments/20080507/6d89c7cf/makefile-0001.bin