vely:Database config file

Database config file

Vely application can use any number of databases, with each specified by a database configuration file. This file provides database name, login and connection settings and preferences.

When making a Vely project, you specify a database vendor and the database configuration file for each database your application uses (see vv), for instance:

vv ... --db="mariadb:db1 postgres:db2 sqlite:db3"  ...

in which case there are three database configuration files (db1, db2 and db3), with db1 being MariaDB, db2 being PostgreSQL and db3 being SQLite database.

You must create a database configuration file for each database your application uses, and this file must be placed with your source code. Such file will be copied to locations specified and used by Vely to connect to database(s) (see how_vely_works).

Each such file contains connection information and authentication to a database, which Vely uses to login. The names of these configuration files are used in queries. There is no limit on how many databases can be used in your application and those from different vendors can be used in the same application.

An example of database configuration file (in this case MariaDB):

[client]
user=mydbuser
password=somepwd
database=mydbname
protocol=TCP
host=127.0.0.1
port=3306

Using in your queries

Database statements that perform queries (such as run-query) must specify the database configuration file used, unless your application uses only a single database. Such configuration is given by "@<database config file>" (for instance in run-query or begin-transaction). For example, in:

run-query @mydb="select name from employees"
...
end-query

the query "myquery" is performed on a database specified by the configuration file "mydb", as in (assuming it's PostgreSQL database):

vv ... --db="postgres:mydb"  ...

You do not need to manually connect to the database; when your application uses it for the first time, a connection is automatically established, and lost connection is automatically re-established when needed.

If a database is the only one used by your application, you can omit it, as in:

run-query ="select name from employees"
...
end-query

Connection settings

The contents of a configuration file depends on the database used:

MariaDB: configuration file is written as a MariaDB client options file. You can see the parameters available at
```
https://mariadb.com/kb/en/configuring-mariadb-connectorc-with-option-files/#options
```
Most of the time, though, you would likely use only a few of those options, as in (for local connection):
```
[client]
user=myuser
password=mypwd 
database=mydb
socket=/run/mysqld/mysqld.sock
```
The above file has fields "user" (MariaDB user), "password" (the password for MariaDB user), "database" (the MariaDB database name) and MariaDB communication "socket" location (assuming your database is local to your computer - if the database is across network you would not use sockets!).

If you use passwordless MariaDB login (such as when the MariaDB user name is the same as your Operating System user name and where unix socket plugin is used for authentication), the password would be empty.

To get the location of the socket, you might use:
```
sudo mysql -u root -e "show variables like 'socket'"
```
Postgres: configuration file has a Postgres connection string. You can see the parameters available at
```
https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNSTRING
```
Most of the time, though, you may be using only a few of those options, as in:
```
user=myuser password=mypwd dbname=mydb
```
The above file has parameters "user" (Postgres user), "password" (the password for Postgres user), "dbname" (the Postgres database name). If you use peer-authenticated (i.e. passwordless) login, then omit "password" - this is when the Postgres user name is the same as your Operating System user name and where local unix domain socket is used for authentication.
SQLite: configuration file should contain a single line of text, and it must be the full path to the SQLite database file used, for example (if you keep it in Vely's application directory):
```
/var/lib/vv/<app name>/app/<db name>.db
```

Substituting environment variables

You can use environment variables in database configuration files by means of substitution, in the form of "${VAR_NAME}". For example in file "mydb":

[client]
user=${DB_USER}
password=${DB_PWD}
database=${DB_NAME}
protocol=TCP
host=127.0.0.1
port=${DB_PORT}

Here, environment variables DB_USER, DB_PWD, DB_NAME and DB_PORT are used. They must be defined in the shell environment prior to calling vv to make your application (if not defined the value will be empty):

#Define environment variables for a configuration file
export DB_USER="my_user"
export DB_PWD="my_password"
export DB_NAME="my_database"
export DB_PORT="3307"

#Make application using the above database configuration file with the environment variables specified
vv -q --db=mariadb:mydb

which results in file /var/lib/vv/<app name>/app/db/mydb:

[client]
user=my_user
password=my_password
database=my_database
protocol=TCP
host=127.0.0.1
port=3307

Besides making application deployment easier, this also adds to its security as the information such as above (including the database password) does not need to be a part of source code and reside in source code control system (such as git).

Your environment variables can have any names, except that they cannot start with an underscore ("_") or be prefixed by "VV_", because those variable names are reserved by Vely.

Note that if your data actually has a dollar sign and is a part of the configuration file, then you can create a variable for it:

export DOLLAR_SIGN='$'

and in the configuration file:

..
database=my${DOLLAR_SIGN}database
..

In this case the database name is "my$database".