esperanto

package module
v0.1.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Sep 15, 2022 License: MIT Imports: 5 Imported by: 0

README

esperanto

go.dev reference Go Report Card golangci-lint tippin.me GitHub tag (latest SemVer)

esperanto is a database access layer. It is based upon ...

This module can help you better organize your queries, especially if you need support for multiple dialects.

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"

	"github.com/wroge/esperanto"
	"github.com/wroge/scan"
	"github.com/wroge/superbasic"

	_ "modernc.org/sqlite"
)

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

	stdDB, err := sql.Open("sqlite", ":memory:?_pragma=foreign_keys(1)")
	if err != nil {
		panic(err)
	}

	db := esperanto.StdDB{
		Placeholder: "?",
		DB:          stdDB,
	}

	err = esperanto.Exec(ctx, db, esperanto.Sqlite, DropPostAuthors, DropPosts, DropAuthors, CreateAuthors, CreatePosts, CreatePostAuthors)
	if err != nil {
		panic(err)
	}

	authorEntities, err := esperanto.Query(ctx, db, esperanto.Sqlite, AuthorInsert, []string{"Jim", "Tim", "Tom"})
	if err != nil {
		panic(err)
	}

	fmt.Println(authorEntities)
	// [{1 Jim} {2 Tim} {3 Tom}]

	postEntities, err := esperanto.QueryAndExec(ctx, db, esperanto.Sqlite, PostInsert, []InsertPostOptions{
		{
			Title:   "Post One",
			Authors: []int64{1, 2},
		},
	}, PostAuthorsInsert)
	if err != nil {
		panic(err)
	}

	fmt.Println(postEntities)
	// [{1 Post One}]

	postTwo, err := esperanto.QueryAndExecOne(ctx, db, esperanto.Sqlite, PostInsertOne, InsertPostOptions{
		Title:   "Post Two",
		Authors: []int64{2, 3},
	}, PostAuthorsInsertOne)
	if err != nil {
		panic(err)
	}

	fmt.Println(postTwo)
	// {2 Post Two []}

	authors, err := esperanto.Query(ctx, db, esperanto.Sqlite, AuthorQuery, QueryAuthorOptions{})
	if err != nil {
		panic(err)
	}

	fmt.Println(authors)
	// [{1 Jim [{1 Post One}]} {2 Tim [{1 Post One} {2 Post Two}]} {3 Tom [{2 Post Two}]}]

	posts, err := esperanto.Query(ctx, db, esperanto.Sqlite, PostQuery, QueryPostOptions{})
	if err != nil {
		panic(err)
	}

	fmt.Println(posts)
	// [{1 Post One [{1 Jim} {2 Tim}]} {2 Post Two [{2 Tim} {3 Tom}]}]
}

type PostEntity struct {
	ID    int64
	Title string
}

type Post struct {
	ID      int64
	Title   string
	Authors []AuthorEntity
}

type AuthorEntity struct {
	ID   int64
	Name string
}

type Author struct {
	ID    int64
	Name  string
	Posts []PostEntity
}

func DropAuthors(dialect esperanto.Dialect) superbasic.Expression {
	return superbasic.SQL("DROP TABLE IF EXISTS authors")
}

func CreateAuthors(dialect esperanto.Dialect) superbasic.Expression {
	return superbasic.Compile("CREATE TABLE IF NOT EXISTS authors (\n\t?\n)",
		superbasic.Join(",\n\t",
			superbasic.Switch(dialect,
				superbasic.Case(esperanto.Postgres, superbasic.SQL("id SERIAL PRIMARY KEY")),
				superbasic.Case(esperanto.Sqlite, superbasic.SQL("id INTEGER PRIMARY KEY AUTOINCREMENT")),
			),
			superbasic.SQL("name TEXT NOT NULL"),
		),
	)
}

type QueryAuthorOptions struct{}

