sqlds

README

SQL Datastore

An implementation of the datastore interface that can be backed by any sql database.

Install

go get github.com/ipfs/go-ds-sql

Usage

PostgreSQL

Ensure a database is created and a table exists with key and data columns. For example, in PostgreSQL you can create a table with the following structure (replacing table_name with the name of the table the datastore will use - by default this is blocks):

CREATE TABLE IF NOT EXISTS table_name (key TEXT NOT NULL UNIQUE, data BYTEA)

It's recommended to create an index on the key column that is optimised for prefix scans. For example, in PostgreSQL you can create a text_pattern_ops index on the table:

CREATE INDEX IF NOT EXISTS table_name_key_text_pattern_ops_idx ON table_name (key text_pattern_ops)

Import and use in your application:

import (
	"database/sql"
	"github.com/ipfs/go-ds-sql"
	pg "github.com/ipfs/go-ds-sql/postgres"
)

mydb, _ := sql.Open("yourdb", "yourdbparameters")

// Implement the Queries interface for your SQL impl.
// ...or use the provided PostgreSQL queries
queries := pg.NewQueries("blocks")

ds := sqlds.NewDatastore(mydb, queries)

SQLite

The SQLite wrapper tries to create the table automatically

Prefix scans are optimized by using GLOB

Import and use in your application:

package main

import (
	sqliteds "github.com/ipfs/go-ds-sql/sqlite"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	opts := &sqliteds.Options{
		DSN: "db.sqlite",
	}

	ds, err := opts.Create()
	if err != nil {
		panic(err)
	}
	defer func() {
		if err := ds.Close(); err != nil {
			panic(err)
		}
	}()
}

If no DSN is specified, an unique in-memory database will be created

SQLCipher

The SQLite wrapper also supports the SQLCipher extension

Import and use in your application:

package main

import (
	sqliteds "github.com/ipfs/go-ds-sql/sqlite"
	_ "github.com/mutecomm/go-sqlcipher/v4"
)

func main() {
	opts := &sqliteds.Options{
		DSN: "encdb.sqlite",
		Key: ([]byte)("32_very_secure_bytes_0123456789a"),
	}

	ds, err := opts.Create()
	if err != nil {
		panic(err)
	}
	defer func() {
		if err := ds.Close(); err != nil {
			panic(err)
		}
	}()
}

API

GoDoc Reference

Contribute

Feel free to dive in! Open an issue or submit PRs.

License

MIT

Documentation

Index

• Variables
• type Datastore
  • func NewDatastore(db *sql.DB, queries Queries) *Datastore
  • func (d *Datastore) Batch(ctx context.Context) (ds.Batch, error)
  • func (d *Datastore) Close() error
  • func (d *Datastore) Delete(ctx context.Context, key ds.Key) error
  • func (d *Datastore) Get(ctx context.Context, key ds.Key) (value []byte, err error)
  • func (d *Datastore) GetSize(ctx context.Context, key ds.Key) (int, error)
  • func (d *Datastore) Has(ctx context.Context, key ds.Key) (exists bool, err error)
  • func (ds *Datastore) NewTransaction(ctx context.Context, _ bool) (datastore.Txn, error)
  • func (d *Datastore) Put(ctx context.Context, key ds.Key, value []byte) error
  • func (d *Datastore) Query(ctx context.Context, q dsq.Query) (dsq.Results, error)
  • func (d *Datastore) Sync(ctx context.Context, key ds.Key) error
• type Queries

Variables

var ErrNotImplemented = fmt.Errorf("not implemented")

ErrNotImplemented is returned when the SQL datastore does not yet implement the function call.

Types

type Datastore

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

Datastore is a SQL backed datastore.

func NewDatastore

func NewDatastore(db *sql.DB, queries Queries) *Datastore

NewDatastore returns a new SQL datastore.

func (*Datastore) Batch

func (d *Datastore) Batch(ctx context.Context) (ds.Batch, error)

Batch creates a set of deferred updates to the database. Since SQL does not support a true batch of updates, operations are buffered and then executed sequentially over a single connection when Commit is called.

func (*Datastore) Close

func (d *Datastore) Close() error

Close closes the underying SQL database.

func (*Datastore) Delete

func (d *Datastore) Delete(ctx context.Context, key ds.Key) error

Delete removes a row from the SQL database by the given key.

func (*Datastore) Get

func (d *Datastore) Get(ctx context.Context, key ds.Key) (value []byte, err error)

Get retrieves a value from the SQL database by the given key.

func (*Datastore) GetSize

func (d *Datastore) GetSize(ctx context.Context, key ds.Key) (int, error)

GetSize determines the size in bytes of the value for a given key.

func (*Datastore) Has

func (d *Datastore) Has(ctx context.Context, key ds.Key) (exists bool, err error)

Has determines if a value for the given key exists in the SQL database.

func (*Datastore) NewTransaction

func (ds *Datastore) NewTransaction(ctx context.Context, _ bool) (datastore.Txn, error)

NewTransaction creates a new database transaction, note the readOnly parameter is ignored by this implementation.

func (*Datastore) Put

func (d *Datastore) Put(ctx context.Context, key ds.Key, value []byte) error

Put "upserts" a row into the SQL database.

func (*Datastore) Query

func (d *Datastore) Query(ctx context.Context, q dsq.Query) (dsq.Results, error)

Query returns multiple rows from the SQL database based on the passed query parameters.

func (*Datastore) Sync

func (d *Datastore) Sync(ctx context.Context, key ds.Key) error

Sync is noop for SQL databases.

type Queries

type Queries interface {
	Delete() string
	Exists() string
	Get() string
	Put() string
	Query() string
	Prefix() string
	Limit() string
	Offset() string
	GetSize() string
}

Queries generates SQL queries for datastore operations.