freedb

package module
v1.0.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Sep 1, 2022 License: MIT Imports: 11 Imported by: 2

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.


Unit Test Integration Test Go Reference

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

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.NewGoogleSheetsRowStore(
	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

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},
)

License

This project is MIT licensed.

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrKeyNotFound = errors.New("error key not found")
)

ErrKeyNotFound is returned only for the key-value store and when the key does not exist.

View Source 
var (
	FreeDBGoogleAuthScopes = auth.GoogleSheetsReadWrite
)

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

Functions

This section is empty.

Types

type Codec 

type Codec interface {
	Encode(value []byte) (string, error)
	Decode(value string) ([]byte, error)
}

Codec is an interface for encoding and decoding the data provided by the client. At the moment, only key-value store requires data encoding.

type ColumnOrderBy 

type ColumnOrderBy struct {
	Column  string
	OrderBy OrderBy
}

ColumnOrderBy defines what ordering is required for a particular column. This is used for GoogleSheetRowStore.Select().

type GoogleSheetCountStmt 

type GoogleSheetCountStmt struct {
	// contains filtered or unexported fields
}

GoogleSheetCountStmt encapsulates information required to count the number of rows matching some conditions.

func (*GoogleSheetCountStmt) Exec 

func (s *GoogleSheetCountStmt) Exec(ctx context.Context) (uint64, error)

Exec counts the number of rows matching the provided condition.

There is only 1 API call behind the scene.

func (*GoogleSheetCountStmt) Where 

func (s *GoogleSheetCountStmt) Where(condition string, args ...interface{}) *GoogleSheetCountStmt

Where specifies the condition to choose which rows are affected.

It works just like the GoogleSheetSelectStmt.Where() method. Please read GoogleSheetSelectStmt.Where() for more details.

type GoogleSheetDeleteStmt 

type GoogleSheetDeleteStmt struct {
	// contains filtered or unexported fields
}

GoogleSheetDeleteStmt encapsulates information required to delete rows.

func (*GoogleSheetDeleteStmt) Exec 

func (s *GoogleSheetDeleteStmt) Exec(ctx context.Context) error

Exec deletes rows matching the condition.

There are 2 API calls behind the scene.

func (*GoogleSheetDeleteStmt) Where 

func (s *GoogleSheetDeleteStmt) Where(condition string, args ...interface{}) *GoogleSheetDeleteStmt

Where specifies the condition to choose which rows are affected.

It works just like the GoogleSheetSelectStmt.Where() method. Please read GoogleSheetSelectStmt.Where() for more details.

type GoogleSheetInsertStmt 

type GoogleSheetInsertStmt struct {
	// contains filtered or unexported fields
}

GoogleSheetInsertStmt encapsulates information required to insert new rows into the Google Sheet.

func (*GoogleSheetInsertStmt) Exec 

func (s *GoogleSheetInsertStmt) Exec(ctx context.Context) error

Exec inserts the provided new rows data into Google Sheet. This method calls the relevant Google Sheet APIs to actually insert the new rows.

There is only 1 API call behind the scene.

type GoogleSheetKVStore 

type GoogleSheetKVStore struct {
	// contains filtered or unexported fields
}

GoogleSheetKVStore encapsulates key-value store functionality on top of a Google Sheet.

There are 2 operation modes for the key-value store: default and append only mode.

For more details on how they differ, please read the explanations for each method or the protocol page: https://github.com/FreeLeh/docs/blob/main/freedb/protocols.md.

func NewGoogleSheetKVStore 

func NewGoogleSheetKVStore(
	auth sheets.AuthClient,
	spreadsheetID string,
	sheetName string,
	config GoogleSheetKVStoreConfig,
) *GoogleSheetKVStore

NewGoogleSheetKVStore creates an instance of the key-value store with the given configuration. It will also try to create the sheet, in case it does not exist yet.

func (*GoogleSheetKVStore) Close 

func (s *GoogleSheetKVStore) Close(ctx context.Context) error

Close cleans up all held resources like the scratchpad cell booked for this specific GoogleSheetKVStore instance.

func (*GoogleSheetKVStore) Delete 

func (s *GoogleSheetKVStore) Delete(ctx context.Context, key string) error

Delete deletes the given key from the key-value store.

In default mode,

  • If the key is not in the store, it will not do anything.
  • If the key is in the store, it will remove that row.
  • There are up to 2 API calls behind the scene: getting the row for the key and remove the row (if the key exists).

In append only mode,

  • It creates a new row at the bottom of the sheet with a tombstone value and timestamp.
  • There is only 1 API call behind the scene.

func (*GoogleSheetKVStore) Get 

func (s *GoogleSheetKVStore) Get(ctx context.Context, key string) ([]byte, error)

Get retrieves the value associated with the given key. If the key exists in the store, the raw bytes value and no error will be returned. If the key does not exist in the store, a nil []byte and a wrapped ErrKeyNotFound will be returned.

In default mode,

  • There will be only one row with the given key. It will return the value for that in that row.
  • There is only 1 API call behind the scene.

In append only mode,

  • As there could be multiple rows with the same key, we need to only use the latest row as it contains the last updated value.
  • Note that deletion using append only mode results in a new row with a tombstone value. This method will also recognise and handle such cases.
  • There is only 1 API call behind the scene.

func (*GoogleSheetKVStore) Set 

func (s *GoogleSheetKVStore) Set(ctx context.Context, key string, value []byte) error

Set inserts the key-value pair into the key-value store.

In default mode,

  • If the key is not in the store, `Set` will create a new row and store the key value pair there.
  • If the key is in the store, `Set` will update the previous row with the new value and timestamp.
  • There are exactly 2 API calls behind the scene: getting the row for the key and creating/updating with the given key value data.

In append only mode,

  • It always creates a new row at the bottom of the sheet with the latest value and timestamp.
  • There is only 1 API call behind the scene.

type GoogleSheetKVStoreConfig 

type GoogleSheetKVStoreConfig struct {
	Mode KVMode
	// contains filtered or unexported fields
}

GoogleSheetKVStoreConfig defines a list of configurations that can be used to customise how the GoogleSheetKVStore works.

type GoogleSheetRowStore 

type GoogleSheetRowStore struct {
	// contains filtered or unexported fields
}

GoogleSheetRowStore encapsulates row store functionality on top of a Google Sheet.

func NewGoogleSheetRowStore 

func NewGoogleSheetRowStore(
	auth sheets.AuthClient,
	spreadsheetID string,
	sheetName string,
	config GoogleSheetRowStoreConfig,
) *GoogleSheetRowStore

NewGoogleSheetRowStore creates an instance of the row based store with the given configuration. It will also try to create the sheet, in case it does not exist yet.

func (*GoogleSheetRowStore) Close 

func (s *GoogleSheetRowStore) Close(_ context.Context) error

Close cleans up all held resources if any.

func (*GoogleSheetRowStore) Count 

func (s *GoogleSheetRowStore) Count() *GoogleSheetCountStmt

Count prepares rows counting operation.

Please note that calling Count() does not execute the query yet. Call GoogleSheetCountStmt.Exec() to actually execute the query.

func (*GoogleSheetRowStore) Delete 

func (s *GoogleSheetRowStore) Delete() *GoogleSheetDeleteStmt

Delete prepares rows deletion operation.

Please note that calling Delete() does not execute the deletion yet. Call GoogleSheetDeleteStmt.Exec() to actually execute the deletion.

func (*GoogleSheetRowStore) Insert 

func (s *GoogleSheetRowStore) Insert(rows ...interface{}) *GoogleSheetInsertStmt

Insert specifies the rows to be inserted into the Google Sheet.

The underlying data type of each row must be a struct or a pointer to a struct. Providing other data types will result in an error.

By default, the column name will be following the struct field name (case-sensitive). If you want to map the struct field name into another name, you can add a "db" struct tag (see GoogleSheetRowStore.Select docs for more details).

Please note that calling Insert() does not execute the insertion yet. Call GoogleSheetInsertStmt.Exec() to actually execute the insertion.

func (*GoogleSheetRowStore) Select 

func (s *GoogleSheetRowStore) Select(output interface{}, columns ...string) *GoogleSheetSelectStmt

Select specifies which columns to return from the Google Sheet when querying and the output variable the data should be stored. You can think of this operation like a SQL SELECT statement (with limitations).

If "columns" is an empty slice of string, then all columns will be returned. If a column is not found in the provided list of columns in `GoogleSheetRowStoreConfig.Columns`, that column will be ignored.

"output" must be a pointer to a slice of a data type. The conversion from the Google Sheet data into the slice will be done using https://github.com/mitchellh/mapstructure.

If you are providing a slice of structs into the "output" parameter and you want to define the mapping between the column name with the field name, you should add a "db" struct tag. 

// Without the `db` struct tag, the column name used will be "Name" and "Age".
type Person struct {
    Name string `db:"name"`
    Age int `db:"age"`
}

Please note that calling Select() does not execute the query yet. Call GoogleSheetSelectStmt.Exec to actually execute the query.

func (*GoogleSheetRowStore) Update 

func (s *GoogleSheetRowStore) Update(colToValue map[string]interface{}) *GoogleSheetUpdateStmt

Update specifies the new value for each of the targeted columns.

The "colToValue" parameter specifies what value should be updated for which column. Each value in the map[string]interface{} is going to be JSON marshalled. If "colToValue" is empty, an error will be returned when GoogleSheetUpdateStmt.Exec() is called.

type GoogleSheetRowStoreConfig 

type GoogleSheetRowStoreConfig struct {
	// Columns defines the list of column names.
	// Note that the column ordering matters.
	// The column ordering will be used for arranging the real columns in Google Sheet.
	// Changing the column ordering in this config but not in Google Sheet will result in unexpected behaviour.
	Columns []string
}

GoogleSheetRowStoreConfig defines a list of configurations that can be used to customise how the GoogleSheetRowStore works.

type GoogleSheetSelectStmt 

type GoogleSheetSelectStmt struct {
	// contains filtered or unexported fields
}

GoogleSheetSelectStmt encapsulates information required to query the row store.

func (*GoogleSheetSelectStmt) Exec 

func (s *GoogleSheetSelectStmt) Exec(ctx context.Context) error

Exec retrieves rows matching with the given condition.

There is only 1 API call behind the scene.

func (*GoogleSheetSelectStmt) Limit 

func (s *GoogleSheetSelectStmt) Limit(limit uint64) *GoogleSheetSelectStmt

Limit specifies the number of rows to retrieve.

The default value is 0.

func (*GoogleSheetSelectStmt) Offset 

func (s *GoogleSheetSelectStmt) Offset(offset uint64) *GoogleSheetSelectStmt

Offset specifies the number of rows to skip before starting to include the rows.

The default value is 0.

func (*GoogleSheetSelectStmt) OrderBy 

func (s *GoogleSheetSelectStmt) OrderBy(ordering []ColumnOrderBy) *GoogleSheetSelectStmt

OrderBy specifies the column ordering.

The default value is no ordering specified.

func (*GoogleSheetSelectStmt) Where 

func (s *GoogleSheetSelectStmt) Where(condition string, args ...interface{}) *GoogleSheetSelectStmt

Where specifies the condition to meet for a row to be included.

"condition" specifies the WHERE clause. Values in the WHERE clause should be replaced by a placeholder "?". The actual values used for each placeholder (ordering matters) are provided via the "args" parameter.

"args" specifies the real value to replace each placeholder in the WHERE clause. Note that the first "args" value will replace the first placeholder "?" in the WHERE clause.

If you want to understand the reason behind this design, please read the protocol page: https://github.com/FreeLeh/docs/blob/main/freedb/protocols.md.

All conditions supported by Google Sheet "QUERY" function are supported by this library. You can read the full information in https://developers.google.com/chart/interactive/docs/querylanguage#where.

type GoogleSheetUpdateStmt 

type GoogleSheetUpdateStmt struct {
	// contains filtered or unexported fields
}

GoogleSheetUpdateStmt encapsulates information required to update rows.

func (*GoogleSheetUpdateStmt) Exec 

func (s *GoogleSheetUpdateStmt) Exec(ctx context.Context) error

Exec updates rows matching the condition with the new values for affected columns.

There are 2 API calls behind the scene.

func (*GoogleSheetUpdateStmt) Where 

func (s *GoogleSheetUpdateStmt) Where(condition string, args ...interface{}) *GoogleSheetUpdateStmt

Where specifies the condition to choose which rows are affected.

It works just like the GoogleSheetSelectStmt.Where() method. Please read GoogleSheetSelectStmt.Where() for more details.

type KVMode 

type KVMode int

KVMode defines the mode of the key value store. For more details, please read the README file. 

const (
	KVModeDefault    KVMode = 0
	KVModeAppendOnly KVMode = 1
)

type OrderBy 

type OrderBy string

OrderBy defines the type of column ordering used for GoogleSheetRowStore.Select(). 

const (
	OrderByAsc  OrderBy = "ASC"
	OrderByDesc OrderBy = "DESC"
)

Source Files

View all Source files

Directories

Path Synopsis
google
auth
Package auth provides general Google authentication implementation agnostic to what specific Google services or resources are used.
 Package auth provides general Google authentication implementation agnostic to what specific Google services or resources are used.
internal
google/fixtures
google/sheets

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.