sqliteadmin

README

SQLite Admin

SQLite Admin is a Golang library and binary which enables you to easily interact with a SQLite database. It allows you to:

• Browse tables and their schemas.
• View table data along with adding filters, limits and offsets.
• Modify individual columns in existing rows.

It can either be integrated into an existing Golang backend as a library or installed as a binary.

The web server can be interacted with by going to https://sqliteadmin.dev.

The source code for the web UI can be found at https://github.com/joelseq/sqliteadmin-ui

Motivation

SQLite is very easy to add as an embedded database but it's difficult to manage the database once it's deployed in an application.

Existing tools primarily focus on local SQLite files, requiring manual interaction through CLI tools or desktop applications. If your SQLite database is running embedded within an application, there are few (if any) solutions that let you inspect, query, and modify it without complex workarounds.

The alternative is to use a cloud-hosted version like those provided by Turso which enables interacting with the database using tools like Drizzle Studio. This adds complexity to the setup and deployment of your application and you lose out on the value of having an embedded database.

This project fills that gap by providing an easy way to view and manage an embedded SQLite database via a web UI — no need to migrate to a cloud provider or use ad-hoc solutions.

Using as a library

config := sqliteadmin.Config{
  DB: db,               // *sql.DB
  Username: "username", // username to use to login from https://sqliteadmin.dev
  Password: "password", // password to use to login from https://sqliteadmin.dev
  Logger: logger,       // optional, implements the Logger interface
}
admin := sqliteadmin.New(config)

// HandlePost is a HandlerFunc that you can pass in to your router
router.Post("/admin", admin.HandlePost)

Check out the full code at examples/chi/main.go.

You can also run the example to test out the admin UI:

go run examples/chi/main.go

This will spin up a server on http://localhost:8080. You can then interact with your local server by going to https://sqliteadmin.dev and passing in the following credentials:

username: user
password: password
endpoint: http://localhost:8080/admin

[!NOTE]
If you are seeing "An unexpected error occurred" when trying to connect, try disabling your adblock.

Installing as a binary

1. Using go install:

go install github.com/joelseq/sqliteadmin-go/cmd/sqliteadmin@latest

1. Using go build (after cloning the repository):

make build

This will add the sqliteadmin binary to /tmp/bin

Usage

In order to add authentication, the following environment variables are required: SQLITEADMIN_USERNAME, SQLITEADMIN_PASSWORD.

e.g.:

export SQLITEADMIN_USERNAME=user
export SQLITEADMIN_PASSWORD=password

Start the server

sqliteadmin serve <path to sqlite db> -p 8080

Your SQLite database can now be accessed by visiting https://sqliteadmin.dev and providing the credentials and endpoint (including port).

Inspiration

The UI is heavily inspired by Drizzle Studio.

Documentation

Index

• Constants
• Variables
• type APIError
  • func (e APIError) Error() string
• type Admin
  • func New(c Config) *Admin
  • func (a *Admin) HandlePost(w http.ResponseWriter, r *http.Request)
• type Case
• type Command
• type CommandRequest
• type Condition
  • func (c Condition) ConditionCaseType() string
• type Config
• type Filter
  • func (f Filter) ConditionCaseType() string
• type LogLevel
• type Logger
• type LogicalOperator
• type Operator

Constants

const (
	DefaultLimit  = 100
	DefaultOffset = 0
)

Variables

var (
	ErrMissingTableName    = errors.New("missing table name")
	ErrMissingRow          = errors.New("missing row")
	ErrInvalidOrMissingIds = errors.New("invalid or missing ids")
	ErrInvalidInput        = errors.New("invalid input")
)

Types

type APIError

type APIError struct {
	StatusCode int    `json:"statusCode"`
	Message    string `json:"message"`
}

func (APIError) Error

func (e APIError) Error() string

type Admin

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

func New

func New(c Config) *Admin

Returns a *Admin which has a HandlePost method that can be used to handle requests from https://sqliteadmin.dev.

func (*Admin) HandlePost

func (a *Admin) HandlePost(w http.ResponseWriter, r *http.Request)

Handles the incoming HTTP POST request. This is responsible for handling all the supported operations from https://sqliteadmin.dev

type Case

type Case interface {
	ConditionCaseType() string
}

type Command

type Command string

const (
	Ping       Command = "Ping"
	ListTables Command = "ListTables"
	GetTable   Command = "GetTable"
	DeleteRows Command = "DeleteRows"
	UpdateRow  Command = "UpdateRow"
)

type CommandRequest

type CommandRequest struct {
	Command Command                `json:"command"`
	Params  map[string]interface{} `json:"params"`
}

type Condition

type Condition struct {
	Cases           []Case          `json:"cases" mapstructure:"cases"`
	LogicalOperator LogicalOperator `json:"logicalOperator" mapstructure:"logicalOperator"`
}

func (Condition) ConditionCaseType

func (c Condition) ConditionCaseType() string

type Config

type Config struct {
	DB       *sql.DB
	Username string
	Password string
	Logger   Logger
}

type Filter

type Filter struct {
	Column   string   `json:"column"`
	Operator Operator `json:"operator"`
	Value    string   `json:"value"`
}

func (Filter) ConditionCaseType

func (f Filter) ConditionCaseType() string

type LogLevel

type LogLevel string

const (
	LogLevelInfo  LogLevel = "info"
	LogLevelDebug LogLevel = "debug"
)

type Logger

type Logger interface {
	Info(format string, args ...interface{})
	Error(format string, args ...interface{})
	Debug(format string, args ...interface{})
}

type LogicalOperator

type LogicalOperator string

const (
	LogicalOperatorAnd LogicalOperator = "and"
	LogicalOperatorOr  LogicalOperator = "or"
)

type Operator

type Operator string

const (
	OperatorEquals              Operator = "eq"
	OperatorLike                Operator = "like"
	OperatorNotEquals           Operator = "neq"
	OperatorLessThan            Operator = "lt"
	OperatorLessThanOrEquals    Operator = "lte"
	OperatorGreaterThan         Operator = "gt"
	OperatorGreaterThanOrEquals Operator = "gte"
	OperatorIsNull              Operator = "null"
	OperatorIsNotNull           Operator = "notnull"
)