How to Build a Semantic Search CLI for Local Markdown Notes Using SQLite-Vec and Gemini 1.5 Flash

Stop Uploading Your Personal Notes to the Cloud

We have all been there. You have a sprawling directory of local Markdown files—meeting minutes, project ideas, half-baked technical specs, and daily logs. When you need to find that one specific observation about a database bug from six months ago, standard keyword search fails you. If you didn't use the exact word "bug", grep won't find it.

But setting up a massive, dockerised vector database just to search a few megabytes of text is like using a sledgehammer to crack a nut. You don't need a cloud-hosted, enterprise-grade vector pipeline. You need something fast, lightweight, and local.

In this guide, we are going to build a local command-line interface (CLI) that indexes your Markdown vault using Gemini 1.5 Flash's highly efficient embedding model, storing those vectors directly inside an SQLite database using sqlite-vec.

If you want to know what makes this local-first approach tick, it is the lack of bloated infrastructure. It is fast, cheap, and runs on standard SQL.

---

The Architecture: Why SQLite-Vec?

Historically, running vector search in SQLite meant compiling complex extensions or using suboptimal flat-file indices. That changed with sqlite-vec, an extremely lightweight virtual table extension for SQLite written in C. It allows you to store floating-point vector embeddings directly in your database file and query them using standard k-nearest-neighbour (k-NN) queries.

By pairing this with Gemini 1.5 Flash for generating embeddings, we get the best of both worlds: highly accurate semantic translation of our texts and zero-latency local retrieval. To understand how embeddings represent concepts as coordinates in high-dimensional space, take a quick detour through our glossary.

---

Step 1: Setting Up the Environment

First, let's set up our workspace. You will need Python 3.10+, an API key from Google AI Studio, and some Markdown files to index.

Create a new project directory and install the required dependencies:

`bash mkdir local-semantic-search cd local-semantic-search python -m venv .venv source .venv/bin/activate # Or .venv\Scripts\activate on Windows pip install google-generativeai sqlite-vec rich `

Ensure you set your API key in your environment variables:

`bash export GEMINI_API_KEY="your-gemini-api-key-here" `

If you run into issues authenticating your API key or initializing the SDK, refer to the Gemini Support Site for troubleshooting credentials.

---

Step 2: Designing the Database Schema

We need a database schema that stores both our raw content (so we can display the search results) and the corresponding high-dimensional vector embeddings.

Create a file named search_engine.py and write the initialisation code. We will use a standard SQLite table for our document metadata and a sqlite-vec virtual table for the high-speed vector index.

`python import os import sqlite3 import sqlite_vec import google.generativeai as genai from typing import List, Tuple

Configure Gemini client genai.configure(api_key=os.environ["GEMINI_API_KEY"])

DB_PATH = "notes_vault.db" EMBEDDING_DIM = 768 # Gemini's text-embedding-004 output dimension

def initialise_db(): conn = sqlite3.connect(DB_PATH) # Load the sqlite-vec extension conn.enable_load_extension(True) sqlite_vec.load(conn) cursor = conn.cursor() # Table for raw text and file paths cursor.execute(""" CREATE TABLE IF NOT EXISTS documents ( id INTEGER PRIMARY KEY AUTOINCREMENT, filepath TEXT UNIQUE, content TEXT, last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """) # Virtual table for vector storage using sqlite-vec # We store 768-dimension vectors using float precision cursor.execute(f""" CREATE VIRTUAL TABLE IF NOT EXISTS vec_documents USING vec_each( id INTEGER PRIMARY KEY, embedding float[{EMBEDDING_DIM}] ) """) conn.commit() return conn `

Note that sqlite-vec utilises virtual tables. When we write an embedding to vec_documents, we must ensure its row ID matches the primary key ID in the documents table.

---

Step 3: Extracting and Embedding Markdown Content

Markdown files can be massive, and feeding a 10,000-word journal entry into a single embedding vector dilutes the semantic resolution. We need to split our Markdown into logical chunks. For simplicity, we will split by headings, but you can customise this to split by paragraphs.

Let's write our parser and the embedding generator:

