gopsql

README

Gopsql

This is a library that helps in serializing structs into database queries.

This is for PostgreSQL.

Usage example

models.go

package main

import (
    "fmt"
    "github.com/Tebro/gopsql"
    "errors"
)

type Book struct {
    ID        int       `column_type:"SERIAL primary key" column_skip_insert:"yes"`
    Title     string    `column_type:"varchar(255)"`
    Author    string    `column_type:"varchar(255)"`
    Pages     []Page    `column_skip:"yes"`
}

// Implement Saveable interface
func (b Book) GetID() int {
    return b.ID
}
// Implement Saveable interface
func (b Book) SetID(id int) {
    b.ID = id
}

func (b *Book) Save() error {
    if b.ID > 0 { //Exists, update
        return gopsql.UpdateExisting(*b)
    }
    // New
    return gopsql.InsertNew(*b).Scan(&b.ID)
}

func (b *Book) Find() error {
    rows, err := gopsql.GetFiltered(*b, "ID", string(b.ID))
    if err != nil {
        return err
    }

    defer rows.Close()
    if rows.Next(){
        err := rows.Scan(&b.ID, &b.Title, &b.Author)
        if err != nil {
            return err
        }
        b.Pages, err = (&Page{}).GetAllForBook(b.ID)
        return err
    }
    return errors.New("No matching book found")
}


type Page struct {
    ID         int      `column_type:"SERIAL primary key" column_skip_insert:"yes" column_order_by:"yes"`
    BookID     int      `column_type:"SERIAL references Book(ID)"`
    Content    string   `column_type:"TEXT"`
}

// Implement Saveable
func (p Page) GetID() int {
    return p.ID
}
// Implement Saveable interface
func (p Page) SetID(id int) {
    p.ID = id
}

func (p *Page) Save() error {
    if p.ID > 0 { // Exists, update
        return gopsql.UpdateExisting(*p)
    }
    // New
    return gopsql.InsertNew(*p).Scan(&p.ID)
}

func (p *Page) GetAllForBook(bookID int) ([]Page, error) {
    var res []Page
    rows, err := gopsql.GetFiltered(p, "BookID", fmt.Sprintf("%d", bookID))
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    for rows.Next() {
        page := Page{}
        err := rows.Scan(&page.ID, &page.BookID, &page.Content)

        if err != nil {
            return nil, err
        }

        res = append(res, page)
    }
    return res, nil
}

main.go

package main

import "github.com/Tebro/gopsql"

func main() {
    dbHost := "localhost"
    dbUser := "root"
    dbPass := "something"
    dbName := "demo"
    sslMode := "disable" // Check: https://godoc.org/github.com/lib/pq

    // Select which models to load into the DB on startup
    models := []gopsql.Saveable{
        Book{},
        Page{},
    }

    gopsql.Setup(dbHost, dbUser, dbPass, dbName, sslMode, models)

    // Let's create a book with one page

    b := Book{}
    b.Title = "Foobar"
    b.Author = "Mr. Foo Bar"

    // Save it to DB
    err := b.Save()

    // Not going to check the error here, but you should

    // Let's add a page to the book
    p := Page{}
    p.BookID = b.ID // The book ID is now there
    p.Content = "Hello World!"
    err := p.Save()

    // Remember the to check the error here as well

    // Let's retrieve it from the DB
    b2 := Book{}
    b2.ID = b.ID
    err := b2.Find()

    // Again, check the error!

    // At this point b2 will have all the fields.
}

For more information check the reference

Documentation

Index

• func Delete(obj Saveable) error
• func GetAll(obj interface{}) (*sql.Rows, error)
• func GetFiltered(obj interface{}, filter ...string) (*sql.Rows, error)
• func InsertNew(obj Saveable) *sql.Row
• func Setup(dbHost string, dbUser string, dbPass string, dbName string, sslMode string, ...) error
• func UpdateExisting(obj Saveable) error
• type Book
  • func (b *Book) Delete() error
  • func (b *Book) Find() error
  • func (b Book) GetID() int
  • func (b *Book) Save() error
  • func (b Book) SetID(id int)
• type Page
  • func GetAllPagesForBook(bookID int) ([]Page, error)
  • func (p Page) GetID() int
  • func (p *Page) Save() error
  • func (p Page) SetID(id int)
• type Saveable

Functions

func Delete

func Delete(obj Saveable) error

Delete removes the provided entry from the database, based on the return value of GetID()

func GetAll

func GetAll(obj interface{}) (*sql.Rows, error)

GetAll gets all the rows for the type

func GetFiltered

func GetFiltered(obj interface{}, filter ...string) (*sql.Rows, error)

GetFiltered does a query with a WHERE section GetFiltered(sometype, field, value, "AND", field, value)

func InsertNew

func InsertNew(obj Saveable) *sql.Row

InsertNew inserts a new entry and returns the Row for someone with more knowledge to retrieve ID

func Setup

func Setup(dbHost string, dbUser string, dbPass string, dbName string, sslMode string, types []Saveable) error

Setup connects to the database and makes sure the schema is inserted

func UpdateExisting

func UpdateExisting(obj Saveable) error

UpdateExisting updates a record in the DB based on ID

Types

type Book

type Book struct {
	ID     int    `column_type:"SERIAL primary key" column_skip_insert:"yes"`
	Title  string `column_type:"varchar(255)"`
	Author string `column_type:"varchar(255)"`
	Pages  []Page `column_skip:"yes"`
}

func (*Book) Delete

func (b *Book) Delete() error

func (*Book) Find

func (b *Book) Find() error

func (Book) GetID

func (b Book) GetID() int

Implement Saveable interface

func (*Book) Save

func (b *Book) Save() error

func (Book) SetID

func (b Book) SetID(id int)

Implement Saveable interface

type Page

type Page struct {
	ID      int    `column_type:"SERIAL primary key" column_skip_insert:"yes" column_order_by:"yes"`
	BookID  int    `column_type:"SERIAL references Book(ID)"`
	Content string `column_type:"TEXT"`
}

func GetAllPagesForBook

func GetAllPagesForBook(bookID int) ([]Page, error)

func (Page) GetID

func (p Page) GetID() int

Implement Saveable

func (*Page) Save

func (p *Page) Save() error

func (Page) SetID

func (p Page) SetID(id int)

Implement Saveable interface

type Saveable

type Saveable interface {
	GetID() int
	SetID(int)
}

Saveable interface is so that Save function always has a way of getting and setting the ID