Data Manipulation Language (DML)

This guide covers the Create, Update, and Delete operations in Velocity. For querying and retrieval operations, see the Query documentation.

Overview

Velocity provides several methods for manipulating data in your database:

• AddRecord - Insert a single record and return the auto-generated identity value
• AddRecordNoIdentity - Insert a single record without returning an identity (faster for tables without auto-generated keys)
• AddRecords - Insert multiple records from a subquery (INSERT INTO ... SELECT FROM), returning the number of rows written
• UpdateRecords - Update existing records based on filter criteria, returning the number of rows modified
• DeleteRecords - Delete records based on filter criteria, returning the number of rows removed

The three set-based operations return an affected-row count. That count is not just informational — it is what allows a caller to tell a real change apart from a no-op, which is the basis of the compare-and-swap pattern described below.

Every DML method has an async equivalent:

Sync	Async	Returns
AddRecord(record)	AddRecordAsync(record, ct)	Task<long> (identity)
AddRecordNoIdentity(record)	AddRecordNoIdentityAsync(record, ct)	Task
AddRecords(table, columns, query)	AddRecordsAsync(table, columns, query, ct)	Task<int> (rows written)
UpdateRecords(update)	UpdateRecordsAsync(update, ct)	Task<int> (rows modified)
DeleteRecords(delete)	DeleteRecordsAsync(delete, ct)	Task<int> (rows removed)

All DML operations are performed through the Manager class, which must be initialized with a database connection.

---

Creating Records

For full documentation on building records, setting field values, using subqueries and functions as values, and data binding, see Working with Records.

The three insert methods are:

• AddRecord — inserts a record and returns the auto-generated identity value as long
• AddRecordNoIdentity — inserts a record without returning an identity; use this for tables without auto-generated keys or when the identity is not needed
• AddRecords — bulk-inserts rows from a subquery (INSERT INTO ... SELECT)

AddRecordNoIdentity

Use AddRecordNoIdentity when you don't need the auto-generated identity value. This is more efficient for bulk operations or when working with tables that don't have auto-generated keys.

using (Manager manager = new Manager(connection))
{
    Table logs = schema["Logs"];

    Record record = new Record(logs);
    record.SetFieldVarChar("Message", "User logged in");
    record.SetFieldDateTime("Timestamp", DateTime.Now);
    record.SetFieldInteger("UserId", 42);

    manager.AddRecordNoIdentity(record);
}

AddRecords (INSERT INTO ... SELECT)

The AddRecords method inserts multiple records into a table using the results from a subquery. This is equivalent to SQL's INSERT INTO ... SELECT statement.

Basic Example

using (Manager manager = new Manager(connection))
{
    Table archive = schema["OrdersArchive"];
    Table orders = schema["Orders"];

    // Create query to select orders older than 1 year
    Query query = new Query()
        .Select([
            orders["OrderID"],
            orders["CustomerID"],
            orders["OrderDate"],
            orders["Total"]
        ])
        .From(orders)
        .Where(new Criterion<DateTime>(orders["OrderDate"],
            ConditionalType.LessThan,
            DateTime.Now.AddYears(-1)));

    // Specify target columns (must match query select items in order)
    List<Column> targetColumns = new List<Column>
    {
        archive["OrderID"],
        archive["CustomerID"],
        archive["OrderDate"],
        archive["Total"]
    };

    // Insert matching records
    int rowsArchived = manager.AddRecords(archive, targetColumns, query);
    Console.WriteLine($"Archived {rowsArchived} orders");
}

Using Expressions in INSERT SELECT

You can use expressions to transform data during insertion:

Query query = new Query()
    .Select([
        sourceTable["IntegerColumn"],
        sourceTable["FloatColumn"],
        // Add a literal value for all inserted records
        new Expression("Status", new Literal<string>("Migrated"))
    ])
    .From(sourceTable);

List<Column> columns = new List<Column>
{
    targetTable["IntegerColumn"],
    targetTable["FloatColumn"],
    targetTable["Status"]
};

manager.AddRecords(targetTable, columns, query);

---

Data Binding for Inserts

For full documentation on binding strategies (Attribute, CamelCase, DirectMap, PascalCase, SnakeCase) and typed record examples, see Working with Records.

