An early PostgreSQL implementation in Go

Overview

gosql

An early PostgreSQL implementation in Go.

gosql

Example

$ git clone [email protected]:eatonphil/gosql
$ cd gosql
$ go run cmd/main.go
Welcome to gosql.
# CREATE TABLE users (id INT PRIMARY KEY, name TEXT, age INT);
ok
# \d users
Table "users"
  Column |  Type   | Nullable
---------+---------+-----------
  id     | integer | not null
  name   | text    |
  age    | integer |
Indexes:
        "users_pkey" PRIMARY KEY, rbtree ("id")

# INSERT INTO users VALUES (1, 'Corey', 34);
ok
# INSERT INTO users VALUES (1, 'Max', 29);
Error inserting values: Duplicate key value violates unique constraint
# INSERT INTO users VALUES (2, 'Max', 29);
ok
# SELECT * FROM users WHERE id = 2;
  id | name | age
-----+------+------
   2 | Max  |  29
(1 result)
ok
# SELECT id, name, age + 3 FROM users WHERE id = 2 OR id = 1;
  id | name  | ?column?
-----+-------+-----------
   1 | Corey |       37
   2 | Max   |       32
(2 results)
ok

Using the database/sql driver

See cmd/sqlexample/main.go:

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/eatonphil/gosql"
)

func main() {
	db, err := sql.Open("postgres", "")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	_, err = db.Query("CREATE TABLE users (name TEXT, age INT);")
	if err != nil {
		panic(err)
	}

	_, err = db.Query("INSERT INTO users VALUES ('Terry', 45);")
	if err != nil {
		panic(err)
	}

	_, err = db.Query("INSERT INTO users VALUES ('Anette', 57);")
	if err != nil {
		panic(err)
	}

	rows, err := db.Query("SELECT name, age FROM users;")
	if err != nil {
		panic(err)
	}

	var name string
	var age uint64
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&name, &age)
		if err != nil {
			panic(err)
		}

		fmt.Printf("Name: %s, Age: %d\n", name, age)
	}

	if err = rows.Err(); err != nil {
		panic(err)
	}
}

Parameterization is not currently supported.

Architecture

  • cmd/main.go
    • Contains the REPL and high-level interface to the project
    • Dataflow is: user input -> lexer -> parser -> in-memory backend
  • lexer.go
    • Handles breaking user input into tokens for the parser
  • parser.go
    • Matches a list of tokens into an AST or fails if the user input is not a valid program
  • memory.go
    • An example, in-memory backend supporting the Backend interface (defined in backend.go)

