Custom PHP Binaries with sqlite-vec SQLite Extension

I recently needed to add vector similarity search capabilities to a PHP application using SQLite. The sqlite-vec extension provides exactly this functionality, allowing you to store and query vector embeddings directly in SQLite. However, getting it to work with PHP turned out to be more complex than expected.

#The Challenge with SQLite Extensions in PHP

When you use SQLite from PHP, you may not be using a system-installed SQLite binary. Instead, often the SQLite library is compiled directly into the PHP binary itself. This means that if you want to use a SQLite extension like sqlite-vec, you can't simply install it system-wide and expect it to work.

Instead you have two options:

  1. Load the extension dynamically using PDO's loadExtension() method
  2. Compile the extension directly into your PHP binary

Unfortunately, many PHP distributions have loadExtension() disabled for security reasons. You can check if your PHP binary supports loading extensions by running the following command:

1php -d detect_unicode=0 -r '
2  $pdo = new PDO("sqlite::memory:");
3  var_dump(method_exists($pdo, "loadExtension"));
4'

If this returns true, you're in luck! You can simply grab a precompiled sqlite-vec build from the releases page and load it like this:

1$pdo = new PDO("sqlite::memory:");
2$pdo->loadExtension('/path/to/vec0.dylib');

But if you don't have loadExtension enabled (as is the case in many production PHP builds), you'll need to compile your own custom php binary.

#What is sqlite-vec?

Before diving into the compilation process, let's understand why you might want to add this extension in the first place. Sqlite-vec is a SQLite extension that brings vector similarity search capabilities to SQLite databases. It allows you to:

  • Store vector embeddings as a native SQLite data type
  • Perform fast similarity searches using various distance metrics
  • Build applications with semantic search, recommendation systems, and RAG (Retrieval-Augmented Generation) capabilities

This is particularly useful if you're working with AI models, embeddings from OpenAI, or building search features that go beyond simple keyword matching.

#Compiling sqlite-vec into PHP

Since we can't load the extension dynamically, we'll compile it directly into the PHP binary. For this task, I'm using static-php-cli, a powerful tool for building custom PHP binaries with specific extensions and configurations.

The process involves:

  1. Setting up the build environment
  2. Downloading the sqlite-vec source code
  3. Creating a patch script to integrate sqlite-vec into PHP's SQLite3 extension
  4. Building the custom PHP binary

Here's the complete build process for macOS on Apple Silicon. The general approach works on Linux and Windows too, though some specific commands will differ.

#1. Set Up Your Environment

First, install the necessary build tools:

1xcode-select --install
2brew install automake autoconf libtool cmake pkg-config gzip wget coreutils

Create a workspace and download static-php-cli:

1mkdir -p ~/sqlite-vec-php/{php-bin,static-php-cli}
2cd ~/sqlite-vec-php/static-php-cli
3curl -fsSL -o spc.tgz \
4  https://github.com/crazywhalecc/static-php-cli/releases/download/2.6.0/spc-macos-aarch64.tar.gz
5tar -xzf spc.tgz && rm spc.tgz
6chmod +x spc

#2. Create the Patch Script

The key to this process is a patch script that tells static-php-cli how to integrate sqlite-vec into the PHP build. Create a file called patch_sqlitevec.php within the static-php-cli directory:

 1<?php
 2// patch_sqlitevec.php – works with static-php-cli ≥ 2.0
 3use SPC\store\FileSystem as FS;
 4 
 5$vecDir = __DIR__ . '/sqlite-vec';           // where sqlite-vec.c/h live
 6$phpDir = SOURCE_PATH . '/php-src';          // root of extracted php-src
 7$sqliteExtDir = $phpDir . '/ext/sqlite3';
 8$sqliteDir = $phpDir . '/sqlite3';
 9 
