In this tutorial we'll run ws4sqlite for the first time, in order to serve a single database, and we'll run a couple of queries and statements against it.
We'll use Linux, but the information here is "portable" to MacOS and Windows as well.
{% hint style="success" %} If ws4sqlite offers a relevant capability that doesn't fit in a tutorial, it will be explained in an info box like this, and a link to the relevant documentation will be provided. {% endhint %}
Let's start!
The installation is simple, as ws4sqlite "is" just an executable file. Download or build it (matching your OS and architecture), and put it somewhere on the filesystem.
Let's now start the application:
ws4sqlite --db testDb.dbThis tells ws4sqlite to serve a database, to be created because a file at the specified path doesn't exist, using default settings. It's now possible to access the database using its id, that is the filename minus the suffix (in this case, testDb).
{% hint style="success" %}
More than one database can be served from the same instance, and it's possible to create in-memory databases. Of course more options are possible: provide authentication, open the file as read only, specify some queries/statements on the server that can be referenced in requests, provide initialization statements to apply when creating a database, and several more. This is done by creating a companion YAML file at the same path, called like the database but with a .yaml extension: testDb.yaml in our example.
{% endhint %}
When the app starts, something like this will be printed; it gives information about what is now being served, and how.
ws4sqlite x.y.z
- Serving database 'testDb' from testDb.db?_journal=WAL
+ No config file loaded, using defaults
+ File not present, it will be created
+ Using WAL
- Web Service listening on 0.0.0.0:12321
The service is now active and serving requests. Use Ctrl-c to exit, as usual.
{% hint style="success" %} From the commandline, it's also possible to specify the port and the host to bind to. {% endhint %}
Let's now do something useful. Use a tool like postman to submit a POST call to http://localhost:12321/testDb, with the following body:
{
"transaction": [
{
"statement": "CREATE TABLE TEST_TABLE (ID int primary key, VAL text)"
}
]
}{% hint style="warning" %}
Ensure that the header Content-Type is set to application/json!
{% endhint %}
Let's see what is in the request:
- URL: in the connection URL, we specify the database ID to submit the request to, as defined in the config file;
- Line 2: specify the transaction operation list: an array of requests (queries or statements) to submit;
- Line 4: as the first (and only) request, specify a statement (i.e. a SQL command that doesn't return a resultset).
If all goes well, you should get a 200 response with the following body:
{
"results": [
{
"success": true,
"rowsUpdated": 0
}
]
}You did it! 🚀 Going through the response:
- Line 2: the array of results has the same size of the corresponding
transactionarray in the request, and lists the results of each request, in turn; - Line 4: specifies that the statement completed with success;
- Line 5: there were no updated rows (as reported by SQLite; this is a DDL command).
Let's say you want to run multiple SQLs in the same request. As you may suspect, this is just a matter of adding another item to the transaction array:
{% hint style="info" %} As the name of the array implies, the queries/statements are run in a single transaction. It will be committed at the end of the call, and rolled back if an unmanaged error occours (see the relevant chapter). {% endhint %}
{
"transaction": [
{
"statement": "CREATE TABLE TEST_TABLE_2 (ID int primary key, VAL text)"
},
{
"statement": "INSERT INTO TEST_TABLE_2 (ID, VAL) VALUES (1, 'hello')"
}
]
}The response now has 2 elements in the array:
{
"results": [
{
"success": true,
"rowsUpdated": 0
},
{
"success": true,
"rowsUpdated": 1
}
]
}Please notice Line 9: rowsUpdated is 1, signaling that we affected one row with the INSERT.
Let's send over the last request again (don't remove the file!). The table already exists, so the response will now fail with 500 Internal Server Error , and with a body of:
{
"reqIdx": 0,
"error": "Table TEST_TABLE_2 already exists",
}- Line 2: the (0-based) index of the statement that failed in the
queriesarray; an index of-1would tell us that it's a generic error, not tied to a particular statement; - Line 3: the reason of the failure, as reported by SQLite.
This is a general failure. As statements in the same request are run in transaction, the whole transaction is rolled back.
It's also possible to "allow" certain statements to fail. The transaction will be completed, and an error will be reported only on that statement. Send the following request, notice the "noFail" in the first statement:
{
"transaction": [
{
"noFail": true,
"statement": "CREATE TABLE TEST_TABLE_2 (ID int primary key, VAL text)"
},
{
"statement": "INSERT INTO TEST_TABLE_2 (ID, VAL) VALUES (1, 'hello')"
}
]
}The following result is produced, signaling that the first statement failed; the transaction is committed anyway, so the second statement is actually persisted:
{
"results": [
{
"success": false,
"error": "Table TEST_TABLE_2 already exists"
},
{
"success": true,
"rowsUpdated": 1
}
]
}Up to now, we tested only statements, that don't return results other than the number of affected rows. Let's see how to run a query.
In the next example we will create a table, insert two rows in it, and read them.
{% hint style="warning" %} Please start from an empty database: stop ws4sqlite, remove the database file and start it again. {% endhint %}
Request:
{
"transaction": [
{
"statement": "CREATE TABLE TEST_TABLE (ID int primary key, VAL text)"
},
{
"statement": "INSERT INTO TEST_TABLE (ID, VAL) VALUES (1, 'hello'), (2, 'world')"
},
{
"query": "SELECT * FROM TEST_TABLE ORDER BY ID ASC"
}
]
}- Line 10: notice that the key now is
query, to signal that it will generate a result set.
{
"results": [
...omitted the first two results...
{
"success": true,
"resultSet": [
{ "ID": 1, "VAL": "hello" },
{ "ID": 2, "VAL": "world" }
]
}
]
}- Lines 7..8: we now have an array with two results, containing objects. Each object has several fields, with the key being the name of the database field and the value being... the value. The key/field name is as reported by the database, so
*works well.
The last capability we'll cover is using parameters, either in a statement (e.g. an INSERT) or in a query. They are specified using named placeholders, like the following.
{
"transaction": [
{
"statement": "INSERT INTO TEST_TABLE (ID, VAL) VALUES (:id, :val)",
"values": { "id": 101, "val": "A hundred and 1" }
},
{
"query": "SELECT * FROM TEST_TABLE WHERE ID = :id",
"values": { "id": 101 }
}
]
}- Line 4: in the statement, we use named placeholders like
:id; - Line 5: we specify the actual values with a
valuesobject, containing a map with the keys being the placeholders; - Same with queries, as at Line 9.
{% hint style="info" %} Using placeholders may seem more verbose than specifying the values in the SQL, but it is always the preferrable solution, allowing for example to avoid nasty SQL injection bugs. {% endhint %}
{% hint style="success" %}
For statements, it is also possible to specify multiple sets of values ('batches'); the statement will be cached and replayed for each set of the list. See valuesBatch in the docs.
{% endhint %}
{
"results": [
{
"success": true,
"rowsUpdated": 1
},
{
"success": true,
"resultSet": [
{ "ID": 101, "VAL": "A hundred and 1" }
]
}
]
}jAs you can see, the response is the same, with only one result in the resultset (since the query selects by primary key).
Thanks for reading so far, I hope you liked it! There are many more topics of interest, among which:
- Learn to protect your transactions with authentication;
- Use a reverse proxy for HTTPS and additional security;
- Use stored statements to avoid passing SQL from the client;
- Perform scheduled activities, that is: sql statements,
VACUUMs or backups; - Configure CORS for more convenient access from a web page;
- ...and much more!
Have a nice day! ☀️