dotConnect for SQLite provides support for creating and working with the FLOAT32 vector data type.

This article shows how to create vector tables, insert and retrieve FLOAT32 vector data, and perform operations such as equality checks, parsing, and similarity search.

Prerequisites

SQLite vector functionality is available only through virtual tables provided by the sqlite-vec extension. To work with vectors, you must install the sqlite-vec NuGet package (with prerelease enabled) and load the vec0.dll library into your project.

To obtain vec0.dll, navigate to the sqlite-vec releases page on GitHub. In the Assets section, download and extract the archive corresponding to your operating system.

You can load the obtained vec0.dll into your project using LoadExtension. For example:

var conn = new SQLiteConnection(connectionString);

conn.Open();

conn.LoadExtension("vec0.dll");

Creating a Virtual FLOAT32 Vector Table

To create a virtual table with a FLOAT32 vector column, use the following SQL statement:

    CREATE VIRTUAL TABLE DOCUMENTS_VECTOR_FLOAT32 USING vec0(
        id INTEGER,
        EMBEDDING FLOAT32[3]  -- A 3-dimensional vector
    );

Establishing a Connection and Loading the Extension Library

Create an instance of the SQLiteConnection class, open the database connection, and load the vec0.dll.

SQLiteConnection conn = new SQLiteConnection(
   "Data Source=SqlVector.db;Version=3;");
conn.Open();

conn.LoadExtension("vec0.dll");

Inserting Data into the Table

Create an instance of the SQLiteCommand class associated with the SQLiteConnection object and specify the SQL statement.

SQLiteCommand cmd = conn.CreateCommand(); 
cmd.CommandText = "INSERT INTO DOCUMENTS_VECTOR_FLOAT32 (EMBEDDING) VALUES ('[1,2,3]');"

Execute the command.

cmd.ExecuteNonQuery();

Retrieving Data

To retrieve FLOAT32 vector data, use the GetSQLiteFloat32Vector method.

while (reader.Read()) {
  SQLiteFloat32Vector vector = reader.GetSQLiteFloat32Vector(0);
}

Working with SQLiteFloat32Vector

This section describes common operations supported by the SQLiteFloat32Vector type.

Checking Equality

To check if two FLOAT32 vectors are equal, use the == operator.

SQLiteFloat32Vector vector1 = new SQLiteFloat32Vector(new List<sbyte> { 10, 20, 30 });
SQLiteFloat32Vector vector2 = new SQLiteFloat32Vector(new List<sbyte> { 10, 20, 30 });

if (vector1 == vector2)
  Console.WriteLine("vector1 and vector2 are the same");

Checking Inequality

To check if two FLOAT32 vectors are not equal, use the != operator.

SQLiteFloat32Vector vector1 = new SQLiteFloat32Vector(new List<sbyte> { 10, 20, 30 });
SQLiteFloat32Vector vector2 = new SQLiteFloat32Vector(new List<sbyte> { 10, 20, 30 });

if (vector1 != vector2)
  Console.WriteLine("vector1 and vector2 are not the same");

Parsing from String

Using TryParse

To safely convert a string to a FLOAT32 vector, use the TryParse method.

SQLiteFloat32Vector vector = new SQLiteFloat32Vector(3, new List<int> {1, 2, 3}, new List<sbyte> { 10, 20, 30 });

string stringVector = "[3,[1,2,3],[10,20,30]]";

if(SQLiteFloat32Vector.TryParse(stringVector, out vector)) {
  Console.WriteLine("You can parse stringVector");
}

Using Parse

To directly convert a string to a FLOAT32 vector, use the Parse method.

string stringVector = "[10,20,30]";

SQLiteFloat32Vector newVector = SQLiteFloat32Vector.Parse(stringVector);

Converting Vector to String

To convert a FLOAT32 vector to its string representation, use the ToString method.

SQLiteFloat32Vector vector = new SQLiteFloat32Vector(new List<sbyte> { 10, 20, 30 });

string stringVector = vector.ToString();

Getting Hash Code

To retrieve the hash code of a FLOAT32 vector, use the GetHashCode method.

SQLiteFloat32Vector vector = new SQLiteFloat32Vector(new List<sbyte> { 10, 20, 30 });

int hashCode = vector.GetHashCode();

Finding Similar Vectors

To search for similar vectors, use metrics such as Manhattan, Euclidean, and cosine distance and MATCH.

// Manhattan distance
SELECT EMBEDDING 
FROM DOCUMENTS_VECTOR_FLOAT32 ORDER BY vec_distance_l1('[1,2,3]', '[4,2,3]')

// Euclidean distance
SELECT EMBEDDING 
FROM DOCUMENTS_VECTOR_FLOAT32 ORDER BY vec_distance_l2('[1,2,3]', '[4,2,3]')

// Cosine distance
SELECT EMBEDDING 
FROM DOCUMENTS_VECTOR_FLOAT32 ORDER BY vec_distance_cosine('[1,2,3]', '[4,2,3]')

// MATCH
SELECT EMBEDDING 
FROM DOCUMENTS_VECTOR_FLOAT32 WHERE '[1,2,3]' MATCH '[4,2,3]'