---

Updating Records

The UpdateRecords method modifies existing records that match specified filter criteria.

Basic Update

using (Manager manager = new Manager(connection))
{
    Table users = schema["Users"];

    Update update = new Update(users,
        new Criterion<DateTime>(users["LastLogin"],
            ConditionalType.LessThan,
            DateTime.Now.AddDays(-90)));
    update.SetFieldBoolean("IsActive", false);
    update.SetFieldDateTime("LastModified", DateTime.Now);

    manager.UpdateRecords(update);
}

Update with Multiple Conditions

Table products = schema["Products"];

// Update products that are low stock AND in a specific category
Update update = new Update(products, [
    new Criterion<int>(products["StockQuantity"], ConditionalType.LessThan, 10),
    new BooleanItem(BooleanType.And),
    new Criterion<string>(products["Category"], ConditionalType.Equals, "Electronics")
]);
update.SetFieldDecimal("Price", 9.99M);
update.SetFieldBoolean("OnSale", true);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

Update Using an Expression as Value

SetFieldAsExpression derives the new value from an arithmetic expression, which may reference the row's own current column values. The database performs the arithmetic, so the value is never round-tripped through your application.

Table accounts = schema["Accounts"];

// balance = balance - 25.00
Expression debit = new Expression([
    accounts["Balance"],
    new ArithmeticOperator(ArithmeticType.Subtract),
    new Literal<decimal>(25.00M)
]);

Update update = new Update(accounts,
    new Criterion<long>(accounts["AccountID"], ConditionalType.Equals, 1234));
update.SetFieldAsExpression("Balance", debit);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

// SQL: UPDATE Accounts SET Balance = (Balance - @p0) WHERE AccountID = @p1

Literals inside an expression are parameterized, exactly as they are elsewhere in Velocity — nothing is inlined into the SQL text.

Expressions compose columns, literals, ArithmeticOperators and functions. To set one column from another, an expression of a single column is enough:

// LastKnownGood = CurrentValue
update.SetFieldAsExpression("LastKnownGood", new Expression(readings["CurrentValue"]));

Atomic read-modify-write (compare-and-swap)

Reading a value, changing it in application code, and writing it back is subject to a lost-update race: two concurrent callers both read the same starting value, and the second write silently discards the first. Combining an expression with a guard condition in the WHERE clause and the returned row count removes that race entirely, in a single statement and without needing a serializable transaction.

decimal amount = 25.00M;

// The new value is computed by the database from the current one.
Expression debit = new Expression([
    accounts["Balance"],
    new ArithmeticOperator(ArithmeticType.Subtract),
    new Literal<decimal>(amount)
]);

// The guard makes the statement a no-op unless the funds are still there.
Update update = new Update(accounts, [
    new Criterion<long>(accounts["AccountID"], ConditionalType.Equals, 1234),
    new BooleanItem(BooleanType.And),
    new Criterion<decimal>(accounts["Balance"], ConditionalType.GreaterThanOrEqualTo, amount)
]);
update.SetFieldAsExpression("Balance", debit);

using (Manager manager = new Manager(connection))
{
    int rowsUpdated = manager.UpdateRecords(update);

    if (rowsUpdated == 0)
    {
        // Insufficient balance — the guard failed and nothing was written.
        // No partial state to unwind.
    }
}

// SQL: UPDATE Accounts SET Balance = (Balance - @p0)
//      WHERE AccountID = @p1 AND Balance >= @p2

Because the read, the comparison and the write are one statement, no other caller can interleave between them. A return of 0 is a definitive "the precondition was false and nothing changed."

The same shape works for optimistic concurrency using a version column — guard on the version you read, increment it in the same statement, and treat 0 as "someone else modified this row first":

Expression nextVersion = new Expression([
    documents["Version"],
    new ArithmeticOperator(ArithmeticType.Add),
    new Literal<int>(1)
]);

Update update = new Update(documents, [
    new Criterion<long>(documents["DocumentID"], ConditionalType.Equals, docId),
    new BooleanItem(BooleanType.And),
    new Criterion<int>(documents["Version"], ConditionalType.Equals, versionWhenRead)
]);
update.SetFieldVarChar("Body", editedBody);
update.SetFieldAsExpression("Version", nextVersion);

