32

Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?

  • 3
    Can you give a reason why this would be a useful thing to have? Otherwise I think it deserves closing as not a real question. SQLite is not a client-server database and is really marketed to the crowd that doesn't need a client-server database. – jcolebrand Jan 5 '11 at 5:57
  • 1
    @Eelke although this is not longer true in WAL mode from version 3.7 - there can only be one write at a time, but "readers do not block writers and a writer does not block readers" – Jack Douglas Aug 18 '12 at 10:15
  • 1
    incidentally, Wikipedia currently seems to have this wrong – Jack Douglas Aug 18 '12 at 10:17
  • 2
    Why? I think you should define your requirements... and maybe you'll find a more suitable data base... – AK_ Jul 30 '13 at 14:25
  • 3
    @AK_ The main requirements are full ACID and very simple database to develop and use. We built a custom version to work as a client-server and the result is simply amazing! People are underrating the SQLite capacity and overrating need for concurrency to small companies. People need to open mind. Follow the recipe is not the only way to go. Now we have a product with no competition in our market. – Maniero Aug 3 '13 at 16:43
25

SQLite is an embedded database and it is not intended to be used as a client/server DB. If you really want to, you can use SQLitening.

What SQLitening is

SQLitening is a client/server implementation of the very popular SQLite database.

SQLitening is a programmer's library in standard Win32 DLL form. It is installed as a standard Windows Service. In addition to client/server mode, the library allows the programmer to also access SQLite databases in local mode. In either mode (local or client/server), the database is extremely fast and robust. -- Source: http://www.planetsquires.com/sqlite_client_server.htm

  • 4
    Can you elaborate or provide a link to What is SQLitening? That link goes to a forum and it has not a word on what it is actually. – develCuy Sep 18 '14 at 15:00
17

As stated before sqlite is not a client-server application and it is not built for highly concurrent operations.

Nevertheless you can "make it client-server", if you use ssh.

ssh user@host sqlite3 databasefile select * from table

works.

  • 1
    Is this considered "client-server" because you've encrypted the connection? – Robert Harvey Jan 18 '18 at 16:06
  • No, it is just because of a network between the machine which hosts the db and the machine accessing the db. – ddeimeke Jan 20 '18 at 7:26
4

No, SQLite doesn't present a network endpoint - it is only accessible via the filesystem. It does support concurrent access from multiple processes on the same machine but at a very coarse-grained level (DML locks an entire table). So you could have a dozen Apache httpd processes all with a SQLite database on the local disk open, all doing SELECTs and it would work just fine. But really, it's the wrong tool for the job - I'd use Postgres in this scenario.

  • 1
    locks an entire table Only when You're writing on it not reading many processers can read from the same table ;) – ucefkh Aug 29 '14 at 17:40
3

Paradigma Software introduces Valentina Server 6.0 (in beta testing now), which is 3 in 1:

  • Valentina DB Server
  • Valentina SQLite Server
  • Valentina Report Server

SQLite Server uses SQLite engine without changes, WAL enabled. SQLite Server works on 3 OS: Mac, Win, Linux.

You can use Valentina Studio (free) application to manager this DB Server, as well as mySQL, postgreSQL, SQLite, MS SQL. It also works as native C++ made application on 3 OS.

SQLite Server contains such features as: SSL, ACL, Backups, REST API, Notification Channels, JSON, XML.

Right now access to this server can be done from C++, Xojo and LiveCode. Soon will be added PHP, Java, .NET.

Free version of Valentina Server includes

  • 10 connections to SQLite DBs
  • 5 connections to Valentina DBs
  • 5 connections to Valentina Reports

Details you can read in the article.

2

SQLabs offers a commercial product called cubeSQL that may suit your needs.

1

You could hack something together using netcat, but I can't imagine it would be a very elegant solution.

-5

You can use a service similar to dropbox. There self-hosted solutions. However, SQLite3 was not created for a client-server model. You'd be better of with other solutions which were developed ground-up by client-server model.

  • 5
    Dropbox and other file synchronization services ARE NOT a solution to this problem. Dropbox does not contain any logic in regards to merging changes in a database that multiple users are writing to concurrently. The end result will be lost data, work, and time. – jptros May 7 '13 at 17:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.