dqlite is a C library that implements an embeddable and replicated SQL database engine with high-availability and automatic failover

Overview

dqlite Build Status codecov

dqlite is a C library that implements an embeddable and replicated SQL database engine with high-availability and automatic failover.

The acronym "dqlite" stands for "distributed SQLite", meaning that dqlite extends SQLite with a network protocol that can connect together various instances of your application and have them act as a highly-available cluster, with no dependency on external databases.

Design highlights

  • Asynchronous single-threaded implementation using libuv as event loop.
  • Custom wire protocol optimized for SQLite primitives and data types.
  • Data replication based on the Raft algorithm and its efficient C-raft implementation.

License

The dqlite library is released under a slightly modified version of LGPLv3, that includes a copyright exception allowing users to statically link the library code in their project and release the final work under their own terms. See the full license text.

Try it

The simplest way to see dqlite in action is to use the demo program that comes with the Go dqlite bindings. Please see the relevant documentation in that project.

Media

A talk about dqlite was given at FOSDEM 2020, you can watch it here.

Wire protocol

If you wish to write a client, please refer to the wire protocol documentation.

Install

If you are on a Debian-based system, you can get the latest development release from dqlite's dev PPA:

sudo add-apt-repository ppa:dqlite/dev
sudo apt-get update
sudo apt-get install libdqlite-dev

Build

To build libdqlite from source you'll need:

  • A reasonably recent version of libuv (v1.8.0 or beyond).
  • A reasonably recent version of sqlite3-dev
  • A build of the C-raft Raft library.

Your distribution should already provide you with a pre-built libuv shared library and libsqlite3-dev.

To build the raft library:

git clone https://github.com/canonical/raft.git
cd raft
autoreconf -i
./configure
make
sudo make install
cd ..

Once all the required libraries are installed, in order to build the dqlite shared library itself, you can run:

autoreconf -i
./configure
make
sudo make install

Usage Notes

Detailed tracing will be enabled when the environment variable LIBDQLITE_TRACE is set before startup.

