Database queries

18.4.0 released Sep 25, 2023

Database queries

Databases

Your application can use any number of databases, specified by database-config-files. If only a single database is used, you may skip specifying a database with "@" clause. You can use different database vendors at the same time.

Query interface, SQL injection

Vely has the same query interface for all databases (see for example run-query). It provides means to execute dynamic as well as static queries; regardless, query input parameters are always separated from the query logic. This separation is in some cases emulated (see run-query), and in others native, such as with prepared statements (see run-query). In either case, Vely provides protection against SQL injections.

Input and output parameters

Input parameters and output resuls are always null-delimited strings. In web applications, more often than not, it is string values that are sought as output or provided as input, and conversion to other types rarely happens; there is surely a number of applications where the opposite may be true, but for the majority of applications this approach may work better. If data conversion is needed, it can be performed pre-query or post-query, depending on the purpose. The placeholder for input parameter is chosen to represent this approach, using single quotes (as text literals are single quoted in SQL) and %s (a C convention for string placeholders), thus '%s' is used for input parameters.

Binary data

Binary data can be handled for storage or retrieval via hexadecimal strings (see encode-hex, decode-hex) or Base64 (see decode-base64, encode-base64); this is reasonable for smaller amounts of data. Note that storing large amounts of binary data in the database is generally less desirable than storing such data (for instance PDF or JPG documents) in the file system (see file-storage), as manipulating and retrieving binary data is generally easier and faster that way.

Prepared statements

Prepared statements typically provide better performance but may not be ideal in every circumstance (see prepared-statements).

Multiple statements

Multiple statements in one query can be executed in PostgreSQL, where only the last statement's result set is available, which is simpler and probably more manageable. MariaDB multiple statement execution is disabled by default, and so is for SQLite. Multiple statement execution isn't ideal as generally the number of statements may not be known in advance and retrieving multiple results sets can be challenging. Using stored procedures that return a single result set, or multiple queries to execute multiple statements are both better approach.

Prepared queries, regardless of the database, can never be multiple statements, which is a limitation that exists in all databases.

Connection persistence and reuse, transactions

A single Vely process (see how-vely-works and vely-architecture) keeps a single connection open to the database, which is shared between all requests a process will handle. Each request will either commit or rollback a transaction; if the transaction is neither committed nor rollback-ed, it will be rollback-ed with error (see begin-transaction). If a database connection is lost, Vely will automatically re-establish it if possible. This allows for very fast query execution and essentially unlimited reuse of prepared statements.

Autocommit

The autocommit feature is enabled by default on all supported databases. Do not disable it, as that would cause unpredictable and inconsistent behavior. Whenever you need a transactional behavior, use commit-transaction to begin a transaction and commit-transaction/rollback-transaction to end it. Also, in general, multiple DML statements (such as INSERT) are faster within a transaction block because the data is not flushed with each one, but rather once per block.