Table of Contents

Database Connections

Introduction

Establishing a connection to a database is the first step in using Velocity. The framework provides flexible connection mechanisms that work with all supported database systems while maintaining a consistent API across vendors.

The Manager class serves as the central entry point for all Velocity operations. For connected operations, a Manager must be created with a database connection. Velocity supports two primary approaches for creating connections:

  1. DatasourceConnection Objects - Strongly-typed connection objects with properties (recommended)
  2. Connection Strings - Standard ADO.NET connection strings with additional context settings
Note

A Manager can also be created without a database connection using the parameterless constructor new Manager(). This is only for disconnected operations such as reading and writing schema files. See Disconnected Operations for details.

Connection Approaches

The recommended approach is to use database-specific connection classes. This approach provides:

  • Type safety and IntelliSense support
  • Clear, self-documenting connection parameters
  • Built-in validation
  • Access to database-specific advanced and context settings

DB2 Connection

DB2DatasourceConnection provides connection configuration for IBM DB2:

var connection = new DB2DatasourceConnection
{
    Hostname = "db2.example.com",
    Port = 50000,
    Username = "db2admin",
    Password = "password",
    Database = "MYDB"
};

// Set context settings individually
connection.ImportTimeout = 120;
connection.ImportBatchSize = 10000;
connection.UpdateFrequency = "d(0,1,2,3,4,5,6) h(0,12) m(0)";
connection.UpdateMinimum = 5;

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
}

MySQL Connection

MySqlDatasourceConnection provides connection configuration for MySQL:

var connection = new MySqlDatasourceConnection
{
    Hostname = "mysql.example.com",
    Port = 3306,
    Username = "root",
    Password = "password",
    Database = "myapp_db"
};

// Set advanced settings for MySQL-specific connection behavior
connection.AllowLoadLocalInfile = true;
connection.AllowUserVariables = true;

// Set context settings individually using strongly-typed properties
connection.ImportTimeout = 120;
connection.LengthUnit = "metre";

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
}

Oracle Connection

OracleDatasourceConnection provides connection configuration for Oracle Database:

var connection = new OracleDatasourceConnection
{
    Hostname = "oracle.example.com",
    Port = 1521,
    Username = "system",
    Password = "password",
    Database = "ORCL"
};

// Set context settings using strongly-typed properties
connection.Tablespace = "USERS";
connection.ImportTimeout = 120;
connection.ImportBatchSize = 10000;
connection.Tolerance = 0.01f;
connection.UseBooleanDataType = false;

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
}

PostgreSQL Connection

PostgreSqlDatasourceConnection provides connection configuration for PostgreSQL:

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "postgres.example.com",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "myapp_db"
};

// Set context settings individually
connection.ImportTimeout = 120;
connection.DefaultSrid = 4326;

using (var manager = new Manager(connection))
{
    var database = manager.LoadDatabase();
}

SQLite Connection

SqliteDatasourceConnection provides connection configuration for SQLite. SQLite uses a unique folder-based approach where the Database property represents a folder path rather than a database name:

var connection = new SqliteDatasourceConnection
{
    Database = "./data"  // Folder path, not a database file
};

// Set context settings
connection.ImportTimeout = 120;

using (var manager = new Manager(connection))
{
    // Creates or opens ./data/main.db
    var schema = manager.LoadSchema("main");
}

SQLite Multi-Schema Support:

SQLite implements schemas as separate database files within the folder:

  • main.db - The default schema
  • {schemaname}.db - Additional schemas

Cross-schema operations use SQLite's ATTACH DATABASE feature automatically.

SQL Server Connection

SqlServerDatasourceConnection provides connection configuration for Microsoft SQL Server:

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Data.SqlServer;
using YndigoBlue.Velocity.Enums;

var connection = new SqlServerDatasourceConnection
{
    Hostname = "sqlserver.example.com",
    Port = 1433,
    Username = "myuser",
    Password = "mypassword",
    Database = "myapp_db"
};

// Set context settings individually using strongly-typed properties
connection.ImportTimeout = 120;           // Universal setting
connection.ImportBatchSize = 10000;       // Universal setting
connection.DefaultSrid = 4326;            // Universal setting

// SSL — see the SSL/TLS section below for the full set of options
connection.SslMode = SslMode.VerifyCA;
connection.SslCaPath = "./certs/ca-cert.pem";

using (var manager = new Manager(connection))
{
    // Perform database operations
    var schema = manager.LoadSchema("myschema");
}

Teradata Connection

TeradataDatasourceConnection provides connection configuration for Teradata Vantage:

var connection = new TeradataDatasourceConnection
{
    Hostname = "teradata.example.com",
    Port = 1025,
    Username = "dbc",
    Password = "password",
    Database = "myapp_db"
};

// Set context settings individually using strongly-typed properties
connection.DefaultSrid = 1619;
connection.FullTextSearchClobs = false;
connection.FullTextSearchEnabled = false;
connection.IdentityStartValue = 1;
connection.ImportBatchSize = 1000;
connection.ImportCastClob = true;
connection.ImportCastGeo = true;
connection.NumberOfStagingTables = 4;
connection.PermanentSpace = 100000000;
connection.SelectCastClob = true;
connection.VarCharCastSize = 32000;

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
}

Approach 2: Connection Strings

