Is a SqlServer vector-store provider on the AI Management roadmap ? #10808

2 Answer(s)

• 

  0

  AI-Bot created 2 months ago

  Support Team ABP Agent

  • ABP AI Management does not currently ship a SQL Server vector-store provider.
  • As of the verified AI Management docs, the built-in vector store providers are MongoDb, Pgvector, and Qdrant (installed via their respective Volo.AIManagement.VectorStores.* packages). There is no mention of SQL Server as a supported vector store provider.
  • Regarding “roadmap”: the publicly available/verified ABP AI Management roadmap items (preview announcement / future goals) do not list a SQL Server vector-store provider, so there is no verified public commitment that it’s planned.

  If you need SQL Server vectors now, the supported approach in ABP is to implement a custom vector store provider (register your own factory so it appears in the “Vector Store Provider” dropdown) and integrate SQL Server’s vector features behind that abstraction.

  Links (verified):

  • https://abp.io/docs/10.2/modules/ai-management
  • https://abp.io/community/articles/nz9404a9
  • https://learn.microsoft.com/en-us/sql/sql-server/ai/vectors?view=sql-server-ver17

  ---

  This response was generated by AI. If it didn’t solve your problem, feel free to post a new answer — our support team will jump in to help you soon.

  Markdown supported.

  Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post)

  Save Cancel
