Is it possible to use SQLite as a client-server?

Question

Is it possible to use SQLite as a client-server?

Navigation

#1 by (10 votes)
#2 by (2 votes)
#3 by (2 votes)

17

Is there any technique or tool to use SQLite using a Client-Server architecture 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 if there is any way to do that.

A little background

Some applications will run in light environments with little competition and a relatively low-access, essentially read-only profile. SQLite is more than enough to meet this demand.

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

But we can have a server that handles connections and accesses the SQLite file locally. There is no difference for 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 (in the browser ), manipulates them, and sends them to SQLite embedded in this application on the server.

SQlite has the benefit of zero maintenance. In these low volume cases where end users may be away for maintenance and do not have the resources to maintain a technician to manage the DB, this is invaluable.

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

In Brazil and in several countries we still have an immeasurable number of systems developed at Clipper or Harbor that use simple files in the format dbf and work at low volumes beautifully. I've actually seen access to hundreds of endpoints working without major issues (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 dbf system does not even have the features that SQLite has for reliability. And I'm not even talking about using technologies such as LetoDB that allows access to dbf as a client-server.

Often people overestimate the need for competition and underestimate simple technologies. SQlite is a huge development compared to dbf (even in Harbor, some programmers often use relational DBs instead of dbf for new applications).

The client-server architecture is still missing for SQLite to be perfect for these scenarios.

sqlite cliente-servidor

asked by anonymous 24.01.2014 / 01:30

3 answers

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: Using SQLitening

OU

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

ssh user@host sqlite3 database SELECT * FROM table

24.01.2014 / 01:41

2

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

The idea is to have an executable running all the time that injects SQLite, perhaps as a service / daemon, and will actually do the database access on the same machine.

This executable will communicate with the outside world by some totally proprietary protocol or by using something ready. TCP will probably be preferred in most situations. There are libraries ready to deal with his details.

Obviously you will need a library to use on the client side that connects between the two parts. The library can be simple or more complex as needed. You may even need nothing if you use HTTP as a protocol and follow web standards. The browser will be the client.

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

You will need to create a format for how data will travel between the client and the server. It can be very simple, binary or textual. It might be JSON, for example. Or something even simpler. If you are going to have compression, encryption, go of necessity.

Yes, maybe it's better to use something ready. But if you need it, 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 a bit out of the box. You have to understand how things work and not just get things done. Although using what already exists is great too. You can not close your eyes to other solutions and accept that "you can not do it."

Eventually to create a server so you can take advantage of these ready-made solutions and extract the most important of them.

27.11.2015 / 15:58

In HTML5 should nulls be enclosed with "" or with "/"? How to create real Permissions instead of simple Roles in Spring Security?

score 10 · Accepted Answer

The SQL Relay tool - middleware that gets in the way of customers HTTP and the database server (s) - supports SQLite . Through it, you can:

Execute a daemon that accepts local and remote connections (specifying address and port);
Create an instance that connects to a SQLite database (local file to 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 seen that:

It gives a "nickname" to your bank for external customers to identify;

It creates a user name and password to guarantee access (supplying the missing ones in a local installation);

It allows you to configure various parameters - such as maximum number of connections, timeout, etc. - that can ensure that the database is not overloaded with too many connections (important, since SQLite it was not meant for that.)

Comments:

SQLRelay assumes a * NIX environment (supports Cygwin), and has a GNU GPL 2 license (I'm not sure if this information is up to date);
Accessing SQLite in this way does not delete all potential problems . For example, it will continue to support only 1 writing at a time. However, since only the server will access it directly, the "network file system" issue is resolved.

A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.

Free translation:

As a general rule, you should avoid using SQLite in situations where the same database can be accessed simultaneously from multiple computers through a networked file system.