Issues
  • Lost node due to raft issue

    Lost node due to raft issue

    I've been testing failure scenarios and I have a node that can't start because it gets the following error on startup:

    raft_start(): io: load closed segment 0000000010000470-0000000010000655: 
    

    All I've been doing is randomly killing the server. ~Let me know if you want the data files.~ I emailed the data files for this node.

    opened by ibuildthecloud 30
  • database is locked errors

    database is locked errors

    I have a goland app running on sqlite today using the configuration "./db/state.db?_journal=WAL&cache=shared." This is essentially a multithreaded app and it runs with no apparent issues. I've switched it to using dqlite and it immediately gets "database is locked" errors. Is there something I can do to allow concurrency without getting "database is locked" errors?

    Incomplete 
    opened by ibuildthecloud 21
  • packaging dqlite in linux distribution / static linking to sqlite3 fork

    packaging dqlite in linux distribution / static linking to sqlite3 fork

    Current way of building dqlite is highly problematic because it needs sqlite3 fork.

    Installing/packaging sqlite3 fork is a problem on distribution because it conflicts with regular sqlite3. Packaging fork would mean patching it to rename libraries to some other name (libsqlite3dqlite.so maybe).

    Other solutions are: a) merge changes into upstream sqlite3 (I assume it wasn't done because upstream doesn't want it) b) change configure system in dqlite to be able to use bundled sqlite3 fork and statically link it (and only it, leave rest shared). Static linking is not a great solution (security issues in sqlite etc) but well...

    Any other ideas how to make dqlite "packagable" for any linux distro?

    opened by arekm 17
  • Memory spike with concurrent operations

    Memory spike with concurrent operations

    When hitting the dqlite-demo with multiple concurrent requests I can pretty much reliably reproduce a memory spike of GBs.

    To reproduce first add an extra HTTP verb handler on the dqlite-demo:

    diff --git a/cmd/dqlite-demo/dqlite-demo.go b/cmd/dqlite-demo/dqlite-demo.go
    index 49f8197..deb4a91 100644
    --- a/cmd/dqlite-demo/dqlite-demo.go
    +++ b/cmd/dqlite-demo/dqlite-demo.go
    @@ -77,6 +77,15 @@ Complete documentation is available at https://github.com/canonical/go-dqlite`,
                                            if _, err := db.Exec(update, key, value); err != nil {
                                                    result = fmt.Sprintf("Error: %s", err.Error())
                                            }
    +                               case "POST":
    +                                       result = "yes!"
    +                                       for i:=0 ; i < 10000; i++{
    +                                               value := fmt.Sprintf("This is so mdata %s", i)
    +                                               if _, err := db.Exec(update, key, value); err != nil {
    +                                                       result = fmt.Sprintf("Error: %s %d", err.Error(), i)
    +                                                       break
    +                                               }
    +                                       }
                                    default:
                                            result = fmt.Sprintf("Error: unsupported method %q", r.Method)
    

    Setup a three node cluster as described in the go-dqlite readme [1].

    On two terminals start trigger the new operations:

    while true ; do curl -X POST -d "foo=bar" http://localhost:8001/mykey1 ; done
    

    And

    while true ; do curl -X POST -d "foo=bar" http://localhost:8001/mykey2 ; done
    

    Let it run on for a few minutes and almost half the times the memory usage of one of the dqlite-demo processes will spike, eg [2].

    FYI, @sevangelatos

    [1] https://github.com/canonical/go-dqlite#demo [2] https://pasteboard.co/JET1frM.jpg

    opened by ktsakalozos 16
  • panic in sqlite

    panic in sqlite

    I keep getting this panic. In this situation this code path is not directly dqlite. In k3s I'm supporting both sqlite and dqlite, so when I run the old sqlite code but with the patched sqlite library it will randomly fail in this same place

    goroutine 77265 [syscall]:
    runtime.cgocall(0x3416b20, 0xc00d447388, 0x0)
            /usr/local/go/src/runtime/cgocall.go:128 +0x5b fp=0xc00d447358 sp=0xc00d447320 pc=0x40472b
    github.com/rancher/k3s/vendor/github.com/mattn/go-sqlite3._Cfunc_sqlite3_close_v2(0x9144e00, 0x0)
            _cgo_gotypes.go:607 +0x49 fp=0xc00d447388 sp=0xc00d447358 pc=0xf282b9
    github.com/rancher/k3s/vendor/github.com/mattn/go-sqlite3.(*SQLiteConn).Close.func1(0xc01717a5a0, 0x0)
            /go/src/github.com/rancher/k3s/vendor/github.com/mattn/go-sqlite3/sqlite3.go:1646 +0x5f fp=0xc00d4473c8 sp=0xc00d447388 pc=0xf3edff
    github.com/rancher/k3s/vendor/github.com/mattn/go-sqlite3.(*SQLiteConn).Close(0xc01717a5a0, 0x8, 0xc0137dd6c0)
            /go/src/github.com/rancher/k3s/vendor/github.com/mattn/go-sqlite3/sqlite3.go:1646 +0x2f fp=0xc00d4473f8 sp=0xc00d4473c8 pc=0xf37f9f
    database/sql.(*driverConn).finalClose.func2()
            /usr/local/go/src/database/sql/sql.go:521 +0x49 fp=0xc00d447430 sp=0xc00d4473f8 pc=0xf194b9
    database/sql.withLock(0x486c400, 0xc016a67a80, 0xc00d4474c8)
            /usr/local/go/src/database/sql/sql.go:3097 +0x63 fp=0xc00d447458 sp=0xc00d447430 pc=0xf19133
    database/sql.(*driverConn).finalClose(0xc016a67a80, 0x3e18580, 0x7fb7fc6d4908)
            /usr/local/go/src/database/sql/sql.go:519 +0x130 fp=0xc00d4474f0 sp=0xc00d447458 pc=0xf0c9d0
    database/sql.finalCloser.finalClose-fm(0xc0007d8560, 0x4823b60)
            /usr/local/go/src/database/sql/sql.go:565 +0x2f fp=0xc00d447518 sp=0xc00d4474f0 pc=0xf1bb9f
    database/sql.(*driverConn).Close(0xc016a67a80, 0xc016a67a80, 0x0)
            /usr/local/go/src/database/sql/sql.go:500 +0x138 fp=0xc00d447568 sp=0xc00d447518 pc=0xf0c878
    database/sql.(*DB).putConn(0xc0007d8540, 0xc016a67a80, 0x0, 0x0, 0xc0000a0200)
            /usr/local/go/src/database/sql/sql.go:1277 +0x1c8 fp=0xc00d4475d8 sp=0xc00d447568 pc=0xf101c8
    database/sql.(*driverConn).releaseConn(...)
            /usr/local/go/src/database/sql/sql.go:421
    database/sql.(*driverConn).releaseConn-fm(0x0, 0x0)
            /usr/local/go/src/database/sql/sql.go:420 +0x4c fp=0xc00d447610 sp=0xc00d4475d8 pc=0xf1bc1c
    database/sql.(*Rows).close(0xc01464ca80, 0x0, 0x0, 0x0, 0x0)
            /usr/local/go/src/database/sql/sql.go:3001 +0x15a fp=0xc00d447660 sp=0xc00d447610 pc=0xf18c0a
    database/sql.(*Rows).Close(...)
            /usr/local/go/src/database/sql/sql.go:2972
    database/sql.(*Rows).Next(0xc01464ca80, 0x0)
            /usr/local/go/src/database/sql/sql.go:2661 +0xb9 fp=0xc00d4476c0 sp=0xc00d447660 pc=0xf17389
    github.com/rancher/k3s/vendor/github.com/rancher/kine/pkg/logstructured/sqllog.RowsToEvents(0xc01464ca80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
            /go/src/github.com/rancher/k3s/vendor/github.com/rancher/kine/pkg/logstructured/sqllog/sql.go:221 +0xd9 fp=0xc00d447740 sp=0xc00d4476c0 pc=0x10143b9
    
    opened by ibuildthecloud 14
  • Case-insensitive column type lookups, DATE support

    Case-insensitive column type lookups, DATE support

    The column types are return as defined by the user, so if it was done lowercase it won't match -- this change fixes that.

    Also added a DQLITE_ISODATE type to mark the column as being a date only (not a timestamp).

    opened by paulstuart 13
  • Support compile with clang on MacOS

    Support compile with clang on MacOS

    A number of changes to make the code compile on MacOS with clang.

    The other patches in series:

    • https://github.com/canonical/go-dqlite/pull/132
    • https://github.com/canonical/raft/pull/173
    opened by rabits 12
  • Intermittent

    Intermittent "no known leader" error

    I have been experiencing no known leader error, when randomly killing the leader.

    The application is packaged in a container, deployed in a multi-node MicroK8s. Sometimes killing the leader does not seem to transfer the leadership, all other nodes will start reporting no leader found.

    This is how the base image containing dqlite is built.

    After sometime (a long time, several minutes) the cluster will form, i think it is due to the context timeout i setup (after 2mins) the dqlite node will restart.

    I am using the latest libraft and dqlite using the this repo ppa:dqlite/master This is running on 3 LXD instances.

    Note: The strange thing is when deploy the application on a single node kubernetes im finding it hard to reproduce. Running locally on host does not present the same behavior. I have seen this behavior before this PR https://github.com/canonical/raft/pull/235 happening on single node Kubernetes (MicroK8s), but have since unable to reproduce it on single node

    opened by balchua 11
  • recover failed with error code 1

    recover failed with error code 1

    I'm testing error scenarios and trying to call Node.Recover() just returns an error "recover failed with error code 1" I'm not sure where to go from here. I have a two node cluster and trying to reset it back to just one node.

    opened by ibuildthecloud 11
  • Question/Documentation: How does dqlite handle 'begin immediate' ?

    Question/Documentation: How does dqlite handle 'begin immediate' ?

    In normal sqlite3 you can force the immediate acquisition of a write lock via 'begin immediate', Is this possible in dqlite? If it is possible, does this guarantee any reads in the immediate transaction occur through the leader?

    opened by andrewchambers 10
  • gpg: no valid OpenPGP data found

    gpg: no valid OpenPGP data found

    Hi, I am trying to install the package so I can use the go library, but my system doesnt want to install it because no valid OpenPGP data is found.

    $ sudo add-apt-repository ppa:dqlite/dev
     Automated builds of the latest code in the following repositories:
     - dqlite
     - go-dqlite
     - raft
     More info: https://launchpad.net/~dqlite/+archive/ubuntu/dev
    Press [ENTER] to continue or ctrl-c to cancel adding it
    
    gpg: keybox '/tmp/tmp_3dlbc1d/pubring.gpg' created
    gpg: /tmp/tmp_3dlbc1d/trustdb.gpg: trustdb created
    gpg: key 06CD096F50FB3D04: public key "Launchpad PPA for dqlite" imported
    gpg: Total number processed: 1
    gpg:               imported: 1
    Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).
    gpg: no valid OpenPGP data found.
    
    $ uname -a
    Linux WDE-N-W0410 5.10.16.3-microsoft-standard-WSL2 #1 SMP Fri Apr 2 22:23:49 UTC 2021 x86_64 GNU/Linux
    
    $ cat /etc/os-release | head -n 1
    PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
    
    opened by bluebrown 10
  • Add missing install of dependency liblz4

    Add missing install of dependency liblz4

    Trying to build using the Dockerfile lead to an error as liblz4 was not installed.

    Unfortunately something else is also borked as it now stops when compiling dqlite:

    [..]
    Step 13/22 : WORKDIR /opt/dqlite
     ---> Running in 5777fd6ffc1b
    Removing intermediate container 5777fd6ffc1b
     ---> 3e24e33ab8a9
    Step 14/22 : COPY . .
     ---> c86f93df29cf
    Step 15/22 : RUN autoreconf -i && ./configure && make && make install
     ---> Running in f71c6f639f7d
    autoreconf: error: 'configure.ac' is required
    The command '/bin/sh -c autoreconf -i && ./configure && make && make install' returned a non-zero code: 1
    

    I didnt have time so far to look into the issue.

    opened by Ruakij 1
  • dqlite disk backend

    dqlite disk backend

    I'm currently working on a disk backend for dqlite, it's in a proof-of-concept phase right now but wanted to start discussion on it.

    My initial idea is/was to keep the WAL in-memory, this allows us to easily reuse the the replication logic and store the DB on disk in a regular sqlite3 database file. Checkpointing then flushes the WAL to the disk DB. The vfsFileMethods for the DB file would then generallly be the vanilla sqlite3 vfsFileMethods.

    A couple of issues:

    • Checkpointing to disk happens in the main loop, this can quickly take a couple of hundred ms, blocking the eventloop. I don't really know how this can be avoided. https://github.com/canonical/dqlite/blob/35b5418af73d5112c156d05eec2ad85268707cb3/src/fsm.c#L274
    • Snapshotting the database means copying the on-disk database, instead of passing pointers to raft. For this we would probably need to add the async snapshot behavior to raft. https://github.com/canonical/raft/pull/268 .
    • Snapshotting aka copying a large database every 1024 transactions is also possibly very heavy on the storage. We can lower the frequency, but this will increase memory footprint of raft log.
    • Because the database can be large, we can't just load the entire snapshot in memory when sending a raft installSnapshot RPC. We would need to add a way to send the snapshot in chunks, adding to overall complexity. (e.g. we can't just delete snapshots that are still being sent, etc.)

    All-in-all I'm not feeling too confident right now if this is practically feasible, do you want to share some insights on this @freeekanayaka ?

    opened by MathieuBordere 7
  • Idea: libsqlite3.so compatible wrapper loaded with LD_PRELOAD

    Idea: libsqlite3.so compatible wrapper loaded with LD_PRELOAD

    A library providing a subset of the libsqlite3.so API could be loaded with LD_PRELOAD and enable existing programs to use dqlite transparently, as long as they do not link sqlite statically and should include all scripting languages. It should also be useful with the sqlite3 utility, but it would need to be compiled differently for this to work.

    The wrapper could also dynamically load the existing libsqlite3.so and forward calls to it for opening existing sqlite3 files. Connecting to a dqlite database could be done by opening a small stub file containing the cluster configuration or using dqlite: uri

    opened by orent 3
  • Allow registration of custom functions via sqlite3_create_function

    Allow registration of custom functions via sqlite3_create_function

    Creating custom functions for sqlite3 is commonplace, it's currently not possible to do that with dqlite. The question is, how would this work, would each function callback in each node be called, or just the leader?

    The end goal would be to allow things like custom triggers.

    An example of what we'd like to use it for:

    // Create a trigger that will dispatch any changes that have been
    // inserted in a transaction, allowing us to rollback if the dispatch
    // fails.
    const schema = `
    CREATE TRIGGER IF NOT EXISTS insert_model_trigger
    BEFORE INSERT ON model
    WHEN dispatch("model.insert", NEW.key, NEW.value) != ""
    BEGIN
    	SELECT RAISE(ROLLBACK, "failed to call model trigger");
    END;
    `
    
    // Register the custom function, that dispatches an event based on
    // changes in a trigger.
    sql.Register("sqlite3_custom", &sqlite3.SQLiteDriver{
    	ConnectHook: func(conn *sqlite3.SQLiteConn) error {
    		if err := conn.RegisterFunc("dispatch", func(name string, args ...interface{}) string {
    			err := pubsub.Publish("dispatch", Delta{
    				Name:   name,
    				Values: args,
    			})
    			if err != nil {
    				return err.Error()
    			}
    			return ""
    		}, false); err != nil {
    			return err
    		}
    		return nil
    	},
    })
    
    Feature 
    opened by SimonRichardson 2
  • context deadline exceeded in perf-test

    context deadline exceeded in perf-test

    @balchua crafted a small performance test that results into a context deadline exceeded under fairly common circumstances (i think). The project with this work in [1] creates a few go routines that insert and query a dqlite database.

    To reproduce the issue I build the snap with snapcraft (thank you @balchua for making this easy for us to reproduce), created a VM with 4 cores and 4GB of ram with multipass launch -c 4 -m 4G (to be sure no local libraries are used), install the snap and then run:

    sudo -E LIBRAFT_TRACE="1" LIBDQLITE_TRACE="1" /snap/perf-dqlite/current/perf-dqlite
    

    I am attaching here the logs of a run. perf-dqlite.log

    [1] https://github.com/balchua/perf-test-dqlite.git

    opened by ktsakalozos 11
  • Wire protocol Client message code 2 is missing

    Wire protocol Client message code 2 is missing

    The wire protocol client message code 2 is missing on the doc, but on the go implementation it is used for a healthcheck, is it missing on purpose? Im beginning to implement the wire protocol in C.

    opened by joseims 16
