jacentino / sqlfun Goto Github PK

View Code? Open in Web Editor NEW

70.0 4.0 5.0 10.44 MB

Idiomatic data access for F#

License: MIT License

F# 5.53% PLpgSQL 0.16% TSQL 94.31%

sql functional-programming fsharp sql-query async reader-monad micro-orm data-access

sqlfun's People

Contributors

Stargazers

Watchers

Forkers

alexzandros beauvankirk bkarvelas mjcaley ingted

sqlfun's Issues

sqlite: don't matching query columns as expected

I was testing some ways to build queries from some table adding some additional field on query time.
Given the follow simple schema:

CREATE TABLE Main (
    pk INTEGER PRIMARY KEY AUTOINCREMENT 
    ,data TEXT  
);

INSERT INTO Main(data) VALUES ('Initial Data'), ('Last Data');

I'd expected to be able query like that:

type Main = {Tag: string; Data : string}

type Repo(db: Db) =
    member val query: int -> AsyncDb<Main list> =
        db.sql """
            SELECT
                'Some Tag' as tag
                ,m.data
            FROM Main as m
            WHERE m.pk = @id
        """

But for some reason, i get stuck with the error below.

SqlFun.Exceptions.CompileTimeException: Error generating function Int32 -> IDataContext -> Main list Async for sql command 
            SELECT
                'Some Tag' as tag
                ,m.data
            FROM Main as m
            WHERE m.pk = @id
        
 ---> System.Exception: No column found for Tag field. Expected: Tag
 ---> System.InvalidOperationException: No coercion operator is defined between types 'System.Byte[]' and 'System.String'.
   at System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
   at System.Linq.Expressions.Expression.Convert(Expression expression, Type type, MethodInfo method)
   at SqlFun.ResultBuilder.buildColumnAccessor[a](ParameterExpression reader, Type colType, a ordinal, Type targetType) in C:\Projekty\FSharp\SqlFun\SqlFun\ResultBuilder.fs:line 267
   at SqlFun.ResultBuilder.getRowBuilderExpression[a](FSharpFunc`2 nextRB, a reader, FSharpMap`2 metadata, String prefix, String fieldName, Type returnType) in C:\Projekty\FSharp\SqlFun\SqlFun\ResultBuilder.fs:line 344

But i realize that when the WHERE clause is changed to WHERE m.pk <> @id that work as expected for some reason. 😕
Maybe the coercion made in compile time is getting some unexpected parser error?

SQLite: Foreign key constraint failed on generation

type UserSession = {
  Session: string
  UserId: int
  ExpiresAt: DateTime
}

let saveUserSession: UserSession -> AsyncDb<unit> =
  sql "INSERT INTO UserSessions (Session, UserId, ExpiresAt) VALUES (@Session, @UserId, @ExpiresAt);"

Unhandled exception. System.TypeInitializationException: The type initializer for '<StartupCode$IssueTracker>.$Login' threw an exception.
 ---> SqlFun.Exceptions.CompileTimeException: Error generating function UserSession -> IDataContext -> unit Async for sql command INSERT INTO UserSessions (Session, UserId, ExpiresAt) VALUES (@Session, @UserId, @ExpiresAt);
 ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at SqlFun.Queries.makeDiagnosticCall@347(FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, String commandText, FSharpList`1 paramDefs)
   at SqlFun.Queries.genExecutor@372[a](FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, FSharpOption`1 commandTimeout, FSharpFunc`2 rowBuilder, Boolean makeDiagnosticCalls, String commandText, a createCommand@372, FSharpList`1 paramDefs, Type returnType)
   at SqlFun.Queries.generateSqlCommandCaller(FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, FSharpOption`1 commandTimeout, FSharpFunc`2 extractParameterNames, FSharpFunc`2 paramBuilder, FSharpFunc`2 rowBuilder, Boolean makeDiagnosticCalls, String commandText, Type t)
   --- End of inner exception stack trace ---
   at SqlFun.Queries.generateSqlCommandCaller(FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, FSharpOption`1 commandTimeout, FSharpFunc`2 extractParameterNames, FSharpFunc`2 paramBuilder, FSharpFunc`2 rowBuilder, Boolean makeDiagnosticCalls, String commandText, Type t)
   at SqlFun.Queries.sql[t](GeneratorConfig config, String commandText)
   at Sql.sql[a](String commandText) in /home/christian/Programming/issue-tracker/src/IssueTracker/Sql.fs:line 32
   at <StartupCode$IssueTracker>.$Login..cctor() in /home/christian/Programming/issue-tracker/src/IssueTracker/Authentication/Login.fs:line 44
   --- End of inner exception stack trace ---
   at Login.get_handler()
   at <StartupCode$IssueTracker>.$Program.main@() in /home/christian/Programming/issue-tracker/src/IssueTracker/Program.fs:line 23