Velocity also supports standard ADO.NET connection strings with optional context settings. The DatasourceType enum is used to specify the database type. This approach is useful when:

  • Working with existing connection string configurations
  • Loading connection parameters from configuration files
  • Integrating with enterprise connection management systems
using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Enums;

// SQL Server with connection string
string connectionString = "Data Source=server,1433;Initial Catalog=mydb;User ID=username;Password=pwd1234;Trust Server Certificate=True";
string contextSettings = "ImportTimeout=120;ImportBatchSize=10000;DefaultSrid=4326";

using (var manager = new Manager(DatasourceType.SQLServer, connectionString, contextSettings))
{
    var schema = manager.LoadSchema("myschema");
}

Connection String Examples by Database

DB2:

string connectionString = "Server=localhost:50000;Database=MYDB;UID=db2admin;PWD=password";
using (var manager = new Manager(DatasourceType.DB2, connectionString))
{
    // Operations
}

MySQL:

string connectionString = "Server=localhost;Port=3306;Database=mydb;Uid=root;Pwd=password;AllowLoadLocalInfile=True;AllowUserVariables=True";
using (var manager = new Manager(DatasourceType.MySQL, connectionString))
{
    // Operations
}

Oracle:

string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=system;Password=password;";
using (var manager = new Manager(DatasourceType.Oracle, connectionString))
{
    // Operations
}

PostgreSQL:

string connectionString = "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=password";
using (var manager = new Manager(DatasourceType.PostgreSQL, connectionString))
{
    // Operations
}

SQLite:

string connectionString = "Data Source=./data/main.db";
using (var manager = new Manager(DatasourceType.Sqlite, connectionString))
{
    // Operations
}

SQL Server:

string connectionString = "Data Source=localhost,1433;Initial Catalog=mydb;User ID=sa;Password=password;Trust Server Certificate=True";
using (var manager = new Manager(DatasourceType.SQLServer, connectionString))
{
    // Operations
}

Teradata:

string connectionString = "Data Source=localhost;Database=mydb;User ID=dbc;Password=password";
using (var manager = new Manager(DatasourceType.Teradata, connectionString))
{
    // Operations
}

Understanding Settings: Advanced vs Context

Velocity uses two types of settings to configure database connections:

  • AdvancedSettings: Database-specific connection parameters passed to the underlying ADO.NET provider
  • ContextSettings: Velocity framework behavior settings that control how Velocity operates

Key Differences

Aspect AdvancedSettings ContextSettings
Purpose Configure ADO.NET provider behavior Configure Velocity framework behavior
Scope Passed to database driver Used by Velocity operations
Examples MultipleActiveResultSets, Application Name, Statement Cache Size ImportTimeout, DefaultSrid, ImportBatchSize
Where Used Connection string to database Velocity commands and operations
Timing Applied during connection Applied during operations

AdvancedSettings

AdvancedSettings control the underlying database connection behavior by passing parameters directly to the ADO.NET provider. These are standard connection string parameters specific to each database vendor.

How AdvancedSettings Work

When you create a Manager with a DatasourceConnection, Velocity:

  1. Reads named properties (Hostname, Port, Username, Password, Database, Pooling, ConnectTimeout, MaxPoolSize, MinPoolSize)
  2. Merges them with AdvancedSettings to build the final connection string
  3. Passes the complete connection string to the ADO.NET provider

Reserved Settings are connection parameters that have corresponding named properties and cannot be overridden via SetAdvancedSettings():

  • Connection Parameters: Hostname/Host, Port, Username/UserID, Password, Database/InitialCatalog/DataSource
  • Universal Properties: Pooling, ConnectTimeout, MaxPoolSize, MinPoolSize
  • SSL/TLS Properties: SslMode, SslCaPath, SslCertificatePath, SslKeyPath
  • Authentication Properties: UseIntegratedSecurity plus database-specific keys like Authentication, Encrypt, TrustServerCertificate, Server Certificate, AuthenticationMechanism, IntegratedSecurity (see Security: SSL/TLS and Authentication)

These reserved settings are always controlled through their strongly-typed properties to prevent conflicts and ensure consistency.

Setting AdvancedSettings

Use the SetAdvancedSettings() method to configure database-specific connection parameters:

var connection = new SqlServerDatasourceConnection
{
    // Required connection parameters (reserved settings)
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "mydb",

    // Universal properties (reserved settings)
    Pooling = true,
    ConnectTimeout = 15,
    MaxPoolSize = 100,
    MinPoolSize = 0
};

// Additional provider-specific settings (non-reserved). Note: SSL behaviour
// (Encrypt, TrustServerCertificate, Server Certificate) is controlled via the
// SslMode / SslCaPath properties — see the SSL/TLS section below.
connection.SetAdvancedSettings("MultipleActiveResultSets=True;Application Name=MyApp");

Important: If you try to override reserved settings via SetAdvancedSettings(), they will be ignored:

connection.Pooling = true;
connection.ConnectTimeout = 15;
connection.MaxPoolSize = 100;
connection.MinPoolSize = 0;

// These will be IGNORED - reserved settings use named properties
connection.SetAdvancedSettings("Pooling=False;Connect Timeout=30;MaxPoolSize=50;MinPoolSize=5");

