Repository: mindsdb/mindsdb
Pull request: Add support for default vectorDB for Knowledge Base
## Description

Allows user to create knowledge base without defining storage. By default, it will create in memory ChromaDB

continuing from https://github.com/mindsdb/mindsdb/pull/7886

How to test:

Using local mindsdb:

```
CREATE ML_ENGINE embedding FROM langchain_embedding;

CREATE MODEL cohere_embedding_model
PREDICT embeddings
-- the name of the column in the output data that contains the embedding vectors
USING
  engine = 'embedding',
  class = 'cohere', -- the name of the embedding class in langchain.embeddings, alternatively, you can also use 'openai' / 'huggingface' instead
  cohere_api_key = 'enter-cohere-api-key',
  input_columns = ['content'];


CREATE KNOWLEDGE_BASE  my_kb
USING
MODEL=cohere_embedding_model;
```



Fixes #7184 

## Type of change

(Please delete options that are not relevant)

- [x] ⚡ New feature (non-breaking change which adds functionality)


## Verification Process

To ensure the changes are working as expected:

 - [x]   Test Location: Specify the URL or path for testing.
 - [x]   Verification Steps: Outline the steps or queries needed to validate the change. Include any data, configurations, or actions required to reproduce or see the new functionality.


## Checklist:

- [x] My code follows the style guidelines(PEP 8) of MindsDB.
- [x] I have appropriately commented on my code, especially in complex areas.
- [x] Necessary documentation updates are either made or tracked in issues.
- [x] Relevant unit and integration tests are updated or added.





Problem statement:
[Bug]: ChromaDB in memory vectorDB doesn't work in cloud
### Short description of current behavior

It expects a full persistence path, not possible for user to know the path

### Video or screenshots

_No response_

### Expected behavior

User should be able to create an in memory database on cloud

### How to reproduce the error

_No response_

### Anything else?

_No response_

Add standardised parameters to VectorStoreHandler 
### Short description and motivation for the proposed feature

add some common parameters to VectorStoreHandler base class

Will make it more unified and easier to switch in and out differe vector store handlers

Hints from issue discussion:

Commit message: only create in memory chroma for local runs and not cloud

Patch to review:
diff --git a/.pre-commit-config.yaml b/.pre-commit-config.yaml
index c585d58c1d9..1ef1e9b557c 100644
--- a/.pre-commit-config.yaml
+++ b/.pre-commit-config.yaml
@@ -4,12 +4,13 @@ repos:
     rev: 22.3.0
     hooks:
       - id: black
-        args: [--skip-string-normalization] #prevents conversion of single to double quotes see https://safjan.com/black-keep-single-quotes-strings/ 
+        args: ['--skip-string-normalization'] #prevents conversion of single to double quotes see https://safjan.com/black-keep-single-quotes-strings/
   - repo: https://github.com/pycqa/flake8
     rev: 4.0.1
     hooks:
       - id: flake8
         additional_dependencies: [flake8-typing-imports==1.10.0]
+        args: ['--max-line-length=88', '--extend-ignore=E203,E704']
   - repo: https://github.com/pre-commit/pre-commit-hooks
     rev: v4.3.0
     hooks:
diff --git a/mindsdb/api/mysql/mysql_proxy/executor/executor_commands.py b/mindsdb/api/mysql/mysql_proxy/executor/executor_commands.py
index bec87d4efb8..22ab634b3d8 100644
--- a/mindsdb/api/mysql/mysql_proxy/executor/executor_commands.py
+++ b/mindsdb/api/mysql/mysql_proxy/executor/executor_commands.py
@@ -431,7 +431,7 @@ def execute_command(self, statement):
             # FIXME if have answer on that request, then DataGrip show warning '[S0022] Column 'Non_unique' not found.'
             elif "show create table" in sql_lower:
                 # SHOW CREATE TABLE `MINDSDB`.`predictors`
-                table = sql[sql.rfind(".") + 1:].strip(" .;\n\t").replace("`", "")
+                table = sql[sql.rfind(".") + 1 :].strip(" .;\n\t").replace("`", "")
                 return self.answer_show_create_table(table)
             elif sql_category in ("character set", "charset"):
                 new_statement = Select(
@@ -819,7 +819,6 @@ def answer_evaluate_metric(self, statement):
         )
 
     def answer_describe_predictor(self, statement):
-
         # try full name
         attribute = None
         model_info = self._get_model_info(statement.value, except_absent=False)
@@ -1256,6 +1255,21 @@ def answer_drop_view(self, statement):
 
         return ExecuteAnswer(answer_type=ANSWER_TYPE.OK)
 