Contributing

  • Add a new operator (such as -, *, etc.)
  • Add a new data type (such as `VARCHAR(n)``)

In each case, you'll probably have to add support in the lexer, parser, and in-memory backend. I recommend going in that order.

In all cases, make sure the code is formatted (make fmt), linted (make lint) and passes tests (make test). New code should have tests.

Blog series

Further reading

Here are some similar projects written in Go.

  • go-mysql-server
    • This is a MySQL frontend (with an in-memory backend for testing only).
  • ramsql
    • This is a WIP PostgreSQL-compatible in-memory database.
  • CockroachDB
    • This is a production-ready PostgreSQL-compatible database.
Issues
  • String style question :)

    String style question :)

    Looking through the code, there's a few uses of strings like "\\d", "\\p" and similar.

    Is there a reason for choosing the double quoted string style over backticks (eg `\d`), or is it just a personal preference thing? :smile:

    opened by justinclift 6
  • Report a `cursor` bug

    Report a `cursor` bug

    Hi, It's an awesome project! But panic throw out when I run the code, it seems to be a problem caused by cursor++ @L413. Maybe you have fixed it already.

    https://github.com/eatonphil/gosql/blob/6a788b65f6842344d79166f61a46f908e95dc7d5/parser.go#L402-L414

    Looking forward to view more commits.

    opened by bingbig 4
  • about  bindingPower

    about bindingPower

    The function mean is ?

    func (t Token) bindingPower() uint {
    	switch t.Kind {
    	case KeywordKind:
    		switch Keyword(t.Value) {
    		case AndKeyword:
    			fallthrough
    		case OrKeyword:
    			return 1
    		}
    	case SymbolKind:
    		switch Symbol(t.Value) {
    		case EqSymbol:
    			fallthrough
    		case NeqSymbol:
    			return 2
    
    		case LtSymbol:
    			fallthrough
    		case GtSymbol:
    			return 3
    
    		// For some reason these are grouped separately
    		case LteSymbol:
    			fallthrough
    		case GteSymbol:
    			return 4
    
    		case ConcatSymbol:
    			fallthrough
    		case PlusSymbol:
    			return 5
    		}
    	}
    
    	return 0
    }
    
    opened by Shuimo03 1
  • Blog update

    Blog update

    I was planning on following this, however, it seems its pretty far behind and has some issues with the code in the blog. Is it possible to get it updated to master? If not, could we create a branch where the simplicity of the blog is captured?

    Thanks for the article, I always love a lexing walkthrough I would love to follow it through code!

    opened by taybart 1
  • 3 questions

    3 questions

    1. production ready? use case?
    2. secondary unique key index possible? is text search possible?
    3. benchmark compared with postgres / mysql?

    great work by the way! keep it up. will use it. a lot of potential.

    opened by gitmko0 1
  • Add the code comments, documetation and golint

    Add the code comments, documetation and golint

    Hi,

    This is an exciting project. I wanted to go through the entire codebase and while doing so I found that it requires some code comments and documentation.

    opened by pallavJha 0
  • Add basic support for indexing

    Add basic support for indexing

    This PR:

    • Adds basic support for indexing
      • The index will be used for every applicable column that is joined to the top by ANDs
        • See the test cases here to more easily understand this
        • No other optimizations are made (e.g. constant eliding) and no transformations are made
      • The index uses a (third-party) rbtree
      • TODO: add support for EXPLAIN and/or EXPLAIN ANALYSE to see the query plan
    • Adds support for \dt and \d commands within the REPL
    • Adds support for \p SELECT * FROM users to pretty-print AST without evaluating
    • Adds support for null and handles it reasonable in operations
    • Adds support for PRIMARY KEY

    image image

    opened by eatonphil 0
  • Add \q to exit, fix exit/quit not working sometimes, small optimization

    Add \q to exit, fix exit/quit not working sometimes, small optimization

    Fixes an oversight in the previous PR: 'quit' and 'exit' ignore trailing whitespace on postgres, so now they do so here as well. Since \q is also a valid way to exit in postgres I've added it (\q ignores all whitespace)

    opened by krischerven 0
  • add boolean type

    add boolean type

    Hey!

    Added support for type BOOLEAN. All changes have been formatted and all tests pass.


    Details

    The implementation seemed to be partially complete before I started, as BoolType is already defined and used throughout memory.go.

    After adding boolKeyword to lexer.go and case "boolean" to func (mb *MemoryBackend) CreateTable in memory.go, I am able to run statements in the REPL like this:

    Welcome to gosql.
    # CREATE TABLE facts (fact TEXT, is_true BOOLEAN);
    ok
    # INSERT INTO facts VALUES ('the sun is purple', false);
    ok
    # SELECT fact, is_true FROM facts;
            fact        | is_true  
    --------------------+----------
      the sun is purple | false    
    (1 result)
    ok
    

    Am I missing any implementation details? As far as I can tell the type is now supported.

    -- Parker

    opened by parkerduckworth 0
  • Add support for binary expressions, filtering via WHERE

    Add support for binary expressions, filtering via WHERE

    This upgrades the parser to support binary expressions which was a prerequisite for filtering with WHERE. INSERTS and SELECTS now pass all values through an evaluation stage so expressions should work everywhere as expected.

    Here are a few examples:

    CREATE TABLE users (name TEXT, age INT);
    INSERT INTO users ('Phil' || 'Eaton', 2 + 4);
    SELECT age + 1, name FROM users;
    

    WHERE support is added by filtering on top of a full in-memory table. This branch does NOT ADD indexes. So the design of fetching data will likely change when indexes are added.

    Furthermore, this branch does not try to handle operator precedence correctly. The only way to get correct operator precedence is via parenthesis.

    Here is an example of WHERE filtering:

    # create table users (name text, age int);
    ok
    # insert into users values ('Kevin', 45);
    ok
    # insert into users values ('Rachelle', 34);
    ok
    # insert into users values ('Sal', 45);
    ok
    # insert into users values ('Courtney', 40);
    ok
    # select name, age from users where age = 40;
        name   | age
    -----------+------
      Courtney |  40
    (1 result)
    ok
    # select name, age from users where (age = 40) or (age = 45);
        name   | age
    -----------+------
      Kevin    |  45
      Sal      |  45
      Courtney |  40
    (3 results)
    ok
    

    This branch also upgrades the REPL and in-memory backend to not panic, simply display errors, and proceed along to the next awaited line. Additionally, it uses two new third-party packages for providing readline support and prettier tables.

    opened by eatonphil 0
  • Upgrade lexer

    Upgrade lexer

    The original lexer was written extremely lazily, trying to treat every lexical token the same. Among other issues, it prevented "special characters" like whitespace in strings.

    This rewrites the lexer to use a similar pattern as the parser: giving control to helper functions to lex different kinds of tokens, returning a pointer to the next not lex-ed character on success.

    The two major features this adds support for is:

    • Support for all allowed characters in strings (including single quotes escaped by a single quote)
    • Support for double quoted identifiers for case preservation, lower-casing un-quoted identifiers

    This also improves the accuracy of token location tracking.

    Blog post to follow.

    opened by eatonphil 0
Owner
Phil Eaton
Software Development Manager @oracle; previously @capsule8, @linode | @phil_eaton on Twitter
Phil Eaton
igor is an abstraction layer for PostgreSQL with a gorm like syntax.

igor igor is an abstraction layer for PostgreSQL, written in Go. Igor syntax is (almost) compatible with GORM. When to use igor You should use igor wh

Paolo Galeone 86 May 9, 2022
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 3.2k Aug 12, 2022
This repo is for early testing of GoFBP ideas and trial balloons!

gofbp This repo holds the beginning of an FBP implementation in Go Features include: delayed start of goroutines (FBP processes), unless MustRun attri

Paul Morrison 19 Jul 29, 2022
An SSH honeypot written in Go. Very early in development.

gopot An SSH honeypot written in Go. Very early in development. A while back I was working with Cowrie and thought the idea was something I'd like to

Evan Edwards 2 Aug 1, 2022
The Babylon project is a re-creation of my original PBX network integration tools from the early 1990's, in go.

What is Babylon? The Babylon project is a re-creation of many of my original PBX network integration tools from the early 1990's, in go. Many of these

David Sugar 1 Jan 23, 2022
Go code for PostgreSQL. A Go language code which connects to PostgreSQL database for CRUD operation

Go code for PostgreSQL. A Go language code which connects to PostgreSQL database for CRUD operation

null 1 Jan 25, 2022
Go-Postgresql-Query-Builder - A query builder for Postgresql in Go

Postgresql Query Builder for Go This query builder aims to make complex queries

Samuel Banks 4 May 24, 2022
PolarDB Stack is a DBaaS implementation for PolarDB-for-Postgres, as an operator creates and manages PolarDB/PostgreSQL clusters running in Kubernetes. It provides re-construct, failover swtich-over, scale up/out, high-available capabilities for each clusters.

PolarDB Stack开源版生命周期 1 系统概述 PolarDB是阿里云自研的云原生关系型数据库,采用了基于Shared-Storage的存储计算分离架构。数据库由传统的Share-Nothing,转变成了Shared-Storage架构。由原来的N份计算+N份存储,转变成了N份计算+1份存储

null 22 Jul 18, 2022
Go language implementation of a blockchain based on the BDLS BFT protocol. The implementation was adapted from Ethereum and Sperax implementation

BDLS protocol based PoS Blockchain Most functionalities of this client is similar to the Ethereum golang implementation. If you do not find your quest

Yongge Wang 0 Jan 1, 2022
pg_timetable: Advanced scheduling for PostgreSQL

pg_timetable: Advanced scheduling for PostgreSQL pg_timetable is an advanced job scheduler for PostgreSQL, offering many advantages over traditional s

CYBERTEC PostgreSQL International GmbH 764 Aug 10, 2022
Cross-platform client for PostgreSQL databases

pgweb Web-based PostgreSQL database browser written in Go. Overview Pgweb is a web-based database browser for PostgreSQL, written in Go and works on O

Dan Sosedoff 7.4k Aug 5, 2022
pREST (PostgreSQL REST), simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new

pREST pREST (PostgreSQL REST), simplify and accelerate development, instant, realtime, high-performance on any Postgres application, existing or new P

pREST 3.3k Aug 10, 2022
Go database query builder library for PostgreSQL

buildsqlx Go Database query builder library Installation Selects, Ordering, Limit & Offset GroupBy / Having Where, AndWhere, OrWhere clauses WhereIn /

Arthur Kushman 69 Aug 4, 2022
igor is an abstraction layer for PostgreSQL with a gorm like syntax.

igor igor is an abstraction layer for PostgreSQL, written in Go. Igor syntax is (almost) compatible with GORM. When to use igor You should use igor wh

Paolo Galeone 86 May 9, 2022
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 3.2k Aug 12, 2022
PostgreSQL driver and toolkit for Go

pgx - PostgreSQL Driver and Toolkit pgx is a pure Go driver and toolkit for PostgreSQL. pgx aims to be low-level, fast, and performant, while also ena

Jack Christensen 5.8k Aug 11, 2022
Simple key-value store abstraction and implementations for Go (Redis, Consul, etcd, bbolt, BadgerDB, LevelDB, Memcached, DynamoDB, S3, PostgreSQL, MongoDB, CockroachDB and many more)

gokv Simple key-value store abstraction and implementations for Go Contents Features Simple interface Implementations Value types Marshal formats Road

Philipp Gillé 453 Aug 11, 2022
100% type-safe ORM for Go (Golang) with code generation and MySQL, PostgreSQL, Sqlite3, SQL Server support. GORM under the hood.

go-queryset 100% type-safe ORM for Go (Golang) with code generation and MySQL, PostgreSQL, Sqlite3, SQL Server support. GORM under the hood. Contents

Denis Isaev 667 Aug 6, 2022
:tophat: Small self-contained pure-Go web server with Lua, Markdown, HTTP/2, QUIC, Redis and PostgreSQL support

Web server with built-in support for QUIC, HTTP/2, Lua, Markdown, Pongo2, HyperApp, Amber, Sass(SCSS), GCSS, JSX, BoltDB (built-in, stores the databas

Alexander F. Rødseth 2k Aug 10, 2022
Stream database events from PostgreSQL to Kafka

PSQL-Streamer This service receives the database events from PostgreSQL using logical replication protocol and feeds them to sinks based on the config

Igor Novgorodov 41 Jul 26, 2022
Golang Event Scheduling Sample Using Postgresql Database as persisting layer.

Database Based Event Scheduling Example that demonstrates super basic database based event scheduling. To run this example; Copy .env.example to .env

Dipesh Dulal 36 Aug 2, 2022
PostgreSQL style Parser splitted from CockroachDB

What's this PostgreSQL style Parser splitted from CockroachDB See: Complex SQL format example

auxten 149 Aug 11, 2022
Simple webhook delivery system powered by Golang and PostgreSQL

postmand Simple webhook delivery system powered by Golang and PostgreSQL. Features Simple rest api with only three endpoints (webhooks/deliveries/deli

Allisson Azevedo 19 Jul 26, 2022
a key-value store with multiple backends including leveldb, badgerdb, postgresql

Overview goukv is an abstraction layer for golang based key-value stores, it is easy to add any backend provider. Available Providers badgerdb: Badger

Mohammed Al Ashaal 52 Jun 7, 2022
Data access layer for PostgreSQL, CockroachDB, MySQL, SQLite and MongoDB with ORM-like features.

upper/db is a productive data access layer (DAL) for Go that provides agnostic tools to work with different data sources

upper.io 3.1k Aug 3, 2022
📖 Build a RESTful API on Go: Fiber, PostgreSQL, JWT and Swagger docs in isolated Docker containers.

?? Tutorial: Build a RESTful API on Go Fiber, PostgreSQL, JWT and Swagger docs in isolated Docker containers. ?? The full article is published on Marc

Vic Shóstak 191 Jul 24, 2022
Kubegres is a Kubernetes operator allowing to create a cluster of PostgreSql instances and manage databases replication, failover and backup.

Kubegres is a Kubernetes operator allowing to deploy a cluster of PostgreSql pods with data replication enabled out-of-the box. It brings simplicity w

Reactive Tech Ltd 1k Aug 11, 2022
Enhanced PostgreSQL logical replication

pgcat - Enhanced postgresql logical replication Why pgcat? Architecture Build from source Install Run Conflict handling Table mapping Replication iden

jinhua luo 356 Jul 15, 2022
Generate PlantUML ER diagram textual description from PostgreSQL tables

Generate PlantUML ER diagram textual description from PostgreSQL tables

Akira Chiku 470 Aug 2, 2022