func CreateTestSqliteDB(t *testing.T) *TestSQLiteDB {
	dir, err := ioutil.TempDir("", "sqlite")
	if err != nil {
		t.Fatalf("sqlite test: %v", err)
	}
	db, err := sqliteConnection(log.NewNopLogger(), filepath.Join(dir, "watchman.db")).Connect()
	if err != nil {
		t.Fatalf("sqlite test: %v", err)
	}
	return &TestSQLiteDB{db, dir}
}
CreateTestSqliteDB returns a TestSQLiteDB which can be used in tests
as a clean sqlite database. All migrations are ran on the db before.

Callers should call close on the returned *TestSQLiteDB.
package database
import (
	"database/sql"
	"fmt"
	"io/ioutil"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"testing"
	"time"
	"github.com/go-kit/kit/log"
	kitprom "github.com/go-kit/kit/metrics/prometheus"
	"github.com/lopezator/migrator"
	"github.com/mattn/go-sqlite3"
	stdprom "github.com/prometheus/client_golang/prometheus"
)
var (
	sqliteConnections = kitprom.NewGaugeFrom(stdprom.GaugeOpts{
		Name: "sqlite_connections",
		Help: "How many sqlite connections and what status they're in.",
	}, []string{"state"})
	sqliteVersionLogOnce sync.Once
	sqliteMigrations = migrator.Migrations(
		execsql(
			"create_customer_name_watches",
			`create table if not exists customer_name_watches(id primary key, name, webhook, auth_token, created_at datetime, deleted_at datetime);`,
		),
		execsql(
			"create_customer_status",
			`create table if not exists customer_status(customer_id primary key, user_id, note, status, created_at datetime, deleted_at datetime);`,
		),
		execsql(
			"create_customer_watches",
			`create table if not exists customer_watches(id primary key, customer_id, webhook, auth_token, created_at datetime, deleted_at datetime);`,
		),
		execsql(
			"create_company_name_watches",
			`create table if not exists company_name_watches(id primary key, name, webhook, auth_token, created_at datetime, deleted_at datetime);`,
		),
		execsql(
			"create_company_status",
			`create table if not exists company_status(company_id primary key, user_id, note, status, created_at datetime, deleted_at datetime);`,
		),
		execsql(
			"create_company_watches",
			`create table if not exists company_watches(id primary key, company_id, webhook, auth_token, created_at datetime, deleted_at datetime);`,
		),
		execsql(
			"create_ofac_download_stats",
			`create table if not exists ofac_download_stats(downloaded_at datetime, sdns, alt_names, addresses);`,
		),
		execsql(
			"create_webhook_stats",
			`create table if not exists webhook_stats(watch_id string, attempted_at datetime, status);`,
		),
		execsql(
			"add_denied_persons_to_ofac_download_stats",
			"alter table ofac_download_stats add column denied_persons default 0;",
		),
		execsql(
			"rename_ofac_download_stats",
			"alter table ofac_download_stats rename to download_stats",
		),
		execsql(
			"add_sectoral_sanctions_to_download_stats",
			"alter table download_stats add column sectoral_sanctions default 0;",
		),
		execsql(
			"add__bis_entities__to_download_stats",
			"alter table download_stats add column bis_entities default 0;",
		),
	)
)
type sqlite struct {
	path string
	connections *kitprom.Gauge
	logger      log.Logger
	err error
}
func (s *sqlite) Connect() (*sql.DB, error) {
	if s.err != nil {
		return nil, fmt.Errorf("sqlite had error %v", s.err)
	}
	sqliteVersionLogOnce.Do(func() {
		if v, _, _ := sqlite3.Version(); v != "" {
			s.logger.Log("main", fmt.Sprintf("sqlite version %s", v))
		}
	})
	db, err := sql.Open("sqlite3", s.path)
	if err != nil {
		return nil, err
	}
	if err := db.Ping(); err != nil {
		return db, err
	}
	if m, err := migrator.New(sqliteMigrations); err != nil {
		return db, err
	} else {
		if err := m.Migrate(db); err != nil {
			return db, err
		}
	}
	go func() {
		t := time.NewTicker(1 * time.Second)
		for range t.C {
			stats := db.Stats()
			s.connections.With("state", "idle").Set(float64(stats.Idle))
			s.connections.With("state", "inuse").Set(float64(stats.InUse))
			s.connections.With("state", "open").Set(float64(stats.OpenConnections))
		}
	}()
	return db, err
}
func sqliteConnection(logger log.Logger, path string) *sqlite {
	return &sqlite{
		path:        path,
		logger:      logger,
		connections: sqliteConnections,
	}
}
func getSqlitePath() string {
	path := os.Getenv("SQLITE_DB_PATH")
	if path == "" || strings.Contains(path, "..") {
		path = "watchman.db"
	}
	return path
}
type TestSQLiteDB struct {
	DB *sql.DB
	dir string  
}
func (r *TestSQLiteDB) Close() error {
	if err := r.DB.Close(); err != nil {
		return err
	}
	return os.RemoveAll(r.dir)
}