How to Build a Vector Database with SQLite in laravel for LLM's

Building a Vector Database with SQLite in Laravel for LLMs on Windows requires configuring SQLite with vector support (via sqlite-vss or pgvector alternative), integrating it with Laravel, and using it for vector search. Here’s a step-by-step guide:

Step 1: Install SQLite with Vector Support
SQLite doesn’t natively support vector storage, but you can enable it using the sqlite-vss extension.

1.1 Download SQLite with Vector Support
Download the latest SQLite3 binary from SQLite’s official site.
Download the sqlite-vss extension from here.

1.2 Enable the sqlite-vss Extension
Place sqlite-vss.dll (Windows) or .so (Linux/macOS) in your project directory.
Load the extension in Laravel by modifying your database configuration.

Step 2: Configure Laravel to Use SQLite
Open the .env file and set up SQLite:

DB_CONNECTION=sqlite
DB_DATABASE=database/database.sqlite

Create the SQLite database file:

mkdir database
touch database/database.sqlite

Ensure config/database.php has SQLite configured:

'sqlite' => [
    'driver' => 'sqlite',
    'database' => env('DB_DATABASE', database_path('database.sqlite')),
    'prefix' => '',
    'foreign_key_constraints' => true,
],

Step 3: Create a Migration for Vector Storage
Create a migration for storing vector embeddings:

php artisan make:migration create_embeddings_table

Edit the migration file:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CreateEmbeddingsTable extends Migration
{
    public function up()
    {
        Schema::create('embeddings', function (Blueprint $table) {
            $table->id();
            $table->string('text');
            $table->json('embedding'); // Store vector as JSON
            $table->timestamps();
        });

        // Load the SQLite extension
        DB::statement("SELECT load_extension('sqlite-vss')");

        // Create a vector index for fast search
        DB::statement("CREATE VIRTUAL TABLE vss_index USING vss(embedding(1536))"); // Example: OpenAI embedding size 1536
    }

    public function down()
    {
        Schema::dropIfExists('embeddings');
    }
}

Run the migration:

php artisan migrate

Step 4: Insert and Query Vector Data

4.1 Insert Vector Data

Modify Embedding.php model:

use Illuminate\Database\Eloquent\Model;

class Embedding extends Model
{
    protected $fillable = ['text', 'embedding'];
    protected $casts = ['embedding' => 'array']; // Convert JSON to array
}

Store embeddings in Laravel:

use App\Models\Embedding;

Embedding::create([
    'text' => 'Hello world',
    'embedding' => json_encode([0.1, 0.2, 0.3, ..., 0.9]) // Example vector
]);

Step 5: Perform Vector Search

To perform a similarity search:

use Illuminate\Support\Facades\DB;

$vector = json_encode([0.1, 0.2, 0.3, ..., 0.9]); // Example query vector

$result = DB::select("SELECT text, vss_distance(embedding, ?) as distance FROM embeddings ORDER BY distance LIMIT 5", [$vector]);

return response()->json($result);

Bonus: Generate Vectors using OpenAI

If you're working with LLM embeddings (like OpenAI), install openai-php:

composer require openai-php/client

Then, generate embeddings:

use OpenAI\Client;

$client = new Client('your-openai-api-key');

$response = $client->embeddings()->create([
    'model' => 'text-embedding-ada-002',
    'input' => 'Hello world',
]);

$embedding = $response->json()['data'][0]['embedding'];

Embedding::create([
    'text' => 'Hello world',
    'embedding' => json_encode($embedding)
]);

Conclusion
Now you have a working Vector Database in SQLite inside Laravel, supporting vector storage and similarity search—ideal for LLM applications like retrieval-augmented generation (RAG).