Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?
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
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
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.
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.
SQLabs offers a commercial product called cubeSQL that may suit your needs.
You could hack something together using netcat, but I can't imagine it would be a very elegant solution.
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.