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.
See also
Database (
begin-transaction commit-transaction current-row database_config_file database_queries on-error prepared_statements query-result rollback-transaction run-query )
SEE ALL (
documentation)