Overview

Raiden utilize PostgREST to run query PostgreSQL database. Here is the quick example to get data from bookModel.

internal/controllers/books.go

func (c *GetBooksController) Get(ctx raiden.Context) error {
  var books []models.Books

  err := db.
    NewQuery(ctx).
    From(models.Books{}).
    Select([]string{"id", "name", "isbn"}).
    Eq("isbn", "9786235266008").
    Get(&books)
  // SQL: select id, name, isbn from books where isbn = '9786235266008'
  // URL: /rest/v1/books?select=id,name,isbn&isbn=9786235266008

  if err != nil {
    fmt.Println(err)
    panic("Whoops!")
  }

  fmt.Println(books)
  fmt.Println(books[0].Id)
  fmt.Println(books[0].Name)
  fmt.Println(books[0].Isbn)

  return ctx.SendJson(books)
}

Retrieve Data

.Select()

var books []models.Books

err := db.NewQuery(ctx).
  From(models.Books{}).
  Select([]string{"id", "slug", "name"}). 
  Get(&books)
// SQL: select id, slug, name from books
// URL: /rest/v1/books?select=id,slug,name

Alias

Use : to make an alias of column name with format alias_name:original_name.

var books []models.Books

err := db.NewQuery(ctx).
  From(models.Books{}).
  Select([]string{"id", "title:name"}). 
  Get(&books)
// SQL: select id, name as title from books
// URL: /rest/v1/books?select=id,title:name

.Get() and .Limit()

Retrieve 10 rows of data.

var books []models.Books

err := db.NewQuery(ctx).
  From(models.Books{}).
  Limit(10).
  Get(&books)
// SQL: select * from books limit 10
// URL: "/rest/v1/books?select=*&limit=10"

.Single()

Get single row of data.

book := models.Books{}

err := db.NewQuery(ctx).
  From(models.Books{}).
  Eq("id", 1). 
  Single(&book)
// SQL: select * from books where id = 1 limit 1
// cURL: "/rest/v1/books?select=*&id=eq.1" -H "Accept: application/vnd.pgrst.object+json"

.Eq()

var books []models.Books

err := db.NewQuery(ctx).
  From(models.Books{}).
  Eq("id", 1). 
  Get(&books)
// SQL: select * from books where id = 1
// URL: /rest/v1/books?select=*&id=eq.1

.Neq()

var books []models.Books

err := db.NewQuery(ctx).
  From(models.Books{}).
  Neq("id", 1). 
  Get(&books)
// SQL: select * from books where id != 1
// URL: /rest/v1/books?select=*&id=neq.1

.Lt() & .Lte()

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Lt("pages", 100). 
  Get(&books)
// SQL: select * from books where pages < 100
// URL: /rest/v1/books?select=*&id=lt.100

err = db.
  NewQuery(ctx).
  From(models.Books{}).
  Lte("pages", 100). 
  Get(&books)
// SQL: select * from books where pages <= 100
// URL: /rest/v1/books?select=*&id=lte.100

.Gt() & .Gte()

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Gt("pages", 100). 
  Get(&books)
// SQL: select * from books where pages > 100
// URL: /rest/v1/books?select=*&id=gt.100

err = db.
  NewQuery(ctx).
  From(models.Books{}).
  Gte("pages", 100). 
  Get(&books)
// SQL: select * from books where pages >= 100
// URL: /rest/v1/books?select=*&id=gte.100

.In()

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  In("id", []int{1, 2, 3}). 
  Get(&books)
// SQL: select * from books where id in (1, 2, 3)
// URL: /rest/v1/books?select=*&id=in.(1,2,3)

err = db.
  NewQuery(ctx).
  From(models.Books{}).
  In("slug", []string{"raiden", "supabase"}). 
  Get(&books)
// SQL: select * from books where slug in ('raiden', 'supabase')
// URL: /rest/v1/books?select=*&slug=in.(raiden,supabase)

.NotIn()

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  NotIn("id", []int{1, 2, 3}). 
  Get(&books)
// SQL: select * from books where id not in (1, 2, 3)
// URL: /rest/v1/books?select=*&id=not.in.(1,2,3)

err = db.
  NewQuery(ctx).
  From(models.Books{}).
  NotIn("slug", []string{"raiden", "supabase"}). 
  Get(&books)
// SQL: select * from books where slug not in ('raiden', 'supabase')
// URL: /rest/v1/books?select=*&slug=not.in.(raiden,supabase)

.Like() & .Ilike()

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Like("name", "%rai%"). 
  Get(&books)
// SQL: select * from books where name like '%rai%'
// URL: /rest/v1/books?select=*&name=like.*rai*

err = db.
  NewQuery(ctx).
  From(models.Books{}).
  Ilike("name", "%SuPa%"). 
  Get(&books)
// SQL: select * from books where name ilike '%SuPa%'
// URL: /rest/v1/books?select=*&name=ilike.*SuPa*

.NotLike() & .NotIlike()

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  NotLike("name", "%rai%"). 
  Get(&books)
// SQL: select * from books where name not like '%rai%'
// URL: /rest/v1/books?select=*&name=not.like.*rai*

err = db.
  NewQuery(ctx).
  From(models.Books{}).
  NotIlike("name", "%SuPa%"). 
  Get(&books)
// SQL: select * from books where name not ilike '%SuPa%'
// URL: /rest/v1/books?select=*&name=not.ilike.*SuPa*

.Is() & .NotIs()

Is() and NotIs() only accepts true, false, or nil.

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Is("is_active", true). 
  Get(&books)
// SQL: select * from books where is_active is true
// URL: /rest/v1/books?select=*&is_active=is.true

err = db.
  NewQuery(ctx).
  From(models.Books{}).
  NotIs("is_active", true). 
  Get(&books)
