Bob: Go SQL Access Toolkit

Links

• Documentation
• Reference

About

Bob is a set of Go packages and tools to work with SQL databases.

Bob’s philosophy centres around the following:

1. Correctness: Things should work correctly. Follow specifications as closely as possible.
2. Convenience (not magic): Bob provides convenient ways to perform actions, it does not add unexplainable magic, or needless abstraction.
3. Cooperation: Bob should work well with other tools and packages as much as possible, especially the standard library.

Bob can be progressively adopted from raw SQL query strings, to fully typed queries with models and factories generated for your database.

Components of Bob

Bob consists of several components that build on each other for the full experience.

1. Query Builder
2. SQL Executor for convenient scanning of results
3. Models for convenient database queries
4. Code generation of Models and Factories from your database schema
5. Code generation of Queries similar to sqlc.

Check out the documentation for more information.

Support

	Queries	Models	ORM Gen	Factory Gen	Query Gen
Postgres	✅	✅	✅	✅	✅
MySQL/MariaDB	✅	✅	✅	✅
SQLite	✅	✅	✅	✅	✅

Comparisons

1. Bob vs GORM
2. Bob vs Ent
3. Bob vs SQLBoiler
4. Bob vs Jet

The layers of Bob

Layer 1: The query builder - Similar to squirrel

This is just a fluent query builder that has no concept of your DB, and by extension cannot offer any type-safety.

The main reason, I consider it better than most alternatives is that since each dialect is hand-crafted, it can support building ANY query for that dialect.

However, each dialect is also independent, so you don’t have to worry about creating an invalid query.

IMPORTANT: Queries are built using “Query Mods”

psql.Select(
    sm.From("users"), // This is a query mod
    sm.Where(psql.Quote("age").GTE(psql.Arg(21))), // This is also a mod
)

Layer 2: ORM Code Generation - Similar to SQLBoiler

This is where the type safety comes.

A full ORM, and query mods that is based on the database schema. If you use the generated query mods, these will ensure correct type safety.

Here is the above query using generated query-mods.

models.Users.Query(
    models.SelectWhere.Users.Age.GTE(21), // This is type-safe
)

Layer 3: Factory Code Generation - Inspired by Ruby’s FactoryBot

Factories make testing much much easier. Especially when the test depends on a database entry that depends on relations in other tables (e.g. testing comments that rely on posts which in turn rely on users).

With knowledge of the database schema, Bob can generate factories for each table.

// Quickly create a 10 comments (posts and users are created appropriately)
comments, err := f.NewComment().CreateMany(ctx, db, 10)

Layer 4: Generating code for SQL Queries - similar to sqlc

I believe this is the final peice of the puzzle, and extends the type-safety to hand-crafted SQL queries.

For example, you could generate code for the query:

-- UserPosts
SELECT * FROM posts WHERE user_id = $1

This will generate a function UserPosts that takes an int32.

// UserPosts
userPosts, err := queries.UserPosts(1).All(ctx, db)

Then, if you need to, you can add an extra filter to get only published posts.

However whether it is type safe or not depends on if you use the generated mods or not:

// Get only published posts
query := psql.Select(
    UserPosts(1),
    models.PostWhere.Status.EQ("published"), // type-safe
    sm.Where(psql.Quote("posts", "status").Eq(psql.Arg("published"))), // not type-safe
)

Contributing

Thanks to all the people who have contributed to Bob!