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

This article shows how to create vector tables, insert and retrieve binary 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 Binary Vector Table

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

    CREATE VIRTUAL TABLE DOCUMENTS_VECTOR_BINARY USING vec0(
        id INTEGER,
        EMBEDDING BIT[24]  -- A 3-dimensional vector (1 dimension is 8 bits)
    );

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(); 
SQLiteBinaryVector vector = new SQLiteBinaryVector(new List<byte> { 10, 20, 30 });
cmd.CommandText = "INSERT INTO DOCUMENTS_VECTOR_BINARY (EMBEDDING) VALUES (vec_bit(vector.ToStringBlob()));"

Execute the command.

cmd.ExecuteNonQuery();

Retrieving Data

To retrieve binary vector data, use the GetSQLiteBinaryVector method.

while (reader.Read()) {
  SQLiteBinaryVector vector = reader.GetSQLiteBinaryVector(0);
}

Working with SQLiteBinaryVector

This section describes common operations supported by the SQLiteBinaryVector type.

Checking Equality

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

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

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

Checking Inequality

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

SQLiteBinaryVector vector1 = new SQLiteBinaryVector(new List<byte> { 10, 20, 30 });
SQLiteBinaryVector vector2 = new SQLiteBinaryVector(new List<byte> { 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 binary vector, use the TryParse method.

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

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

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

Using Parse

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

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

SQLiteBinaryVector newVector = SQLiteBinaryVector.Parse(stringVector);

Converting Vector to String

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

SQLiteBinaryVector vector = new SQLiteBinaryVector(new List<byte> { 10, 20, 30 });

string stringVector = vector.ToString();

Getting Hash Code

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

SQLiteBinaryVector vector = new SQLiteBinaryVector(new List<byte> { 10, 20, 30 });

int hashCode = vector.GetHashCode();

Finding Similar Vectors

To search for similar vectors, use the Hamming distance metric and MATCH.

// Hamming distance
SELECT EMBEDDING 
FROM DOCUMENTS_VECTOR_BINARY ORDER BY vec_distance_hamming(vec_int8('[1,2,3]'), vec_int8('[4,2,3]'))

// MATCH
SELECT EMBEDDING 
FROM DOCUMENTS_VECTOR_BINARY WHERE vec_int8('[1,2,3]') MATCH vec_int8('[4,2,3]')