+    def _create_persistent_chroma(self, kb_name, collection_name, engine="chromadb"):
+        """Create default vector database for knowledge base, if not specified"""
+
+        vector_store_name = f"{kb_name}_{engine}"
+
+        vector_store_folder_name = f"{vector_store_name}"
+        connection_args = {"persist_directory": vector_store_folder_name}
+        self._create_integration(vector_store_name, engine, connection_args)
+
+        self.session.datahub.get(vector_store_name).integration_handler.create_table(
+            collection_name
+        )
+
+        return ExecuteAnswer(answer_type=ANSWER_TYPE.OK), vector_store_name
+
     def answer_create_kb(self, statement: CreateKnowledgeBase):
         project_name = (
             statement.name.parts[0]
@@ -1285,14 +1299,25 @@ def answer_create_kb(self, statement: CreateKnowledgeBase):
         embedding_model_id = model_record["model_record"].id
 
         # search for the vector database table
-        if len(statement.storage.parts) < 2:
+        if statement.storage and len(statement.storage.parts) < 2:
             raise SqlApiException(
                 f"Invalid vectordatabase table name: {statement.storage}"
                 "Need the form 'database_name.table_name'"
             )
 
-        vector_db_name = statement.storage.parts[0]
-        vector_table_name = statement.storage.parts[-1]
+        is_cloud = self.session.config.get("cloud", False)
+
+        vector_table_name = (
+            statement.storage.parts[-1] if statement.storage else "default_collection"
+        )
+
+        vector_db_name = (
+            statement.storage.parts[0]
+            if statement.storage and is_cloud
+            else self._create_persistent_chroma(
+                kb_name, collection_name=vector_table_name
+            )[1]
+        )
 
         # verify the vector database exists and get its id
         database_records = self.session.database_controller.get_dict()
@@ -1856,7 +1881,6 @@ def answer_select(self, query):
         )
 
     def answer_update_model_version(self, statement):
-
         # get project name
         if len(statement.table.parts) > 1:
             project_name = statement.table.parts[0]
diff --git a/mindsdb/integrations/handlers/chromadb_handler/README.md b/mindsdb/integrations/handlers/chromadb_handler/README.md
index 01cc8620299..ac4ba573df5 100644
--- a/mindsdb/integrations/handlers/chromadb_handler/README.md
+++ b/mindsdb/integrations/handlers/chromadb_handler/README.md
@@ -12,8 +12,8 @@ This handler uses `chromadb` python library connect to a chromadb instance, it u
 
 The required arguments to establish a connection are:
 
-* `chroma_server_host`: the host name or IP address of the ChromaDB instance
-* `chroma_server_http_port`: the port to use when connecting
+* `host`: the host name or IP address of the ChromaDB instance
+* `port`: the port to use when connecting
 * `persist_directory`: the directory to use for persisting data
 
 
