SheetsDB

A SQL-inspired interface for Google Sheets. SheetsDB lets you treat a Google Sheets workbook like a lightweight database: the workbook is the database, each sheet (tab) is a table, and rows are records. The public API mirrors Go's database/sql, exposing Open, Exec, Query, QueryRow, and Rows.Scan, so it feels familiar from the first line.

db, _ := sheetsdb.Open(dsn)

db.Exec(sheetsdb.Statement{
    Command: "INSERT INTO",
    Name:    "users",
    Columns: "name, email",
    Values:  "Ada Lovelace, ada@example.com",
})

Why

Sometimes you want structured, queryable data but a spreadsheet is the actual source of truth: a sheet non-engineers can open and edit, a zero-infrastructure backing store for a side project, or a quick prototype you do not want to host a database for. SheetsDB gives you a familiar SQL-shaped API over exactly that, while keeping the data human-readable in the sheet.

Prerequisites

SheetsDB authenticates as a Google Cloud service account. One-time setup:

1. Create (or pick) a project in the Google Cloud Console.
2. Enable the Google Sheets API for that project.
3. Create a service account and generate a JSON key. You need two values from it: client_email and private_key.
4. Create the Google Sheets workbook you want to use and copy its workbook ID from the URL: https://docs.google.com/spreadsheets/d/<workbook-id>/edit.
5. Share the workbook with the service account's client_email and give it Editor access.

Connecting

Open takes a Postgres-style DSN with three keys: user (the service account email), password (the private key), and dbname (the workbook ID). Because the private key contains spaces and newlines, wrap it in double quotes.

package main

import (
    "fmt"
    "log"
    "os"

    "github.com/bwlp/sheetsdb"
)

func main() {
    dsn := fmt.Sprintf(
        `user=%s password="%s" dbname=%s`,
        os.Getenv("SHEETS_CLIENT_EMAIL"),
        os.Getenv("SHEETS_PRIVATE_KEY"),
        os.Getenv("SHEETS_WORKBOOK_ID"),
    )

    db, err := sheetsdb.Open(dsn)
    if err != nil {
        log.Fatal(err)
    }
    _ = db
}

Quick start

A complete, runnable program that creates a table, inserts a row, and reads it back:

package main

import (
    "fmt"
    "log"
    "os"

    "github.com/bwlp/sheetsdb"
)

type User struct {
    ID    int
    Name  string
    Email string
}

func main() {
    dsn := fmt.Sprintf(
        `user=%s password="%s" dbname=%s`,
        os.Getenv("SHEETS_CLIENT_EMAIL"),
        os.Getenv("SHEETS_PRIVATE_KEY"),
        os.Getenv("SHEETS_WORKBOOK_ID"),
    )

    db, err := sheetsdb.Open(dsn)
    if err != nil {
        log.Fatal(err)
    }

    // Create a table.
    if _, err = db.Exec(sheetsdb.Statement{
        Command: "CREATE TABLE",
        Name:    "users",
        Columns: `
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            name  TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL
        `,
    }); err != nil {
        log.Fatal(err)
    }

    // Insert a row.
    if _, err = db.Exec(sheetsdb.Statement{
        Command: "INSERT INTO",
        Name:    "users",
        Columns: "name, email",
        Values:  "John Doe, john.doe@example.com",
    }); err != nil {
        log.Fatal(err)
    }

    // Query rows.
    rows, err := db.Query(sheetsdb.QueryStatement{
        Select:  "id, name, email",
        From:    "users",
        Where:   "email=john.doe@example.com",
        OrderBy: "name DESC",
        Limit:   10,
    })
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%+v\n", u)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
}

Statements

All mutations go through db.Exec, which accepts a single Statement or a batch of Statements. The examples below assume an open db and reuse err.

Insert

_, err = db.Exec(sheetsdb.Statement{
    Command: "INSERT INTO",
    Name:    "users",
    Columns: "name, email",
    Values:  "Jane Roe, jane.roe@example.com",
})

Bind parameters

Use ? placeholders, filled positionally from the trailing arguments to Exec. For UPDATE, Set placeholders are bound before Where placeholders:

_, err = db.Exec(sheetsdb.Statement{
    Command: "UPDATE",
    Name:    "users",
    Set:     "name=?",
    Where:   "email=?",
}, "John Wick", "john.doe@example.com")

