bob/README.md

Bob - SQL Query Builder

I really need a create table SQL builder, and I can't find one. So, like everything else, I made one. Heavily inspired by Squirrel and Knex. And of course, I still use Squirrel for other types of queries (insert, select, and all), think this package as an extension for Squirrel.

Oh, and of course, heavily inspired by Bob the Builder.

import "github.com/aldy505/bob"

Usage

It's not ready for production yet. But, the API is probably close to how you'd do things on Squirrel. This is an example for using with pgx.

import (
  "context"
  "log"
  "strings"

  "github.com/aldy505/bob"
  "github.com/jackc/pgx/v4"
)

func main() {
  db := pgx.Connect()

  // Check if a table is exists
  sql, args, err = bob.HasTable("users").PlaceholderFormat(bob.Dollar).ToSql()
  if err != nil {
    log.Fatal(err)
  }

  var hasTableUsers bool
  err = db.QueryRow(context.Background(), sql, args...).Scan(&hasTableUsers)
  if err != nil {
    if err.Error() == "no rows in result set" {
      hasTableUsers = false
    } else {
      log.Fatal(err)
    }
  }

  if !hasTableUsers {
    // Create "users" table
    // Note that this will return multiple query in a single string.
    sql, _, err := bob.CreateTable("users").
      Columns("id", "email", "name", "password", "date").
      Types("varchar(36)", "varchar(255)", "varchar(255)", "text", "date").
      Primary("id").
      Unique("email")
      ToSql()
    if err != nil {
      log.Fatal(err)
    }

    // If you don't do this, you will get the error:
    // ERROR: cannot insert multiple commands into a prepared statement (SQLSTATE 42601)
    splitQuery := strings.Split(sql, ";")
    for i := range splitQuery {
      _, err = db.Query(context.Background(), splitQuery[i])
      if err != nil {
        log.Fatal(err)
      }
    }

    // Create another table, this time with CREATE TABLE IF NOT EXISTS
    sql, _, err := bob.CreateTableIfNotExists("inventory").
      Columns("id", "userID", "items", "quantity").
      Types("varchar(36)", "varchar(36)", "json", "int").
      Primary("id").
      ToSql()
    if err != nil {
      log.Fatal(err)
    }
    
    inventoryQuery := strings.Split(sql, ";")
    for i := range inventoryQuery {
      _, err = db.Query(context.Background(), inventoryQuery[i])
      if err != nil {
        log.Fatal(err)
      }
    }
  }
}

Contributing

Contributions are always welcome! As long as you add a test for your changes.

License

Bob is licensed under MIT license