bartlett

README

Bartlett

Bartlett is a library that automatically generates a CRUD API for your Go web application.

Usage

Invoke Bartlett by providing a database connection, a Bartlett driver, a slice of tables, and a function that returns a userID. Bartlett will return a slice of structs with paths corresponding to your table names and a request handler for each one.

Server Setup

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"
    _ "github.com/go-sql-driver/mysql"
    "github.com/royallthefourth/bartlett"
    "github.com/royallthefourth/bartlett/mariadb"
)

func indexPage(w http.ResponseWriter, r *http.Request) {
    fmt.Fprint(w, `Welcome to your Bartlett application! The interesting parts are mounted under /api`)
}

func dummyUserProvider(_ *http.Request) (interface{}, error) {
    return 0, nil // In a real application, use a closure that includes your session handler to generate a user ID. 
}

func main() {
    http.HandleFunc(`/`, indexPage)
    
    // The students table will be available from the API, but the rest of the database will not.
    tables := []bartlett.Table{
    	{
            Name: `students`,
            UserID: `student_id`, // Requests will only return rows corresponding to their ID for this table.
    	},
    }
    db, err := sql.Open("mysql", ":@/school")
    if err != nil {
        log.Fatal(err)
    }
    
    // Bartlett is not a web application.
    // Instead, it is a tool that allows you to quickly add an API to your existing application.
    b := bartlett.Bartlett{DB: db, Driver: &mariadb.MariaDB{}, Tables: tables, Users: dummyUserProvider}
    routes := b.Routes()
    for _, route := range routes {
    	http.HandleFunc(`/api` + route.Path, route.Handler) // Adds /api/students to the server.
    }
    
    log.Fatal(http.ListenAndServe(`:8080`, nil))
}

See the todo list demo application for a bigger example.

Choosing Tables

You may manually select tables to put into your API by providing a slice of bartlett.Table when you create your Bartlett struct. As a quick alternative, you may also invoke the Bartlett.probeTables() method to populate the internal table list automatically:

b := bartlett.Bartlett{DB: db, Driver: &mariadb.MariaDB{}, Users: dummyUserProvider}
b.ProbeTables(false)

ProbeTables accepts one argument to decide whether the probed tables should be writable or not. This should almost always be set to false!

Querying

SELECT

To SELECT from a table, make a GET request to its corresponding URL. For example, SELECT * FROM students; against the example above may be achieved by curl -XGET http://localhost:8080/students The result set will be emitted as a JSON array:

[
    {
        "student_id": 1,
        "age": 18,
        "grade": 85
    },
    {
        "student_id": 2,
        "age": 20,
        "grade": 91
    }
]

Note that all results are emitted as an array, even if there is only one row.

Requests may filter columns by the select= query parameter, eg /students?select=student_id,grade

WHERE

To filter on simple WHERE conditions, specify a column name as a query string parameter and the conditions as the value. For example: /students?age=eq.20 produces WHERE age = 20.

Operator	SQL	Note
eq	=
neq	!=
gt	>
gte	>=
lt	<
lte	<=
like	LIKE	use * in place of %
is	IS	eg is.true or is.null
in	IN	eg in."hi, there","bye"

Any of these conditions can be negated by prefixing it with not. eg /students?age=not.eq.20

ORDER BY

To order results, add order to the query: /students?order=student_id

Order by mutliple columns by separating them with ,: /students?order=age,grade

Choose ASC or DESC by appending .asc or .desc to the field name: /students?order=age.asc,grade.desc

LIMIT and OFFSET

To restrict result output, add limit. The request /students?limit=10 will return 10 results.

To add an offset, use offset in your query: /students?limit=10&offset=2 will return 10 after skipping the first 2 results.

INSERT

To write rows to a table, make a POST request to the corresponding table's URL. Your request payload may come in the form of a JSON array of rows to insert or a single value.

Inserts return an object containing an array of error messages and the IDs of all successful inserts.

To generate your own surrogate key for each row, identify in your Table struct an IDColumn. Provide a function that returns a new ID each time it's invoked. This column will be protected from tampering by users. The UserID column is also filtered out incoming POST requests.

UPDATE

To run an UPDATE query, issue a PATCH request. Set your WHERE params on the URL exactly the way you do with a SELECT. Any PATCH requests that do not have a WHERE will be rejected for your safety.

PATCH requests must include a JSON payload body with the fields to be updated and their values:

{
  "age": 71,
  "name": "Alex"
}

DELETE

To delete rows from a table, make a DELETE request to the corresponding table's URL.

You must specify at least one WHERE clause, otherwise the request will return an error. This is a design feature to prevent users from deleting everything by mistake.

Status

This project is under heavy development. Bartlett currently supports SQLite3 and MariaDB. Postgres support is planned once support for existing databases is more robust. Most data types are not yet under test and may not produce useful results. Some MariaDB types do not have a clear JSON representation. These types are marshaled as []byte.

Security

Taking user input from the web to paste into a SQL query does present some hazards. The only place where user input is placed into queries is by parameter placeholders. All other dynamic SQL strings are generated from the strings passed into the arguments at startup time, never from the URL.

To restrict access per-row, specify a column name in your Table.UserID. Tables with a UserID set will always filter according to Table.UserID = ? with the result of the userProvider function.

Prior Art

This project is inspired by Postgrest. Instead of something that runs everything on its own, though, I prefer a tool that integrates with my existing application.

Documentation

Overview

Package bartlett automatically generates an API from your database schema.

Index

• type Bartlett
  • func (b *Bartlett) ProbeTables(writable bool) *Bartlett
  • func (b *Bartlett) Routes() []Route
• type Driver
• type IDSpec
• type Route
• type Table
• type UserIDProvider

Types

type Bartlett

type Bartlett struct {
	DB     *sql.DB
	Driver Driver
	Tables []Table
	Users  UserIDProvider
}

Bartlett holds all of the configuration necessary to generate an API from the database.

func (*Bartlett) ProbeTables

func (b *Bartlett) ProbeTables(writable bool) *Bartlett

func (*Bartlett) Routes

func (b *Bartlett) Routes() []Route

Routes generates all of the paths and handlers for the tables specified in Bartlett. Iterate this output to feed it into your web server, prefix or otherwise alter the route names, and add filtering to the handler functions.

type Driver

type Driver interface {
	GetColumns(db *sql.DB, t Table) ([]string, error)
	MarshalResults(rows *sql.Rows, w http.ResponseWriter) error
	ProbeTables(db *sql.DB) []Table
}

The Driver interface contains database-specific code, which I'm trying to keep to a minimum. Implement a column-identifying function and a result marshaling function for your database of choice.

type IDSpec

type IDSpec struct {
	Name      string
	Generator func() interface{}
}

An IDSpec is used for primary keys that are generated by the application rather than the database. If you need a random ID, for example, this is where you would generate it. This column is protected from tampering by API users in INSERT and UPDATE operations.

type Route

type Route struct {
	Handler http.HandlerFunc
	Path    string
}

type Table

type Table struct {
	Name     string
	IDColumn IDSpec
	Writable bool
	UserID   string
	// contains filtered or unexported fields
}

A Table represents a table in the database. Name is required. Writable determines whether the table allows INSERT, UPDATE, and DELETE queries. Default is read-only. UserID is the name of column containing user IDs. It should match the output of the UserIDProvider passed to Bartlett. If UserID is left blank, all rows will be available regardless of the UserIDProvider.

type UserIDProvider

type UserIDProvider func(r *http.Request) (interface{}, error)

A UserIDProvider is a function that is able to use an incoming request to produce a user ID.