sheetdb

A golang package for using Google spreadsheets as a database instead of the actual database management system.

!!! Caution !!!

• Currently we are not measuring performance. It is intended for use in small applications where performance is not an issue.
• The Google Sheets API has a usage limit. Do not use this package for applications that require access beyond this usage limit.

---

• Features
• Installation
• Requirement
• Usage
  • 1. Write models
  • 2. Generate codes from models
  • 3. Set up Google spreadsheet
  • 4. Initialize models
    • Set up nortificaltion
    • Create new client
    • Load sheet data
  • 5. Use CRUD functions
    • Read (Get/Select)
    • Create (Add/Insert)
    • Update
    • Delete

Features

• Load sheet data into cache
• Apply cache update information to sheet asynchronously
• Exclusive control when updating cache and inserting a row to a sheet
• Automatic generation of CRUD functions based on model (structure definition)
• Automatic numbering and initial value setting of ID
• Unique and non-null constraints
• Cascade delete child data when deleting parent data
• Notification mechanism on asynchronous update error

The following features are not included.

• SQL query
• Transaction control (commit and rollback)
• Read Lock for Update

Installation

go get github.com/takuoki/sheetdb

Requirement

This package uses Google OAuth2.0. So before executing tool, you have to prepare credentials.json. See Go Quickstart, or blog post (Japanese) for the details.

Usage

1. Write models

Write the structure of the model as follows. Please refer to the sheetdb-modeler tool documentation for details.

//go:generate sheetdb-modeler -type=User -children=Foo,FooChild,Bar -initial=10001

// User is a struct of user.
type User struct {
  UserID   int           `json:"user_id" db:"primarykey"`
  Name     string        `json:"name"`
  Email    string        `json:"email" db:"unique"`
  Sex      Sex           `json:"sex"`
  Birthday *sheetdb.Date `json:"birthday"`
}

//go:generate sheetdb-modeler -type=Foo -parent=User -children=FooChild

// Foo is a struct of foo which is a child of user.
type Foo struct {
  UserID int     `json:"user_id" db:"primarykey"`
  FooID  int     `json:"foo_id" db:"primarykey"`
  Value  float32 `json:"value"`
  Note   string  `json:"note" db:"allowempty"`
}

//go:generate sheetdb-modeler -type=FooChild -parent=Foo

// FooChild is a struct of foo child.
type FooChild struct {
  UserID  int    `json:"user_id" db:"primarykey"`
  FooID   int    `json:"foo_id" db:"primarykey"`
  ChildID int    `json:"child_id" db:"primarykey"`
  Value   string `json:"value" db:"unique"`
}

//go:generate sheetdb-modeler -type=Bar -parent=User

// Bar is a struct of bar which is a child of user.
type Bar struct {
  UserID   int              `json:"user_id" db:"primarykey"`
  Datetime sheetdb.Datetime `json:"datetime" db:"primarykey"`
  Value    float32          `json:"value"`
  Note     string           `json:"note" db:"allowempty"`
}

2. Generate codes from models

You can generate in bulk with the go generate command by putting //go:generate sheetdb-modeler comments in the code of the target package. Please refer to the sheetdb-modeler tool documentation for details.

go generate ./sample

3. Set up Google spreadsheet

Prepare a spreadsheet according to the header comments of each generated file.

// Code generated by "sheetdb-modeler"; DO NOT EDIT.
// Create a Spreadsheet (sheet name: "users") as data storage.
// The spreadsheet header is as follows:
//   user_id | name | email | sex | birthday | updated_at | deleted_at
// Please copy and paste this header on the first line of the sheet.

4. Initialize models

Set up nortificaltion

By setting Logger, you can customize log output freely. If you want to send an alert to Slack, you can use SlackLogger in this package. Configuration of Logger is package scope. Please note that it is not client scope.

sheetdb.SetLogger(sheetdb.NewSlackLogger(
  "project name",
  "service name",
  "icon_emoji",
  "https://hooks.slack.com/services/zzz/zzz/zzzzz",
  sheetdb.LevelError,
))

