ws4sqlite is a free software to query SQLite database using HTTP API request. This software creates a virtual server for a specific SQLite database, set by you. You can then make simple HTTP API requests to run queries and retrieve data in JSON format. Not only data retrieval, but you can also update data in database tables as well.
In the API request, you can include many SQL statements and it will be able to execute them all with ease. If you are working with a remote SQLite database, then it is a very nice tool to quickly update and retrieve data. All you have to do is start the ws4sqlite sever on your VPS or whenever you have put your SQLite database file.
SQLite is a static self-contained database just like MS Access. Unlike MySQL and Postgres, it doesn’t run a server and hence, it cannot be updates remotely. As a result, you have to manually download the SQLite DB file again and again to retrieve the data, as well as update it via SQLite editor apps.
But ws4sqlite here removes that problem. It runs a server for a SQLite database that you specify, and then you can do data update and retrieval without having to download the DB file again and again. This is as simple as that.
Free Software to Query SQLite Database using HTTP API Requests: ws4sqlite
ws4sqlite is a free and open source software that can turn any SQLite database into a remote database. It is even a cross-platform tool, and that is why you can easily install and use it on any platform.
To start using it, you simply download it from here for your platform. Extract the ZIP file and then you will see the EXE file of ws4sqlite as you can see in the screenshot below.
To test it locally, you can download a sample SQLite database from here. Extract the “chinook.db” file and then keep it in the same folder as “ws4sqlite.exe”. I am using Windows in this post for demonstrating how to use it. But the process is the same for Linux and macOS.
Run the following command to start the server. In the CLI parameters, you have to pass the correct database name. After the server starts, you can start making the API requests.
Syntax: ws4sqlite --db SQLite_Database_Name
Example: ws4sqlite --db chinook.db
Now, when the server is running, you can open another command prompt and then start executing the SQL statements by making a simple API call. The JSON payload for POST request to execute SQL query or queries is below.
{
"transaction": [
{
"query": "SELECT * FROM TEST_TABLE limit 5"
},
{
"query": "SELECT * FROM TEST_TABLE"
}
]
}You can use cURL like tools to make the API requests to execute SQL on the remote database. See the example request for Chinook DB below to retrieve 10 records from a table.
curl -XPOST -d '{
"transaction": [
{
"query": "SELECT * FROM artists limit 5"
}
]
}' -H 'Content-Type: application/json' 'http://localhost:12321/chinook'
In the above screenshot, you can see the JSON response that it returns for the POST request I made. Similarly, you can make other request with different SQL queries. You can even batch SQL queries in the API call and you can learn more about that by reading the official technical documentation of ws4sqlite.
Closing thoughts:
Update data on a remote SQLite is a real time-consuming problem that I have also faced in the past. But I am happy to come across this amazing tool that makes it every easy to update data in a remote SQLite database with ease. ws4sqlite is a great tool for developers and database engineers. So, if you are also looking for a tool that lets you manipulate SQLite database without downloading it on your PC, then you can give this a try and let me know what you think about it.
