Using Template Haskell to make type-safe database access
Hi, I was wondering if anybody had experimented with using Template Haskell (TH) and ordinary SQL to make type-safe database access? To clarify what I am thinking about I will sketch how it could be done. The TH function should take two inputs. SQL (as a string) and a database source name (DSN). It should return an IO action as output. The TH-function should: 1. Connect to the database using the DSN 2. Ask the database which types will be returned from the expression 3. Build an IO action which can be used to execute the SQL at run-time. The action could return the result as a (lazy) list. Due to step two we can make the returned values type-safe. Greetings, Mads Lindstrøm
Hi Mads,
I was wondering if anybody had experimented with using Template Haskell (TH) and ordinary SQL to make type-safe database access?
I know HaskellDB, for example, does something quite similar. There's a preprocessor that generates a Haskell file with a Haskell representation of the types of the database's tables. You could of course replace this with a TH function. There are two very nice papers about the design of HaskellDB: http://research.microsoft.com/users/daan/download/papers/dsec.ps http://haskelldb.sourceforge.net/haskelldb.pdf I think there may a bit of problem with the approach you suggest: as the type returned by the query is computed by the SQL server (if I understand you correctly), it's very hard to do anything with the result of the query - the Haskell compiler has no idea what type the result has, so you can't do anything with it. I think it makes much more sense to bookkeep type information on the Haskell side. Hope this helps, Wouter
Hi, Wouter Swierstra wrote:
Hi Mads,
I was wondering if anybody had experimented with using Template Haskell (TH) and ordinary SQL to make type-safe database access?
I know HaskellDB, for example, does something quite similar. There's a preprocessor that generates a Haskell file with a Haskell representation of the types of the database's tables. You could of course replace this with a TH function. There are two very nice papers about the design of HaskellDB:
http://research.microsoft.com/users/daan/download/papers/dsec.ps
Thanks.
I think there may a bit of problem with the approach you suggest: as the type returned by the query is computed by the SQL server (if I understand you correctly), it's very hard to do anything with the result of the query - the Haskell compiler has no idea what type the result has, so you can't do anything with it. I think it makes much more sense to bookkeep type information on the Haskell side.
But you can ask the SQL server for the type of the result. In the TH function you could: 1) Call ODBC function SQLPrepare (http://msdn.microsoft.com/en-us/library/ms710926(VS.85).aspx) . This just prepares a statement. It do _not_ execute it. 2) Call ODBC function SQLNumParams (http://msdn.microsoft.com/en-us/library/ms715409(VS.85).aspx) . This returns the number of parameters. 3) Call ODBC function SQLDescribeParam (http://msdn.microsoft.com/en-us/library/ms710188(VS.85).aspx) for each parameter to get type information. I imagine that this would all be done at compile time by the TH function. At run time we would call SQLPrepare (again) and SQLExecute. We obtained type information at compile time, so it should all be quite type safe. HDBC almost supports this with describeResult (http://software.complete.org/static/hdbc/doc/Database-HDBC.html#v% 3AdescribeResult ) and prepare (http://software.complete.org/static/hdbc/doc/Database-HDBC.html#v% 3Aprepare ), except that we need to execute a prepared SQL statement before calling describeResult (see link to describeResult). Calling functions that could potentially change data at compile time seems like a bad idea. For some people, maybe even a deal breaker. HSQL has similar functions. But I have not got it to work yet, so that I could test it. We would of cause need to map the type information returned by SQLDescribeParam to Haskell data types. But is seems to me that HSQL and HDBC can already do that. Of cause it all requires that the database have identical metadata at run and compile -time. Either using the same database or a copy. Though one should note that HaskellDB has the same disadvantage. Actually it do not seem much of a disadvantage it all, as most code accessing SQL databases depends on database metadata anyway. Greetings, Mads Lindstrøm
Hope this helps,
Wouter
Hi Mads,
I think there may a bit of problem with the approach you suggest: as the type returned by the query is computed by the SQL server (if I understand you correctly), it's very hard to do anything with the result of the query - the Haskell compiler has no idea what type the result has, so you can't do anything with it. I think it makes much more sense to bookkeep type information on the Haskell side.
But you can ask the SQL server for the type of the result. In the TH function you could:
Thanks for your interesting reply. I'd forgotten that you can do I/O in TH's quotation monad. I agree that you can ask the database server for the type that an SQL expression will return. I don't understand metaprogramming enough to see how computing types with TH effects the rest of your program. 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? 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.
Of cause it all requires that the database have identical metadata at run and compile -time. Either using the same database or a copy. Though one should note that HaskellDB has the same disadvantage. Actually it do not seem much of a disadvantage it all, as most code accessing SQL databases depends on database metadata anyway.
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 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 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. Wouter This message has been checked for viruses but the contents of an attachment may still contain software viruses, which could damage your computer system: you are advised to perform your own checks. Email communications with the University of Nottingham may be monitored as permitted by UK legislation.
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 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:
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
Hello Mads, Thursday, May 8, 2008, 1:24:05 AM, you wrote:
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
no, TH is dificult by itself. if you have spare time - read about metalua, which implements the same idea in Lua environment. it's simple and straightforward, and even allows to easily change syntax. one possible reason of TH difficulty may be that Haskell is strict-typed language -- Best regards, Bulat mailto:Bulat.Ziganshin@gmail.com
Hi Mads,
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.
Nice! I have to admit, it's much nicer than I expected it to be. Just out of curiousity, what happens when you write: selectTupleList :: Connection -> IO [Integer] instead of selectTupleList :: Connection -> IO [(Integer, String, String)] What kind of error message do you get? More specifically, is this error caught statically or dynamically. The only other limitation I can think of, would be in the situation where you don't have compile-time access to the database, e.g. developing software for a client with a database that can only be accessed from their intranet. I have no idea how much of a limitation that is.
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.
The message is a bit verbose, but if you ignore the noise, it gives pretty good feedback about what's wrong. Good.
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.
Yes - but parsing the result of an SQL describe statement is pretty easy.
A library that will be a lot more complex to learn than what I am proposing (assuming the developer already knows SQL).
Hmm. This is a rather sticky point. One might also argue that Haskell developers have to learn SQL to use the solution you propose. I'm not particularly convinced. Both approaches have their merits I think. Anyhow - nice work! Have you asked Bjorn Bringert what he thinks? He's a really clever and approachable guy - and he knows a lot more about interfacing with databases than I do. Kind regards, Wouter This message has been checked for viruses but the contents of an attachment may still contain software viruses, which could damage your computer system: you are advised to perform your own checks. Email communications with the University of Nottingham may be monitored as permitted by UK legislation.
Hi Wouter, Wouter Swierstra wrote:
Nice! I have to admit, it's much nicer than I expected it to be. Just out of curiousity, what happens when you write:
selectTupleList :: Connection -> IO [Integer]
instead of
selectTupleList :: Connection -> IO [(Integer, String, String)]
What kind of error message do you get? More specifically, is this error caught statically or dynamically.
The type annotation in UseSqlExpr.hs was just for the reader. The compiler can infer the types completely. Thus when I make the suggested change I get a compile time error. It looks like this: UseSqlExpr.hs:27:6: Couldn't match expected type `Integer' against inferred type `(Integer, String, String)' Expected type: IO [Integer] Inferred type: IO [(Integer, String, String)] In the expression: (return $ (map (\ [x0[a2ZY], x1[a2ZZ], x2[a300]] -> (readInteger x0[a2ZY], readString x1[a2ZZ], readString x2[a300])) rows[a2ZX])) In the expression: do rows[a2ZX] <- fetchRows dsn[a2ZW] ['S', 'E', 'L', 'E', 'C', 'T', ' ', 'u', 's', 'e', 'r', '_', 'i', 'd', ',', ' ', 'u', 's', 'e', 'r', '_', 'n', 'a', 'm', 'e', ',', ' ', 'u', 's', 'e', 'r', '_', 'r', 'e', 'a', 'l', '_', 'n', 'a', 'm', 'e', ' ', 'F', 'R', 'O', 'M', ' ', 'u', 's', 'e', 'r', ';'] (return $ (map (\ [x0[a2ZY], x1[a2ZZ], x2[a300]] -> (readInteger x0[a2ZY], readString x1[a2ZZ], readString x2[a300])) rows[a2ZX])) make: *** [all] Fejl 1
The only other limitation I can think of, would be in the situation where you don't have compile-time access to the database, e.g. developing software for a client with a database that can only be accessed from their intranet. I have no idea how much of a limitation that is.
True, but this limitation is only relevant when you do not have access to the production database or a database with identical metadata. How often do people develop like that? How are they testing? I have a hard time picturing a setup without a test database with identical metadata to the production database.
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.
Yes - but parsing the result of an SQL describe statement is pretty easy. ok.
A library that will be a lot more complex to learn than what I am proposing (assuming the developer already knows SQL).
Hmm. This is a rather sticky point. One might also argue that Haskell developers have to learn SQL to use the solution you propose. I'm not particularly convinced. Both approaches have their merits I think.
Yes. I was _not_ making what you could call a strong argument. I was assuming that most (Haskell) developers knew SQL anyway. I have no data to back it up. Just my gut feeling. To be fair I should mention a couple of drawbacks with the TH-based approach. While SQL got static typing, it is not really as powerful as it could be. For example if you do "select sum(...) from ..." the type system will tell you that a set of values are returned. In reality this set will never have more than one member. Your proposal would be able to return a Float in stead of a [Float]. Another advantage your proposal (and disadvantage of the TH based one) would be that it can abstract over variances in different database implementation. That is, you could translate to SQL depending on SQL backend. This would be really nice. But I guess it would also be a big task.
Anyhow - nice work! Have you asked Bjorn Bringert what he thinks? He's a really clever and approachable guy - and he knows a lot more about interfacing with databases than I do.
Kind regards,
Wouter
/Mads Lindstrøm
On Thu, May 8, 2008 at 5:32 PM, Mads Lindstrøm
Hi Wouter,
Wouter Swierstra wrote:
Nice! I have to admit, it's much nicer than I expected it to be. Just out of curiousity, what happens when you write:
selectTupleList :: Connection -> IO [Integer]
instead of
selectTupleList :: Connection -> IO [(Integer, String, String)]
What kind of error message do you get? More specifically, is this error caught statically or dynamically.
The type annotation in UseSqlExpr.hs was just for the reader. The compiler can infer the types completely. Thus when I make the suggested change I get a compile time error. It looks like this:
UseSqlExpr.hs:27:6: Couldn't match expected type `Integer' against inferred type `(Integer, String, String)' Expected type: IO [Integer] Inferred type: IO [(Integer, String, String)] In the expression: (return $ (map (\ [x0[a2ZY], x1[a2ZZ], x2[a300]] -> (readInteger x0[a2ZY], readString x1[a2ZZ], readString x2[a300])) rows[a2ZX])) In the expression: do rows[a2ZX] <- fetchRows dsn[a2ZW] ['S', 'E', 'L', 'E', 'C', 'T', ' ', 'u', 's', 'e', 'r', '_', 'i', 'd', ',', ' ', 'u', 's', 'e', 'r', '_', 'n', 'a', 'm', 'e', ',', ' ', 'u', 's', 'e', 'r', '_', 'r', 'e', 'a', 'l', '_', 'n', 'a', 'm', 'e', ' ', 'F', 'R', 'O', 'M', ' ', 'u', 's', 'e', 'r', ';'] (return $ (map (\ [x0[a2ZY], x1[a2ZZ], x2[a300]] -> (readInteger x0[a2ZY], readString x1[a2ZZ], readString x2[a300])) rows[a2ZX])) make: *** [all] Fejl 1
The only other limitation I can think of, would be in the situation where you don't have compile-time access to the database, e.g. developing software for a client with a database that can only be accessed from their intranet. I have no idea how much of a limitation that is.
True, but this limitation is only relevant when you do not have access to the production database or a database with identical metadata. How often do people develop like that? How are they testing? I have a hard time picturing a setup without a test database with identical metadata to the production database.
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.
Yes - but parsing the result of an SQL describe statement is pretty easy. ok.
A library that will be a lot more complex to learn than what I am proposing (assuming the developer already knows SQL).
Hmm. This is a rather sticky point. One might also argue that Haskell developers have to learn SQL to use the solution you propose. I'm not particularly convinced. Both approaches have their merits I think.
Yes. I was _not_ making what you could call a strong argument. I was assuming that most (Haskell) developers knew SQL anyway. I have no data to back it up. Just my gut feeling.
To be fair I should mention a couple of drawbacks with the TH-based approach. While SQL got static typing, it is not really as powerful as it could be. For example if you do "select sum(...) from ..." the type system will tell you that a set of values are returned. In reality this set will never have more than one member. Your proposal would be able to return a Float in stead of a [Float].
Another advantage your proposal (and disadvantage of the TH based one) would be that it can abstract over variances in different database implementation. That is, you could translate to SQL depending on SQL backend. This would be really nice. But I guess it would also be a big task.
Anyhow - nice work! Have you asked Bjorn Bringert what he thinks? He's a really clever and approachable guy - and he knows a lot more about interfacing with databases than I do.
I guess I'll just have to reply then :-) Mads: Preparing the statement and asking the DB about the type at compile is a great idea! I've never thought of that. Please consider completing this and packaging it as a library. I can't really see any major problems with this approach, other than the obivious "changing schema" problem that it shares with HaskellDB. Of course there are some things that it won't catch, like the singleton results from aggregates, but it does go a long way towards a statically safe DB interface. I think that the main disadvantages compared to HaskellDB are that: - It won't let you write your own query combinators. - The program becomes dependent on the exact SQL dialect you chose to use. On the other hand it addresses some of the problems with HaskellDB: - Poorly optimized queries. - Missing SQL features (e.g. outer joins). - Poor support for DB-specific features. - Difficult to understand type errors. /Björn
Hi Bjorn Bringert wrote:
Mads: Preparing the statement and asking the DB about the type at compile is a great idea! I've never thought of that. Please consider completing this and packaging it as a library.
Thanks for the nice remark. And I will begin completing the idea, as soon I have packaged up and wrote a little tutorial about my other project (SybWidget). I already started that about three weeks ago, so it should be finished soon. Greetings, Mads Lindstrøm
participants (4)
-
Bjorn Bringert -
Bulat Ziganshin -
Mads Lindstrøm -
Wouter Swierstra