Type-safe generative SQL Query Builder based on you DB schema.
// SQLi generates Predefined Queries
newUserId := uuid.MustParse("ebb5dd71-1214-40dc-b602-bb9af74b3aae")
id, err := InsertIntoUserReturningID( // This is a generated function
ctx,
db,
yourdb.InsertableUserModel{
ID: newUserId,
Name: "User 1",
},
)
println(id) // ebb5dd71-1214-40dc-b602-bb9af74b3aae
// ... and you can also use Dynamic Queries
insertQuery, err := sqli.Query(
sqli.INSERT_INTO(yourdb.User),
sqli.VALUES(
sqli.ValueSet(
VALUE(yourdb.User.ID, newUserId), // VALUE function will validate the value UUID type
VALUE(yourdb.User.Name, "User 1"), // And this will validate the value string type
),
),
sqli.RETURNING(
yourdb.User.ID,
),
)
println(insertQuery.SQL) // `INSERT INTO "user" (VALUES ($1, $2));`
println(insertQuery.Args) // [ebb5dd71-1214-40dc-b602-bb9af74b3aae, User 1]
row := db.QueryRowxContext(ctx, insertQuery.SQL, insertQuery.Args...)
var id uuid.UUID
err = row.Scan(&id)
println(id) // ebb5dd71-1214-40dc-b602-bb9af74b3aaego install github.com/Dionid/sqli/cmd/sqli@latestDownload from Release
git clone git@github.com:Dionid/sqli.git
cd sqli
make build
# you will find executables in ./dist folderSQLi generates:
- Constants
- Predefined Queries
- Dynamic Queries
All tables as types and names can be found in constants.sqli.go
type TablesSt struct {
Office string `json:"office" db:"office"`
OfficeUser string `json:"office_user" db:"office_user"`
User string `json:"user" db:"user"`
}
var Tables = TablesSt{
Office: "office",
OfficeUser: "office_user",
User: "user",
}
// Named "T" for shortness
var T = TablesThis functions are generated for each table in your DB schema and capture most common queries, like Insert, Update, Delete, Select by Primary key / Unique key / etc.
Lets look at the example of generated function for user table:
newUserId := uuid.MustParse("ebb5dd71-1214-40dc-b602-bb9af74b3aae")
id, err := InsertIntoUserReturningID(
ctx,
db,
InsertableUserModel{
ID: newUserId,
Name: "User 1",
},
)
println(id) // ebb5dd71-1214-40dc-b602-bb9af74b3aae
// Not lets select it by primary key
userByPrimaryKey, err := SelectUserByID(
ctx,
db,
id,
)
println(userByPrimaryKey) // {ID: "ebb5dd71-1214-40dc-b602-bb9af74b3aae", Name: "User 1"}
// Now lets update it
err = UpdateUserByID(
ctx,
db,
id,
UpdatableOfficeModel{
Name: "Updated User 1",
}
)
// And delete
err = DeleteFromUserByID(
ctx,
db,
id,
)- Returning Result
- Returning All
- Returning Primary key
- Returning Unique key
- Primary
- By Primary compound
- Sequence
- Primary
- By Primary compound
- Sequence
- By Primary
- By Primary compound
- By Sequence
This is a dynamic query builder, that allows you to build queries in a type-safe way, using the generated constants and functions.
import (
. "github.com/Dionid/sqli"
. "github.com/Dionid/sqli/examples/pgdb/db"
)
// Insert user
newUserId := uuid.MustParse("ebb5dd71-1214-40dc-b602-bb9af74b3aae")
insertQuery, err := Query(
INSERT_INTO(User),
VALUES(
ValueSet(
VALUE(User.ID, newUserId), // VALUE function will validate the value UUID type
VALUE(User.Name, "User 1"), // And this will validate the value string type
),
),
RETURNING(
User.ID,
),
)
// `insertQuery` will have raw SQL and raw arguments, that can be used to execute the query
println(insertQuery.SQL) // `INSERT INTO "user" (VALUES ($1, $2));`
println(insertQuery.Args) // [ebb5dd71-1214-40dc-b602-bb9af74b3aae, User 1]
// Now lets execute the query
row := db.QueryRowxContext(ctx, insertQuery.SQL, insertQuery.Args...)
var id uuid.UUID
err = row.Scan(&id)
println(id) // ebb5dd71-1214-40dc-b602-bb9af74b3aae
// Select it by primary key
selectQuery, err := Query(
SELECT(
User.AllColumns(), // *
),
FROM(User),
WHERE(
EQUAL(User.ID, id),
),
)
println(selectQuery.SQL) // SELECT * FROM "user" AS "user" WHERE "user"."id" = $1;
println(selectQuery.Args) // [ebb5dd71-1214-40dc-b602-bb9af74b3aae]
row := db.QueryRowxContext(ctx, selectQuery.SQL, selectQuery.Args...)
user := &UserModel{} // Also generated by SQLi
err = row.Scan(
&user.ID,
&user.Name,
)
println(user) // {ID: "ebb5dd71-1214-40dc-b602-bb9af74b3aae", Name: "User 1"}
// Now lets update it
query, err := Query(
UPDATE(User),
SET(
SET_VALUE(User.Name, "Updated User 1"),
),
WHERE(
EQUAL(User.ID, id),
),
)
println(query.SQL) // UPDATE "user" SET "user"."name" = $1 WHERE "user"."id" = $2;
println(query.Args) // [Updated User 1, ebb5dd71-1214-40dc-b602-bb9af74b3aae]
row := db.ExecContext(ctx, query.SQL, query.Args...)
println(row.RowsAffected()) // 1
// And delete
query, err := Query(
DELETE_FROM(User),
WHERE(
EQUAL(User.ID, id),
),
)
println(query.SQL) // DELETE FROM "user" WHERE "user"."id" = $1;
println(query.Args) // [ebb5dd71-1214-40dc-b602-bb9af74b3aae]
row := db.ExecContext(ctx, query.SQL, query.Args...)
println(row.RowsAffected()) // 1For more examples, see the examples folder.
Every function is generated for each table in your DB schema and typed according to the table schema.
EQUAL(User.ID, 123) // This will not compile, because ID is UUID
EQUAL(User.ID, uuid.MustParse("ebb5dd71-1214-40dc-b602-bb9af74b3aae")) // This will compileMost Query Builders uses dot notation to build queries, like db.Table("user").Where("id = ?", id),
but SQLi uses functional approach Query(SELECT(User.ID), FROM(User) WHERE(EQUAL(User.ID, id)) where
each function needs to return a Statement struct, that contains SQL and arguments.
That gives us ability to extend the library and add new functions, like JSON_AGG, SUM, COUNT, etc.
WITHOUT even commiting to the library itself.
Example:
We got some database that has operators like MERGE table WHERE ... COLLISION free | restricted, but we
don't has this functions in SQLi, so we can create our own functions and use them in the query builder:
func MERGE(table NameWithAliaser) string {
stmt := fmt.Sprintf("FROM %s", table.GetNameWithAlias())
return sqli.Statement{
SQL: stmt,
Args: []interface{}{},
}
}
func COLLISION_FREE() string {
return sqli.Statement{
SQL: "COLLISION free",
Args: []interface{}{},
}
}
func COLLISION_RESTRICTED() string {
return sqli.Statement{
SQL: "COLLISION restricted",
Args: []interface{}{},
}
}
func main() {
query := Query(
MERGE("table"),
WHERE(
EQUAL("id", 1),
),
COLLISION_FREE(),
)
fmt.Println(query.SQL) // MERGE table WHERE id = 1 COLLISION free
}So you don't even need to wait for the library to implement this functions, you can do it yourself.
- Upsert
- SUM
- JSON_AGG
- InsertOnConflict
- CopySimple
- Add pgx types
- Safe-mode (validating every field)