Question: How to handle composite paged queries

Is there any suggestions in how to handle paged queries with templates in other words offset and limit (or whatever the exact clauses are for the given database)
Thanks.

Question: sample that controls the SqlConnection

Hi,

I noticed that in all your samples that I've seen (see: https://jacentino.github.io/SqlFun/Configuration) you create a function let createConnection () = new SqlConnection(connectionString) and everything flows from here. But what if I want to update multiple databases? Do I have to add the set of functions createConnection/generatorConfig/sql/proc for each database in separate modules? It kind of defeats the purpose of functional programming which promotes re-usability. But more likely I am missing something.

Do you have a sample where I have a SqlConnection object and I pass it around to execute different statements?

It tried this:

let cn = new SqlConnection(connectionString)
cn.Open()

let getDataList (cn: SqlConnection) : DbAction<DataList array> =
  let config = createDefaultConfig (fun () -> cn) 
  Queries.sql config "select dl.ListCode, dl.ListName from dbo.DataList dl order by 1"


let data2 = getDataList cn |> DbAction.run (fun () -> cn)
data2 |> Array.length |> printfn "%d"

but I am getting some weird error:

 ---> System.InvalidOperationException: The connection was not closed. The connection's current state is open.
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at SqlFun.Queries.getResultMetadata@354(FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, String commandText, FSharpList`1 paramDefs)
   at SqlFun.Queries.genExecutor@368[a](FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, FSharpOption`1 commandTimeout, FSharpFunc`2 rowBuilder, Boolean makeDiagnosticCalls, String commandText, a createCommand@368, FSharpList`1 paramDefs, Type returnType)
   at SqlFun.Queries.generateSqlCommandCaller(FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, FSharpOption`1 commandTimeout, FSharpFunc`2 extractParameterNames, FSharpFunc`2 paramBuilder, FSharpFunc`2 rowBuilder, Boolean makeDiagnosticCalls, String commandText, Type t)

I have to admit, I don't think I understand how SqlFun works. My expectation was to define queries and then run them anyway I want it against a connection object. But it looks like the code tries to figure out at runtime metadata information about the query (structure of the result, parameters) and that fails.

After tweaking the code, this works:

let cn = new SqlConnection(designTimeConnectionString)


let getDataList (cn: SqlConnection) : DbAction<DataList array> =
  let config = createDefaultConfig (fun () -> new SqlConnection(connectionString)) 
  Queries.sql config "select dl.ListCode, dl.ListName from dbo.DataList dl order by 1"


let data2 = getDataList cn |> DbAction.run (fun () -> cn)

It looks like the connection for the GeneratorConfig has to be independent of the connection that is used to run the queries. I guess it makes sense.

Thanks

Upgrade to net5 and use of the new SqlDataClient?

Is it possible to upgrade this project to net5 and use of the Microsoft.Data.SqlClient? Then you could us this on any OS.

Postgresql: The given key was not present in the dictionary.