if (manager.UpdateRecords(update) == 0)
{
    // Stale write — reload and retry, or surface a conflict to the user.
}

Update Using Subquery as Value

Set a column's value based on a subquery result:

Table orders = schema["Orders"];
Table customers = schema["Customers"];

// Subquery to get customer's preferred shipping method
Query shippingQuery = new Query()
    .Select([customers["PreferredShipping"]])
    .From(customers)
    .Where(new Criterion<int>(customers["CustomerID"], ConditionalType.Equals, 42));

Update update = new Update(orders,
    new Criterion<int>(orders["CustomerID"], ConditionalType.Equals, 42));
update.SetFieldAsQuery("ShippingMethod", shippingQuery);
update.SetFieldDateTime("UpdatedAt", DateTime.Now);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

Update with Subquery in WHERE Clause

Use a subquery as part of the filter criteria:

Table products = schema["Products"];
Table orders = schema["Orders"];

// Subquery to find products ordered in the last 30 days
Query recentOrdersQuery = new Query()
    .Select([orders["ProductID"]])
    .From(orders)
    .Where(new Criterion<DateTime>(orders["OrderDate"],
        ConditionalType.GreaterThan,
        DateTime.Now.AddDays(-30)));

Update update = new Update(products,
    new Criterion<Query>(products["ProductID"], ConditionalType.In, recentOrdersQuery));
update.SetFieldBoolean("RecentlyOrdered", true);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

Update Using BETWEEN

Table inventory = schema["Inventory"];

// Update items with quantity between 5 and 20
Update update = new Update(inventory,
    new Criterion<int>(inventory["Quantity"],
        ConditionalType.Between,
        new List<int> { 5, 20 }));
update.SetFieldVarChar("Status", "Reorder Required");

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

Update Using NOT IN

Table users = schema["Users"];

// Don't update users with specific roles
Update update = new Update(users,
    new Criterion<string>(users["Role"],
        ConditionalType.NotIn,
        new List<string> { "Admin", "Moderator", "System" }));
update.SetFieldBoolean("SendNotifications", false);

using (Manager manager = new Manager(connection))
{
    manager.UpdateRecords(update);
}

---

Deleting Records

The DeleteRecords method removes records that match specified filter criteria.

Warning: Always include a WHERE clause when deleting records. To remove all records from a table, use manager.EmptyTable(table) instead, which makes the intent explicit.

Basic Delete

using (Manager manager = new Manager(connection))
{
    Table sessions = schema["Sessions"];

    // Delete expired sessions
    Delete delete = new Delete(sessions,
        new Criterion<DateTime>(sessions["ExpiresAt"],
            ConditionalType.LessThan,
            DateTime.Now));

    int rowsDeleted = manager.DeleteRecords(delete);
    Console.WriteLine($"Removed {rowsDeleted} expired sessions");
}

Delete with Multiple Conditions

Table logs = schema["Logs"];

// Delete old info-level logs (keep errors and warnings longer)
Delete delete = new Delete(logs, [
    new Criterion<DateTime>(logs["CreatedAt"],
        ConditionalType.LessThan,
        DateTime.Now.AddMonths(-6)),
    new BooleanItem(BooleanType.And),
    new Criterion<string>(logs["Level"], ConditionalType.Equals, "Info")
]);

using (Manager manager = new Manager(connection))
{
    manager.DeleteRecords(delete);
}

Delete Using Subquery

Delete records based on a subquery condition:

Table users = schema["Users"];
Table orders = schema["Orders"];

// Subquery to find users who have placed an order in the last year
Query activeUsersQuery = new Query()
    .Select([orders["UserID"]])
    .From(orders)
    .Where(new Criterion<DateTime>(orders["OrderDate"],
        ConditionalType.GreaterThan,
        DateTime.Now.AddYears(-1)));

// Delete users NOT in the active users list
Delete delete = new Delete(users,
    new Criterion<Query>(users["UserID"],
        ConditionalType.NotIn,
        activeUsersQuery));

using (Manager manager = new Manager(connection))
{
    manager.DeleteRecords(delete);
}

