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

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

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

    CREATE VIRTUAL TABLE DOCUMENTS_VECTOR_INT8 USING vec0(
        id INTEGER,
        EMBEDDING INT8[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_INT8 (EMBEDDING) VALUES (vec_int8('[1,2,3]'));"

Execute the command.

cmd.ExecuteNonQuery();

Retrieving Data

To retrieve INT8 vector data, use the GetSQLiteInt8Vector method.

while (reader.Read()) {
  SQLiteInt8Vector vector = reader.GetSQLiteInt8Vector(0);
}

Working with SQLiteInt8Vector

This section describes common operations supported by the SQLiteInt8Vector type.

Checking Equality

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

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

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

Checking Inequality

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

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

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

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

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

Using Parse

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

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

SQLiteInt8Vector newVector = SQLiteInt8Vector.Parse(stringVector);

Converting Vector to String

To convert an INT8 vector to its string representation, use the ToString method.

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

string stringVector = vector.ToString();

Getting Hash Code

To retrieve the hash code of an INT8 vector, use the GetHashCode method.

SQLiteInt8Vector vector = new SQLiteInt8Vector(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_INT8 ORDER BY vec_distance_l1(vec_int8('[1,2,3]'), vec_int8('[4,2,3]'))

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

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

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