Bob Documentation

Your SQL, simplified

Welcome to the Bob docs! Here you'll find clear guides, concise examples, and everything you need to get started with Bob—a lightweight, declarative DSL for SQL.
Browse the sections below to learn how to define tables, use intuitive data types, and run powerful database queries faster, easier, and safer than ever.

How to define tables

Tables in Bob define the structure of your data. Each table consists of a name and a set of fields, where every field has a type and may include modifiers such as unique, index, required, or optional. Use tables to describe how your data is organized, the types it holds, and its relations to other entities. This section explains how to create tables and introduces the supported data types.

Supported Types

Numerics

int
int8
int16
int32
int64
float32
float64

Text

string
string8
string16
string32
string64
text
blob

Aliases

id
- primary key
- default auto_increment
current
- timestamp
- default current timestamp

Booleans

boolean

Temporal

time
date
datetime
timestamp

# Define a 'Users' table
table Users {
  # Primary identifier (id type)
  id id                               
  # Name field (string type), default value is "anon"
  name string = "anon"                
  # Email must be unique, indexed, and required
  email string unique index required  
  # Created_at field uses the 'current' temporal type, defaults to current time
  # using 'current' type is equal to timestamp = @now
  created_at current                  
  # Optional foreign key referencing the 'Profile' table
  Profile id optional                 
}

# Example of a related 'Profile' table
table Profiles {
  avatar string                    
}

How to define getters

Getters in Bob are used to retrieve data from tables in a flexible and expressive way. You can specify exactly which fields you want, add conditions to filter your results, and even traverse relationships between tables by nesting getters. Getters allow for aliasing, calculated fields, filtering, relationship traversal, and grouping/aggregation. This section explains how to use the get statement to obtain the data you need, including how to group and aggregate records in your queries.

# Retrieve fields from the 'Users' table
get Users {
  id
  name
  email

  # An alias is defined using a name before the field
  last_name: family_name

  # 'meet' is a calculated field using a function. Here it concatenates "Hello " with the user's name
  # note: you still cannot reference fields previously saved as an alias.
  meet: concat("Hello ", name)

  # Filter users with a specific email
  if email = "test@test.com"

  # You can nest getters to retrieve related data 
  profile: get Profiles {
    if id = User->Profiles.id
    if avatar != "default.png"
  }

  # You can also use a join-like arrow syntax (->) to traverse relationships;
  # By default, the connection between tables uses the 'id' field.
  -> Profiles { 
    if avatar != "default.png"  
  }

  # You could also specify the join field
  -> Posts {
    if upvotes > 100
  }
}

# GROUP BY and aggregation: 
# You can group results and perform calculations using group and aggregate functions (see playground example)
get Posts {
  rating
  total_posts: count(id)
  group rating
  # Under a group condition, 'if' behaves like a SQL HAVING clause, filtering the grouped results.
  if total_posts > 10
}

# If you do an empty query, by default all fields are retrieved
get Users {}

How to insert

Inserts in Bob are used to add new data into your tables. You can insert a single record by providing field values, or insert many records at once by specifying the fields and multiple rows of values.

# Insert a single record into 'User'
new Users {
  name  "John Doe"
  email "johndoe@test.com"
  age   18
}

# Insert multiple records into 'User'
new Users name email age {
  "John Doe" "johndoe@test.com" 18
  "Mary Sue" "marysue@test.com" 20
}

How to set data

Set in Bob is used to update fields in records that already exist in your tables. You can specify which fields to change and add conditions to update only certain records.

# Update the 'name' field for users whose current name is "Anon"
set Users {
  name "Public"
  if name = "Anon"
}

How to delete data

Delete in Bob removes records from your tables. You can specify conditions to delete only certain records.

# Delete users whose current id is 1
delete Users {
  if id = 1
}

How to use raw SQL

**The `raw` block in Bob lets you write and embed direct SQL statements in your .bob files.** This is useful when you need to run SQL queries that aren't directly supported by Bob's standard syntax, or you want precise control over the database operations.

# Use the raw block to run a custom SQL query
raw {
    SELECT * FROM User;
}