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).

Feb 19, 2025 - 08:18

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).