Delete by ID

Table products = schema["Products"];

Delete delete = new Delete(products,
    new Criterion<int>(products["ProductID"], ConditionalType.Equals, 12345));

using (Manager manager = new Manager(connection))
{
    if (manager.DeleteRecords(delete) == 0)
    {
        // The product was already gone — nothing was removed.
    }
}

Empty Table

To delete all records from a table (equivalent to TRUNCATE in some databases):

using (Manager manager = new Manager(connection))
{
    Table tempData = schema["TempData"];
    manager.EmptyTable(tempData);
}

---

Complete Example

Here's a comprehensive example showing Create, Update, and Delete operations together:

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

// Load schema
Schema schema;
using (Manager manager = new Manager())
{
    schema = manager.ReadSchemaFromFile("database_schema.xml", ConfigType.Xml);
}

// Create connection
var connection = new PostgreSqlDatasourceConnection(
    hostname: "localhost",
    port: 5432,
    database: "myapp",
    username: "appuser",
    password: "secret123"
);

using (Manager manager = new Manager(connection))
{
    Table users = schema["users"];
    Table orders = schema["orders"];

    // CREATE: Insert a new user using binding
    var userRecord = new Record<User>(users, BindingType.SnakeCase);
    userRecord.Bind(new User
    {
        Username = "jane.doe",
        Email = "jane@example.com",
        FirstName = "Jane",
        LastName = "Doe",
        CreatedAt = DateTime.Now,
        IsActive = true
    });

    long userId = manager.AddRecord(userRecord);
    Console.WriteLine($"Created user ID: {userId}");

    // CREATE: Insert an order manually
    Record orderRecord = new Record(orders);
    orderRecord.SetFieldLong("UserID", userId);
    orderRecord.SetFieldDateTime("OrderDate", DateTime.Now);
    orderRecord.SetFieldDecimal("Total", 149.99M);
    orderRecord.SetFieldVarChar("Status", "Pending");

    long orderId = manager.AddRecord(orderRecord);

    // UPDATE: Mark the order as shipped
    Update updateOrder = new Update(orders,
        new Criterion<long>(orders["OrderID"], ConditionalType.Equals, orderId));
    updateOrder.SetFieldVarChar("Status", "Shipped");
    updateOrder.SetFieldDateTime("ShippedDate", DateTime.Now);

    manager.UpdateRecords(updateOrder);

    // UPDATE: Deactivate users with no orders
    Query noOrdersQuery = new Query()
        .Select([orders["UserID"]])
        .From(orders);

    Update deactivateUsers = new Update(users,
        new Criterion<Query>(users["UserID"], ConditionalType.NotIn, noOrdersQuery));
    deactivateUsers.SetFieldBoolean("IsActive", false);

    manager.UpdateRecords(deactivateUsers);

    // DELETE: Remove cancelled orders older than 90 days
    Delete deleteCancelled = new Delete(orders, [
        new Criterion<string>(orders["Status"], ConditionalType.Equals, "Cancelled"),
        new BooleanItem(BooleanType.And),
        new Criterion<DateTime>(orders["OrderDate"],
            ConditionalType.LessThan,
            DateTime.Now.AddDays(-90))
    ]);

    manager.DeleteRecords(deleteCancelled);
}

---

Filter Operators

Velocity supports the following conditional operators for WHERE clauses:

Operator	Description	Example
Between	Value between two values (inclusive)	ConditionalType.Between
Equals	Equal to	ConditionalType.Equals
GreaterThan	Greater than	ConditionalType.GreaterThan
GreaterThanOrEqualTo	Greater than or equal	ConditionalType.GreaterThanOrEqualTo
In	Value in list	ConditionalType.In
IsNotNull	Value is not NULL	ConditionalType.IsNotNull
IsNull	Value is NULL	ConditionalType.IsNull
LessThan	Less than	ConditionalType.LessThan
LessThanOrEqualTo	Less than or equal	ConditionalType.LessThanOrEqualTo
Like	Pattern matching	ConditionalType.Like
NotEquals	Not equal to	ConditionalType.NotEquals
NotIn	Value not in list	ConditionalType.NotIn

---

Best Practices

