Alternatives To Sqlfrags
Project NameStarsDownloadsRepos Using ThisPackages Using ThisMost Recent CommitTotal ReleasesLatest ReleaseOpen IssuesLicenseLanguage
2 hours ago1,744apache-2.0Java
Free universal database tool and SQL client
Directus21,752502 hours ago55September 22, 2022228otherTypeScript
The Modern Data Stack 🐰 — Directus is an instant REST+GraphQL API and intuitive no-code data collaboration app for any SQL database.
Knex17,61418,0962,7882 days ago248August 31, 2022830mitJavaScript
A query builder for PostgreSQL, MySQL, CockroachDB, SQL Server, SQLite3 and Oracle, designed to be flexible, portable, and fun to use.
6 days ago217otherJava
🏆 零代码、全功能、强安全 ORM 库 🚀 后端接口和文档零代码,前端(客户端) 定制返回 JSON 的数据和结构。 🏆 A JSON Transmission Protocol and an ORM Library 🚀 provides APIs and Docs without writing any code.
Usql8,047293 hours ago125August 26, 202262mitGo
Universal command-line interface for SQL databases
2 days ago31gpl-2.0PLpgSQL
Everything about database,business.(Most for PostgreSQL).
2 hours ago5June 09, 202224otherGo
Database CI/CD for DevOps teams. https://www.bytebase.com
Jooq5,4491,1752595 hours ago178September 06, 20221,899otherJava
jOOQ is the best way to write SQL in Java
2 months ago150mitJavaScript
Free, open-source SQL client for Windows and Mac 🦅
8 hours ago107apache-2.0Python
SQL 审核查询平台
Alternatives To Sqlfrags
Select To Compare

Alternative Project Comparisons

SqlFrags - a SQL generator for F#

Build Status

The problem:

Tools like Dapper make it easy to materialize objects, but you need to write SQL strings by hand.

SQL generation is painful and requires careful typing. It can be made easier by adding a static component. SqlFrags is that static component.

F# gives you a nice list syntax, so you provide a list of "Frags" (a distriminated union of SQL fragments you want to have in your query), like so:

open SqlFrags.SqlGen

// declaring some table names for later reference...

let Emp = Table "employee"
let Org = Table "organization"

let upd = [
            "salary", "10"
            "name", "'heimo'"
            "address", "@addressparam"

    WhereS "foo > bar"

let rendered = upd |> Frags.Emit SqlSyntax.Any

This renders to string:

update employee
set salary = 10, name = 'heimo', address = @addressparam
where foo > bar

That should make it a bit harder to screw up.

Simple "select ... from ..." is simple (with Table extension methods):

let fields = [ Emp.Select [ "Salary"; "Name" ] ]
let all = [ Emp.SelectAll ]

You can do nested subqueries:

let nested =
    [ Raw "select * from"
        NestAs("root", [ User.SelectAll ]) ]

This gives you indented, parenthesized SQL (aliased as you specified):

select *
    select *
    from USER_DATA
) root

Simple updates are easy enough to do with existing micro-orms like Dapper.Contrib or PetaPoco. However, you often need to produce complex queries, so you can crank up the difficulty with nesting, aliases etc. Emitting completely illegal SQL is fine, SqlFrags is not one to second guess you - it diligently renders the garbage you feed it:

let query =
    [ Emp.SelectC <| Emp.Cols [ "id"; "name"; "salary"; "team" ]
        SelectAs [ Emp?Foo, "testalias" ]
        From Emp
        WhereS "salary > 1000"
        Many [ Skip
                WhereS "foo > bar"
                Skip ]
        JoinOn(Org.Col "ID", Emp.Col "OrgID", Table "OrgAlias", "")
        Where [ Emp?Company == Org?Id ]
        GroupBy [ "team"; "foo" ]
        OrderBy [ "salary asc"; "hello desc" ] ]

Did you you see that JoinOn? It does:

inner join organization OrgAlias on employee.OrgID=OrgAlias.ID

If you wanted "outer", just pass "outer" as the last argument to JoinOn (empty string defaults to "inner join").

And what are those "Many" and Skip parts? They are provided for convenience, when splicing sublists in programmatically generated queries.

There are some overloaded operators and extension methods to simplify select and where clauses:

        // select stuff with --> and --->
[ Emp --> [ "Salary"; "Name" ] ]
|> rendersTo "select Salary, Name\nfrom employee"

// same with extension method - please use extension methods instead of operators!
[ Emp.Select [ "Salary"; "Name" ] ]
|> rendersTo "select Salary, Name\nfrom employee"

[ Emp ---> [ Emp?Salary; Emp?Name ] ]
|> rendersTo "select employee.Salary, employee.Name\nfrom employee"
// ===^ (where condition without quoting)
    Emp --> ["*"]
    Where [Emp?ID ===^ "@ID"]
] |> rendersTo "select *\nfrom employee\nwhere [email protected]"

// === (where condition with quoting)
    Emp --> ["*"]
    Where [Emp?ID === "jorma"]
] |> rendersTo "select *\nfrom employee\nwhere employee.ID='jorma'"

[ Emp.Delete ]
|> rendersTo "delete from employee"

    Where [Emp?ID.Equals "@bar"]
    Where [Emp?ID.EqualsQ "bar"]
    Where [Emp?ID.EqualsCol Org?ID]
    Where [Emp?ID.In "[1,2]"]
    Where [Emp?ID.Op "<" "12"]

] |> rendersTo "where [email protected]\nwhere employee.ID='bar'\nwhere employee.ID=organization.ID\nwhere employee.ID in [1,2]\nwhere employee.ID < 12"


This README is out of date

Maybe. See the test case for up to date syntax.

Why SqlFrags when there are millions of other SQL generators on the web?

There aren't for .NET. Search for yourself.

Can I use this on C#?

Nope, too tied to F# data structures. Similar "mechanical SQL emission" philosophy for C# is implemented e.g. in sqlkata/querybuilder

What's up with the name?

Yes, this project was earlier known as "Fapper", which turned out to be rather problematic. Enjoy the new name.

What databases does it support?

All of them, but depends. E.g. "Page" fragment won't work in old Oracle versions. If your SQL contains @ like query parameters they won't work with oracle (and I didn't yet do a helper for that). You get the idea. The API has support to branch the rendering based on Sql syntax, but currently only SqlSyntax.Any is used.

Why not use XXX or YYY instead?

SqlFrags allows you to compose queries from fragments. You can create the fragments (or lists of fragments) in functions, assign repeated fragments to variables, etc. This is like creating HTML with Suave.Html, Giraffe ViewModel or Elm.

You don't need to have access to database schema (yet alone live database, like with SqlProvider) to create queries. This helps if you are building software against arbitrary databases (think tools like Django Admin), or where schema is configurable.

You don't need to learn to "trick" the ORM to emit the SQL you want. What you write is what you get.

The codebase is trivial mapping of DU cases to emitted strings:

let rec serializeFrag (syntax: SqlSyntax) frag =
    match frag with
    | SelectS els -> "select " + colonList els
    | Select cols ->
        "select " +
            (cols |> Seq.map (fun c -> c.Str) |> colonList)
    | SelectAs cols ->
        "select " +
            (cols |> Seq.map (fun (c,alias) -> sprintf "%s as %s" c.Str alias) |> colonList)
    | FromS els -> "from " + colonList els
    | From (Table t) -> "from " + t

So, if you want to add something you need, you just do it. Copy the SqlGen.fs to your project, or make a PR and join the SqlFrags family.





Popular Sql Projects
Popular Oracle Projects
Popular Data Processing Categories

Get A Weekly Email With Trending Projects For These Categories
No Spam. Unsubscribe easily at any time.
F Sharp