Build a semantic search with vector embeddings

Store OpenAI embeddings in a SQL Database table, index them for approximate nearest-neighbor search, and return the documents closest to a question.

Last updated

View as Markdown Agent setup

In this tutorial, you will build a semantic search that returns the documents closest in meaning to a question, over data stored in SQL Database. You will create a database, add a table with a vector column and an index over it, generate embeddings with OpenAI, store them, and query the table by similarity.

The artifact is a TypeScript function that runs on an Azion application. It reaches the database through the Azion SQL library and produces its embeddings with the LangChain OpenAI package.

Prerequisites

  • Node.js installed. The application and the azion library run on it.
  • The Azion CLI installed. For more information, refer to Azion CLI.
  • An Azion personal token. For more information, refer to How to manage a personal token.
  • SQL Database enabled on the account. The product is in Preview, and access is requested through the technical support team. To request it, refer to Technical Support.
  • An OpenAI API key. The embedding step calls the OpenAI API with it. To create one, refer to Create and export an API key.

1. Create the database

The database is the container the table lives in, and createDatabase creates it from the application code. To set the project up and create it:

Install the dependencies

In the application directory, install the azion library and the LangChain OpenAI package:

npm install azion @langchain/openai

Both packages are listed under the project dependencies.

Store the credentials

In the .env file, add the personal token and the OpenAI API key:

Shell
01AZION_TOKEN=[TOKEN VALUE]
02OPENAI_API_KEY=[OPENAI API KEY]

AZION_TOKEN authenticates the library, and OPENAI_API_KEY authenticates the embedding calls.

Create the database in main.ts

In main.ts, import what the function uses and create the database. createDatabase answers with data and error, and a failure carries its message in error:

TypeScript
01import { createDatabase, useExecute, useQuery } from 'azion/sql'
02import { OpenAIEmbeddings } from '@langchain/openai'
03
04export default async function vectorSearch() {
05 const { error: createError } = await createDatabase('vectorDatabase', { debug: true })
06
07 if (createError) {
08 return Response.json({ error: createError.message }, { status: 500 })
09 }

The account holds a database named vectorDatabase. Provisioning is asynchronous: the status reads creating first and reads created about 15 seconds later, and a statement runs against the database only after the status reads created.

2. Create the table and the vector index

The table holds the text of each document and its embedding. A vector lives in a column of its own, declared with a blob type that carries its dimension count: F32_BLOB(1536) stores 1,536 32-bit floating-point elements, which is the number of dimensions the text-embedding-3-small model returns.

The index makes a search consult an approximate-nearest-neighbor structure instead of every row. It is built over libsql_vector_idx(embedding, 'metric=cosine'), and the table it covers needs a ROWID or a single-column primary key, which id INTEGER PRIMARY KEY AUTOINCREMENT provides.

Continue in main.ts, and run both statements with useExecute, which carries the statements that create and write:

TypeScript
01 const setupStatements = [
02 `CREATE TABLE documents (
03 id INTEGER PRIMARY KEY AUTOINCREMENT,
04 content TEXT NOT NULL,
05 embedding F32_BLOB(1536)
06 );`,
07 `CREATE INDEX documents_idx ON documents (
08 libsql_vector_idx(embedding, 'metric=cosine')
09 );`
10 ]
11
12 const { error: setupError } = await useExecute('vectorDatabase', setupStatements)
13
14 if (setupError) {
15 return Response.json({ error: setupError.message }, { status: 500 })
16 }

The database holds a documents table and a documents_idx index. Creating a vector index also adds a shadow table named documents_idx_shadow, which getTables and the EdgeSQL Shell both list beside the table.

3. Generate and store the embeddings

The embedding model turns text into a vector. text-embedding-3-small returns 1,536 dimensions, the number the embedding column declares, and the same model produces the vectors stored here and the vector the search uses in stage 4.

Continue in main.ts. Build the model, embed each document with embedQuery, and wrap the result in vector('[...]'), which converts an array of numbers into the type the column stores:

TypeScript
01 const embeddings = new OpenAIEmbeddings({
02 model: 'text-embedding-3-small',
03 verbose: false,
04 apiKey: process.env.OPENAI_API_KEY
05 })
06
07 const documents = [
08 'Paris is the capital of France',
09 'The Eiffel Tower is a French landmark',
10 'London is the capital of England',
11 'Big Ben is located in London',
12 'Brasilia is the capital of Brazil',
13 'The Amazon rainforest is in Brazil',
14 'French cuisine is world-famous',
15 'Tea is popular in England',
16 'The English Channel separates Britain and France'
17 ]
18
19 const insertStatements = []
20 for (const doc of documents) {
21 const embedding = await embeddings.embedQuery(doc)
22 insertStatements.push(
23 `INSERT INTO documents (content, embedding) VALUES ('${doc}', vector('[${embedding}]'));`
24 )
25 }
26
27 const { error: insertError } = await useExecute('vectorDatabase', insertStatements, { debug: true })
28
29 if (insertError) {
30 return Response.json({ error: insertError.message }, { status: 500 })
31 }

The documents table holds one row per entry in the list, each with its text and its embedding, and the index covers those rows. vector('[...]') rejects a vector of more than 65,536 dimensions.

4. Query by similarity

A search embeds the question with the same model and asks the index for the nearest rows. vector_top_k takes the index name, the query vector, and the number of rows to return, and answers with an id column that the table joins on rowid:

SELECT content FROM vector_top_k('documents_idx', vector('[...]'), 2) JOIN documents ON documents.rowid = id;

Continue in main.ts, and run the statement with useQuery, which carries the statements that return rows:

TypeScript
01 const query = 'What is the capital of Brazil?'
02 const queryEmbedding = await embeddings.embedQuery(query)
03
04 const searchStatements = [
05 `SELECT content FROM vector_top_k('documents_idx', vector('[${queryEmbedding}]'), 2)
06 JOIN documents ON documents.rowid = id;`
07 ]
08
09 const { data: searchData, error: searchError } = await useQuery('vectorDatabase', searchStatements)
10
11 if (searchError) {
12 return Response.json({ error: searchError.message }, { status: 500 })
13 }
14
15 return Response.json({ data: searchData })
16}

The answer carries results, one entry per statement, each with its columns and its rows. A statement that fails does not fail the call: its message arrives in error and in the matching entry of results, which is why every operation above reads error before it reads data.

The function returns the two rows whose embeddings are nearest the question, and a failure anywhere in the chain returns a JSON message with status 500.

SQL Database also supports the LangChain Vector Store integration for document storage, and the LangChain Retriever for hybrid search that combines vector search with full-text search.

5. Run it

The function creates its own database, table, index, and rows on the first request. To run it locally:

Build the application

From the application directory, build it:

azion build

The build output is written to the project directory.

Start the local server

Serve the function from your machine:

azion dev

The function answers on the local address the command prints.

Send a request

Request that local address. The function creates the database, stores the embeddings, embeds What is the capital of Brazil?, and searches the index.

The response carries the documents whose embeddings are nearest the question, and the account holds the vectorDatabase database with the documents table and its rows. To search for something else, change the value of query and send the request again.

Next steps

Vector searchEvery vector type, every vector function, and how the index is built.Databases and queriesEvery field a database carries, the five operations, and the error codes.Query a database from a functionRead the same table from a function at run time.Best practicesHow to write statements and handle their errors before you put this in production.