10// 1 Create core_init.c manually
11if (patch_point() === 'after-php-extract') {
12    $coreInit = <<<C
13#define SQLITE_CORE 1
14#include "sqlite3.h"
15#include "sqlite-vec.h"
16#include <stdio.h>
17 
18int core_init(const char *dummy) {
19    return sqlite3_auto_extension((void *)sqlite3_vec_init);
20}
21C;
22 
23    file_put_contents($sqliteExtDir . '/core_init.c', $coreInit);
24    file_put_contents($sqliteExtDir . '/sqlite-vec.c', file_get_contents($vecDir . '/sqlite-vec.c'));
25    file_put_contents($sqliteExtDir . '/sqlite-vec.h', file_get_contents($vecDir . '/sqlite-vec.h'));
26}
27 
28// 2 Overwrite config0.m4 with working content
29if (patch_point() === 'before-php-configure') {
30    $cfg = $sqliteExtDir . '/config0.m4';
31    $workingM4 = <<<M4
32PHP_ARG_WITH([sqlite3],
33  [whether to enable the SQLite3 extension],
34  [AS_HELP_STRING([--without-sqlite3],
35    [Do not include SQLite3 support.])],
36  [yes])
37 
38if test \$PHP_SQLITE3 != "no"; then
39  PHP_SETUP_SQLITE([SQLITE3_SHARED_LIBADD])
40  AC_DEFINE([HAVE_SQLITE3], [1],
41    [Define to 1 if the PHP extension 'sqlite3' is available.])
42 
43  PHP_CHECK_LIBRARY([sqlite3], [sqlite3_errstr],
44    [AC_DEFINE([HAVE_SQLITE3_ERRSTR], [1],
45      [Define to 1 if SQLite library has the 'sqlite3_errstr' function.])],
46    [],
47    [\$SQLITE3_SHARED_LIBADD])
48 
49  PHP_CHECK_LIBRARY([sqlite3], [sqlite3_expanded_sql],
50    [AC_DEFINE([HAVE_SQLITE3_EXPANDED_SQL], [1],
51      [Define to 1 if SQLite library has the 'sqlite3_expanded_sql' function.])],
52    [],
53    [\$SQLITE3_SHARED_LIBADD])
54 
55  PHP_CHECK_LIBRARY([sqlite3], [sqlite3_load_extension],
56    [],
57    [AC_DEFINE([SQLITE_OMIT_LOAD_EXTENSION], [1],
58      [Define to 1 if SQLite library was compiled with the
59      SQLITE_OMIT_LOAD_EXTENSION and does not have the extension support with
60      the 'sqlite3_load_extension' function. For usage in the sqlite3 PHP
61      extension. See https://www.sqlite.org/compile.html.])],
62    [\$SQLITE3_SHARED_LIBADD])
63 
64  PHP_NEW_EXTENSION([sqlite3],
65    [sqlite3.c],
66    [\$ext_shared],,
67    [-DZEND_ENABLE_STATIC_TSRMLS_CACHE=1])
68  PHP_SUBST([SQLITE3_SHARED_LIBADD])
69fi
70 
71dnl ---- sqlite-vec static compile ----
72AC_MSG_NOTICE([Adding sqlite-vec.c + core_init.c to SQLite3 static library])
73AC_DEFINE([SQLITE_ENABLE_VEC0], [1], [Enable sqlite-vec virtual table])
74AC_DEFINE([SQLITE_CORE], [1], [Build sqlite-vec as builtin])
75PHP_ADD_SOURCES(ext/sqlite3, [sqlite-vec.c core_init.c])
76 
77PHP_SQLITE3_PRIVATE_SRCS="\$PHP_SQLITE3_PRIVATE_SRCS sqlite-vec.c"
78AC_DEFINE([SQLITE_VEC_ENABLE_NEON],1,[Enable NEON])
79AC_DEFINE([SQLITE_VEC_STATIC],1,[Static sqlite-vec])
80M4;
81 
82    file_put_contents($cfg, $workingM4);
83}
84 
85// 3 hook the extension in MINIT just before make
86if (patch_point() === 'before-php-make') {
87    $cfile = $phpDir . '/ext/sqlite3/sqlite3.c';
88    FS::replaceFileUser($cfile, function ($code) {
89        if (str_contains($code, 'sqlite3_vec_init')) return $code;      // already patched
90        $inject = "\n    extern int sqlite3_vec_init(sqlite3*,char**,const sqlite3_api_routines*);\n"
91                . "    sqlite3_auto_extension((void(*)(void))sqlite3_vec_init);\n";
92        return preg_replace('/PHP_MINIT_FUNCTION\\(sqlite3\\)\\s*\\{/', '$0' . $inject, $code, 1);
93    });
94}

#3. Configure Extensions and Libraries

Create configuration files for the PHP extensions and libraries you want to include:

1cd ~/sqlite-vec-php
2cat > php-extensions.txt <<'TXT'
3bcmath,bz2,ctype,curl,dom,fileinfo,filter,gd,iconv,mbstring,opcache,openssl,pdo,pdo_sqlite,phar,session,simplexml,sockets,sqlite3,tokenizer,xml,zip,zlib
4TXT
5 
6cat > php-libraries.txt <<'TXT'
7libjpeg,freetype,libwebp
8TXT

#4. Download Dependencies

Download PHP 8.4 and all required dependencies:

1cd ~/sqlite-vec-php/static-php-cli
2EXT=$(grep -v '^\s*#' ../php-extensions.txt | tr -d '\n ')
3LIB=$(grep -v '^\s*#' ../php-libraries.txt | tr -d '\n ')
4./spc download --with-php=8.4 --for-extensions "$EXT" --prefer-pre-built

#5. Get sqlite-vec Source

Download the sqlite-vec amalgamation build:

1curl -L -o sqlite-vec.zip \
2     https://github.com/asg017/sqlite-vec/releases/download/v0.1.7-alpha.2/sqlite-vec-0.1.7-alpha.2-amalgamation.zip
3unzip sqlite-vec.zip -d sqlite-vec

#6. Build Your Custom PHP Binary

Now for the main event - building PHP with sqlite-vec included:

1EXT=$(tr -d '\n ' < ../php-extensions.txt)
2LIB=$(tr -d '\n ' < ../php-libraries.txt)
3 
4./spc build --build-cli --build-fpm "$EXT" --with-libs "$LIB" \
5            -P patch_sqlitevec.php --debug

#7. Test Your New Binary

Once the build completes, test that sqlite-vec is working:

1buildroot/bin/php -r 'echo (new SQLite3(":memory:"))->querySingle("SELECT vec_version()"), PHP_EOL;'

If everything worked correctly, you should see the sqlite-vec version number printed to the console.

#Using sqlite-vec in Your PHP Application

With your custom PHP binary, you can now use sqlite-vec directly without any extension loading:

 1$vectorDb = new PDO('sqlite::memory:');
 2$vectorDb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 3 
 4// Create a table with vector column
 5$vectorDb->exec('CREATE TABLE embeddings (id INTEGER PRIMARY KEY, vec F32_BLOB(384))');
 6 
 7// Insert a vector
 8$vector = array_fill(0, 384, 0.1);
 9$blob = pack('f*', ...$vector);
10$stmt = $vectorDb->prepare('INSERT INTO embeddings (vec) VALUES (?)');
11$stmt->bindValue(1, $blob, PDO::PARAM_LOB);
12$stmt->execute();
13 
14// Query similar vectors
15$queryVector = array_fill(0, 384, 0.15);
16$queryBlob = pack('f*', ...$queryVector);
17$stmt = $vectorDb->prepare('SELECT id, vec_distance_l2(vec, ?) as distance FROM embeddings ORDER BY distance LIMIT 5');
18$stmt->bindValue(1, $queryBlob, PDO::PARAM_LOB);
19$stmt->execute();
20dd($stmt->fetchAll(PDO::FETCH_ASSOC));

#Packaging for NativePHP

If you're building this for use with NativePHP, you can package it like this:

1ROOT=~/sqlite-vec-php
2SPC=$ROOT/static-php-cli
3PKG=$ROOT/php-bin/bin/mac/arm64
4mkdir -p "$PKG"
5cp $SPC/buildroot/bin/php "$PKG/php"
6(cd "$PKG" && zip -q php-8.4.zip php && rm php)

#Conclusion

The sqlite-vec extension opens up exciting possibilities for PHP applications, from semantic search to recommendation systems. By following this guide, you can bring these vector search capabilities to your PHP projects without relying on external vector databases.

For more information about sqlite-vec capabilities and usage, check out the official documentation. And if you're interested in building custom PHP binaries for other purposes, the static-php-cli documentation is an excellent resource.