- Notifications
You must be signed in to change notification settings - Fork2
SQL query builder for Go. Might also be used as an extension for Squirrel.
License
aldy505/bob
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
Bob is an SQL builder library initially made as an extension forSquirrelwith functionality likeKnex (from the Node.js world). Squirrel itselfdoesn't provide other types of queries for creating a table, upsert,and some other things. Bob is meant to fill those gaps.
The difference between Bob and Squirrel is that Bob is solely a query builder.The users have to execute and manage the SQL connection themselves.Meaning there is no ExecWith() function implemented in Bob, as you canfind it on Squirrel.
The purpose of an SQL query builder is to prevent any typo or mistypeson the SQL queries. Although also with that reason, Bob might not alwayshave the right query for you, depending on what you are doing with theSQL query. It might sometimes be better for you to write the SQL queryyourself, if your problem is specific and needs some micro-tweaks.
With that being said, I hope you enjoy using Bob and consider starring orreporting any issues regarding the usage of Bob in your projects.
Oh, and of course, heavily inspired byBob the Builder.
import"github.com/aldy505/bob"
Like any other Go projects when you're using Go modules, just put thattext right there on the top of your projects, dogo mod tidy andyou are good to go.
Either way, I'm not 100% confident enough to say that this thing isproduction ready. But, the way I see it, it's good enough to be usedon a production-level application. In fact, I'm using it on one of mycurrent projects that's getting around 100-200 hits per day.
If you have any feature request or improvement ideas for the project,please kindly open an issue
import"github.com/aldy505/bob"funcmain() {// Note that CREATE TABLE doesn't returns args params.sql,_,err:=bob.CreateTable("tableName").// The first parameter is the column's name.// The second parameter and so on forth are extras.StringColumn("id","NOT NULL","PRIMARY KEY","AUTOINCREMENT").StringColumn("email","NOT NULL","UNIQUE").// See the list of available column definition types through pkg.go.dev or scroll down below.TextColumn("password").// Or add your custom type.AddColumn(bob.ColumnDef{Name:"tableName",Type:"customType",Extras: []string{"NOT NULL"}}).ToSql()iferr!=nil {// handle your error }}
Available column definition types (please be aware that some only works on certain database):
StringColumn()- Default toVARCHAR(255)TextColumn()- Default toTEXTUUIDColumn()- Defaults toUUIDBooleanColumn()- Defaults toBOOLEANIntegerColumn()- Defaults toINTEGER. Postgres and SQLite only.IntColumn()- Defaults toINT. MySQL and MSSQL only.RealColumn()- Defaults toREAL. Postgres, MSSQL, and SQLite only.FloatColumn()- Defaults toFLOAT. Postgres and SQLite only.DateTimeColumn()- Defaults toDATETIME.TimeStampColumn()- Defaults toTIMESTAMP.TimeColumn()- Defaults toTIME.DateColumn()- Defaults toDATE.JSONColumn()- Defaults toJSON. MySQL and Postgres only.JSONBColumn()- Defaults toJSONB. Postgres only.BlobColumn()- Defaults toBLOB. MySQL and SQLite only.
For any other types, please useAddColumn().
Another builder ofbob.CreateTableIfNotExists() is also available.
funcmain() {sql,_,err:=bob.CreateIndex("idx_email").On("users").// To create a CREATE UNIQUE INDEX ...Unique().// Method "Spatial()" and "FullText()" are also available.// You can specify as many columns as you like.Columns(bob.IndexColumn{Name:"email",Collate:"DEFAULT",Extras: []string{"ASC"}}).ToSql()iferr!=nil {log.Fatal(err) }}
Another builder ofbob.CreateIndexIfNotExists() is also available.
funcmain() {sql,args,err:=bob.HasTable("users").ToSql()iferr!=nil {log.Fatal(err) }}
funcmain() {sql,args,err:=bob.HasColumn("email").ToSql()iferr!=nil {log.Fatal(err) }}
funcmain() {sql,_,err:=bob.DropTable("users").ToSql()iferr!=nil {log.Fatal(err) }// sql = "DROP TABLE users;"sql,_,err=bob.DropTableIfExists("users").ToSql()iferr!=nil {log.Fatal(err) }// sql = "DROP TABLE IF EXISTS users;"sql,_,err=bob.DropTable("users").Cascade().ToSql()iferr!=nil {log.Fatal(err) }// sql = "DROP TABLE users CASCADE;"sql,_,err=bob.DropTable("users").Restrict().ToSql()iferr!=nil {log.Fatal(err) }// sql = "DROP TABLE users RESTRICT;"}
funcmain() {sql,_,err:=bob.Truncate("users").ToSql()iferr!=nil {log.Fatal(err) }}
funcmain() {sql,_,err:=bob.RenameTable("users","people").ToSql()iferr!=nil {log.Fatal(err) }}
funcmain() {sql,args,err:=bob.// Notice that you should give database dialect on the second params.// Available database dialect are MySQL, PostgreSQL, SQLite, and MSSQL.Upsert("users",bob.MySQL).Columns("name","email","age").// You could do multiple Values() call, but I'd suggest to not do it.// Because this is an upsert function, not an insert one.Values("Thomas Mueler","tmueler@something.com",25).Replace("age",25).ToSql()// Another example for PostgreSQLsql,args,err=bob.Upsert("users",bob.PostgreSQL).Columns("name","email","age").Values("Billy Urtha","billu@something.com",30).Key("email").Replace("age",40).ToSql()// One more time, for MSSQL / SQL Server.sql,args,err=bob.Upsert("users",bob.MSSQL).Columns("name","email","age").Values("George Rust","georgee@something.com",19).Key("email","georgee@something.com").Replace("age",18).ToSql()}
Default placeholder is a question mark (MySQL-like). If you want to change it, simply use something like this:
funcmain() {// Option 1sql,args,err:=bob.HasTable("users").PlaceholderFormat(bob.Dollar).ToSql()iferr!=nil {log.Fatal(err) }// Option 2sql,args,err=bob.HasTable("users").ToSql()iferr!=nil {log.Fatal(err) }correctPlaceholder:=bob.ReplacePlaceholder(sql,bob.Dollar)}
Available placeholder formats:
bob.Question-INSERT INTO "users" (name) VALUES (?)bob.Dollar-INSERT INTO "users" (name) VALUES ($1)bob.Colon-INSERT INTO "users" (name) VALUES (:1)bob.AtP-INSERT INTO "users" (name) VALUES (@p1)
import ("context""log""strings""github.com/aldy505/bob""github.com/jackc/pgx/v4")funcmain() {db:=pgx.Connect()// Check if a table existssql,args,err=bob.HasTable("users").PlaceholderFormat(bob.Dollar).ToSql()iferr!=nil {log.Fatal(err) }varhasTableUsersboolerr=db.QueryRow(context.Background(),sql,args...).Scan(&hasTableUsers)iferr!=nil {iferr==bob.ErrEmptyTablePg {hasTableUsers=false }else {log.Fatal(err) } }if!hasTableUsers {// Create "users" tablesql,_,err:=bob.CreateTable("users").IntegerColumn("id","PRIMARY KEY","SERIAL").StringColumn("name","NOT NULL").TextColumn("password","NOT NULL").DateColumn("created_at").ToSql()iferr!=nil {log.Fatal(err) }_,err=db.Query(context.Background(),splitQuery[i])iferr!=nil {log.Fatal(err) }// Create another table, this time with CREATE TABLE IF NOT EXISTSsql,_,err:=bob.CreateTableIfNotExists("inventory").UUIDColumn("id","PRIMARY KEY").IntegerColumn("userID","FOREIGN KEY REFERENCES users(id)").JSONColumn("items").IntegerColumn("quantity").ToSql()iferr!=nil {log.Fatal(err) }_,err=db.Query(context.Background(),inventoryQuery[i])iferr!=nil {log.Fatal(err) } }}
bob.CreateTable(tableName)- Basic SQL create tablebob.CreateTableIfNotExists(tableName)- Create table if not existsbob.CreateIndex(indexName)- Basic SQL create indexbob.CreateIndexIfNotExists(tableName)- Create index if not existsbob.HasTable(tableName)- Checks if column exists (return error if false, check example above for error handling)bob.HasColumn(columnName)- Check if a column exists on current tablebob.DropTable(tableName)- Drop a table (drop table "users")bob.DropTableIfExists(tableName)- Drop a table if exists (drop table if exists "users")bob.RenameTable(currentTable, desiredName)- Rename a table (rename table "users" to "people")bob.Truncate(tableName)- Truncate a table (truncate "users")bob.Upsert(tableName, dialect)- UPSERT function (insert into "users" ("name", "email") values (?, ?) on duplicate key update email = ?)
Contributions are always welcome! As long as you add a test for your changes.
Bob is licensed underMIT license
About
SQL query builder for Go. Might also be used as an extension for Squirrel.