Releases(v1.10.0)
HybridSE (Hybrid SQL Engine) is an LLVM-based, hybrid-execution and high-performance SQL engine

HybridSE (Hybrid SQL Engine) is an LLVM-based, hybrid-execution and high-performance SQL engine. It can provide fast and consistent execution on heterogeneous SQL data systems, e.g., OLAD database, HTAP system, SparkSQL, and Flink Stream SQL.

4Paradigm 45 Sep 12, 2021
Nebula Graph is a distributed, fast open-source graph database featuring horizontal scalability and high availability

Nebula Graph is an open-source graph database capable of hosting super large scale graphs with dozens of billions of vertices (nodes) and trillions of edges, with milliseconds of latency.

vesoft inc. 807 Jun 30, 2022
YugabyteDB is a high-performance, cloud-native distributed SQL database that aims to support all PostgreSQL features

YugabyteDB is a high-performance, cloud-native distributed SQL database that aims to support all PostgreSQL features. It is best to fit for cloud-native OLTP (i.e. real-time, business-critical) applications that need absolute data correctness and require at least one of the following: scalability, high tolerance to failures, or globally-distributed deployments.

yugabyte 6.6k Jul 1, 2022
PGSpider: High-Performance SQL Cluster Engine for distributed big data.

PGSpider: High-Performance SQL Cluster Engine for distributed big data.

