..with the 'sqld' daemon and access it over the network using  the PostgreSQL wire protocol. The use cases do not stop there.

It seems that the Postgresql wire protocol, which enables you to migrate application code written for another dbms to Postgres, has set a trend. We've already checked BabelFish and MangoDB (now FerretDB) in action using it:

Babelfish and MangoDB let PostgreSQL understand queries from applications written for Microsoft SQL Server and MongoDB respectively.

Specifically:

Babelfish is a set of extensions that provide both T-SQL capabilities and a Tabular Data Stream (TDS) listener port as enhancements to PostgreSQL. It supports the SQLServer dialect, T-SQL, and notable features including savepoints, stored procedures, nested transactions, etc.

while for MangoDB/FerretDb:

[it acts] as a stateless proxy, which converts MongoDB protocol queries to SQL, using PostgreSQL as a database engine. It is compatible with MongoDB drivers, and should work as a drop-in replacement to MongoDB in many cases.

Now there's yet another player using it under a slightly different use case. libSQL “server mode” (sqld) enables access to SQLite-based databases using Postgres and HTTP network protocols, thus allowing developers to use the SQLite database as a server, an integration should prove a boost to SQLite. 

SQLite is easy to use since it requires no setup, no maintenance, no scaling, and the result of execution resides entirely in a single file that you can drop into your CI/CD for quick verification. This simplicity is, however, a shortcoming when wanting to move your load online because of its lack of network accessibility for things like monitoring and backups. Most modern applications also need replicas for availability. Hence SQLite has been not a common choice for production backends.

Also, as in MongoDB, there are issues with the license of the original SQLite, which while open, is not open source in the
strictest sense. For instance, SQLite famously doesn't accept external contributors and doesn't adhere to a code of conduct. So community improvements cannot be widely enjoyed. For that reasons a fork of SQLite has been made, libSQL, that is both Open Source, and Open to Contributions.

sqld ("SQL daemon")  is the server mode of libSQL that allows you to consume your database as a server while also enables network access to libSQL and replication to multiple instances.

Features of sqld:

• SQLite dialect layered on top of HTTP.
• SQLite-compatible API that you can drop-in with LD_PRELOAD in your application to switch from local database to a remote database.
• Read replica support.
• Integration with mvSQLite for high availability and fault tolerance.

Accessing your SQLite-compatible database over the network, can be done using the postgres or the http protocols:

Start the server with:

sqld -d foo. db -p 127. 0. 0. 1:5432 --http-listen-addr=127. 0. 0. 1:8000

-d indicates the file name, -p the address for the postgres protocol listener, and --http-listen-addr the address of the http listener.

Now connect with psql and do SQL using SQLite syntax and types:

$ psql -q postgres://127. 0. 0. 1
glaubercosta=> create table databases (name text);
-
(0 rows)
glaubercosta=> insert into databases (name) values ('libsql');
-
(0 rows)
glaubercosta=> select * from databases;
name
- - - -
libsql
(1 row)

You can now issue commands over HTTP, so you can just curlto it with a JSON payload

$ curl -s -d "{\"statements\":
[\"SELECT * from databases;\"] }" \
http://127. 0. 0. 1:8000
[[{"name":"libsql"}]]

The sqlite shell can be used to inspect the file directly:

$ sqlite3 foo. db
SQLite version 3. 37. 0 2021–12–09 01:34:53
Enter ". help" for usage hints.
sqlite> select * from databases;
libsql

There you have it. Acessing SQLite as a server is now possible thank to Postgres which just keeps on giving beyond its boundaries.

More Information

libsql

sqld

Related Articles

Move Over To PostgreSQL With Babelfish and MangoDB 

FerretDB The MongoDB Drop-In Replacement Is Production Ready 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info