Is it possible to use Sqlite as client-server?

Question

There is any technique or tool to use Sqlite using an architecture Client-Server instead of local access?

Of course you would need a client library to communicate with the server. And a server application to handle these connections and access Sqlite.

The question is whether there is something ready, reliable to use, or whether there is any way to do it.

A little bit of background

Some applications will run in light environments, with little competition and a profile of relatively low access, and essentially reading. The Sqlite is more than enough to meet this demand.

The only drawback is that Sqlite is not good for receiving competing remote accesses. It even works but can present problems. I know that officially recommended not to use for this scenario. But this recommendation is for the case of sharing a file on a server for direct access from multiple remote instances.

But we can have a server that handles the connections and makes access to the Sqlite file locally. There is no difference to the use on websites where Apache, IIS, etc. function as the server, even if indirectly. Of course, you should have an application that receives requests from other remote applications (on browser), manipulate them and send to Sqlite embedded in this application on the server.

Sqlite has the advantage of zero maintenance. In these low-volume cases where end-users may be distant from receiving maintenance and lack the resources to maintain a technician to manage DB, this is invaluable.

---

If someone thinks it’s not a good solution, think again.

In Brazil and in several countries we still have an immeasurable amount of systems developed in Clipper or Harbour using simple files in format dbf And they work at low volumes wonderfully well. I’ve actually seen access to hundreds of terminals working without major problems (when the system was well done).

Note that access to dbf is similar to Sqlite. Each remote application directly accesses the files shared by the network. And the system dbf has nowhere near the features that Sqlite has to give reliability. Nor am I talking about the use of technologies like the Letodb which allows access to dbf as client-server.

People often overestimate the need for competition and underestimate simple technologies. Sqlite is a huge evolution compared to dbf (even in Harbour, some programmers often use relational Dbs instead of dbf for new applications).

Just missing the client-server architecture for Sqlite to be perfect for these scenarios.

Answer 1

The tool SQL Relay - middleware between HTTP clients and the database server(s) - supports the Sqlite. Through it, it is possible:

• Execute a daemon accepting local and remote connections (specifying address and port);
• Create an instance that connects to an Sqlite database (local file to the server).

The tool is quite complex to display a step-by-step here, but by the configuration example [in the link above]:

<instance id="sqlitetest" port="9000" socket="/tmp/sqlitetest.socket" dbase="sqlite"
          connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60"
          endofsession="commit" sessiontimeout="600" runasuser="testuser"
          runasgroup="testuser" cursors="5">
    <users>
        <user user="sqlitetest" password="sqlitetest"/>
    </users>
    <connections>
        <connection connectionid="sqlitetest" string="db=/usr/local/sqlite/var/testdb"
                    metric="1"/>
    </connections>
</instance>

It can be observed that:

1. She gives a "nickname" to her bank, so that outside customers can identify it;
2. It creates a username and password to ensure access (supplying the lack of them in a local installation);
3. It allows you to configure multiple parameters - such as the maximum number of connections, the timeout, etc - that can ensure that the bank is not overloaded with an excess of connections (important, since Sqlite was not made for this).

Remarks:

• Sqlrelay assumes a *NIX environment (supports Cygwin), and is licensed under GNU GPL 2 (I’m not sure if this information is up to date);

• Access Sqlite this way does not eliminate all the potential problems. For example, it will continue to support only 1 writing at a time. However, since only the server will directly access it, the "network file system" problem is solved.

  A good Rule of Thumb is that you should avoid using Sqlite in situations Where the same database will be simultaneously accessed from Many Computers over a network filesystem.

  Free translation:

  As a rule, you should avoid using Sqlite in situations where the same database can be accessed simultaneously from multiple cheeky computers of a network file system.

Answer 2

As you wrote, it is not recommended that Sqlite be used with a server (since it is serveless), but there are a few ways to resolve it: Use the Sqlitening

OR

You can simulate a client-server architecture using SSH, as in the following example:

ssh user@host sqlite3 database SELECT * FROM table

Answer 3

The question is well answered, but I want to complement that even not using a ready solution is easy to create a small server to access the Sqlite.

The idea is to have an executable running all the time that embeds the Sqlite, perhaps as a service/daemon, and that will actually make the accesses to the bank on the same machine.

This executable will have communication with the outside world by some fully proprietary protocol or using something ready. TCP will probably be preferred in most situations. There are libraries ready to handle the details of it.

You will obviously need a library to use on the client side that makes the connection between the two parts. The library can be simple or more complex, according to the need.

You can even use nothing if you use HTTP as a protocol and follow web standards. The browser will be the client. So on web the Sqlite shines a lot.

Not that this is the most recommendable, but it is one way. If necessary it is best to follow the architecture normally adopted and have a more complete application on the server side that handles a level above the application and not directly the HTTP protocol.

It will be necessary to create a format of how the data will move between the client and the server. It can be something very simple, binary or textual, it can be JSON, for example, or something even simpler. If you’re going to have compression, encryption, it goes from necessity.

Yeah, you might want to use something ready. But if you need to, it’s relatively trivial to make a server to use Sqlite remotely. It’s not direct access, but it solves the problem, and there are several advantages to doing this.

Of course, to do this you need to think outside the box a little. You need to understand how things work and not just consume things ready. Although using what already exists is great too. You can’t close your eyes to other solutions and accept that "you can’t do it".

Eventually to create such a server you can take advantage of these ready-made solutions and extract what is most important from them.