PGSpider 127 Jun 24, 2022
DuckDB is an in-process SQL OLAP Database Management System

DuckDB is an in-process SQL OLAP Database Management System

DuckDB 5.4k Jun 27, 2022
TimescaleDB is an open-source database designed to make SQL scalable for time-series data.

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.

Timescale 13.3k Jun 27, 2022
An embeddable fulltext search engine. Groonga is the successor project to Senna.

README Groonga is an open-source fulltext search engine and column store. Reference manual See doc/source/ directory or http://groonga.org/docs/. Here

Groonga Project 678 Jun 19, 2022
MySQL Server, the world's most popular open source database, and MySQL Cluster, a real-time, open source transactional database.

Copyright (c) 2000, 2021, Oracle and/or its affiliates. This is a release of MySQL, an SQL database server. License information can be found in the

MySQL 7.9k Jun 24, 2022
A mini database for learning database

A mini database for learning database

Chuckie Tan 3 Nov 3, 2021
A lightweight header-only C++11 library for quick and easy SQL querying with QtSql classes.

EasyQtSql EasyQtSql is a lightweight header-only C++11 library for quick and easy SQL querying with QtSql classes. Features: Header only C++11 library

null 44 Jun 28, 2022
A type safe SQL template library for C++

sqlpp11 A type safe embedded domain specific language for SQL queries and results in C++ Documentation is found in the wiki So what is this about? SQL

