18.4.0 released Sep 25, 2023
What are ACID transactions and how to use them with files: a PostgreSQL example


Vely

Files and transactions
While general operating system files are not a part of database systems, you can commit a database transaction and write a file so that a file is assured to be written if the database record is committed. Note this isn't the same as file being a part of the transaction - you would have to use database's facilities for that; however it's a way to practically tie files to database records. For example you might be writing files and inserting their information into a database. You will do just that with PostgreSQL in this example, but in general you can use other databases that support transactions - more on this next.
PostgreSQL
PostgreSQL (or just Postgres) is a popular Open Source database that uses SQL to create and manipulate data, and which is also ACID compliant. This means Atomicity, Consistency, Isolation and Durability, which are properties that a database should comply with to process transactions reliably. This is very important, as without it, data used in virtually any business might get lost, duplicated or become just plain wrong. Imagine if banks didn't implement transactions correctly - your check deposit or a simple transfer between accounts might not go through as you expect.

So what does ACID mean exactly?
These qualities are guaranteed by PostgreSQL when you use transactions. Other databases (such as SQLite or MariaDB with InnoDB engine) are also ACID-compliant. For the most part, you can write the same SQL statements that would work with all of these databases, and you can use transactions in the same fashion.
Connecting to database
Working with PostgreSQL is the fastest with a native C library. Here I will use Vely, which uses this kind of library. It also keeps the connection alive, which avoids wasting time on connect/disconnect cycles. In addition, you can use prepared SQL statements, where the server will parse a SQL statement once and then use the parsed statement tree in the future, without having to do it again. Persistent connections and parsed statements go hand in hand, because a parsed statement is only valid within a single session. So if you were to use connection method that isn't persistent, then you would get very little out of prepared statements. Vely will automatically re-establish the connection if it gets lost - for example that can happen if the PostgreSQL server is restarted.
Prerequisites
To begin with, install Postgres. Also, install Vely, which will be used to create a native executable for this example.

Create a new directory for this example:
mkdir postgres
cd postgres

Setup the database
First, you will login as root to "psql" utility and setup database objects:
echo "create user $(whoami);
create database db_items with owner=$(whoami);
grant all on database db_items to $(whoami);
\q
" | sudo -u postgres psql

Here, you've created database "db_items" and user named after your OS Linux user, creating a passwordless Postgres user. The reason for this is because it gives you better and easier security - only you, logged in as your current OS user, can access the database, and thus you don't need a password. Then you'll give database user the permissions to basically own database "db_items" and be able to create objects, data etc.

Finally, create table "item_list" in this database:
echo "create table item_list (item_id bigserial primary key, item_name varchar(30), item_desc varchar(100))" | psql -d db_items

This will create table "item_list" which contains item names and descriptions, as well as auto-generated primary key as an ID.
Access the database
In order to access the database, you will need a database-config-file for it. This file specifes the database user name and password and any other connection string parameters. Consult Postgres documentation to see all the parameters available. Here, create database configuration file named "items". You can call this file anything, but its name is used in the code to reference the database, so if you change it, then also change it in the code below. Create file "items" with this bash code:
echo "user=$(whoami) dbname=db_items"  > items

This is actually a native PostgreSQL client configuration file, so learning the format of it may help you elsewhere as well. You'd specify Postgres user name (which is the name of your OS user, or the result of $(whoami) bash expression), and the database name is "db_items" - again that's the database we created already. There's no password because the login is passwordless, as explained above.
The code
Create file "add_item.vely" and copy this to it (note that the name of this file always matches a function name implemented in it; see how-vely-works):
#include "vely.h"

request-handler /add_item
    out-header default

    input-param name
    input-param desc

    // Start transaction
    begin-transaction @items
    // Insert data
    run-query @items = "insert into item_list (item_name, item_desc) values ('%s', '%s') returning (item_id)" \
            output define item_id : name, desc  \
            error define err_code error-text define err_text affected-rows define rows
        // Check if no error and a row is actually inserted
        if (!strcmp (err_code, "0") && rows == 1) {
            // Construct file name
            write-string define item_file
            @item_added_<<p-out item_id>>
            end-write-string
            // Write file
            write-file item_file from item_id status define write_st
            if (write_st < 0) {
                // Could not write file, even if insert okay
                rollback-transaction @items
                @Could not write to file, status <<p-num write_st>>
            } else {
                // Both write file and insert okay
                commit-transaction @items
                @SUCCESS, item added to database and written to a file (<<p-out item_file>>)
            }
        } else {
            // Could not insert
            rollback-transaction @items
            @Could not insert to database, error <<p-out err_text>>, error code <<p-out err_code>>
        }
    end-query