Create new client

Create a new client using the New function. Set the created client in the package global variable. The name of the variable is the name specified with the -client option (the default is dbClient).

var dbClient *sheetdb.Client

// Initialize initializes this package.
func Initialize(ctx context.Context) error {
  client, err := sheetdb.New(
    ctx,
    `{"installed":{"client_id":"..."}`, // Google API credentials
    `{"access_token":"..."`,            // Google API token
    "xxxxx",                            // Google spreadsheet ID
  )
  // ...
  dbClient = client
  return nil
}

Load sheet data

err := client.LoadData(ctx)

5. Use CRUD functions

The functions in this section are generated automatically by sheetdb-modeler.

Read (Get/Select)

GetModelName function returns an instance of that model by the primary key(s). If it can not be found, this function returns *sheetdb.NotFoundError.

user, err := GetUser(userID)
foo, err := GetFoo(userID, fooID)
fooChild, err := GetFooChild(userID, fooID, childID)
bar, err := GetBar(userID, datetime)

If the model is a child model of another model, GetModelName method is also added to the parent model.

foo, err := user.GetFoo(fooID)
fooChild, err := foo.GetFooChild(childID)
bar, err := user.GetBar(datetime)

For fields defined as unique, GetModelNameByFieldName function is also generated. For child models, this method is also added to the parent model.

user, err := GetUserByEmail(email)
fooChild, err := GetFooChildByValue(userID, fooID, value)

fooChild, err := foo.GetFooChildByValue(value)

GetModelNames function returns all instances of that model. If the model is a child model of another model, this function returns all instances that parent model has. For child models, this method is also added to the parent model.

users, err := GetUsers()
foos, err := GetFoos(userID)
fooChildren, err := GetFooChildren(userID, fooID)
bars, err := GetBars(userID)

foos, err := user.GetFoos()
fooChildren, err := foo.GetFooChildren()
bars, err := user.GetBars()

You can get filtered results using ModelFilter option.

users, err := GetUsers(sample.UserFilter(func(user *sample.User) bool {
  return user.Sex == sample.Male
}))

You can also get sorted results using ModelSort option. If the sort option is not specified, the order of results is random.

users, err := GetUsers(sample.UserSort(func(users []*sample.User) {
  sort.Slice(users, func(i, j int) bool {
    return users[i].UserID < users[j].UserID
  })
}))

Create (Add/Insert)

AddModel adds a new instance. If the primary key matches the automatic numbering rule, it will be automatically numbered. For child models, AddModel method is added to the parent model.

user, err := AddUser(name, email, sex, birthday)
foo, err := AddFoo(userID, value, note)
fooChild, err := AddFooChild(userID, fooID, value)
bar, err := AddBar(userID, datetime, value, note)

foo, err := user.AddFoo(value, note)
fooChild, err := foo.AddFooChild(value)
bar, err := user.AddBar(datetime, value, note)

Update

UpdateModel updates an existing instance. You can not change the primary key(s). For child models, UpdateModel method is added to the parent model.

user, err := UpdateUser(userID, name, email, sex, birthday)
foo, err := UpdateFoo(userID, fooID, value, note)
fooChild, err := UpdateFooChild(userID, fooID, childID, value)
bar, err := UpdateBar(userID, datetime, value, note)

foo, err := user.UpdateFoo(fooID, value, note)
fooChild, err := foo.UpdateFooChild(childID, value, note)
bar, err := user.UpdateBar(datetime, value, note)

Delete

DeleteModel deletes an existing instance. If the model has child models, this function deletes the instances of the child model in cascade. For child models, DeleteModel method is added to the parent model.

err = DeleteUser(userID)
err = DeleteFoo(userID, fooID)
err = DeleteFooChild(userID, fooID, childID)
err = DeleteBar(userID, datetime)

err = user.DeleteFoo(fooID)
err = foo.DeleteFooChild(childID)
err = user.DeleteBar(datetime)