// connection.Pooling is still true
// connection.ConnectTimeout is still 15
// connection.MaxPoolSize is still 100
// connection.MinPoolSize is still 0

Database-Specific Advanced Properties

Some databases expose additional advanced settings as strongly-typed properties:

MySqlDatasourceConnection:

var connection = new MySqlDatasourceConnection
{
    Hostname = "localhost",
    Port = 3306,
    Username = "root",
    Password = "password",
    Database = "mydb"
};

// MySQL exposes these as strongly-typed properties
connection.AllowLoadLocalInfile = true;  // Exposed as property
connection.AllowUserVariables = true;    // Exposed as property

// Other settings via SetAdvancedSettings
connection.SetAdvancedSettings("SSL Mode=None;Default Command Timeout=30");

OracleDatasourceConnection:

var connection = new OracleDatasourceConnection
{
    Hostname = "localhost",
    Port = 1521,
    Username = "system",
    Password = "password",
    Database = "ORCL"
};

// Oracle has no database-specific strongly-typed advanced properties beyond universal settings
connection.MaxPoolSize = 100;
connection.MinPoolSize = 1;

// Additional settings via SetAdvancedSettings
connection.SetAdvancedSettings("Validate Connection=true;Statement Cache Size=10");

PostgreSqlDatasourceConnection:

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "localhost",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "mydb"
};

// PostgreSQL has no database-specific strongly-typed properties beyond universal settings
connection.MaxPoolSize = 100;
connection.MinPoolSize = 1;

// Additional settings via SetAdvancedSettings
connection.SetAdvancedSettings("SSL Mode=Prefer;Command Timeout=60");

SqlServerDatasourceConnection:

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "mydb"
};

// SQL Server exposes TrustServerCertificate as a property
connection.TrustServerCertificate = true;

// Other settings via SetAdvancedSettings
connection.SetAdvancedSettings("Encrypt=False;MultipleActiveResultSets=True");

SqliteDatasourceConnection:

var connection = new SqliteDatasourceConnection
{
    Database = "./data"  // Folder path, not a database file
};

// SQLite has no database-specific strongly-typed advanced properties beyond universal settings
connection.MaxPoolSize = 100;

// Additional settings via SetAdvancedSettings
connection.SetAdvancedSettings("Cache=Shared;Foreign Keys=True");

TeradataDatasourceConnection:

var connection = new TeradataDatasourceConnection
{
    Hostname = "localhost",
    Port = 1025,
    Username = "dbc",
    Password = "password",
    Database = "mydb"
};

// Teradata exposes SessionCharacterSet as a property
connection.SessionCharacterSet = "UTF8";

// Additional Teradata-specific connection parameters
connection.SetAdvancedSettings("Connect Function=1;Charset=UTF8");

Strongly-Typed Advanced Properties by Database

Some databases expose additional advanced settings as strongly-typed properties (in addition to the universal reserved settings like MaxPoolSize and MinPoolSize, and the SSL/auth properties documented in Security: SSL/TLS and Authentication):

DB2

DB2DatasourceConnection exposes:

  • AuthenticationMethod - DB2AuthenticationMethod selector when UseIntegratedSecurity = true (Kerberos, GSS plugin, Client auth, etc.)

MySQL

MySqlDatasourceConnection exposes:

  • AllowLoadLocalInfile - Enable LOAD DATA LOCAL INFILE (required for imports)
  • AllowUserVariables - Enable user variable support (required for some operations)

Oracle

OracleDatasourceConnection has no database-specific strongly-typed advanced properties beyond universal settings. Authentication mechanism (OS auth, Kerberos, RADIUS) is configured via sqlnet.ora on the client.

PostgreSQL

PostgreSqlDatasourceConnection has no database-specific strongly-typed advanced properties beyond universal settings. Authentication mechanism is server-driven via pg_hba.conf; Npgsql auto-negotiates SSPI / GSSAPI when no credentials are supplied.

SQL Server

SqlServerDatasourceConnection exposes:

  • TrustServerCertificate - Bypass server certificate validation. Automatically disabled when SslMode is VerifyCA or VerifyFull
  • AuthenticationMethod - SqlServerAuthenticationMethod selector when UseIntegratedSecurity = true (Windows Integrated or any of seven Azure AD variants)

SQLite

SqliteDatasourceConnection has no database-specific strongly-typed advanced properties beyond universal settings. SQLite is file-based and has no network authentication.

Teradata

TeradataDatasourceConnection exposes:

  • SessionCharacterSet - Character set for session
  • AuthenticationMethod - TeradataAuthenticationMethod selector when UseIntegratedSecurity = true (LDAP, SPNEGO, TDNEGO, JWT, Bearer)

Context Settings

Context Settings are Velocity-specific settings that control how the framework operates, as opposed to AdvancedSettings which control the database connection itself. These settings affect Velocity's behavior when executing operations like imports, queries, and schema management.

How Context Settings Work

Context Settings are:

  • Framework-Level: Applied to Velocity operations, not the database connection
  • Operation-Specific: Used by Velocity commands (e.g., ImportTimeout for import operations, CommandTimeout for queries)
  • Cross-Database: Universal settings work the same across all database types
  • Type-Safe: Always exposed as strongly-typed properties for compile-time checking

