Tag: sqlite

Distributed SQLite for Go applications

On By irrlabIn programming

This repository provides the dqlite Go package, which can be used to replicate a SQLite database across a cluster, using the Raft algorithm.

Design higlights

  • No external processes needed: dqlite is just a Go library, you link it it to your application exactly like you would with SQLite.
  • Replication needs a SQLite patch which is not yet included upstream.
  • The Go Raft package from Hashicorp is used internally for replicating the write-ahead log frames of SQLite across all nodes.

How does it compare to rqlite?

The main differences from rqlite are:

  • Full support for transactions
  • No need for statements to be deterministic (e.g. you can use time())
  • Frame-based replication instead of statement-based replication, this means in dqlite there’s more data flowing between nodes, so expect lower performance. Should not really matter for most use cases.

https://github.com/CanonicalLtd/dqlite

Working with SQLite Databases using Python and Pandas

On By irrlabIn programming

SQLite is a database engine that makes it simple to store and work with relational data. Much like the csvformat, SQLite stores data in a single file that can be easily shared with others. Most programming languages and environments have good support for working with SQLite databases. Python is no exception, and a library to access SQLite databases, called sqlite3, has been included with Python since version 2.5. In this post, we’ll walk through how to use sqlite3 to create, query, and update databases. We’ll also cover how to simplify working with SQLite databases using the pandas package. We’ll be using Python 3.5, but this same approach should work with Python 2.

Before we get started, let’s take a quick look at the data we’ll be working with. We’ll be looking at airline flight data, which contains information on airlines, airports, and routes between airports. Each route represents a repeated flight that an airline flies between a source and a destination airport.

All of the data is in a SQLite database called flights.db, which contains three tables – airports, airlines, and routes. You can download the data here.

Working with SQLite Databases using Python and Pandas

The beets blog: we’re pretty happy with SQLite & not urgently interested in a fancier DBMS

On By irrlabIn programming

Every once in a while, someone suggests that beets should use a “real database.” I think this means storing music metadata in PostgreSQL or MySQL as an alternative to our current SQLite database. The idea is that a more complicated DBMS should be faster, especially for huge music libraries.

The pseudo-official position of the beets project is that supporting a new DBMS is probably not worth your time. If you’re interested in performance, please consider helping to optimize our database queries instead.

There are three reasons I’m unenthusiastic about alternative DBMSes: I’m skeptical that they will actually help performance; it’s a clear case of premature optimization; and SQLite is unbeatably convenient.

http://beets.io/blog/sqlite-performance.html

Replicating SQLite using the Raft consensus protocol

On By irrlabIn programming

Detailed background on rqlite can be found on these blog posts. Note that master represents 2.0 development (which is still in progress), with a new API and Raft consensus module. If you want to work with 1.0 rqlite, you can find it here.

rqlite is a distributed system that provides a replicated SQLite database. rqlite is written in Go and uses Raft to achieve consensus across all the instances of the SQLite databases. rqlite ensures that every change made to the database is made to a quorum of SQLite files, or none at all.

rqlite gives you the functionality of a fault-tolerant, replicated relational database, but with very easy installation, deployment, and operation.

https://github.com/otoolep/rqlite

Replicated SQLite using the Raft consensus protocol

On By irrlabIn programming

“SQLite is a “self-contained, serverless, zero-configuration, transactional SQL database engine”.  However, it doesn’t come with replication built in, so if you want to store mission-critical data in it, you better back it up. The usual approach is to continually copy the SQLite file on every change.

I wanted SQLite, I wanted it distributed, and I really wanted a more elegant solution for replication. So rqlite was born…”

http://www.philipotoole.com/replicating-sqlite-using-raft-consensus/

“rqlite is a distributed system that provides a replicated SQLite database. rqlite is written in Go and uses Raft to achieve consensus across all the instances of the SQLite databases. rqlite ensures that every change made to the database is made to a majority of underlying SQLite files, or none-at-all…”

http://www.philipotoole.com/replicating-sqlite-using-raft-consensus/