end-request-handler

This code is a request handler - it handles a request, such as an HTTP(S) request, or a request from command line. Here's how it works:
You can also use prepared SQL statements by using "run-prepared-query" statement instead of "run-query" in the code above.

And the "@" output-statement sends the data to standard output, which can be the actual "stdout" stream if this is going to be a command-line program, or to the browser if this is a web application. The nice thing is, it works the same for both. p-out statement outputs a string, and when placed in between << and >> it is "inlined" into an output statement.
Create and Make the application
When you get started on a Vely application, you have to create it first with the vf program manager:
sudo vf -i -u $(whoami) items_app

"-i" option says to create an application. "-u" option says which user will own it, in this case it's "$(whoami)" which is Linix-speak for the "currently logged in user". And finally the application name is "items_app".

To make your application, use vv tool:
vv -q --db='postgres:items'

This gathers all the .vely files in the current directory (in this case just one), processes all the Vely statements (like run-query) into C code, and then compiles and links it all together into a native application. Note "--db='postgres:items'" option - it states your program uses database named "items" and the database vendor is PostgreSQL. You can have any number of databases and any number of supported vendors.

Two executables will be produced, both in the "/var/lib/vv/bld/items_app" directory. Note the "items_app" subdirectory - it matches your application name created above. This directory is like a scratch-pad for your application, this is where all the generated code goes. One executable created will be "items_app" that you can run from the command line. The other one is "items_app.fcgi" that you can run as a FastCGI application server, which is the web application.
Run from command line
Execute your program:
vv -r --req="/add-item?name=Wifi+Camera&desc=Feature+rich+wifi+camera+for+the+home" --silent-header --exec

You might get:
SUCCESS, item added to database and written to a file (item_added_1)

Verify the data has been added:
echo "select * from item_list" | psql -d db_items

The result is:
 item_id |  item_name  |               item_desc
---------+-------------+---------------------------------------
       1 | Wifi Camera | Feature rich wifi camera for the home
(1 row)

Your request has added data to the database!

Note you can also obtain the direct commands to execute a program by omitting "--exec" option in "vv -r" call above:
vv -r --req="/add-item?name=Wifi+Camera&desc=Feature+rich+wifi+camera+for+the+home" --silent-header

which produces:
export CONTENT_TYPE=
export CONTENT_LENGTH=
export VV_SILENT_HEADER=yes
export REQUEST_METHOD=GET
export SCRIPT_NAME="/items_app"
export PATH_INFO="/add-item"
export QUERY_STRING="name=Wifi+Camera&desc=Feature+rich+wifi+camera+for+the+home"
/var/lib/vv/bld/items_app/items_app

You can copy these to your script and execute, to the same result.

Vely is all about standard HTTP requests. So even when you run a command line program, it does so by receiving an HTTP request. That's why there's a request method ("GET"), a script name (which is a path to application name "items_app"), a path info (which is a path to request handler "add_item", i.e. your code above), and a query string containing the input data ("name" and "desc") which match the input parameters in your code.

This makes is very easy to build programs for both command-line and web execution, because they are the same. You don't need to write two code bases and you only debug once. Plus, you can do on command line virtually anything you can on the web, so you can write your program without ever even having a web server setup.

Note the VV_SILENT_HEADER environment variable - it suppresses HTTP header output. If it weren't there, you'd get the HTTP header, the same one that a browser will get.

You can of course run this example as a web service by starting your own application server - see this as an example.
See also
Examples
example-client-API  
example-cookies  
example-create-table  
example-distributed-servers  
example-docker  
example-encryption  
example-file-manager  
example-form  
example-hash-server  
example-hello-world  
example-how-to-design-application  
example-json  
example-multitenant-SaaS  
example-postgres-transactions  
examples  
example-sendmail  
example-shopping  
example-stock  
example-uploading-files  
example-using-mariadb-mysql  
example-utility  
example-write-report    
See all
documentation

You are free to copy, redistribute and adapt this web page (even commercially), as long as you give credit and provide a link back to this page (dofollow) - see full license at CC-BY-4.0. Copyright (c) 2019-2023 Dasoftver LLC. Vely and elephant logo are trademarks of Dasoftver LLC. The software and information on this web site are provided "AS IS" and without any warranties or guarantees of any kind.