Key Difference from Advanced Settings:

  • ConnectTimeout (Advanced Setting) = time to establish connection to database
  • CommandTimeout (Context Setting) = time for a database command to execute
  • ImportTimeout (Context Setting) = time for bulk import operations to complete

Setting Context Settings

Context settings can be configured in two ways:

1. Individual strongly-typed properties (Recommended):

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "mydb"
};

// Set context settings using strongly-typed properties
connection.CommandTimeout = 30;        // Default timeout for queries/commands
connection.ImportTimeout = 120;        // Timeout for bulk import operations
connection.ImportBatchSize = 10000;    // Rows per batch during imports
connection.DefaultSrid = 4326;         // Default spatial reference ID

2. Connection string format using SetContextSettings():

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "mydb"
};

// Set multiple context settings at once
connection.SetContextSettings("CommandTimeout=30;ImportTimeout=120;ImportBatchSize=10000;DefaultSrid=4326");

When using the connection string approach, context settings are passed as the third parameter to the Manager constructor:

string connectionString = "Data Source=localhost,1433;Initial Catalog=mydb;User ID=sa;Password=password";
string contextSettings = "CommandTimeout=30;ImportTimeout=120;ImportBatchSize=10000";

using (var manager = new Manager(DatasourceType.SQLServer, connectionString, contextSettings))
{
    // Operations will use these timeouts
    var schema = manager.LoadSchema("myschema");  // Uses CommandTimeout
    manager.Import(importConfig, dataTable);       // Uses ImportTimeout
}

Universal Context Settings

These settings are available on all DatasourceConnection classes and apply to all database types:

Property Type Default Description
CommandTimeout int 30 Timeout in seconds for database commands (queries, DDL, DML)
DefaultSrid int 4326 Default spatial reference identifier for geospatial operations
ImportTimeout int 120 Timeout in seconds for bulk import operations
ImportBatchSize int 10000 Number of rows per batch during bulk imports

Example:

connection.CommandTimeout = 60;        // Queries timeout after 60 seconds
connection.DefaultSrid = 3857;
connection.ImportTimeout = 180;        // Imports timeout after 180 seconds
connection.ImportBatchSize = 5000;

// Or using SetContextSettings
connection.SetContextSettings("CommandTimeout=60;DefaultSrid=3857;ImportTimeout=180;ImportBatchSize=5000");

Understanding Timeouts:

  • ConnectTimeout (AdvancedSetting): Maximum time to establish database connection
  • CommandTimeout (ContextSetting): Maximum time for queries/DDL/DML to execute
  • ImportTimeout (ContextSetting): Maximum time for bulk import operations (often needs to be higher than CommandTimeout)

Database-Specific ContextSettings

Each database connection class exposes its context settings as strongly-typed properties for maximum type safety and IntelliSense support.

DB2

DB2DatasourceConnection exposes:

Property Type Default Description
UpdateFrequency string "d(0,1,2,3,4,5,6) h(0,12) m(0)" Full-text index update frequency schedule
UpdateMinimum int 5 Minimum number of changes before full text index update

Example:

connection.UpdateFrequency = "d(0,1,2,3,4,5,6) h(0,12) m(0)";
connection.UpdateMinimum = 5;

// Or using SetContextSettings
connection.SetContextSettings("UpdateFrequency=d(0,1,2,3,4,5,6) h(0,12) m(0);UpdateMinimum=5");

MySQL

MySqlDatasourceConnection exposes:

Property Type Default Description
LengthUnit string "metre" Unit for spatial length calculations

Example:

connection.LengthUnit = "metre";

// Or using SetContextSettings
connection.SetContextSettings("LengthUnit=metre");

Oracle

OracleDatasourceConnection exposes:

Property Type Default Description
EscapeIdentifiers bool true Quote all identifiers (table/column names) in generated SQL. Set to false when Oracle should use unquoted identifiers (names are then stored and compared in uppercase).
Tablespace string "" Default tablespace for table creation
Tolerance float 0.01 Tolerance for spatial operations
UseBooleanDataType bool false Use Oracle 23c+ native boolean type (requires Oracle 23c+)
SslCaPath string "" Path to the PEM-encoded CA certificate used to verify the server's certificate chain
SslCertificatePath string "" Path to the PEM-encoded client certificate (mTLS only)
SslKeyPath string "" Path to the PEM-encoded client private key (mTLS only)
Note

EscapeIdentifiers is Oracle-only. All other databases always escape identifiers. When EscapeIdentifiers=true (the default), Velocity wraps every identifier in double quotes and Oracle preserves the declared case. When false, identifiers are unquoted and Oracle uppercases them, which means schema/table/column names with spaces or mixed case are not supported.

Important

Oracle SSL certificate paths are context settings, not advanced settings. ODP.NET has no connection-string parameters for certificate configuration, so SslCaPath, SslCertificatePath, and SslKeyPath cannot be passed via SetAdvancedSettings. When using the connection-string approach, pass them via the contextSettings parameter instead (e.g. "SslMode=VerifyCA;SslCaPath=/certs/ca.pem"). When using an OracleDatasourceConnection object directly, set them as properties as usual.

Example:

connection.EscapeIdentifiers = true;   // Default — preserves declared case
connection.Tablespace = "USERS";
connection.Tolerance = 0.01f;
connection.UseBooleanDataType = false;