func AuthorQuery(dialect esperanto.Dialect, options QueryAuthorOptions) (superbasic.Expression, []scan.Column[Author]) {
	return superbasic.Compile(`
	SELECT authors.id, authors.name, ? AS posts
	FROM authors
	LEFT JOIN post_authors ON post_authors.author_id = authors.id
	LEFT JOIN posts ON posts.id = post_authors.post_id
	GROUP BY authors.id, authors.name`,
			superbasic.Switch(dialect,
				superbasic.Case(esperanto.Postgres, superbasic.SQL("JSON_AGG(JSON_BUILD_OBJECT('id', posts.id, 'title', posts.title))")),
				superbasic.Case(esperanto.Sqlite, superbasic.SQL(`CASE WHEN posts.id IS NULL THEN '[]' 
				ELSE JSON_GROUP_ARRAY(JSON_OBJECT('id', posts.id, 'title', posts.title)) END`)),
			),
		),
		[]scan.Column[Author]{
			scan.Any(func(author *Author, id int64) { author.ID = id }),
			scan.Any(func(author *Author, name string) { author.Name = name }),
			scan.AnyErr(func(author *Author, posts []byte) error { return json.Unmarshal(posts, &author.Posts) }),
		}
}

func AuthorInsert(dialect esperanto.Dialect, names []string) (superbasic.Expression, []scan.Column[AuthorEntity]) {
	return superbasic.Compile("INSERT INTO authors (name) VALUES ? RETURNING id, name", superbasic.Join(", ", superbasic.Map(names, func(_ int, name string) superbasic.Expression {
			return superbasic.Values{name}
		})...)),
		[]scan.Column[AuthorEntity]{
			scan.Any(func(author *AuthorEntity, id int64) { author.ID = id }),
			scan.Any(func(author *AuthorEntity, name string) { author.Name = name }),
		}
}

func AuthorInsertOne(dialect esperanto.Dialect, name string) (superbasic.Expression, []scan.Column[AuthorEntity]) {
	return superbasic.SQL("INSERT INTO authors (name) VALUES (?) RETURNING id, name", name),
		[]scan.Column[AuthorEntity]{
			scan.Any(func(author *AuthorEntity, id int64) { author.ID = id }),
			scan.Any(func(author *AuthorEntity, name string) { author.Name = name }),
		}
}

func DropPosts(dialect esperanto.Dialect) superbasic.Expression {
	return superbasic.SQL("DROP TABLE IF EXISTS posts")
}

func DropPostAuthors(dialect esperanto.Dialect) superbasic.Expression {
	return superbasic.SQL("DROP TABLE IF EXISTS post_authors")
}

func CreatePosts(dialect esperanto.Dialect) superbasic.Expression {
	return superbasic.Compile("CREATE TABLE IF NOT EXISTS posts (\n\t?\n)",
		superbasic.Join(",\n\t",
			superbasic.Switch(dialect,
				superbasic.Case(esperanto.Postgres, superbasic.SQL("id SERIAL PRIMARY KEY")),
				superbasic.Case(esperanto.Sqlite, superbasic.SQL("id INTEGER PRIMARY KEY AUTOINCREMENT")),
			),
			superbasic.SQL("title TEXT NOT NULL"),
		))
}

func CreatePostAuthors(dialect esperanto.Dialect) superbasic.Expression {
	return superbasic.Compile("CREATE TABLE IF NOT EXISTS post_authors (\n\t?\n)",
		superbasic.Join(",\n\t",
			superbasic.SQL("post_id INTEGER REFERENCES posts (id) ON DELETE CASCADE"),
			superbasic.SQL("author_id INTEGER REFERENCES authors (id) ON DELETE RESTRICT"),
			superbasic.SQL("PRIMARY KEY (post_id, author_id)"),
		),
	)
}

type QueryPostOptions struct{}