@@ -25,8 +25,8 @@ In order to make use of this handler and connect to a hosted ChromaDB instance i
 CREATE DATABASE chroma_dev
 WITH ENGINE = "chromadb",
 PARAMETERS = {
-   "chroma_server_host": "localhost",
-   "chroma_server_http_port": 8000
+   "host": "localhost",
+   "port": 8000
     }
 ```
 
diff --git a/mindsdb/integrations/handlers/chromadb_handler/chromadb_handler.py b/mindsdb/integrations/handlers/chromadb_handler/chromadb_handler.py
index 80167a6225c..2b9d5801dac 100644
--- a/mindsdb/integrations/handlers/chromadb_handler/chromadb_handler.py
+++ b/mindsdb/integrations/handlers/chromadb_handler/chromadb_handler.py
@@ -24,37 +24,15 @@ class ChromaDBHandler(VectorStoreHandler):
     name = "chromadb"
 
     def __init__(self, name: str, **kwargs):
-        super().__init__(name)
-
-        self._connection_data = kwargs.get("connection_data")
+        super().__init__(name, **kwargs)
 
         self._client_config = {
-            "chroma_server_host": self._connection_data.get("chroma_server_host"),
-            "chroma_server_http_port": self._connection_data.get(
-                "chroma_server_http_port"
-            ),
-            "persist_directory": self._connection_data.get("persist_directory"),
+            "chroma_server_host": self.config.host,
+            "chroma_server_http_port": self.config.port,
+            "persist_directory": self.persist_directory,
         }
 
-        # either host + port or persist_directory is required
-        # but not both
-        if (
-            self._client_config["chroma_server_host"] is None
-            or self._client_config["chroma_server_http_port"] is None
-        ) and self._client_config["persist_directory"] is None:
-            raise Exception(
-                "Either host + port or persist_directory is required for ChromaDB connection!"
-            )
-        elif (
-            self._client_config["chroma_server_host"] is not None
-            and self._client_config["chroma_server_http_port"] is not None
-        ) and self._client_config["persist_directory"] is not None:
-            raise Exception(
-                "Either host + port or persist_directory is required for ChromaDB connection, but not both!"
-            )
-
         self._client = None
-        self.is_connected = False
         self.connect()
 
     def _get_client(self):
@@ -72,8 +50,7 @@ def _get_client(self):
             )
 
     def __del__(self):
-        if self.is_connected is True:
-            self.disconnect()
+        super().__del__()
 
     def connect(self):
         """Connect to a ChromaDB database."""
@@ -368,12 +345,12 @@ def get_columns(self, table_name: str) -> HandlerResponse:
 
 
 connection_args = OrderedDict(
-    chroma_server_host={
+    host={
         "type": ARG_TYPE.STR,
         "description": "chromadb server host",
         "required": False,
     },
-    chroma_server_http_port={
+    port={
         "type": ARG_TYPE.INT,
         "description": "chromadb server port",
         "required": False,
@@ -386,7 +363,7 @@ def get_columns(self, table_name: str) -> HandlerResponse:
 )
 
 connection_args_example = OrderedDict(
-    chroma_server_host="localhost",
-    chroma_server_http_port=8000,
-    persist_directoryn="chroma",
+    host="localhost",
+    port=8000,
+    persist_directory="chroma",
 )
diff --git a/mindsdb/integrations/libs/vectordatabase_handler.py b/mindsdb/integrations/libs/vectordatabase_handler.py
index 21fb9a335c6..81d470130d5 100644
--- a/mindsdb/integrations/libs/vectordatabase_handler.py
+++ b/mindsdb/integrations/libs/vectordatabase_handler.py
@@ -1,4 +1,5 @@
 import ast
+import difflib
 import uuid
 from enum import Enum
 from typing import Any, List, Optional
@@ -17,8 +18,10 @@
     Update,
 )
 from mindsdb_sql.parser.ast.base import ASTNode
+from pydantic import BaseModel, Extra, root_validator
 
 from mindsdb.integrations.libs.response import RESPONSE_TYPE, HandlerResponse
+from mindsdb.interfaces.storage.model_fs import HandlerStorage
 from mindsdb.utilities.log import get_log
 
 from ..utilities.sql_utils import query_traversal
@@ -91,6 +94,79 @@ class TableField(Enum):
     DISTANCE = "distance"
 
 
+class VectorStoreHandlerConfig(BaseModel):
+    """
+    Configuration for VectorStoreHandler.
+    """
+
+    vector_store: str
+    persist_directory: str = None
+    host: str = None
+    port: int = None
+    url: str = None
+
+    class Config:
+        extra = Extra.forbid
+
+    @root_validator(pre=True)
+    def check_param_typos(cls, values):
+        """Check if there are any typos in the parameters."""
+
+        expected_params = cls.__fields__.keys()
+        for key in values.keys():
+            if key not in expected_params:
+                close_matches = difflib.get_close_matches(
+                    key, expected_params, cutoff=0.4
+                )
+                if close_matches:
+                    raise ValueError(
+                        f"Unexpected parameter '{key}'. Did you mean '{close_matches[0]}'?"
+                    )
+                else:
+                    raise ValueError(f"Unexpected parameter '{key}'.")
+        return values
+
+    @root_validator
+    def check_config(cls, values):
+        """Check if config is valid."""
+
+        vector_store = values.get("vector_store")
+        host = values.get("host")
+        port = values.get("port")
+        url = values.get("url")
+        persist_directory = values.get("persist_directory")
+
+        if host and not port:
+            raise ValueError(
+                f"For {vector_store} handler - if host is provided, port must also be provided."
+            )
+
+        if port and not host:
+            raise ValueError(
+                f"For {vector_store} handler - if port is provided, host must also be provided."
+            )
+
+        if port and host and (url or persist_directory):
+            raise ValueError(
+                f"For {vector_store} handler - if host and port are provided, "
+                f"url and persistence_folder should not be provided."
+            )
+
+        if url and (host or port or persist_directory):
+            raise ValueError(
+                f"For {vector_store} handler - if url is provided, host, port, "
+                f"persist_directory should not be provided."
+            )
+
+        if persist_directory and (url or host or port):
+            raise ValueError(
+                f"For {vector_store} handler - if persistence_folder is provided, "
+                f"url, host, port should not be provided."
+            )
+
+        return values
+
+
 class VectorStoreHandler(BaseHandler):
     """
     Base class for handlers associated to vector databases.
@@ -115,8 +191,34 @@ class VectorStoreHandler(BaseHandler):
         },
     ]
 
-    def __init__(self, name: str):
+    def __init__(self, name: str, **kwargs):
         super().__init__(name)
+        kwargs["connection_data"].pop("password", None)
+        self.handler_storage = HandlerStorage(kwargs.get("integration_id"))
+
+        _config = kwargs.get("connection_data")
+        _config["vector_store"] = name
+
+        self.config = VectorStoreHandlerConfig(**_config)
+
+        self.is_connected = False
+
+        if self.config.persist_directory and not self.handler_storage.is_temporal:
+            # get full persistence directory from handler storage
+            self.persist_directory = self.handler_storage.folder_get(
+                self.config.persist_directory
+            )
+
+    def __del__(self):
+        if self.is_connected is True:
+            if self.persist_directory:
+                # sync folder to handler storage
+                self.handler_storage.folder_sync(self.persist_directory)
+
+            self.disconnect()
+
+    def disconnect(self):
+        raise NotImplementedError()
 
     def _value_or_self(self, value):
         if isinstance(value, Constant):
@@ -147,7 +249,8 @@ def _extract_comparison_conditions(node, **kwargs):
                         # Constant could be actually a list i.e. [1.2, 3.2]
                         right_hand = [
                             ast.literal_eval(item.value)
-                            if isinstance(item, Constant) and not isinstance(item.value, list)
+                            if isinstance(item, Constant)
+                            and not isinstance(item.value, list)
                             else item.value
                             for item in node.args[1].items
                         ]
diff --git a/requirements/requirements.txt b/requirements/requirements.txt
index 68a2480d1f3..9960d12f016 100644
--- a/requirements/requirements.txt
+++ b/requirements/requirements.txt
@@ -27,10 +27,12 @@ redis >=5.0.0, < 6.0.0
 walrus==0.9.3
 flask-compress >= 1.0.0
 appdirs >= 1.0.0
-mindsdb-sql >= 0.7.2, < 0.8.0
+mindsdb-sql ~= 0.7.3
 mindsdb-evaluator >= 0.0.7, < 0.1.0
 checksumdir >= 1.2.0
 duckdb == 0.6.1
+pydantic~=1.10.13
+chromadb~=0.4.8
 requests >= 2.0.0
 mysql-connector-python
 charset-normalizer
diff --git a/tests/unit/test_knowledge_base.py b/tests/unit/test_knowledge_base.py
index 6cf96de482e..64a82705478 100644
--- a/tests/unit/test_knowledge_base.py
+++ b/tests/unit/test_knowledge_base.py
@@ -156,6 +156,21 @@ def test_create_kb(self):
         with pytest.raises(Exception):
             self.run_sql(sql)
 
+        # todo this is to be supported - currently it throws an exception, waiting for the fix to be merged
+        # create a knowledge base without a storage name, default should be used
+
+        sql = f"""
+            CREATE KNOWLEDGE BASE test_kb5
+            USING
+            MODEL = {self.embedding_model_name}
+        """
+
+        self.run_sql(sql)
+
+        # verify the knowledge base is created
+        kb_obj = self.db.session.query(KnowledgeBase).filter_by(name="test_kb5").first()
+        assert kb_obj is not None
+
     def test_drop_kb(self):
         # create a knowledge base
         sql = f"""


Code review comments:
diff_hunk: @@ -1285,14 +1299,25 @@ def answer_create_kb(self, statement: CreateKnowledgeBase):
         embedding_model_id = model_record["model_record"].id
 
         # search for the vector database table
-        if len(statement.storage.parts) < 2:
+        if statement.storage and len(statement.storage.parts) < 2:
             raise SqlApiException(
                 f"Invalid vectordatabase table name: {statement.storage}"
                 "Need the form 'database_name.table_name'"
             )
 
-        vector_db_name = statement.storage.parts[0]
-        vector_table_name = statement.storage.parts[-1]
+        is_cloud = self.session.config.get("cloud", False)
+
+        vector_table_name = (
+            statement.storage.parts[-1] if statement.storage else "default_collection"
+        )
+
+        vector_db_name = (
+            statement.storage.parts[0]
+            if statement.storage and is_cloud
path: mindsdb/api/mysql/mysql_proxy/executor/executor_commands.py
text: @user1:
This will still create a persistent Chroma if `statement.storage` is not given and `is_cloud` is True (`if statement.storage and is_cloud` will evaluate to `False`). Also, it won't correctly use the specified storage if we're _not_ in cloud (the same statement will also evaluate to `False`). Until we support default vector databases in the cloud, we should add a separate check above:

```python
if not statement.storage and is_cloud:
    raise SqlApiException('No default vector database exists in MindsDB. Please specify one using the "storage" parameter')
```

Then we can change this to:
```python
vector_db_name = (
    statement.storage.parts[0]
    if statement.storage
    else self._create_persistent_chroma(
        kb_name, collection_name=vector_table_name
    )[1]
)



@author:
fair, I was going under the assumption we would get default setup for cloud up asap. Will add raise condition