// Or using SetContextSettings
connection.SetContextSettings("EscapeIdentifiers=true;Tablespace=USERS;Tolerance=0.01;UseBooleanDataType=false");

// SSL via context settings (connection-string approach only — see note above)
connection.SetContextSettings("SslCaPath=/certs/ca.pem;SslCertificatePath=/certs/client.crt;SslKeyPath=/certs/client.key");

PostgreSQL

PostgreSqlDatasourceConnection has no database-specific context settings beyond the universal settings.

All context settings for PostgreSQL are available through the universal ImportTimeout, ImportBatchSize, and DefaultSrid properties.

SQL Server

SqlServerDatasourceConnection has no database-specific context settings beyond the universal settings.

All context settings for SQL Server are available through the universal ImportTimeout, ImportBatchSize, and DefaultSrid properties.

SQLite

SqliteDatasourceConnection has no database-specific context settings beyond the universal settings.

All context settings for SQLite are available through the universal ImportTimeout, ImportBatchSize, and DefaultSrid properties.

Teradata

TeradataDatasourceConnection exposes:

Property Type Default Description
FullTextSearchClobs bool false Index CLOB columns in full-text indexes; if false, CLOBs are truncated at 32,000 characters (faster indexing)
FullTextSearchEnabled bool false Enable full-text index and search support
IdentityStartValue long 1 Starting value for identity columns
ImportCastClob bool true Cast Clob columns to VARCHAR(VarCharCastSize) in staging tables during import
ImportCastGeo bool true Cast Geometry/Geography columns to VARCHAR(VarCharCastSize) in staging tables during import
NumberOfStagingTables int 4 Number of parallel staging tables used during bulk import
PermanentSpace int 100000000 Permanent space allocation for newly created schemas/databases in bytes
SelectCastClob bool true Cast Clob columns to VARCHAR(VarCharCastSize) on SELECT (affects queries and exports)
SourceSpatialReference string (WGS 84 WKT) Source spatial reference for coordinate transformations
TargetSpatialReference string (NAD27 Albers WKT) Target spatial reference for coordinate transformations
VarCharCastSize int 32000 VARCHAR size used when casting Clob and Geometry/Geography columns

Example:

connection.FullTextSearchClobs = false;
connection.FullTextSearchEnabled = false;
connection.IdentityStartValue = 1;
connection.ImportCastClob = true;
connection.ImportCastGeo = true;
connection.NumberOfStagingTables = 4;
connection.PermanentSpace = 100000000;
connection.SelectCastClob = true;
connection.VarCharCastSize = 32000;

// Or using SetContextSettings
connection.SetContextSettings("FullTextSearchEnabled=false;NumberOfStagingTables=4;PermanentSpace=100000000;VarCharCastSize=32000");

Security: SSL/TLS and Authentication

Velocity provides a unified, cross-platform abstraction for SSL/TLS and enterprise authentication. The same set of strongly-typed properties — backed by plain PEM files for trust material — works identically on Windows, Linux, and macOS, and across every supported database. The same code and the same certificate files can be deployed unchanged across all supported platforms.

Design philosophy

Every database driver exposes SSL and authentication very differently:

  • DB2 uses IBM GSKit keystores (.kdb files)
  • Oracle uses Oracle wallets created with orapki
  • SQL Server uses the Windows Certificate Store on Windows
  • MySQL and PostgreSQL accept paths to PEM files
  • Teradata has its own TeraGSS configuration

Velocity wraps all of these into a single set of properties:

Property Purpose
SslMode Encryption level and certificate verification mode
SslCaPath PEM file containing the CA certificate used to verify the server's certificate
SslCertificatePath PEM file containing the client certificate for mutual TLS
SslKeyPath PEM file containing the client private key for mutual TLS
UseIntegratedSecurity Replace username/password with enterprise authentication
AuthenticationMethod Choose the specific mechanism when UseIntegratedSecurity = true (database-specific enum)

Velocity then translates these into the keystore/wallet/PEM/store-specific machinery that the underlying driver expects. The goal is that the same configuration produces the same security posture against any supported database, given the appropriate server-side setup.

Escape hatches: when the built-in abstraction isn't enough

The file-based abstraction covers the most common cases. For anything outside this — OS trust stores, GSKit keystores, Oracle wallets, custom Kerberos config, Kubernetes ConfigMap-mounted secrets, identity-provider-specific knobs — there are two escape hatches:

1. Use a full connection string. Velocity passes the connection string verbatim to the underlying driver, so any driver-specific key is available:

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Enums;

string connectionString = "Server=mysrv;Database=mydb;Integrated Security=SSPI;Encrypt=Strict;HostNameInCertificate=mysrv.internal";
string contextSettings = "CommandTimeout=60";

using (var manager = new Manager(DatasourceType.SQLServer, connectionString, contextSettings))
{
    // Driver receives the connection string unchanged
}

2. Use SetAdvancedSettings. Augment a DatasourceConnection with driver-specific keys that Velocity doesn't expose natively:

var conn = new DB2DatasourceConnection
{
    Hostname = "db2srv",
    Database = "MYDB",
    SslMode = SslMode.VerifyCA,
    SslCaPath = "./ca-cert.pem"
};

// Layer a GSKit client keystore on top of Velocity's PEM-based trust
conn.SetAdvancedSettings("SSLClientKeystoreDB=C:\\DB2SSL\\client.kdb;SSLClientKeystash=C:\\DB2SSL\\client.sth");