Delete

_, err = db.Exec(sheetsdb.Statement{
    Command: "DELETE FROM",
    Name:    "users",
    Where:   "email=?",
}, "john.doe@example.com")

UPDATE and DELETE currently require the WHERE column to be indexed (a PRIMARY KEY or UNIQUE column, or one you added with CREATE INDEX).

Drop a table

_, err = db.Exec(sheetsdb.Statement{
    Command: "DROP TABLE",
    Name:    "users",
})

Alter a table

ALTER TABLE takes one or more subcommands:

// Add a column
_, err = db.Exec(sheetsdb.Statement{
    Command:     "ALTER TABLE",
    Name:        "users",
    Subcommands: []string{"ADD gender TEXT"},
})

// Drop a column
_, err = db.Exec(sheetsdb.Statement{
    Command:     "ALTER TABLE",
    Name:        "users",
    Subcommands: []string{"DROP COLUMN gender"},
})

// Rename a column
_, err = db.Exec(sheetsdb.Statement{
    Command:     "ALTER TABLE",
    Name:        "users",
    Subcommands: []string{"RENAME COLUMN gender TO sex"},
})

Create an index

_, err = db.Exec(sheetsdb.Statement{
    Command: "CREATE INDEX",
    Name:    "idx_users_name",
    On:      "users",
    Columns: "name",
})

Batch statements

Pass sheetsdb.Statements (a []*Statement) to execute several at once. A batch may not mix command types; only CREATE TABLE and DROP TABLE are batchable.

_, err = db.Exec(sheetsdb.Statements{
    {
        Command: "CREATE TABLE",
        Name:    "users",
        Columns: "id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL",
    },
    {
        Command: "CREATE TABLE",
        Name:    "jobs",
        Columns: "id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT",
    },
})

Querying

db.Query returns *Rows you iterate with Next/Scan/Err, just like database/sql. db.QueryRow returns a single *Row.

WHERE supports a single equality (col=value) or membership clause (col IN (a, b, c)). ORDER BY supports ASC (default) and DESC.

rows, err := db.Query(sheetsdb.QueryStatement{
    Select:  "id, name, email",
    From:    "users",
    Where:   "email IN (a@example.com, b@example.com)",
    OrderBy: "name ASC",
    Limit:   25,
})

Left joins

Tables can be aliased in From and joined with LeftJoin:

rows, err := db.Query(sheetsdb.QueryStatement{
    Select:   "u.name, u.email, j.title",
    From:     "users u",
    LeftJoin: []string{"jobs j ON u.id = j.user_id"},
})

Supported features

Category	Supported
Commands	CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX, INSERT INTO, INSERT OR REPLACE INTO, UPDATE, DELETE FROM, DROP DATABASE
Subcommands	ADD, DROP COLUMN, RENAME COLUMN, DROP INDEX
Constraints	PRIMARY KEY, AUTOINCREMENT, UNIQUE, NOT NULL, DEFAULT
Types	TEXT, INTEGER, FLOAT, BOOLEAN, DATE, DATETIME
Query	SELECT, FROM, WHERE (=, IN), ORDER BY (ASC/DESC), LIMIT, LEFT JOIN ... ON

How it works

SheetsDB never infers schema from the grid. Each table's schema (columns, constraints, and indexes) is serialized as JSON and stored in the workbook's developer metadata, so the schema travels with the spreadsheet.

Every operation runs in two phases:

• prepare: a pure, in-memory step that validates the statement, resolves bind parameters, enforces constraints, and computes the resulting cell changes.
• exec / query: translates the prepared plan into Google Sheets API batch requests.

To keep reads cheap, indexed column values are cached in memory. A query whose selected columns are all covered by indexes is served entirely from that cache with no API call; otherwise SheetsDB fetches only the ranges it needs.

Limitations

• Not concurrency-safe. A DB caches schema and index state in memory.
• Single WHERE clause. No AND/OR; one = or IN condition only.
• UPDATE/DELETE require an indexed WHERE column.
• Batches cannot mix command types, and only CREATE TABLE / DROP TABLE are batchable.
• INSERT OR REPLACE replace only supports a single-column primary key.
• Everything is stored as text in the sheet; typing is applied on read.
• Subject to Google Sheets API quotas (per-minute read/write limits), so it is unsuitable for heavy load.

Testing

go test ./...