jacentino / sqlfun Goto Github PK
View Code? Open in Web Editor NEWIdiomatic data access for F#
License: MIT License
Idiomatic data access for F#
License: MIT License
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?
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
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.
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
Is it possible to upgrade this project to net5 and use of the Microsoft.Data.SqlClient? Then you could us this on any OS.
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"
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 the System.DateOnly and System.TimeOnly values that have been available since .net 6.0 for both field reading and parameter writing.
Do you have cancellation token support evaluated?
Thanks ๐
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/
Hi, I started using SqlFun yesterday. It works great so far for Select and Upsert queries.
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'
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
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!
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?
Hi,
Are you planning to publish your library on nuget.org?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.