User-Defined Functions in SQLite: Enhancing SQL with Custom C# Procedures

by Joche Ojeda | Jan 15, 2024 | ADO.NET, C#, Database, Sqlite

SQLite, known for its simplicity and lightweight architecture, offers unique opportunities for developers to integrate custom functions directly into their applications. Unlike most databases that require learning an SQL dialect for procedural programming, SQLite operates in-process with your application. This design choice allows developers to define functions using their application’s programming language, enhancing the database’s flexibility and functionality.

Scalar Functions

Scalar functions in SQLite are designed to return a single value for each row in a query. Developers can define new scalar functions or override built-in ones using the CreateFunction method. This method supports various data types for parameters and return types, ensuring versatility in function creation. Developers can specify the state argument to pass a consistent value across all function invocations, avoiding the need for closures. Additionally, marking a function as isDeterministic optimizes query compilation by SQLite if the function’s output is predictable based on its input.

Example: Adding a Scalar Function

connection.CreateFunction(
    "volume",
    (double radius, double height) => Math.PI * Math.Pow(radius, 2) * height);

var command = connection.CreateCommand();
command.CommandText = @"
    SELECT name,
           volume(radius, height) AS volume
    FROM cylinder
    ORDER BY volume DESC
";
        

Operators

SQLite implements several operators as scalar functions. Defining these functions in your application overrides the default behavior of these operators. For example, functions like glob, like, and regexp can be custom-defined to change the behavior of their corresponding operators in SQL queries.

Example: Defining the regexp Function

connection.CreateFunction(
    "regexp",
    (string pattern, string input) => Regex.IsMatch(input, pattern));

var command = connection.CreateCommand();
command.CommandText = @"
    SELECT count()
    FROM user
    WHERE bio REGEXP '\w\. {2,}\w'
";
var count = command.ExecuteScalar();
        

Aggregate Functions

Aggregate functions return a consolidated value from multiple rows. Using CreateAggregate, developers can define and override these functions. The seed argument sets the initial context state, and the func argument is executed for each row. The resultSelector parameter, if specified, calculates the final result from the context after processing all rows.

Example: Creating an Aggregate Function for Standard Deviation

connection.CreateAggregate(
    "stdev",
    (Count: 0, Sum: 0.0, SumOfSquares: 0.0),
    ((int Count, double Sum, double SumOfSquares) context, double value) => {
        context.Count++;
        context.Sum += value;
        context.SumOfSquares += value * value;
        return context;
    },
    context => {
        var variance = context.SumOfSquares - context.Sum * context.Sum / context.Count;
        return Math.Sqrt(variance / context.Count);
    });

var command = connection.CreateCommand
();
command.CommandText = @"
SELECT stdev(gpa)
FROM student
";
var stdDev = command.ExecuteScalar();

Errors

When a user-defined function throws an exception in SQLite, the message is returned to the database engine, which then raises an error. Developers can customize the SQLite error code by throwing a SqliteException with a specific SqliteErrorCode.

Debugging

SQLite directly invokes the implementation of user-defined functions, allowing developers to insert breakpoints and leverage the full .NET debugging experience. This integration facilitates debugging and enhances the development of robust, error-free custom functions.

This article illustrates the power and flexibility of SQLite’s approach to user-defined functions, demonstrating how developers can extend the functionality of SQL with the programming language of their application, thereby streamlining the development process and enhancing database interaction.

Github Repo