Outside Velocity's responsibility

Velocity sets connection-string properties on behalf of the driver. The runtime authentication and trust infrastructure must be configured externally, and that configuration is the responsibility of the implementer:

  • OS-level trust store maintenance (Windows Certificate Store, macOS Keychain, Linux CA bundle)
  • Kerberos TGT acquisition, KDC configuration, krb5.conf, kinit
  • Oracle wallet creation (orapki)
  • IBM GSKit client keystore creation (gsk8capicmd_64)
  • LDAP / Active Directory server configuration and user-to-identity mapping
  • Azure AD tenant configuration, app registrations, managed identities
  • Identity provider (OIDC / JWT issuer) registration and signing keys
  • TLS server certificate installation, renewal, and rotation on each database server

If any of those is misconfigured, Velocity will surface the underlying driver's exception unchanged — it does not try to wrap or recover from infrastructure errors.

SslMode support matrix

Mode MySQL PostgreSQL SQL Server Oracle DB2 Teradata SQLite
Disable ❌¹ ✅ ✅ ✅ ✅ ✅ N/A²
Prefer ✅ ✅ ❌ ❌³ ❌³ ✅ N/A²
Require ✅ ✅ ✅ ❌³ ❌³ ✅ N/A²
VerifyCA ✅ ✅ ✅ ✅ ✅ ✅ N/A²
VerifyFull ✅ ✅ ✅ ✅ ✅ ✅ N/A²

✅ = Supported. ❌ = Throws NotSupportedException at connection time.

¹ MySQL defaults to caching_sha2_password, which requires either an SSL connection or RSA key exchange to protect credentials. There is no plaintext path. Use Prefer or higher.

² SQLite is file-based — no network layer, so SSL does not apply.

³ Oracle's ODP.NET and IBM's GSKit always validate the server's certificate chain during the TLS handshake. There is no "encrypt without verification" mode, so Prefer and Require (both of which imply encrypt-without-verify) have no equivalent.

Mapping to driver-level settings

Velocity property SQL Server PostgreSQL MySQL DB2 Teradata Oracle
SslMode.Disable Encrypt=Optional sslmode=disable (rejected) (no Security=SSL) sslmode=DISABLE PROTOCOL=TCP
SslMode.Prefer (rejected) sslmode=prefer SslMode=Preferred (rejected) sslmode=PREFER (rejected)
SslMode.Require Encrypt=Mandatory + TrustServerCertificate=true sslmode=require SslMode=Required (rejected) sslmode=REQUIRE (rejected)
SslMode.VerifyCA Encrypt=Mandatory + TrustServerCertificate=false + Server Certificate=... sslmode=verify-ca + Root Certificate=... SslMode=VerifyCA + SslCa=... Security=SSL + SSLClientHostnameValidation=Off + SSLServerCertificate=... sslmode=VERIFY-CA + sslca=... PROTOCOL=TCPS + injected OraclePEM
SslMode.VerifyFull Encrypt=Strict + Server Certificate=... sslmode=verify-full + Root Certificate=... SslMode=VerifyFull + SslCa=... Security=SSL + SSLClientHostnameValidation=Basic + SSLServerCertificate=... sslmode=VERIFY-FULL + sslca=... PROTOCOL=TCPS + injected OraclePEM

Mutual TLS (client certificate authentication)

When supported, set SslCertificatePath and SslKeyPath to PEM files on the client. The server must trust the CA that signed the client certificate and must have a database user whose external identity matches the certificate's subject DN.

Database Supported Driver mechanism Server-side user mapping
DB2 ❌ Net.IBM.Data.Db2 does not expose client cert parameters —
MySQL ✅ SslCert + SslKey connection string keys REQUIRE X509 clause on the user
Oracle ✅ ODP.NET OraclePEM API (injected at connection time) IDENTIFIED EXTERNALLY AS 'CN=...'
PostgreSQL ✅ SSL Certificate + SSL Key (Npgsql) cert method in pg_hba.conf
SQL Server ❌ SqlClient does not support client certificate authentication —
SQLite N/A — —
Teradata ❌ Teradata .NET driver has no SslCert/SslKey properties —

Example: PostgreSQL with mutual TLS

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "postgres.example.com",
    Port = 5432,
    Username = "yndigo_mtls",
    // No Password — the cert is the credential
    Database = "mydb",
    SslMode = SslMode.VerifyCA,
    SslCaPath = "./certs/ca-cert.pem",
    SslCertificatePath = "./certs/client-cert.pem",
    SslKeyPath = "./certs/client-key.pem",
};

Integrated Security and Authentication Methods

Setting UseIntegratedSecurity = true switches off username/password authentication. AuthenticationMethod then selects the specific mechanism — it is a database-specific enum, so each adaptor exposes only the values that its driver actually supports.