1. Always use using statements - The Manager class manages database connections and should always be disposed properly

2. Use typed methods - Use SetFieldInteger(), SetFieldVarChar(), etc. instead of generic methods for type safety

3. Use binding for complex objects - For objects with many properties, use Record<T> with binding instead of manually setting each field

4. Include WHERE clauses for safety - Always specify WHERE clauses when updating or deleting to avoid accidentally modifying all records

5. Use transactions for related operations - When performing multiple DML operations that should succeed or fail together, use database transactions

6. Use AddRecordNoIdentity for bulk inserts - When inserting many records and you don't need the identity values, use AddRecordNoIdentity for better performance

7. Prefer AddRecords for bulk data - When copying or transforming data from one table to another, use AddRecords with a query instead of looping with AddRecord

8. Check the affected-row count when the outcome matters - A return of 0 from UpdateRecords or DeleteRecords means no row satisfied the WHERE clause. Silently ignoring it turns a failed precondition into an apparent success

9. Modify values in place with expressions, not read-then-write - For counters, balances and any other value derived from its own current state, use SetFieldAsExpression with a guard condition rather than reading the value into application code and writing it back. See compare-and-swap

---

Async DML

Every DML method has an async variant. Use them in web/API hosts and async pipelines to release the calling thread during the database round-trip.

AddRecordAsync

using (Manager manager = new Manager(connection))
{
    Table users = schema["users"];

    // Typed binding
    var record = new Record<User>(users, BindingType.SnakeCase);
    record.Bind(new User
    {
        Username  = "jane_doe",
        Email     = "jane@example.com",
        CreatedAt = DateTime.UtcNow,
        IsActive  = true
    });

    long userId = await manager.AddRecordAsync(record);
    Console.WriteLine($"Created user ID: {userId}");
}

AddRecordNoIdentityAsync

using (Manager manager = new Manager(connection))
{
    Table logs = schema["Logs"];

    Record record = new Record(logs);
    record.SetFieldVarChar("Message", "Order dispatched");
    record.SetFieldDateTime("Timestamp", DateTime.UtcNow);
    record.SetFieldInteger("OrderId", 99);

    await manager.AddRecordNoIdentityAsync(record);
}

Async bulk insert

using (Manager manager = new Manager(connection))
{
    Table products = schema["products"];

    foreach (var product in incomingProducts)
    {
        var record = new Record<Product>(products, BindingType.SnakeCase);
        record.Bind(product);
        await manager.AddRecordAsync(record);
    }
}

AddRecordsAsync (INSERT INTO … SELECT)

using (Manager manager = new Manager(connection))
{
    Table archive = schema["OrdersArchive"];
    Table orders  = schema["Orders"];

    Query query = new Query()
        .Select([orders["OrderID"], orders["CustomerID"], orders["OrderDate"], orders["Total"]])
        .From(orders)
        .Where(new Criterion<DateTime>(orders["OrderDate"],
            ConditionalType.LessThan,
            DateTime.Now.AddYears(-1)));

    List<Column> targetColumns = [
        archive["OrderID"], archive["CustomerID"], archive["OrderDate"], archive["Total"]
    ];

    int rowsArchived = await manager.AddRecordsAsync(archive, targetColumns, query);
}

UpdateRecordsAsync

using (Manager manager = new Manager(connection))
{
    Table users = schema["Users"];

    Update update = new Update(users,
        new Criterion<DateTime>(users["LastLogin"],
            ConditionalType.LessThan,
            DateTime.Now.AddDays(-90)));
    update.SetFieldBoolean("IsActive", false);
    update.SetFieldDateTime("LastModified", DateTime.Now);

    int rowsUpdated = await manager.UpdateRecordsAsync(update);
}

DeleteRecordsAsync

using (Manager manager = new Manager(connection))
{
    Table sessions = schema["Sessions"];

    Delete delete = new Delete(sessions,
        new Criterion<DateTime>(sessions["ExpiresAt"],
            ConditionalType.LessThan,
            DateTime.Now));

    int rowsDeleted = await manager.DeleteRecordsAsync(delete);
}

---

Next Steps

• Learn about querying data
• Explore schema management
• Understand data types and conversions