Roland Bock 2k Jul 2, 2022
A PostgreSQL extension providing an async networking interface accessible via SQL using a background worker and curl.

pg_net is a PostgreSQL extension exposing a SQL interface for async networking with a focus on scalability and UX.

Supabase 41 Jun 19, 2022
A bare-bone SQL implementation

MiniSQL A bare-bone SQL implementation. Project Structure include folder contains header files of all modules. These header files are meant to be shar

null 16 Apr 23, 2022
DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.

DB Browser for SQLite What it is DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files c

null 16.7k Jun 27, 2022
ESE is an embedded / ISAM-based database engine, that provides rudimentary table and indexed access.

Extensible-Storage-Engine A Non-SQL Database Engine The Extensible Storage Engine (ESE) is one of those rare codebases having proven to have a more th

Microsoft 780 Jun 13, 2022
A very fast lightweight embedded database engine with a built-in query language.

upscaledb 2.2.1 Fr 10. Mär 21:33:03 CET 2017 (C) Christoph Rupp, [email protected]; http://www.upscaledb.com This is t

Christoph Rupp 531 Jun 20, 2022
An Embedded NoSQL, Transactional Database Engine

UnQLite - Transactional Embedded Database Engine

PixLab | Symisc Systems 1.7k Jun 23, 2022
A friendly and lightweight C++ database library for MySQL, PostgreSQL, SQLite and ODBC.

QTL QTL is a C ++ library for accessing SQL databases and currently supports MySQL, SQLite, PostgreSQL and ODBC. QTL is a lightweight library that con

null 155 Jun 26, 2022
Velox is a new C++ vectorized database acceleration library aimed to optimizing query engines and data processing systems.

Velox is a C++ database acceleration library which provides reusable, extensible, and high-performance data processing components

Facebook Incubator 893 Jun 27, 2022