func PostQuery(dialect esperanto.Dialect, options QueryPostOptions) (superbasic.Expression, []scan.Column[Post]) {
	return superbasic.Compile(`
	SELECT posts.id, posts.title, ? AS authors
	FROM posts
	LEFT JOIN post_authors ON post_authors.post_id = posts.id
	LEFT JOIN authors ON authors.id = post_authors.author_id
	GROUP BY posts.id, posts.title`,
			superbasic.Switch(dialect,
				superbasic.Case(esperanto.Postgres, superbasic.SQL("JSON_AGG(JSON_BUILD_OBJECT('id', authors.id, 'name', authors.name))")),
				superbasic.Case(esperanto.Sqlite, superbasic.SQL(`CASE WHEN authors.id IS NULL THEN '[]' 
		ELSE JSON_GROUP_ARRAY(JSON_OBJECT('id', authors.id, 'name', authors.name)) END`)),
			)),
		[]scan.Column[Post]{
			scan.Any(func(post *Post, id int64) { post.ID = id }),
			scan.Any(func(post *Post, title string) { post.Title = title }),
			scan.AnyErr(func(post *Post, authors []byte) error { return json.Unmarshal(authors, &post.Authors) }),
		}
}

type InsertPostOptions struct {
	Title   string
	Authors []int64
}

func PostInsert(dialect esperanto.Dialect, options []InsertPostOptions) (superbasic.Expression, []scan.Column[PostEntity]) {
	return superbasic.Compile("INSERT INTO posts (title) VALUES ? RETURNING id, title", superbasic.Join(", ", superbasic.Map(options, func(_ int, option InsertPostOptions) superbasic.Expression {
			return superbasic.Values{option.Title}
		})...)),
		[]scan.Column[PostEntity]{
			scan.Any(func(post *PostEntity, id int64) { post.ID = id }),
			scan.Any(func(post *PostEntity, title string) { post.Title = title }),
		}
}

func PostAuthorsInsert(dialect esperanto.Dialect, options []InsertPostOptions, entities []PostEntity) superbasic.Expression {
	return superbasic.Compile("INSERT INTO post_authors (post_id, author_id) VALUES ?",
		superbasic.Join(", ", superbasic.Map(entities, func(index int, entity PostEntity) superbasic.Expression {
			return superbasic.Join(", ", superbasic.Map(options[index].Authors, func(_ int, author int64) superbasic.Expression {
				return superbasic.Values{entity.ID, author}
			})...)
		})...),
	)
}

func PostInsertOne(dialect esperanto.Dialect, options InsertPostOptions) (superbasic.Expression, []scan.Column[Post]) {
	return superbasic.SQL("INSERT INTO posts (title) VALUES (?) RETURNING id, title", options.Title),
		[]scan.Column[Post]{
			scan.Any(func(post *Post, id int64) { post.ID = id }),
			scan.Any(func(post *Post, title string) { post.Title = title }),
		}
}

func PostAuthorsInsertOne(dialect esperanto.Dialect, insert InsertPostOptions, post Post) superbasic.Expression {
	return superbasic.Compile("INSERT INTO post_authors (post_id, author_id) VALUES ?",
		superbasic.Join(", ", superbasic.Map(insert.Authors, func(_ int, author int64) superbasic.Expression {
			return superbasic.Values{post.ID, author}
		})...),
	)
}

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Exec added in v0.1.0 

func Exec(ctx context.Context, db DB, dialect Dialect, executables ...Executable) error

func Query added in v0.1.0 

func Query[MODEL, OPTIONS any](
	ctx context.Context,
	db DB,
	dialect Dialect,
	queryable Queryable[MODEL, OPTIONS],
	options OPTIONS) ([]MODEL, error)

func QueryAndExec added in v0.1.0 

func QueryAndExec[MODEL, OPTIONS any](
	ctx context.Context,
	db DB,
	dialect Dialect,
	queryable Queryable[MODEL, OPTIONS],
	options OPTIONS,
	executables ...QueryExecutable[MODEL, OPTIONS]) ([]MODEL, error)

func QueryAndExecOne added in v0.1.0 

func QueryAndExecOne[MODEL, OPTIONS any](
	ctx context.Context,
	db DB, dialect Dialect,
	queryable Queryable[MODEL, OPTIONS],
	options OPTIONS,
	executables ...QueryOneExecutable[MODEL, OPTIONS]) (MODEL, error)

