sqlx snippets

How to Join Two Tables with jmoiron/sqlx

Tagged join, go, golang, sqlx  Languages go

Here’s an example of how to define relationships between structs when using SQL joins and sqlx:

type Feed struct {
  ID        int       `json:"id"`
  Title     string    `json:"title"`
  URL       string    `json:"url"`
  CreatedAt time.Time `json:"created_at" db:"created_at"`
}

type FeedItem struct {
  ID          int            `json:"id"`
  Title       string         `json:"title"`
  Description sql.NullString `json:"description"`
  URL         string         `json:"url"`
  CreatedAt   time.Time      `json:"created_at" db:"created_at"`
  FeedId      int            `json:"feed_id" db:"feed_id"`
  Feed        `db:"feed"` // << NOTE PREFIX 
}

func getFeeds() (*[]Feed, error) {
  feeds := []Feed{}
  err := db.Select(&feeds, "SELECT * FROM feeds")
  return &feeds, err
}

func getFeedItems() (*[]FeedItem, error) {
  items := []FeedItem{}
  sql := `SELECT
      feed_items.*,
      feeds.id "feed.id",
      feeds.title "feed.title",
      feeds.url "feed.url",
      feeds.created_at "feed.created_at"
    FROM
      feed_items JOIN feeds ON feed_items.feed_id = feeds.id;`
  err := db.Select(&items, sql)
  return &items, err
}

Checking if a row exists in Go (database/sql and SQLX)

Tagged sqlx, go, golang  Languages go

Code:

func rowExists(query string, args ...interface{}) bool {
    var exists bool
    query = fmt.Sprintf("SELECT exists (%s)", query)
    err := db.QueryRow(query, args...).Scan(&exists)
    if err != nil && err != sql.ErrNoRows {
            glog.Fatalf("error checking if row exists '%s' %v", args, err)
    }
    return exists
}

Usage:

if rowExists("SELECT id FROM feed_items WHERE url=$1", item.Link) {
    return
}