On Wed, Nov 20, 2013 at 02:42:05AM +0000, Tom Ellis wrote:

On Tue, Nov 19, 2013 at 09:51:20PM -0430, Manuel Gómez wrote:

...

I’m looking for a rather specific point in the design space for database access libraries in Haskell and I’m having some trouble figuring out whether any existing library covers my use cases.

Specifically, I need to

1. get a Yesod application to access a PostgreSQL database without having to reinvent the universe to get them decently integrated,

2. write queries in an EDSL with a good approach to composability and decent static verification against some declared form of the database objects,

3. be able to perform joins

4. be able to specify the schema name for each database object used in queries

I'm working on a Haskell relational query EDSL similar in spirit to HaskellDB. It certainly satisfies your requirements 2 and 3. I'm not exactly sure what exactly your requirements 1 and 4 mean but feel free to contact me off list and we can discuss it further.

For the sake of the mailing list archives I am replying here to confirm that my Haskell relational query language targeting Postgres has now indeed been published: http://hackage.haskell.org/package/opaleye Tom

On Tue, Nov 19, 2013 at 11:20 PM, Erik de Castro Lopo wrote:

Manuel Gómez wrote:

...

* Persistent: it’s perfectly integrated into Yesod, and together with Esqueleto, it provides a nice, typesafe and fully expressive EDSL — but, as far as I can tell, there is no support for specifying schema names, which is essential for my use case.

I'm not sure of what you mean here. DO you mean that you already have a database and schema and you now want to connect Persisent/Esqueleto to it without changing the existing schema?

Hi Erik, Indeed, that’s more or less the situation I’m facing. Perhaps the overloading of the term “schema” is a problem here: what I specifically meant with this point is the namespacing mechanism present in (some implementations of) SQL databases: database object names are bound within namespaces called schemas, and a name can be qualified with the name of the schema, separated by a dot; hence a table named `"with spaces"` in a schema/namespace called `"some tables"` would be used qualified as `"some tables"."with spaces"`. I have an existing schema (read: database) with many schemas (read: namespaces) and I need to do joins between tables in them. The names have spaces and unusual bits of Unicode, and the dot that separates the schema name from the object name mustn’t be quoted, so it’s not simply a matter of specifying a complex name to Persistent, as escaping and quoting wouldn’t be done correctly. A workaround could be to put everything in a single namespace and use prefixes in names, but that causes other issues for me (other tools would break). The other issue I have with Persistent+Esqueleto is that the database I have to work with has some composite or otherwise non–serial-integer primary keys, and I understand Persistent requires a serial primary key. I could probably work around this, though.

Hi Manuel, I'm currently in the same position as you are, except I'm not using Yesod. I've spent some time trying to migrate our current DB setup to use persistent. It's not really built to handle existing schemas. There's no inherent mismatch here, it just needs to be implemented. The main things I've found are missing: * The ability to declare foreign relations across different QQ blocks and verifying them with migrations. * There is no support for non-int primary keys (support for omitting a pk for join tables was recently merged, but I need support for other types of primary keys on normal tables, such as postgres uuid). * It's not possible to define foreign keys on uniqueness constraints. I might not care about this anymore because of the join table patch I mentioned above. * It's not flexible in column types, Text maps to character varying (but not text), and serial to int8. This isn't wrong but you cannot use aliases for these types. This should be fairly straight forward to fix I think. I also think persistent is too backend agnostic :) I only really care about postgres, and I think it would simplify a lot of things if there was a library built only for it. So unless you are willing to modify your schemas or patch persistent it doesn't seem to be a viable alternative. The good news is that Michael and Greg are very helpful and I'm sure they'd happily accept these changes, and esqueleto is a very nice EDSL. I have the same impression of HaskellDB as you have, but I haven't tested using it yet. I also need joins so groundhog is currently off the table too. Tom's library seems interesting but I haven't had the chance to try it out yet. I'm currently using HDBC which is middleware like the -simple packages, but without the static guarantees you mention maintenance has become a pain. We haven't decided how to proceed on this, but I'll try to write a summary once we have something up and running. Please let me know if you find a good solution too! - Adam On Wed, Nov 20, 2013 at 7:44 PM, Boris Lykah wrote:

Hi all,

As the author of Groundhog library I agree that it and Persistent/Esqueleto have strengths in different areas and none of them fully matches power of SQL. Grounhog has better support of SQL expressions and relational schemas (composite keys, schemas, indexes, etc.) Persistent cannot do a query with projections and nested function calls like:

project (upper (FirstNameField `append` SecondNameField), toArith BalanceField * 10) $ lower UserNameField `like` "%smith%"

On the other hand, while Groundhog can query only one table at a time, Persistent combined with Esqueleto gives relational query capabilities that match a lot of the SQL syntax (notably excluding column aliasing).

I like Esqueleto and it seems to be easy to port to Groundhog. The ported codebase may be even smaller because Groundhog already has flexible functions and comparison operators which uniformly support fields and constant values. Alas, I don't have enough time to do this. If someone is interested in porting I will be happy to help.

Thanks, Boris

On Wed, Nov 20, 2013 at 12:11 AM, Manuel Gómez wrote:

...

On Tue, Nov 19, 2013 at 11:20 PM, Erik de Castro Lopo wrote:

...

Manuel Gómez wrote:

...

* Persistent: it’s perfectly integrated into Yesod, and together with Esqueleto, it provides a nice, typesafe and fully expressive EDSL — but, as far as I can tell, there is no support for specifying schema names, which is essential for my use case.

I'm not sure of what you mean here. DO you mean that you already have a database and schema and you now want to connect Persisent/Esqueleto to it without changing the existing schema?

Hi Erik,

Indeed, that’s more or less the situation I’m facing. Perhaps the overloading of the term “schema” is a problem here: what I specifically meant with this point is the namespacing mechanism present in (some implementations of) SQL databases: database object names are bound within namespaces called schemas, and a name can be qualified with the name of the schema, separated by a dot; hence a table named `"with spaces"` in a schema/namespace called `"some tables"` would be used qualified as `"some tables"."with spaces"`.

I have an existing schema (read: database) with many schemas (read: namespaces) and I need to do joins between tables in them. The names have spaces and unusual bits of Unicode, and the dot that separates the schema name from the object name mustn’t be quoted, so it’s not simply a matter of specifying a complex name to Persistent, as escaping and quoting wouldn’t be done correctly. A workaround could be to put everything in a single namespace and use prefixes in names, but that causes other issues for me (other tools would break).

The other issue I have with Persistent+Esqueleto is that the database I have to work with has some composite or otherwise non–serial-integer primary keys, and I understand Persistent requires a serial primary key. I could probably work around this, though. _______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe

-- Regards, Boris _______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe