GoFreeDB
is a Golang library that provides common and simple database abstractions on top of Google Sheets.
- Provide a straightforward key-value and row based database interfaces on top of Google Sheets.
- Serve your data without any server setup (by leveraging Google Sheets infrastructure).
- Support flexible enough query language to perform various data queries.
- 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
.
Clients are strongly encouraged to read through the protocols document to see how things work under the hood and the limitations.
go get github.com/FreeLeh/GoFreeDB
- Obtain a Google OAuth2 or Service Account credentials.
- Prepare a Google Sheets spreadsheet where the data will be stored.
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())
// 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())
// 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())
err := store.Insert(
Person{Name: "no_pointer", Age: 10},
&Person{Name: "with_pointer", Age: 20},
).Exec(context.Background())
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())
// 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())
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"`
}
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())
If the key is not found, freedb.ErrKeyNotFound
will be returned.
value, err := kv.Get(context.Background(), "k1")
err := kv.Set(context.Background(), "k1", []byte("some_value"))
err := kv.Delete(context.Background(), "k1")
For more details on how the two modes are different, please read the protocol document.
There are 2 different modes supported:
- Default mode.
- 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},
)
This project is MIT licensed.