• 

  0

  maliming created 2 months ago

  Support Team Fullstack Developer

  Hi,

  The AI Management module currently ships three built-in vector store providers: MongoDB, Pgvector and Qdrant. A SQL Server provider is not planned at the moment, but we'll take your request into account for future versions.

  The good news is that the vector store side is extensible, so you can plug in SQL Server (2025+ with the native VECTOR type) yourself. You implement two interfaces from the Volo.AIManagement.Abstractions package (IVectorStore and IVectorStoreFactory) in one of your application's modules, then register the factory. AI Management syncs the registered provider names at application startup (enabled by default), so after a restart SqlServer will appear in the workspace's vector store provider dropdown.

  1. Implement IVectorStore using Microsoft.Data.SqlClient. Here are the key parts (table creation, upsert and vector search):

  using System;
  using System.Collections.Generic;
  using System.Text.Json;
  using System.Threading;
  using System.Threading.Tasks;
  using Microsoft.Data.SqlClient;
  using Microsoft.Extensions.Logging;
  using Volo.Abp;
  using Volo.AIManagement.VectorStores;
  
  namespace MyCompanyName.MyProjectName.VectorStores.SqlServer;
  
  public class SqlServerVectorStore : IVectorStore
  {
      protected readonly string ConnectionString;
      protected readonly Guid WorkspaceId;
      protected readonly ILogger<SqlServerVectorStore> Logger;
  
      public SqlServerVectorStore(string connectionString, Guid workspaceId, ILogger<SqlServerVectorStore> logger)
      {
          Check.NotNullOrWhiteSpace(connectionString, nameof(connectionString));
  
          ConnectionString = connectionString;
          WorkspaceId = workspaceId;
          Logger = logger;
      }
  
      protected virtual string GetTableName()
      {
          return $"AIVectorEmbeddings_{WorkspaceId:N}";
      }
  
      protected virtual async Task EnsureTableExistsAsync(SqlConnection connection, int dimension, CancellationToken cancellationToken)
      {
          var tableName = GetTableName();
          var sql = $"""
              IF OBJECT_ID(N'{tableName}', 'U') IS NULL
              BEGIN
                  CREATE TABLE [{tableName}] (
                      [Id] NVARCHAR(450) NOT NULL PRIMARY KEY,
                      [WorkspaceId] UNIQUEIDENTIFIER NOT NULL,
                      [Embedding] VECTOR({dimension}) NOT NULL,
                      [Metadata] NVARCHAR(MAX) NULL,
                      [CreationTime] DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
                  );
                  CREATE INDEX [IX_{tableName}_WorkspaceId] ON [{tableName}] ([WorkspaceId]);
              END
              """;
  
          try
          {
              await using var command = new SqlCommand(sql, connection);
              await command.ExecuteNonQueryAsync(cancellationToken);
          }
          catch (SqlException ex) when (ex.Number is 2714 or 1913)
          {
              // 2714/1913: the table/index was created by a concurrent call in the meantime.
          }
      }
  
      public virtual async Task<string> StoreAsync(
          Guid workspaceId,
          string id,
          float[] embedding,
          Dictionary<string, object>? metadata = null,
          CancellationToken cancellationToken = default)
      {
          await using var connection = new SqlConnection(ConnectionString);
          await connection.OpenAsync(cancellationToken);
  
          await EnsureTableExistsAsync(connection, embedding.Length, cancellationToken);
  
          var tableName = GetTableName();
          var sql = $"""
              MERGE [{tableName}] WITH (HOLDLOCK) AS [Target]
              USING (SELECT @Id AS [Id]) AS [Source]
              ON [Target].[Id] = [Source].[Id]
              WHEN MATCHED THEN
                  UPDATE SET [Embedding] = CAST(@Embedding AS VECTOR({embedding.Length})), [Metadata] = @Metadata
              WHEN NOT MATCHED THEN
                  INSERT ([Id], [WorkspaceId], [Embedding], [Metadata])
                  VALUES (@Id, @WorkspaceId, CAST(@Embedding AS VECTOR({embedding.Length})), @Metadata);
              """;
  
          await using var command = new SqlCommand(sql, connection);
          command.Parameters.AddWithValue("@Id", id);
          command.Parameters.AddWithValue("@WorkspaceId", WorkspaceId);
          command.Parameters.AddWithValue("@Embedding", JsonSerializer.Serialize(embedding));
          command.Parameters.AddWithValue("@Metadata", metadata != null ? JsonSerializer.Serialize(metadata) : "{}");
  
          await command.ExecuteNonQueryAsync(cancellationToken);
          return id;
      }
  
      public virtual async Task<List<VectorSearchResult>> SearchAsync(
          Guid workspaceId,
          float[] queryEmbedding,
          int topK,
          CancellationToken cancellationToken = default)
      {
          // Return an empty list if the table doesn't exist yet (see the notes below the code).
  
          await using var connection = new SqlConnection(ConnectionString);
          await connection.OpenAsync(cancellationToken);
  
          var tableName = GetTableName();
          var sql = $"""
              SELECT TOP (@TopK)
                  [Id],
                  [Metadata],
                  1 - VECTOR_DISTANCE('cosine', [Embedding], CAST(@Query AS VECTOR({queryEmbedding.Length}))) AS [Score]
              FROM [{tableName}]
              WHERE [WorkspaceId] = @WorkspaceId
              ORDER BY VECTOR_DISTANCE('cosine', [Embedding], CAST(@Query AS VECTOR({queryEmbedding.Length})))
              """;
  
          await using var command = new SqlCommand(sql, connection);
          command.Parameters.AddWithValue("@TopK", topK);
          command.Parameters.AddWithValue("@WorkspaceId", WorkspaceId);
          command.Parameters.AddWithValue("@Query", JsonSerializer.Serialize(queryEmbedding));
  
          var results = new List<VectorSearchResult>();
          await using var reader = await command.ExecuteReaderAsync(cancellationToken);
          while (await reader.ReadAsync(cancellationToken))
          {
              var metadataJson = reader.IsDBNull(1) ? "{}" : reader.GetString(1);
              results.Add(new VectorSearchResult
              {
                  Id = reader.GetString(0),
                  Score = Convert.ToSingle(reader.GetValue(2)),
                  Metadata = JsonSerializer.Deserialize<Dictionary<string, object>>(metadataJson)
              });
          }
  
          return results;
      }
  
      public virtual async Task<int> DeleteAllByDataSourceAsync(
          Guid workspaceId,
          Guid dataSourceId,
          CancellationToken cancellationToken = default)
      {
          // Return 0 if the table doesn't exist yet (see the notes below the code).
  
          await using var connection = new SqlConnection(ConnectionString);
          await connection.OpenAsync(cancellationToken);
  
          var sql = $"""
              DELETE FROM [{GetTableName()}]
              WHERE [WorkspaceId] = @WorkspaceId
                AND JSON_VALUE([Metadata], '$.data_source_id') = @DataSourceId
              """;
  
          await using var command = new SqlCommand(sql, connection);
          command.Parameters.AddWithValue("@WorkspaceId", WorkspaceId);
          command.Parameters.AddWithValue("@DataSourceId", dataSourceId.ToString());
  
          return await command.ExecuteNonQueryAsync(cancellationToken);
      }
  }
  

  The remaining IVectorStore members are straightforward:

  • InitializeAsync: just open a connection to validate the settings. The table itself is created lazily on the first StoreAsync call, because the embedding dimension is not known before that.
  • DeleteAsync: DELETE ... WHERE [Id] = @Id AND [WorkspaceId] = @WorkspaceId, return rowsAffected > 0.
  • DeleteByWorkspaceIdAsync: DROP TABLE IF EXISTS [{GetTableName()}], return 1 (same as the Pgvector and Qdrant providers).
  • SearchAsync and the delete methods should check OBJECT_ID(@TableName, 'U') first and return empty/0/false when the table doesn't exist yet (before the first document is indexed).

  2. Implement the factory. The connectionSettings parameter is the value you enter in the workspace's vector store settings field (the SQL Server connection string):

  using System;
  using System.Threading.Tasks;
  using Microsoft.Extensions.Logging;
  using Volo.Abp.DependencyInjection;
  using Volo.AIManagement.VectorStores;
  
  namespace MyCompanyName.MyProjectName.VectorStores.SqlServer;
  
  [ExposeServices(typeof(IVectorStoreFactory), typeof(SqlServerVectorStoreFactory))]
  public class SqlServerVectorStoreFactory : IVectorStoreFactory, ITransientDependency
  {
      public const string ProviderName = "SqlServer";
  
      protected ILoggerFactory LoggerFactory { get; }
  
      public SqlServerVectorStoreFactory(ILoggerFactory loggerFactory)
      {
          LoggerFactory = loggerFactory;
      }
  
      public virtual string Provider => ProviderName;
  
      public virtual Task<IVectorStore> CreateAsync(Guid workspaceId, string? connectionSettings = null)
      {
          var logger = LoggerFactory.CreateLogger<SqlServerVectorStore>();
          return Task.FromResult<IVectorStore>(new SqlServerVectorStore(connectionSettings!, workspaceId, logger));
      }
  }
  

  3. Register the factory in your module's ConfigureServices:

  Configure<VectorStoreFactoryOptions>(options =>
  {
      options.AddFactory<SqlServerVectorStoreFactory>(SqlServerVectorStoreFactory.ProviderName);
  });
  

  After that, restart the application and select SqlServer as the vector store provider when creating/editing a workspace, and put your SQL Server connection string into the vector store settings field.

  A few notes:

  • This requires SQL Server 2025 (or Azure SQL Database) for the native VECTOR type and VECTOR_DISTANCE function.
  • The VECTOR type supports up to 1998 dimensions, so pick your embedding model accordingly (e.g. text-embedding-3-small with 1536 dimensions works, text-embedding-3-large with 3072 dimensions won't fit): https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type
  • VECTOR_DISTANCE performs an exact (brute-force) search. That's fine for small/medium datasets; for large ones check SQL Server's approximate vector search options: https://learn.microsoft.com/en-us/sql/sql-server/ai/vectors
  • The module filters embeddings by the data_source_id metadata key when a data source is removed, that's why DeleteAllByDataSourceAsync uses JSON_VALUE([Metadata], '$.data_source_id').
  • The score returned by SearchAsync is the cosine similarity (1 - cosine distance), which matches the behavior of the built-in providers.
  • This is a minimal sample to get you started, you may want to add better error handling and diagnostics for production use.

  Thanks

  Markdown supported.

  Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post)

  Save Cancel