`python def chunk_markdown(filepath: str) -> List[str]: with open(filepath, 'r', encoding='utf-8') as f: content = f.read() # Split by major Markdown headings chunks = content.split("\n## ") processed_chunks = [] for i, chunk in enumerate(chunks): if not chunk.strip(): continue # Re-add the heading syntax if it wasn't the very first block prefix = "## " if i > 0 else "" processed_chunks.append(f"{prefix}{chunk.strip()}") return processed_chunks

def get_embedding(text: str) -> List[float]: response = genai.embed_content( model="models/text-embedding-004", content=text, task_type="retrieval_document" ) return response['embedding'] `

Now, let's write the indexing engine. This function will read your files, chunk them, get embeddings from Gemini, and insert them into our dual-table SQLite setup:

`python def index_file(conn, filepath: str): cursor = conn.cursor() chunks = chunk_markdown(filepath) for chunk in chunks: try: # Insert metadata first cursor.execute( "INSERT OR REPLACE INTO documents (filepath, content) VALUES (?, ?)", (filepath, chunk) ) row_id = cursor.lastrowid # Generate embedding vec = get_embedding(chunk) # Store vector with corresponding ID # sqlite-vec expects raw bytes for high-performance inserts import array serialized_vec = array.array('f', vec).tobytes() cursor.execute( "INSERT OR REPLACE INTO vec_documents (id, embedding) VALUES (?, ?)", (row_id, serialized_vec) ) except Exception as e: print(f"Error indexing chunk in {filepath}: {e}") conn.commit() `

---

Step 4: Building the Query Engine

To search our notes, we need to convert our search query into a vector using the exact same embedding model, and then execute a k-NN search against our virtual table.

Here is how we perform the vector match in SQL:

`python def semantic_search(conn, query: str, limit: int = 3) -> List[Tuple[str, str, float]]: # Generate query embedding using the query task type query_response = genai.embed_content( model="models/text-embedding-004", content=query, task_type="retrieval_query" ) query_vec = query_response['embedding'] import array serialized_query = array.array('f', query_vec).tobytes() cursor = conn.cursor() # SQLite-vec uses the 'vec_distance_cosine' function for similarity matching cursor.execute(""" SELECT d.filepath, d.content, vec_distance_cosine(v.embedding, ?) as distance FROM vec_documents v JOIN documents d ON v.id = d.id ORDER BY distance ASC LIMIT ? """, (serialized_query, limit)) return cursor.fetchall() `

---

Step 5: Packaging the CLI Interface

Let's wrap this in a neat, interactive CLI command using Python's sys.argv and rich for formatting. Add this block to the bottom of search_engine.py:

`python import sys from rich.console import Console from rich.panel import Panel

console = Console()

def main(): if len(sys.argv) < 2: console.print("[bold red]Usage:[/bold red] python search_engine.py [index <folder> | search \"your query\"]") return

action = sys.argv[1] conn = initialise_db()

if action == "index": folder = sys.argv[2] console.print(f"[bold green]Indexing all Markdown files in {folder}...[/bold green]") for root, _, files in os.walk(folder): for file in files: if file.endswith(".md") or file.endswith(".markdown"): full_path = os.path.join(root, file) index_file(conn, full_path) console.print(f"✓ Indexed {file}") console.print("[bold green]Indexing complete![/bold green]")

elif action == "search": query = sys.argv[2] console.print(f"[bold yellow]Searching for:[/bold yellow] '{query}'\n") results = semantic_search(conn, query) for i, (filepath, content, score) in enumerate(results): # Higher similarity = lower cosine distance similarity = 1.0 - score console.print(Panel( f"[bold cyan]{filepath}[/bold cyan] (Similarity: {similarity:.2%})\n\n{content[:300]}...", title=f"Result #{i+1}", border_style="dim" )) else: console.print("[bold red]Unknown command.[/bold red]")

if __name__ == "__main__": main() `

---

Testing Your New Local Search

To test your newly built engine, create a mock directory with a few markdown files, index them, and run your search:

`bash mkdir test_notes echo -e "# Datastore Issues\n\n## Database bug regarding thread pools\nWe discovered that when running under high concurrency, the PostgreSQL connection pool exhausts all available sockets and hangs the main thread process. Switch to Pydantic validation on input to prevent malformed connection strings." > test_notes/db_notes.md

Run the indexer python search_engine.py index test_notes

Search using natural language python search_engine.py search "connection issues on heavy load" ```

You will see the relevant chunk pulled directly from your local SQLite database without having run a single Docker container or spinning up a heavy vector microservice. Now you can easily search through your local archives at lightning speed, keeping your knowledge base safe, secure, and entirely under your control.