Database UseIntegratedSecurity AuthenticationMethod enum Values
DB2 ✅ DB2AuthenticationMethod Client, Kerberos, KerberosServerEncrypt, GssPlugin, GssServerEncrypt
MySQL ✅¹ — Server-driven. The plugin is negotiated automatically from the user account's IDENTIFIED WITH clause; no client-side mechanism selector is needed.
Oracle ✅ — Single mechanism (User ID=/). The actual provider (OS auth, Kerberos, RADIUS) is configured in sqlnet.ora, not in the connection string.
PostgreSQL ✅ — Server-driven via pg_hba.conf. Npgsql auto-negotiates SSPI on Windows or GSSAPI/Kerberos on Linux when no credentials are supplied.
SQL Server ✅ SqlServerAuthenticationMethod WindowsIntegrated (SSPI) + 7 Azure AD variants
SQLite N/A — —
Teradata ✅ TeradataAuthenticationMethod Ldap, Spnego, Tdnego, Jwt, Bearer

¹ MySQL Enterprise Edition only. Community Edition has no OS-level authentication plugins. The authentication mechanism is determined entirely by the server based on the user account's IDENTIFIED WITH configuration — there is no client-side enum selector.

Example: SQL Server with Azure Managed Identity

var connection = new SqlServerDatasourceConnection
{
    Hostname = "myserver.database.windows.net",
    Database = "mydb",
    UseIntegratedSecurity = true,
    AuthenticationMethod = SqlServerAuthenticationMethod.ActiveDirectoryManagedIdentity,
    SslMode = SslMode.VerifyFull
};

Example: DB2 with Kerberos

var connection = new DB2DatasourceConnection
{
    Hostname = "db2.example.com",
    Database = "SAMPLE",
    UseIntegratedSecurity = true,
    AuthenticationMethod = DB2AuthenticationMethod.Kerberos
};
// A valid Kerberos TGT must already exist on the client machine
// (run `kinit user@REALM` first, or join the machine to the AD domain).

Example: Teradata with LDAP

var connection = new TeradataDatasourceConnection
{
    Hostname = "teradata.example.com",
    Database = "MYDB",
    UseIntegratedSecurity = true,
    AuthenticationMethod = TeradataAuthenticationMethod.Ldap
};
// The Teradata Gateway must be configured for LDAP, and the database user
// must be mapped to an LDAP identity:
//   MODIFY USER dbuser AS EXTERNAL SECURITY DOMAIN LdapDomain;

Example: PostgreSQL with integrated security

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "postgres.example.com",
    Database = "mydb",
    UseIntegratedSecurity = true
    // No AuthenticationMethod — Npgsql negotiates SSPI/GSSAPI automatically
    // based on the OS and pg_hba.conf.
};

Example: Oracle with OS authentication

var connection = new OracleDatasourceConnection
{
    Hostname = "oracle.example.com",
    Database = "ORCL",
    UseIntegratedSecurity = true
    // The DB user must be created with IDENTIFIED EXTERNALLY, and the OS user
    // must match (subject to OS_AUTHENT_PREFIX), or Kerberos must be configured
    // in sqlnet.ora on both client and server.
};

Round-tripping connection strings

The full connection-string overload of Manager participates in the same SSL/auth model. When you pass a connection string in, Velocity parses it back into the strongly-typed properties on a DatasourceConnection:

  • Encrypt, TrustServerCertificate, Server Certificate → SslMode + SslCaPath
  • sslmode, Root Certificate, SSL Certificate, SSL Key → SslMode + SslCaPath/SslCertificatePath/SslKeyPath
  • SslMode, SslCa, SslCert, SslKey → ditto
  • Security=SSL + SSLClientHostnameValidation + SSLServerCertificate → ditto
  • Integrated Security=SSPI or Authentication=... → UseIntegratedSecurity = true + corresponding AuthenticationMethod
  • Oracle User ID=/ → UseIntegratedSecurity = true
  • Teradata IntegratedSecurity=true or AuthenticationMechanism=... → UseIntegratedSecurity = true + corresponding AuthenticationMethod

This means the two approaches (DatasourceConnection vs connection string) are interchangeable for SSL and authentication — the same configuration is reachable from either side.

Connection Management Best Practices

1. Always Use Using Statements

Manager objects should always be wrapped in using statements to ensure proper disposal of database connections:

using (var manager = new Manager(connection))
{
    // Perform operations
    var schema = manager.LoadSchema("myschema");
} // Connection automatically closed and disposed

2. Store Connection Parameters Securely

Never hardcode credentials in source code. Use configuration files, environment variables, or secure credential stores:

using Microsoft.Extensions.Configuration;

// Load from configuration
var config = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .AddEnvironmentVariables()
    .Build();

var connection = new SqlServerDatasourceConnection
{
    Hostname = config["Database:Hostname"],
    Port = int.Parse(config["Database:Port"]),
    Username = config["Database:Username"],
    Password = config["Database:Password"],
    Database = config["Database:Database"]
};

appsettings.json:

{
  "Database": {
    "Hostname": "localhost",
    "Port": "1433",
    "Username": "myuser",
    "Password": "mypassword",
    "Database": "mydb"
  }
}

3. Configure Appropriate Timeouts

Set timeouts based on your operational requirements. All connection types support CommandTimeout and ImportTimeout:

// Development environment - shorter timeouts for faster feedback
connection.ConnectTimeout = 5;      // AdvancedSetting: quick connection timeout
connection.CommandTimeout = 30;     // ContextSetting: standard query timeout
connection.ImportTimeout = 120;     // ContextSetting: moderate import timeout
connection.ImportBatchSize = 1000;  // Smaller batches for development

