sqlds

README

SQL Datastore

sqlds allows using arbitrary SQL databases as an IPFS datastore. It defines a set of functionality that can be used by any golang client that satisfies the database/sql interface. Included in an implementation for the PostgreSQL database. This datastore is forked from ipfs/go-ds-sql with a few modifications, and cleanup to the codebase, consisting of:

• Enable table recreation, migration, and index creation as an optional capability when using PostgreSQL
• Change testing scheme to ensure that the postgres functionality is tested and subsequently the datastore implementation is tested
  • Upstream doesn't specifically test the postgres implementation which is likely to introduce bugs.
• Use the uber-go/multierr package to combine errors where appropriatey
  • In transaction mode if any errors happen, the error returned from the rollback was not returned, instead we combine the error for better visibility

Usage (Postgres)

Easy (Automatic)

The postgres datastore has some helper utility to enable automatic table and index creation, as well as the ability to recreate the table by dropping it, and creating it. Note that this setting requires RunMigrations also set to true. If not the CreateIndex setting will be ignored. Additionally note that we will never delete tables unless specified.

Note that we will never delete tables automatically, and tables will only be deleted when RecreateTables is set to true, and AcceptRecreateWarning is set to the value of RecreateTables. Additionally you must make sure to set RunMigrations otherwise we wont actually create the tables, and instead just delete the existing tables.

The following options can be used to enable table creation, and index creation:

import (
    pgds "github.com/RTradeLtd/go-datastores/sql/postgres"
    "github.com/ipfs/go-datastore"
)
opts := &pgds.Options{
	Host:                  "127.0.0.1",
	Port:                  "5432",
	User:                  "postgres",
	Database:              "datastores",
	Password:              "password123",
    SSLMode:               "disable",
    Table:                  "blocks",
    // this will drop the existing table
	AcceptRecreateWarning: pgds.RecreateTables,
	RunMigrations:         true,
	RecreateTables:        true,
	CreateIndex:           true,
}
ds, err := opts.Create()
if err != nil {
	log.Fatal(err)
}
if err := ds.Put(
    datastore.NewKey("much test very wow"), 
    []byte("whose even going to read this anyways"),
); err != nil {
    log.Fatal(err)
}

Hard (Manual)

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/RTradeLtd/go-datastores/sql"
	pg "github.com/RTradeLtd/go-datastores/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)

license

As this is forked from upstream, it retains its existing license which can be found in LICENSE.orig.

Documentation

Index

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

Variables

var (

	// ErrNotImplemented is returned when the SQL datastore does not yet implement the function call.
	ErrNotImplemented = fmt.Errorf("not implemented")
)

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() (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(key ds.Key) error

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

func (*Datastore) Get

func (d *Datastore) Get(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(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(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(_ bool) (ds.Txn, error)

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

func (*Datastore) Put

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

Put "upserts" a row into the SQL database.

func (*Datastore) Query

func (d *Datastore) Query(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(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.