freedb

README

GoFreeDB

Ship Faster with Google Sheets as a Database!

GoFreeDB is a Golang library that provides common and simple database abstractions on top of Google Sheets.

Features

1. Provide a straightforward key-value and row based database interfaces on top of Google Sheets.
2. Serve your data without any server setup (by leveraging Google Sheets infrastructure).
3. Support flexible enough query language to perform various data queries.
4. Manually manipulate data via the familiar Google Sheets UI (no admin page required).

For more details, please read our analysis on other alternatives and how it compares with FreeDB.

Table of Contents

• Protocols
• Getting Started
  • Installation
  • Pre-requisites
• Row Store
  • Querying Rows
  • Counting Rows
  • Inserting Rows
  • Updating Rows
  • Deleting Rows
  • Struct Field to Column Mapping
• KV Store
  • Get Value
  • Set Key
  • Delete Key
  • Supported Modes
• KV Store V2
  • Get Value
  • Set Key
  • Delete Key
  • Supported Modes

Protocols

Clients are strongly encouraged to read through the protocols document to see how things work under the hood and the limitations.

Getting Started

Installation

go get github.com/FreeLeh/GoFreeDB

Pre-requisites

1. Obtain a Google OAuth2 or Service Account credentials.
2. Prepare a Google Sheets spreadsheet where the data will be stored.

Row Store

Let's assume each row in the table is represented by the Person struct.

type Person struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}

Please read the struct field to column mapping section to understand the purpose of the db struct field tag.

import (
	"github.com/FreeLeh/GoFreeDB"
	"github.com/FreeLeh/GoFreeDB/google/auth"
)

// If using Google Service Account.
auth, err := auth.NewServiceFromFile(
	"<path_to_service_account_json>", 
	freedb.FreeDBGoogleAuthScopes, 
	auth.ServiceConfig{},
)

// If using Google OAuth2 Flow.
auth, err := auth.NewOAuth2FromFile(
	"<path_to_client_secret_json>", 
	"<path_to_cached_credentials_json>", 
	freedb.FreeDBGoogleAuthScopes, 
	auth.OAuth2Config{},
)

store := freedb.NewGoogleSheetRowStore(
	auth, 
	"<spreadsheet_id>", 
	"<sheet_name>", 
	freedb.GoogleSheetRowStoreConfig{Columns: []string{"name", "age"}},
)
defer store.Close(context.Background())

Querying Rows

// Output variable
var output []Person

// Select all columns for all rows
err := store.
	Select(&output).
	Exec(context.Background())

// Select a few columns for all rows (non-selected struct fields will have default value)
err := store.
	Select(&output, "name").
	Exec(context.Background())

// Select rows with conditions
err := store.
	Select(&output).
	Where("name = ? OR age >= ?", "freedb", 10).
	Exec(context.Background())

// Select rows with sorting/order by
ordering := []freedb.ColumnOrderBy{
	{Column: "name", OrderBy: freedb.OrderByAsc},
	{Column: "age", OrderBy: freedb.OrderByDesc},
}
err := store.
	Select(&output).
	OrderBy(ordering).
	Exec(context.Background())

// Select rows with offset and limit
err := store.
	Select(&output).
	Offset(10).
	Limit(20).
	Exec(context.Background())

Counting Rows

// Count all rows
count, err := store.
	Count().
	Exec(context.Background())

// Count rows with conditions
count, err := store.
	Count().
	Where("name = ? OR age >= ?", "freedb", 10).
	Exec(context.Background())

Inserting Rows

err := store.Insert(
	Person{Name: "no_pointer", Age: 10}, 
	&Person{Name: "with_pointer", Age: 20},
).Exec(context.Background())

Updating Rows

colToUpdate := make(map[string]interface{})
colToUpdate["name"] = "new_name"
colToUpdate["age"] = 12

// Update all rows
err := store.
	Update(colToUpdate).
	Exec(context.Background())

// Update rows with conditions
err := store.
	Update(colToUpdate).
	Where("name = ? OR age >= ?", "freedb", 10).
	Exec(context.Background())

Deleting Rows

// Delete all rows
err := store.
	Delete().
	Exec(context.Background())

// Delete rows with conditions
err := store.
	Delete().
	Where("name = ? OR age >= ?", "freedb", 10).
	Exec(context.Background())

Struct Field to Column Mapping

The struct field tag db can be used for defining the mapping between the struct field and the column name. This works just like the json tag from encoding/json.

Without db tag, the library will use the field name directly (case-sensitive).

// This will map to the exact column name of "Name" and "Age".
type NoTagPerson struct {
	Name string
	Age  int
}

// This will map to the exact column name of "name" and "age" 
type WithTagPerson struct {
	Name string  `db:"name"`
	Age  int     `db:"age"`
}

KV Store

Please use KV Store V2 as much as possible, especially if you are creating a new storage.

import (
	"github.com/FreeLeh/GoFreeDB"
	"github.com/FreeLeh/GoFreeDB/google/auth"
)

// If using Google Service Account.
auth, err := auth.NewServiceFromFile(
	"<path_to_service_account_json>", 
	freedb.FreeDBGoogleAuthScopes, 
	auth.ServiceConfig{},
)

// If using Google OAuth2 Flow.
auth, err := auth.NewOAuth2FromFile(
	"<path_to_client_secret_json>", 
	"<path_to_cached_credentials_json>", 
	freedb.FreeDBGoogleAuthScopes, 
	auth.OAuth2Config{},
)

kv := freedb.NewGoogleSheetKVStore(
	auth, 
	"<spreadsheet_id>", 
	"<sheet_name>", 
	freedb.GoogleSheetKVStoreConfig{Mode: freedb.KVSetModeAppendOnly},
)
defer kv.Close(context.Background())

Get Value

If the key is not found, freedb.ErrKeyNotFound will be returned.

value, err := kv.Get(context.Background(), "k1")

Set Key

err := kv.Set(context.Background(), "k1", []byte("some_value"))

Delete Key

err := kv.Delete(context.Background(), "k1")

Supported Modes

For more details on how the two modes are different, please read the protocol document.

There are 2 different modes supported:

1. Default mode.
2. Append only mode.

// Default mode
kv := freedb.NewGoogleSheetKVStore(
	auth,
	"<spreadsheet_id>",
	"<sheet_name>",
	freedb.GoogleSheetKVStoreConfig{Mode: freedb.KVModeDefault},
)

// Append only mode
kv := freedb.NewGoogleSheetKVStore(
	auth,
	"<spreadsheet_id>",
	"<sheet_name>",
	freedb.GoogleSheetKVStoreConfig{Mode: freedb.KVModeAppendOnly},
)

KV Store V2

The KV Store V2 is implemented internally using the row store.

The original KV Store was created using more complicated formulas, making it less maintainable. You can still use the original KV Store implementation, but we strongly suggest using this new KV Store V2.

You cannot use an existing sheet based on KV Store with KV Store V2 as the sheet structure is different.

• If you want to convert an existing sheet, just add an _rid column and insert the first key-value row with 1 and increase it by 1 until the last row.
• Remove the timestamp column as KV Store V2 does not depend on it anymore.

import (
	"github.com/FreeLeh/GoFreeDB"
	"github.com/FreeLeh/GoFreeDB/google/auth"
)

// If using Google Service Account.
auth, err := auth.NewServiceFromFile(
	"<path_to_service_account_json>", 
	freedb.FreeDBGoogleAuthScopes, 
	auth.ServiceConfig{},
)

// If using Google OAuth2 Flow.
auth, err := auth.NewOAuth2FromFile(
	"<path_to_client_secret_json>", 
	"<path_to_cached_credentials_json>", 
	freedb.FreeDBGoogleAuthScopes, 
	auth.OAuth2Config{},
)

kv := freedb.NewGoogleSheetKVStoreV2(
	auth, 
	"<spreadsheet_id>", 
	"<sheet_name>", 
	freedb.GoogleSheetKVStoreV2Config{Mode: freedb.KVSetModeAppendOnly},
)
defer kv.Close(context.Background())

Get Value V2

If the key is not found, freedb.ErrKeyNotFound will be returned.

value, err := kv.Get(context.Background(), "k1")

Set Key V2

err := kv.Set(context.Background(), "k1", []byte("some_value"))

Delete Key V2

err := kv.Delete(context.Background(), "k1")

Supported Modes V2

For more details on how the two modes are different, please read the protocol document.

There are 2 different modes supported:

1. Default mode.
2. Append only mode.

// Default mode
kv := freedb.NewGoogleSheetKVStoreV2(
	auth,
	"<spreadsheet_id>",
	"<sheet_name>",
	freedb.GoogleSheetKVStoreV2Config{Mode: freedb.KVModeDefault},
)

// Append only mode
kv := freedb.NewGoogleSheetKVStoreV2(
	auth,
	"<spreadsheet_id>",
	"<sheet_name>",
	freedb.GoogleSheetKVStoreV2Config{Mode: freedb.KVModeAppendOnly},
)

License

This project is MIT licensed.

Documentation

Index

• Variables
• type ColumnOrderBy
• type GoogleSheetDeleteStmt
• type GoogleSheetInsertStmt
• type GoogleSheetKVStore
• type GoogleSheetKVStoreConfig
• type GoogleSheetKVStoreV2
• type GoogleSheetKVStoreV2Config
• type GoogleSheetRowStore
• type GoogleSheetRowStoreConfig
• type GoogleSheetSelectStmt
• type GoogleSheetUpdateStmt
• type KVMode
• type OrderBy

Examples

• GoogleSheetKVStore
• GoogleSheetKVStoreV2
• GoogleSheetRowStore

Variables

var (
	NewGoogleSheetKVStore   = store.NewGoogleSheetKVStore
	NewGoogleSheetKVStoreV2 = store.NewGoogleSheetKVStoreV2

	KVModeDefault    = models.KVModeDefault
	KVModeAppendOnly = models.KVModeAppendOnly
)

var (
	NewGoogleSheetRowStore = store.NewGoogleSheetRowStore

	OrderByAsc  = models.OrderByAsc
	OrderByDesc = models.OrderByDesc
)

var (
	GoogleAuthScopes = auth.GoogleSheetsReadWrite
)

GoogleAuthScopes specifies the list of Google Auth scopes required to run FreeDB implementations properly.

Types

type ColumnOrderBy

type ColumnOrderBy = models.ColumnOrderBy

type GoogleSheetDeleteStmt

type GoogleSheetDeleteStmt = store.GoogleSheetDeleteStmt

type GoogleSheetInsertStmt

type GoogleSheetInsertStmt = store.GoogleSheetInsertStmt

type GoogleSheetKVStore

type GoogleSheetKVStore = store.GoogleSheetKVStore

Example

googleAuth, err := auth.NewServiceFromFile(
	"<path_to_file>",
	GoogleAuthScopes,
	auth.ServiceConfig{},
)
if err != nil {
	panic(err)
}

store := NewGoogleSheetKVStore(
	googleAuth,
	"spreadsheet_id",
	"sheet_name",
	GoogleSheetKVStoreConfig{
		Mode: KVModeDefault,
	},
)

val, err := store.Get(context.Background(), "key1")
if err != nil {
	panic(err)
}
fmt.Println("get key", val)

err = store.Set(context.Background(), "key1", []byte("value1"))
if err != nil {
	panic(err)
}

err = store.Delete(context.Background(), "key1")
if err != nil {
	panic(err)
}

type GoogleSheetKVStoreConfig

type GoogleSheetKVStoreConfig = store.GoogleSheetKVStoreConfig

type GoogleSheetKVStoreV2

type GoogleSheetKVStoreV2 = store.GoogleSheetKVStoreV2

Example

googleAuth, err := auth.NewServiceFromFile(
	"<path_to_file>",
	GoogleAuthScopes,
	auth.ServiceConfig{},
)
if err != nil {
	panic(err)
}

storeV2 := NewGoogleSheetKVStoreV2(
	googleAuth,
	"spreadsheet_id",
	"sheet_name",
	GoogleSheetKVStoreV2Config{
		Mode: KVModeDefault,
	},
)

val, err := storeV2.Get(context.Background(), "key1")
if err != nil {
	panic(err)
}
fmt.Println("get key", val)

err = storeV2.Set(context.Background(), "key1", []byte("value1"))
if err != nil {
	panic(err)
}

err = storeV2.Delete(context.Background(), "key1")
if err != nil {
	panic(err)
}

type GoogleSheetKVStoreV2Config

type GoogleSheetKVStoreV2Config = store.GoogleSheetKVStoreV2Config

type GoogleSheetRowStore

type GoogleSheetRowStore = store.GoogleSheetRowStore

Example

// Initialize authentication
googleAuth, err := auth.NewServiceFromFile(
	"<path_to_service_account_file>",
	GoogleAuthScopes,
	auth.ServiceConfig{},
)
if err != nil {
	panic(err)
}

// Create row store with columns definition
store := NewGoogleSheetRowStore(
	googleAuth,
	"<spreadsheet_id>",
	"<sheet_name>",
	GoogleSheetRowStoreConfig{
		Columns: []string{"name", "age", "email"},
	},
)

// Insert some rows
type Person struct {
	Name  string `db:"name"`
	Age   int    `db:"age"`
	Email string `db:"email"`
}

err = store.Insert(
	Person{Name: "Alice", Age: 30, Email: "alice@example.com"},
	Person{Name: "Bob", Age: 25, Email: "bob@example.com"},
).Exec(context.Background())
if err != nil {
	panic(err)
}

// Query rows
var people []Person
err = store.Select(&people).
	Where("age > ?", 20).
	OrderBy([]ColumnOrderBy{{Column: "age", OrderBy: OrderByAsc}}).
	Limit(10).
	Exec(context.Background())
if err != nil {
	panic(err)
}
fmt.Println("Selected people:", people)

// Update rows
update := map[string]interface{}{"age": 31}
err = store.Update(update).Where("name = ?", "Alice").
	Exec(context.Background())
if err != nil {
	panic(err)
}

// Count rows
count, err := store.Count().
	Where("age > ?", 20).
	Exec(context.Background())
if err != nil {
	panic(err)
}
fmt.Println("Number of people over 20:", count)

// Delete rows
err = store.Delete().
	Where("name = ?", "Bob").
	Exec(context.Background())
if err != nil {
	panic(err)
}

// Clean up
err = store.Close(context.Background())
if err != nil {
	panic(err)
}

type GoogleSheetRowStoreConfig

type GoogleSheetRowStoreConfig = store.GoogleSheetRowStoreConfig

type GoogleSheetSelectStmt

type GoogleSheetSelectStmt = store.GoogleSheetSelectStmt

type GoogleSheetUpdateStmt

type GoogleSheetUpdateStmt = store.GoogleSheetUpdateStmt

type KVMode

type KVMode = models.KVMode

type OrderBy

type OrderBy = models.OrderBy