Unhandled exception. SqlFun.Exceptions.CompileTimeException: Error generating function FeedQuery -> IDataContext -> Post list Async for sql command select * from feeds where name = @FeedName
 ---> System.Exception: No column found for FeedName field. Expected: FeedName
 ---> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
   at Microsoft.FSharp.Collections.MapTreeModule.throwKeyNotFound[a]() in D:\a\_work\1\s\src\FSharp.Core\map.fs:line 187
   at Microsoft.FSharp.Collections.MapTreeModule.find[TKey,TValue](IComparer`1 comparer, TKey k, MapTree`2 m) in D:\a\_work\1\s\src\FSharp.Core\map.fs:line 196
   at SqlFun.ResultBuilder.getRowBuilderExpression[a](FSharpFunc`2 nextRB, a reader, FSharpMap`2 metadata, String prefix, String fieldName, Type returnType)
   --- End of inner exception stack trace ---
   at SqlFun.ResultBuilder.getRowBuilderExpression[a](FSharpFunc`2 nextRB, a reader, FSharpMap`2 metadata, String prefix, String fieldName, Type returnType)
   at [email protected](Type returnType)
   at SqlFun.ResultBuilder.getRowBuilderExpression[a](FSharpFunc`2 nextRB, a reader, FSharpMap`2 metadata, String prefix, String fieldName, Type returnType)
   at [email protected](Type returnType)
   at SqlFun.ResultBuilder.buildRow(FSharpFunc`2 rowBuilder, FSharpMap`2 metadata, Type returnType)
   at [email protected](FSharpMap`2 metadata, Type returnType)
   at SqlFun.ResultBuilder.buildOneResultSet$cont@417(Boolean isAsync, FSharpMap`2 collectionResultBuilders, FSharpMap`2 metadata, Type returnType, FSharpFunc`2 buildRow, Unit unitVar)
   at SqlFun.ResultBuilder.buildOneResultSet@414(FSharpFunc`2 rowBuilder, Boolean isAsync, FSharpMap`2 collectionResultBuilders, FSharpMap`2 metadata, Type returnType)
   at SqlFun.ResultBuilder.generateResultBuilder(FSharpFunc`2 rowBuilder, FSharpList`1 metadata, Type returnType, Boolean isAsync)
   at SqlFun.Queries.genExecutor@372[a](FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, FSharpOption`1 commandTimeout, FSharpFunc`2 rowBuilder, Boolean makeDiagnosticCalls, String commandText, a createCommand@372, FSharpList`1 paramDefs, Type returnType)
   at SqlFun.Queries.generateSqlCommandCaller(FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, FSharpOption`1 commandTimeout, FSharpFunc`2 extractParameterNames, FSharpFunc`2 paramBuilder, FSharpFunc`2 rowBuilder, Boolean makeDiagnosticCalls, String commandText, Type t)
   --- End of inner exception stack trace ---
   at SqlFun.Queries.generateSqlCommandCaller(FSharpFunc`2 createConnection, FSharpFunc`2 createCommand, FSharpOption`1 commandTimeout, FSharpFunc`2 extractParameterNames, FSharpFunc`2 paramBuilder, FSharpFunc`2 rowBuilder, Boolean makeDiagnosticCalls, String commandText, Type t)
   at SqlFun.Queries.sql[t](GeneratorConfig config, String commandText)
   at Program.sql[a](String commandText) in /home/christian/Programming/YoutubeVideos/RssReader/src/RssReader/Program.fs:line 29
   at <StartupCode$RssReader>.$Program.main@() in /home/christian/Programming/YoutubeVideos/RssReader/src/RssReader/Program.fs:line 77

type FeedQuery = { FeedName: string }

let queryPostsByFeed: FeedQuery -> AsyncDb<Post list> =
  sql "select * from feeds where name = @FeedName"

A question about property mapping

Often times I use postgresql where its idiomatic to use snake_case for column names (PascalCase requires escaping column, tables, and types with quotations) . Is there any way to configure a mapper for snake_case -> PascalCase when reading query results into record types?

Support DateOnly and TimeOnly values

Support the System.DateOnly and System.TimeOnly values that have been available since .net 6.0 for both field reading and parameter writing.

Cancellation Token

Do you have cancellation token support evaluated?

https://devblogs.microsoft.com/azure-sdk/how-to-use-cancellationtokens-to-cancel-tasks-in-the-azure-sdk-for-net/

Thanks 🙏

CG/SQL implementation

Hey !!

sorry to bother you but what do you think about this project or how it would work in a f# project. https://cgsql.dev/

Temporary Tables

Hi, I started using SqlFun yesterday. It works great so far for Select and Upsert queries.

Question

Is it possible to use temporary tables with SqlFun? It's throwing an exception, but my query works in Dapper and Azure Data Studio.

ClassName: "System.Data.SqlClient.SqlException",
Message: "Invalid object name '#tempRows'

More Detail

My existing codebase uses Dapper + M$ SQL Server, and includes queries that clone tables from one 'scenario' to another, often using this form:

Create Table #tempRows(
   RowIdNew UniqueIdentifier Not Null,
   RowIdOld UniqueIdentifier Not Null,
);

Insert Into #tempRows(RowIdNew, RowIdOld)
Select NewId() as RowIdNew, RowId as RowIdOld
From Rows
Where ScenarioId=@ScidToClone;

Insert Into Rows (ScenarioId, RowId)
Select @ScidNew, RowIdNew
From #tempRows;

-- Cells copy would go here

Drop Table #tempRows

Dependency Injection Strategies

I have been toying around with SqlFun and have some questions regarding its design choices. Considering the README, this is how you are supposed to write code on it:

module Database =
    // ...
    let createConnection () = new SqliteConnection("Data Source=:memory:")
    let config = createDefaultConfig createConnection
    let runAsync f = AsyncDb.run createConnection f

