Support for Prepared and Procedural Statements

[smaccoun]

First, thanks for this amazing library.

Wondering if there are any plans on the roadmap to support prepared statements. Here's the documentation for Postgres, but I believe MySql and most DB's support it Postgres Prepared statements

I am also wondering if Procedural statements could be supported. I'm guessing this would have to be done in DB specific way. I think this fits in with the Custom Queries support, as sometimes I may want to leverage calling a PG/SQL function that contains Postgres logic that Beam doesn't yet support. One example is if I have Postgres with PGCrypto and I want to call a function that runs crypt

[tathougies]

Thanks for the request.

One thing: If you want postgres with PgCrypto, then just use thepgCryptoCrypt function from Database.Beam.Postgres.PgCrypto. You will have to make sure the extension exists in your database. There's an example in Database.Beam.Postgres.Extensions.

[smaccoun]

I did not know that, but that is awesome. I'm doing the encryption with Scrypt right now, but that's ideal and I'll switch over. I also noticed this tidbit in the source:

-- To implement your own extension, create a record type, and implement the
-- 'IsPgExtension' type class.
data PgExtensionEntity extension

-- | Type class implemented by any Postgresql extension
class IsPgExtension extension where

That is a really powerful feature, and there are a number of postgres extensions I could see wanting to add. This might be worth having in the docs...including the current PGCrypto support.

Regarding PGSQL/Prepared statement support: The reason I lumped these together is that I think early on having support for parameterized functions that can be manually written in the DB can be really beneficial when you don't yet have 100% support for all features. Here are some reasons why I could see them being used:

• A SQL query involves currently unsupported BEAM functions and requires parameters (ie cannot be achieved with just a View)
• You have an existing DB with lots of pre-existing PGSQL functions and you want to put the BEAM layer on top without rewriting them
• You have really complex queries that maybe at the time being are just easier to write in raw SQL for some devs
• You need the performance benefit of prepared statements

Note that on the last one, Prepared statements rarely give much performance benefit. Like indexes, they should be used sparingly and only when you are really trying to squeeze performance. That said, they are a nice to have for really complex queries that get called repeatedly.

[tathougies]

The answer to this question is, yes, I would like to implement prepared statements, but it may be some time before I get around to it. In the meantime, I am happy to advise. It would need minimal changes to the core types and backend to support prepared statements.

[tathougies]

Procedural statements I am not so sure on. Invoking them sure (use customExpr_), but defining them in a Haskell dsl may be beyond the scope of what beam-core at least is attempting to solve

[smaccoun]

Sweet :) I think prepared statements are a nice to have, but definitely not urgent. If I get around to it I'll take a pass at implementing them and get your advice on it. And agreed on the procedural statements, I will also give a shot at doing them with customExpr_.

[enobayram]

I'd like to second this feature request, it would be very useful to support prepared statements.