func QueryOne added in v0.1.0 

func QueryOne[MODEL, OPTIONS any](
	ctx context.Context,
	db DB,
	dialect Dialect,
	queryable Queryable[MODEL, OPTIONS],
	options OPTIONS) (MODEL, error)

Types

type DB added in v0.1.0 

type DB interface {
	Close() error
	Begin(ctx context.Context) (Tx, error)
	Query(ctx context.Context, expression superbasic.Expression) (scan.Rows, error)
	QueryRow(ctx context.Context, expression superbasic.Expression) scan.Row
	Exec(ctx context.Context, expression superbasic.Expression) error
}

type Dialect 

type Dialect string

Dialect can be any string to distinguish between different syntaxes of databases. 

const (
	MySQL     Dialect = "mysql"
	Sqlite    Dialect = "sqlite"
	Postgres  Dialect = "postgres"
	Oracle    Dialect = "oracle"
	SQLServer Dialect = "sqlserver"
)

type Executable added in v0.1.0 

type Executable func(dialect Dialect) superbasic.Expression

type QueryExecutable added in v0.1.0 

type QueryExecutable[MODEL, OPTIONS any] func(dialect Dialect, options OPTIONS, models []MODEL) superbasic.Expression

type QueryOneExecutable added in v0.1.0 

type QueryOneExecutable[MODEL, OPTIONS any] func(dialect Dialect, options OPTIONS, model MODEL) superbasic.Expression

type Queryable added in v0.1.0 

type Queryable[MODEL, OPTIONS any] func(dialect Dialect, options OPTIONS) (superbasic.Expression, []scan.Column[MODEL])

type RollbackError added in v0.1.0 

type RollbackError struct {
	Err  error
	Wrap error
}

func (RollbackError) Error added in v0.1.0 

func (re RollbackError) Error() string

func (RollbackError) Unwrap added in v0.1.0 

func (re RollbackError) Unwrap() error

type RowError added in v0.1.0 

type RowError struct {
	Err error
}

func (RowError) Scan added in v0.1.0 

func (re RowError) Scan(dest ...any) error

type StdDB added in v0.1.0 

type StdDB struct {
	Placeholder string
	DB          *sql.DB
}

func (StdDB) Begin added in v0.1.0 

func (s StdDB) Begin(ctx context.Context) (Tx, error)

func (StdDB) Close added in v0.1.0 

func (s StdDB) Close() error

func (StdDB) Exec added in v0.1.0 

func (s StdDB) Exec(ctx context.Context, expression superbasic.Expression) error

func (StdDB) Query added in v0.1.0 

func (s StdDB) Query(ctx context.Context, expression superbasic.Expression) (scan.Rows, error)

func (StdDB) QueryRow added in v0.1.0 

func (s StdDB) QueryRow(ctx context.Context, expression superbasic.Expression) scan.Row

type StdTx added in v0.1.0 

type StdTx struct {
	Placeholder string
	Tx          *sql.Tx
}

func (StdTx) Commit added in v0.1.0 

func (s StdTx) Commit(ctx context.Context) error

func (StdTx) Exec added in v0.1.0 

func (s StdTx) Exec(ctx context.Context, expression superbasic.Expression) error

func (StdTx) Query added in v0.1.0 

func (s StdTx) Query(ctx context.Context, expression superbasic.Expression) (scan.Rows, error)

func (StdTx) QueryRow added in v0.1.0 

func (s StdTx) QueryRow(ctx context.Context, expression superbasic.Expression) scan.Row

func (StdTx) Rollback added in v0.1.0 

func (s StdTx) Rollback(ctx context.Context, err error) error

type Tx added in v0.1.0 

type Tx interface {
	Commit(ctx context.Context) error
	Rollback(ctx context.Context, err error) error
	Query(ctx context.Context, expression superbasic.Expression) (scan.Rows, error)
	QueryRow(ctx context.Context, expression superbasic.Expression) scan.Row
	Exec(ctx context.Context, expression superbasic.Expression) error
}

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.