A Simple and Powerful Golang SQL Library
KSQL was created to offer an actually simple and satisfactory tool for interacting with SQL Databases in Golang.
The core goal of KSQL is not to offer new features that are unavailable on other libraries (although we do have some), but to offer a well-thought and well-planned API so that users have an easier time, learning, debugging, and avoiding common pitfalls.
KSQL is also decoupled from its backend so that
the actual communication with the database is performed by
well-known and trusted technologies, namely: pgx and database/sql.
You can even create your own backend adapter for KSQL which is
useful in some situations.
In this README you will find examples for "Getting Started" with the library, for more advanced use-cases please read our Wiki.
database/sql and pgxsql.Scanner and sql.Valuer and also all pgx special types (when using kpgx)Hi! I'm currently looking for new opportunities as a Senior Software or Platform Engineer—preferably working with Golang, backend systems, or open-source infrastructure. If you're hiring or know someone who is, feel free to reach out: [email protected] or on LinkedIn.
This short example below is a TLDR version to illustrate how easy it is to use KSQL.
You will find more complete examples in the sections below.
This example is available on ./examples/overview/main.go if you want to run it
…Note: In the example above we are using the
$1,$2and$3as placeholders on the query because this example is meant to run on top of Postgres.If you are running on top of MySQL or SQLite use
?instead, and if you are running on top of SQLServer use@p1,@p2and@p3instead.
We support a few different adapters,
one of them is illustrated above (kpgx),
the other ones have the exact same signature
but work on different databases or driver versions,
they are:
kpgx.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) for Postgres, it works on top of pgxpool
and pgx version 4, download it with:
go get github.com/vingarcia/ksql/adapters/kpgxkpgx5.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) for Postgres, it works on top of pgxpool
and pgx version 5, download it with:
go get github.com/vingarcia/ksql/adapters/kpgx5kmysql.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) for MySQL, it works on top of database/sql,
download it with:
go get github.com/vingarcia/ksql/adapters/kmysqlksqlserver.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) for SQLServer, it works on top of database/sql,
download it with:
go get github.com/vingarcia/ksql/adapters/ksqlserverksqlite3.New(ctx, os.Getenv("DATBAASE_PATH"), ksql.Config{}) for SQLite3, it works on top of database/sql
and mattn/go-sqlite3 which relies on CGO, download it with:
go get github.com/vingarcia/ksql/adapters/ksqlite3ksqlite.New(ctx, os.Getenv("DATABASE_PATH"), ksql.Config{}) for SQLite, it works on top of database/sql
and modernc.org/sqlite which does not require CGO, download it with:
go get github.com/vingarcia/ksql/adapters/modernc-ksqliteFor more detailed examples see:
./examples/all_adapters/all_adapters.goThe current interface contains the methods the users are expected to use, and it is also used for making it easy to mock the whole library if needed.
This interface is declared in the project as ksql.Provider and is displayed below.
We plan on keeping it very simple with a small number of well-thought functions that cover all use cases, so don't expect many additions:
…In the example below we'll cover all the most common use cases such as:
More advanced use cases are illustrated on their own pages on our Wiki:
ksql.MockFor the more common use cases please read the example below, which is also available here if you want to compile it yourself.
…The results of the benchmark are good for KSQL, but not flawless.
The next section summarizes the results so its more comprehensible, but if you prefer to read the raw benchmark data just scroll down to the Benchmark Results section.
For transparency purposes this summary will focus at the benchmark showing the worst results for KSQL which is querying multiple lines, this is the summary:
Comparing KSQL running on top of database/sql with sqlx, sqlx is
5% faster than KSQL, which is in practical terms an insignificant difference.
And if KSQL is running on top of pgx then KSQL becomes 42% faster
because pgx is significantly faster than sqlx.
Finally if you are using sqlx with prepared statements everytime
then sqlx is 7.5% faster than KSQL on top of pgx.
So between KSQL vs sqlx the performance difference is very small, and
if you are using Postgres odds are KSQL will be much faster.
Comparing KSQL running on top of pgx with pgx itself, KSQL
is 13.66% slower (on average), which is not insignificant but isn't much either.
Comparing KSQL running on top pgx with gorm, KSQL is
11.87% faster than gorm or inversely gorm is 13.4% slower.
It is worth noting that KSQL is only caching of prepared statements when using postgres, because this is performed by
pgx, and this means that when using MySQL, SQLServer or SQLite, if you plan on also using prepared statements other libaries such assqlxwill be significantly faster than KSQL.We are working on adding support for cached prepared statements for these other databases in the future.
To understand the benchmark below you must know that all tests are performed using Postgres 12.1 and that we are comparing the following tools:
database/sqlpgxdatabase/sqlsqlxpgx (with pgxpool)gormsqlcsqlboilerFor each of these tools, we are running 3 different queries:
The insert-one query looks like this:
INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id
The single-row query looks like this:
SELECT id, name, age FROM users OFFSET $1 LIMIT 1
The multiple-rows query looks like this:
SELECT id, name, age FROM users OFFSET $1 LIMIT 10
Keep in mind that some of the tools tested (like GORM) actually build the queries internally so the actual code used for the benchmark might differ a little bit from the example ones above.
Without further ado, here are the results:
…The tests use docker-test for setting up all the supported databases,
which means that:
You need to have docker installed
You must be able to run docker without sudo, i.e.
if you are not root you should add yourself to the docker group, e.g.:
$ sudo usermod -aG dockerAnd then restart your login session (or just reboot)
Finally run make pre-download-all-images only once so your tests don't
timeout downloading the database images.
After that, you can just run the tests by using:
make testUpsert helper methodksqltest.FillStructWith to work with ksql:"..,json" tagged attributeskbuilder packageksql.NewTable()Update, Insert and Delete..Transaction(db ksql.Provider) to a .Transaction(ctx context.Context).Query() method to return a type Query interface { One(); All(); Chunks(); }Update() method that updates without ignoring NULLs as Patch() doesskipNullUpdates so that the Update function will do the job of the PatchPatch function.NewTable() to just Table() so it feels right to declare it inline when convenientNo open issues yet, or sync has not completed.