module Repository =
    open Database
    // ...

    module Account =
        let create: Account -> AsyncDb<Account.Username> =
            sql config "INSERT INTO account (username, password) VALUES (@username, @password) RETURNING username;"

In this case, you can see that the module Repository depends on the variable config from the module Database. In this sense, if I were to configure my string connection, I would have something like this:

module Configuration =
    // imagine some private logic happening here
    let sqlConnection =
        sprintfn "Data Source=%s" config.Database.Host

module Database =
   open Configuration
   // ...
   let createConnection () = new SqliteConnection(sqlConnection)

I couldn't figure it out a way to use this library without depending on module state. Is there another way to manage the database connection?

Thanks for this awesome library, by the way!

AsyncDb helper functions

Last week I ripped out the last Dapper query in my company's codebase, so we're now entirely using SqlFun. Some helper functions seem to be missing, so I built my own. Either SqlFun should include these functions, or I'm doing something wrong.

SqlFun/SqlFun/DbBuilder.fs module AsyncDb defines a useful 'map' function, but not much else.

let map (f: 't1 -> 't2) (v: AsyncDb<'t1>): AsyncDb<'t2> =
   fun ctx -> async {
      let! x = v ctx
      return f x
   }

The SqlFun documentation performs many transforms on multi-result queries, but doesn't remove the Ids used for the joins when denormalizing.

let getBlogWithPosts: int -> Blog AsyncDb = 
    sql "select id, name, title, description, owner, 
                createdAt, modifiedAt, modifiedBy 
         from Blog 
         where id = @id;

         select id, blogId, name, title, content, author, 
                createdAt, modifiedAt, modifiedBy, status 
         from post 
         where blogId = @id"
    >> AsyncDb.map (fun b p -> { b with posts = p })

This makes sense for some high performance queries, although I remove the Ids when denormalizing to reduce payload size.

type Cell = { Year: int16; Amount: float }
type Row = { Id: Guid; Cells: Cell[] }
type CellDb =
  { RowId: Guid; Year: int16; Amount: float } with
  static member FromDb(c: CellDb): Cell =
     { Year=c.Year; Amount=c.Amount }

let GetRows: Guid -> AsyncDb<Row seq> =
  let key1 (r: Row) = r.Id
  let key2 (c: CellDb) = c.RowId
  let combine: Row -> CellDb seq -> Row = fun r cs ->
     { r with Cells=cs |> Array.ofSeq |> Array.map CellDb.FromDb }
  Db.Sql "
  Select rows From TableA Where Id=@Id;
  Select cells From TableB Where RowId=@Id;"
  >> Db.Map (Db.Join key1 key2 combine)

For querying records with more complicated nested fields, I find it easier to use nested queries:

let private getA: CardId -> AsyncDb<TypeA[]> = sql ""
let private getB: CardId -> AsyncDb<TypeB[]> = sql ""
let private cardGet: UserId -> AsyncDb<CardDb[]> = sql ""

let private withFields(card: CardDb) = asyncdb {
  let! a = getA card.Id
  let! b = getB card.Id
  return { card with A=a; B=b }
}
let CardGet(userId: UserId): AsyncDb<Card[]> =
  cardGet userId
  |> Db.BindSequential withFields
  |> Db.MapArray Card.FromDb

These functions don't exist in the AsyncDb module, so I'm using an in-house helper module called 'Db':

let Bind<'t1,'t2>(f: 't1 -> AsyncDb<'t2>)(x: AsyncDb<'t1>): AsyncDb<'t2> =
   fun ctx -> async {
      let! v = x ctx
      return! (f v) ctx
   }

let MapArray<'t1,'t2>(f: 't1 -> 't2)(x: AsyncDb<'t1[]>): AsyncDb<'t2[]> =
   fun ctx -> async {
      let! vs = x ctx
      return vs |> (Array.map f)
   }

// WARNING -- this requires a SEQUENCE, not an ARRAY
let Sequential<'t1,'t2>(f: 't1 -> AsyncDb<'t2>)(items: seq<'t1>): AsyncDb<'t2[]> =
   fun ctx ->
      items
      |> Seq.map (fun x -> (f x) ctx)
      |> Async.Sequential

let BindSequential<'t1,'t2>(f: 't1 -> AsyncDb<'t2>) = Bind (Sequential f)

Is this a gap in SqlFun? If so, are you interested in contributions?

Nuget package

Hi,

Are you planning to publish your library on nuget.org?