// SQL: select * from books where is_active not is true
// URL: /rest/v1/books?select=*&is_active=not.is.true

.OrderAsc() & .OrderDesc()

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  OrderAsc("created_at"). 
  Get(&books)
// SQL: select * from books order by created_at asc
// URL: /rest/v1/books?select=*&order=created_at.asc

err = db.
  NewQuery(ctx).
  From(models.Books{}).
  OrderDesc("created_at"). 
  Get(&books)
// SQL: select * from books order by created_at desc
// URL: /rest/v1/books?select=*&order=created_at.desc

.Offset()

Skip a number of rows before returning results. Commonly used with .Limit() for pagination.

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Limit(10).
  Offset(20). 
  Get(&books)
// SQL: select * from books limit 10 offset 20
// URL: /rest/v1/books?select=*&limit=10&offset=20

Or Conditions

Use Or variants of filter methods to build OR conditions. Multiple Or calls are grouped together.

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  OrEq("status", "published"). 
  OrEq("status", "draft"). 
  Get(&books)
// URL: /rest/v1/books?select=*&or=(status.eq.published,status.eq.draft)

Available Or methods: .OrEq(), .OrNeq(), .OrLt(), .OrLte(), .OrGt(), .OrGte(), .OrIn(), .OrLike(), .OrIlike().

Not Conditions

Use Not variants to negate filter conditions.

var books []models.Books

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  NotEq("status", "archived"). 
  Get(&books)
// URL: /rest/v1/books?select=*&status=not.eq.archived

Available Not methods: .NotEq(), .NotNeq(), .NotLt(), .NotLte(), .NotGt(), .NotGte(), .NotLike(), .NotIlike().

Aggregate Functions

.Count()

Get the total number of rows matching the query.

count, err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Eq("is_active", true).
  Count() 
// Returns the count of active books

You can specify the count method with CountOptions:

count, err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Count(db.CountOptions{Count: "exact"}) 
// Options: "exact", "planned", "estimated"

.Sum()

var result []map[string]interface{}

err := db.
  NewQuery(ctx).
  From(models.Products{}).
  Sum("price", "total_price"). 
  Get(&result)
// URL: /rest/v1/products?select=total_price:price.sum()

.Avg()

var result []map[string]interface{}

err := db.
  NewQuery(ctx).
  From(models.Products{}).
  Avg("price", "average_price"). 
  Get(&result)
// URL: /rest/v1/products?select=average_price:price.avg()

.Min() & .Max()

var result []map[string]interface{}

err := db.
  NewQuery(ctx).
  From(models.Products{}).
  Min("price", "lowest_price"). 
  Max("price", "highest_price"). 
  Get(&result)
// URL: /rest/v1/products?select=lowest_price:price.min(),highest_price:price.max()

Insert

payload := models.Products{name: "Chair", price: 20}

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Insert(&payload, nil)
// insert into products (name, price) values ('Chair', 20)

Update

payload := models.Products{slug: "raiden"}

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Eq("id", 1).
  Update(payload, nil)
// SQL: update products set slug = 'raiden' where id = 1

Delete

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Eq("id", 1).
  Delete()
// SQL: delete from products where id = 1

Upsert

id1 := int64(6)
name1 := "Cake"
slug1 := "cake"
rating1 := int64(4)

id2 := int64(42)
name2 := "Water"
slug2 := "water"
rating2 := int64(5)

products := []models.Products{
  {
    Id:        id1,
    Name:      &name1,
    Slug:      &slug1,
    Rating:    &rating1,
  },
  {
    Id:        id2,
    Name:      &name2,
    Slug:      &slug2,
    Rating:    &rating2,
  },
}

// Convert to []interface{}
var payload = make([]interface{}, len(products))
for i, v := range teams {
  payload[i] = v
}

opt := db.UpsertOptions{
  OnConflict: db.MergeDuplicates, // or db.IgnoreDuplicates
}

err := db.
  NewQuery(ctx).
  From(models.Books{}).
  Upsert(payload, opt) 

// SQL:
// insert into products (id, name, slug, rating)
// values (6, 'Cake', 'cake', 4), (6, 'Water', 'water', 4)
// on conflict(id)
// do update set
//   price = excluded.price,
//   quantity = excluded.quantity;

Relationship

Get Relations

By using .Preload(), you can get the relation of a model. The first argument is the relation field name on the model struct.

var books []models.Books

res := db.
  NewQuery(ctx).
  From(models.Books{}).
  Preload("Authors"). // models.Authors{}
  Preload("Publishers"). // models.Publishers{}
  Get(&books)

// SQL:
// select books.*, users.*, publishers.* from books
// inner join users on books.author_id = users.id
// inner join publishers on books.published_id = publishers.id;

// URL: /rest/v1/books?select=*,users(*),publishers(*)

Nested Relations

var books []models.Books

res := db.
  NewQuery(ctx).
  From(models.Books{}).
  Preload("Authors.Teams.Orgs").
  Get(&books)

// URL: /rest/v1/books?select=*,users(*,teams(*,orgs(*)))

WARNING

Nested relations support up to 3 levels deep.

Filtering Relations

You can add a where condition to the preloaded relation by passing additional arguments in the format: field, operator, value.

var users []models.Users

err := db.
  NewQuery(ctx).
  From(models.Users{}).
  Preload("Articles", "rating", "eq", "5").
  Get(&users)

// URL: /rest/v1/users?select=*,articles!article_id(*)&articles.rating=eq.5

Multiple Relations

var users []models.Users

err := db.
  NewQuery(ctx).
  From(models.Users{}).
  Preload("Articles").
  Preload("Team").
  Get(&users)

// URL: /rest/v1/users?select=*,articles!article_id(*),team:teams!team_id(*)