Skip to content
Simple document database built on the JSON features of MySQL, PostgreSQL, and SQLite
Go Other
  1. Go 99.6%
  2. Other 0.4%
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.circleci
dialect
internal
test
.gitignore
LICENSE
Makefile
NOTES
README.md
client.go
client_test.go
condition.go
condition_test.go
constants.go
delete.go
delete_test.go
dialect.go
dialect_test.go
docker-compose.yml
errors.go
field.go
helper_test.go
insert.go
insert_test.go
keys.go
options.go
order.go
order_test.go
placeholder.go
placeholder_test.go
query.go
row.go
row_test.go
rows.go
select.go
select_test.go
tx.go
tx_test.go
update.go
update_test.go
where.go
where_test.go

README.md

jdb

jdb is a simple document database built on the JSON features of MySQL, PostgreSQL, and SQLite.

It provides a migration helper, query builder, and struct mapper.

This package is currently in development and the API is not stable.

Usage

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/silas/jdb"
	_ "github.com/silas/jdb/dialect/sqlite3"
)

type Name struct {
	GivenName  string   `jdb:",omitempty"`
	FamilyName string   `jdb:",omitempty"`
	Aliases    []string `jdb:",omitempty"`
}

type User struct {
	Kind       string    `jdb:"-kind"`
	ID         string    `jdb:"-id"`
	Email      string    `jdb:",uniquestringkey"`
	Name       Name      `jdb:",omitempty"`
	Age        int       `jdb:",omitempty"`
	CreateTime time.Time `jdb:"-createtime"`
	UpdateTime time.Time `jdb:"-updatetime"`
}

func (u User) DatabaseNumericKey() (*float64, bool) {
	if u.Age > 0 {
		age := float64(u.Age)
		return &age, true
	} else {
		return nil, true
	}
}

func main() {
	ctx := context.Background()

	db, err := jdb.Open("sqlite3", "test.db?cache=shared")
	if err != nil {
		panic(err)
	}

	err = db.Migrate(ctx)
	if err != nil {
		panic(err)
	}

	err = db.Tx(ctx, func(tx *jdb.Tx) error {
		user := User{
			ID:    "1",
			Email: "jane@example.com",
			Name: Name{
				GivenName:  "Jane",
				FamilyName: "Doe",
				Aliases:    []string{"Janie", "Roe"},
			},
			Age: 34,
		}

		err = db.Query("user").Insert(user).Exec(ctx, tx)
		if err != nil {
			return err
		}

		return tx.Commit()
	})
	if err != nil {
		panic(err)
	}

	err = db.Tx(ctx, func(tx *jdb.Tx) error {
		var user User
		err = db.Query("user").Get("1").Select().First(ctx, tx, &user)
		if err != nil {
			return err
		}

		fmt.Println(user)

		return nil
	})
	if err != nil {
		panic(err)
	}
}

Run with required sqlite3 flags:

$ go run \
  -tags "libsqlite3 $(uname -s | tr '[:upper:]' '[:lower:]') json1" \
  main.go

This will result in a row that looks something like the following:

sqlite> SELECT * FROM jdb WHERE kind = 'user' AND id = '1';
             kind = user
               id = 1
      parent_kind = ¤
        parent_id = ¤
unique_string_key = jane@example.com
       string_key = ¤
      numeric_key = 34.0
         time_key = ¤
             data = {"Email":"jane@example.com","Name":{"GivenName":"Jane","FamilyName":"Doe","Aliases":["Janie","Roe"]},"Age":34}
      create_time = 2018-06-12 12:41:37.035
      update_time = 2018-06-12 12:41:37.035

With the following schema (in SQLite):

sqlite> .schema
CREATE TABLE jdb (
  kind VARCHAR(64),
  id VARCHAR(64),
  parent_kind VARCHAR(64),
  parent_id VARCHAR(64),
  unique_string_key VARCHAR(255),
  string_key VARCHAR(255),
  numeric_key REAL,
  time_key DATETIME,
  data JSON,
  create_time DATETIME NOT NULL DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')),
  update_time DATETIME NOT NULL DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')),
  PRIMARY KEY (kind, id),
  FOREIGN KEY (parent_kind, parent_id) REFERENCES jdb (kind, id)
);
CREATE INDEX jdb_r2 ON jdb (create_time);
CREATE INDEX jdb_r3 ON jdb (update_time);
CREATE UNIQUE INDEX jdb_r4 ON jdb (kind, unique_string_key);
CREATE INDEX jdb_r5 ON jdb (kind, string_key);
CREATE INDEX jdb_r6 ON jdb (kind, numeric_key);
CREATE INDEX jdb_r7 ON jdb (kind, time_key);
CREATE INDEX jdb_r8 ON jdb (kind, create_time);
CREATE INDEX jdb_r9 ON jdb (kind, update_time);
CREATE INDEX jdb_r10 ON jdb (kind, id, parent_kind);
CREATE INDEX jdb_r11 ON jdb (parent_kind, parent_id, kind, unique_string_key);
CREATE INDEX jdb_r12 ON jdb (parent_kind, parent_id, kind, string_key);
CREATE INDEX jdb_r13 ON jdb (parent_kind, parent_id, kind, numeric_key);
CREATE INDEX jdb_r14 ON jdb (parent_kind, parent_id, kind, time_key);
CREATE INDEX jdb_r15 ON jdb (parent_kind, parent_id, kind, create_time);
CREATE INDEX jdb_r16 ON jdb (parent_kind, parent_id, kind, update_time);

License

This work is licensed under the MIT License (see the LICENSE and NOTICE files).

You can’t perform that action at this time.