// Production environment - longer timeouts for reliability
connection.ConnectTimeout = 15;     // AdvancedSetting: allow more time for connection
connection.CommandTimeout = 60;     // ContextSetting: longer query timeout
connection.ImportTimeout = 300;     // ContextSetting: extended import timeout
connection.ImportBatchSize = 10000; // Larger batches for performance

Timeout Guidelines:

  • ConnectTimeout (AdvancedSetting): Usually 5-15 seconds
  • CommandTimeout (ContextSetting): 30-60 seconds for most queries
  • ImportTimeout (ContextSetting): 120-300+ seconds depending on data volume

4. Use Context Settings for Environment-Specific Behavior

Separate connection parameters from operational settings:

// Development environment
connection.ImportBatchSize = 1000;
connection.ImportTimeout = 60;

// Production environment
connection.ImportBatchSize = 10000;
connection.ImportTimeout = 300;

5. Test Connections Before Operations

Use the GetDatabaseInfo method on Manager to verify database connectivity:

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "postgres.example.com",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "mydb"
};

try
{
    using (var manager = new Manager(connection))
    {
        var info = manager.GetDatabaseInfo();

        Console.WriteLine($"Vendor: {info.Vendor}");
        Console.WriteLine($"Name: {info.Name}");
        Console.WriteLine($"Edition: {info.Edition}");
        Console.WriteLine($"Version: {info.MajorVersion}.{info.MinorVersion}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Connection failed: {ex.Message}");
}

6. Prefer Strongly-Typed Properties Over String Parsing

When possible, use the strongly-typed properties for better type safety and compile-time checking. Universal settings like ImportTimeout and ImportBatchSize are available on all connection types:

// Preferred: Type-safe, compile-time checked
connection.ImportTimeout = 120;
connection.ImportBatchSize = 10000;
connection.DefaultSrid = 4326;

// Alternative: Useful when loading from configuration
connection.SetContextSettings("ImportTimeout=120;ImportBatchSize=10000;DefaultSrid=4326");

Disconnected Operations

For operations that don't require a database connection (reading and writing Schema files using ReadSchemaFromFile and WriteSchema), use the parameterless Manager constructor:

using (var manager = new Manager())
{
    // Load schema from file without connecting to database
    Schema schema = manager.ReadSchemaFromFile("schema.json", ConfigType.Json);

    // Modify schema programmatically
    schema.CreateTable("new_table").AddColumn("id", DataType.Long);

    // Write back to file
    manager.WriteSchema(schema, "modified_schema.json", ConfigType.Json);
}

Note: Attempting to perform connected operations (like LoadSchema, LoadDatabase, etc.) with a disconnected Manager will throw an exception.

Connection String vs DatasourceConnection Tradeoffs

Use DatasourceConnection When:

  • Building new applications
  • You want type safety and IntelliSense
  • Connection parameters are constructed programmatically
  • You need clear, self-documenting code
  • Advanced validation is beneficial

Use Connection Strings When:

  • Integrating with existing systems
  • Connection strings are managed externally
  • Working with configuration management systems
  • You need maximum flexibility in connection parameters
  • Migrating from legacy ADO.NET code

Both approaches are fully supported and provide identical functionality—choose based on your application architecture and requirements.

Summary

Velocity provides flexible connection mechanisms to suit different application architectures:

  1. DatasourceConnection objects offer type safety with strongly-typed properties (recommended for new development)
  2. Connection strings provide compatibility with existing configuration systems
  3. AdvancedSettings control ADO.NET provider behavior (passed to database connection string):
    • Set via SetAdvancedSettings() method
    • Map to database-specific connection string properties
    • Reserved settings (Pooling, ConnectTimeout, etc.) use named properties
    • Cannot override reserved settings via SetAdvancedSettings()
    • Some databases expose common settings as strongly-typed properties
  4. ContextSettings configure Velocity framework behavior (used by Velocity operations):
    • Set via strongly-typed properties or SetContextSettings() method
    • Control how Velocity executes operations (CommandTimeout, ImportTimeout, etc.)
    • Universal settings work across all database types
    • Applied to Velocity commands, not the connection itself
  5. Understand timeout types:
    • ConnectTimeout (AdvancedSetting) = time to establish connection
    • CommandTimeout (ContextSetting) = time for queries/DDL/DML
    • ImportTimeout (ContextSetting) = time for bulk imports
  6. SSL/TLS and authentication (see Security: SSL/TLS and Authentication):
    • Cross-platform PEM-file abstraction via SslMode, SslCaPath, SslCertificatePath, SslKeyPath
    • Enterprise authentication via UseIntegratedSecurity + a database-specific AuthenticationMethod enum (DB2, SQL Server, Teradata)
    • For anything outside this model, drop down to a full connection string or SetAdvancedSettings
    • OS trust stores, Kerberos infrastructure, Oracle wallets, GSKit keystores, and identity provider configuration are the implementer's responsibility, not Velocity's
  7. Always use using statements for proper resource management
  8. Store credentials securely outside source code
  9. Configure timeouts and batch sizes appropriate to your environment
  10. Prefer strongly-typed properties over string parsing when possible

By understanding the distinction between AdvancedSettings (connection behavior) and ContextSettings (framework behavior), you can build robust, secure, and performant database applications with Velocity.