.Net: Bug: [SqliteVectorStore] NOT NULL constraint failed: words.json during UpsertAsync

[P47K0]

Describe the bug
Calling UpsertAsync (single or batch) on a SqliteVectorStore collection throws:

SQLite Error 19: 'NOT NULL constraint failed: words.json'

The json column in the words table (or collection-named table) is defined as NOT NULL, but the connector serializes the non-vector properties into a null or empty value, violating the constraint.

Even after:

• GetCollection succeeds
• Dummy upsert attempts (single record, non-zero vector, empty batch) fail with the same error

To Reproduce

[VectorStoreRecord]
public record HangmanWordRecord
{
    [VectorStoreRecordKey]
    public int Key { get; init; }

    [VectorStoreData]
    public string Word { get; init; } = string.Empty;

    [VectorStoreData]
    public string Category { get; init; } = string.Empty;

    [VectorStoreData]
    public string Language { get; init; } = string.Empty;

    [VectorStoreData]
    public int Length { get; init; }

    [VectorStoreRecordVector(Dimensions = 768)]
    public ReadOnlyMemory<float> Embedding { get; init; } = default;
}

var store = new SqliteVectorStore("Data Source=:memory:");
var collection = store.GetCollection<int, HangmanWordRecord>("words");

var record = new HangmanWordRecord
{
    Key = 1,
    Word = "apple",
    Category = "food",
    Language = "en",
    Length = 5,
    Embedding = new ReadOnlyMemory<float>(new float[768])
};

await collection.UpsertAsync(record);  // throws NOT NULL constraint failed: words.json

Expected behavior

• UpsertAsync succeeds
• json column contains serialized non-vector properties (or is nullable if empty)

Workarounds tried

• Explicit non-empty string values in all [VectorStoreData] properties — still fails
• Relaxed schema (json TEXT without NOT NULL) — insert succeeds, but json column remains null/empty
• Manual raw SQL insert with JsonSerializer.Serialize — works reliably

Screenshots

Platform

• Language: C#
• .NET: 10.0
• Source:
  • Semantic Kernel version: 1.70.0
  • Connector: Microsoft.SemanticKernel.Connectors.SqliteVec 1.70.0-preview
• Database: SQLite (file-based)
• vec0.dll ->0.1.7-alpha.2 (loadable extension from https://github.com/asg017/sqlite-vec/releases)
• AI model: nomic-embed-text via ollama
• IDE: Visual Studio 2026 Pro
• OS: Winsows 11 x64
• GPU: Intel Arc iGPU

Additional context

• Occurs with both in-memory SQLite file-based DB
• Same issue in SqliteVec connector (chunk tables also remain empty)
• Manual SQL workaround bypasses the bug completely

You can see my simple Hangman game where I encountered the issue here:
https://github.com/P47K0/SKOrchestrationPractice

[feiyun0112]

i think it caused by use memory in Sqlite

var store = new SqliteVectorStore("Data Source=:memory:");

SqliteCollection use a new connection for every operation, so it will lose collection before UpsertAsync

semantic-kernel/dotnet/src/VectorData/SqliteVec/SqliteCollection.cs

Line 408 in a3be1fd

private async ValueTask<SqliteConnection> GetConnectionAsync(CancellationToken cancellationToken = default)

try to change your code to use a file as data source

var store = new SqliteVectorStore("Data Source=db_file");

[P47K0]

Even with:
var store = "Data Source=c:/temp/hangman_vectors.db" I get the exception.

[feiyun0112]

Even with: var store = "Data Source=c:/temp/hangman_vectors.db" I get the exception.

i tested it works

using Microsoft.Extensions.AI;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.VectorData;
using VectorData.ConformanceTests.Support;
using VectorData.ConformanceTests.Xunit;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.SemanticKernel.Connectors.SqliteVec;
using SqliteVec.ConformanceTests.Support;
using Xunit;

namespace SqliteVec.ConformanceTests;

/// <summary>
/// Tests for reproducing and validating SqliteVectorStore UpsertAsync behavior.
/// </summary>
public sealed class SqliteUpsertTests : IClassFixture<SqliteFixture>
{
    private readonly SqliteFixture _fixture;

    private const string CollectionName = "words";

    public SqliteUpsertTests(SqliteFixture fixture)
    {
        this._fixture = fixture;
    }
    
    /// <summary>
    /// Tests batch upsert operation to ensure it doesn't throw NOT NULL constraint error.
    /// </summary>
    [Fact]
    public async Task UpsertBatchAsyncShouldNotThrowNotNullConstraintError()
    {
        // Arrange
        var tempDbPath = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.db");
        var store = new SqliteVectorStore($"Data Source={tempDbPath}");
        var collection = store.GetCollection<int, HangmanWordRecord>(CollectionName);
        await collection.EnsureCollectionExistsAsync();

        var records = new[]
        {
            new HangmanWordRecord
            {
                Key = 1,
                Word = "apple",
                Category = "food",
                Language = "en",
                Length = 5,
                Embedding = new ReadOnlyMemory<float>(new float[768])
            },
            new HangmanWordRecord
            {
                Key = 2,
                Word = "banana",
                Category = "food",
                Language = "en",
                Length = 6,
                Embedding = new ReadOnlyMemory<float>(new float[768])
            }
        };

        // Act & Assert
        // This should not throw "NOT NULL constraint failed: words.json"
        var exception = await Xunit.Record.ExceptionAsync(async () =>
        {
            await collection.UpsertAsync(records);
        });

        Assert.Null(exception);

        // Verify the records were inserted successfully
        var retrievedRecords = await collection.GetAsync([1, 2]).ToListAsync();
        Assert.Equal(2, retrievedRecords.Count);
        Assert.Contains(retrievedRecords, r => r.Word == "apple");
        Assert.Contains(retrievedRecords, r => r.Word == "banana");
    } 

    /// <summary>
    /// Test record model reproducing the structure from issue #13476.
    /// </summary>
    public record HangmanWordRecord
    {
        [VectorStoreKey]
        public int Key { get; init; }

        [VectorStoreData]
        public string Word { get; init; } = string.Empty;

        [VectorStoreData]
        public string Category { get; init; } = string.Empty;

        [VectorStoreData]
        public string Language { get; init; } = string.Empty;

        [VectorStoreData]
        public int Length { get; init; }

        [VectorStoreVector(Dimensions: 768)]
        public ReadOnlyMemory<float> Embedding { get; init; } = default;
    }
}

[P47K0]

So if it is not the connector, could it be the vec0.dll that is causing this exception?