A Playground for SQL RDBMS, custom Ollama API Interaction with RAG on Timescale DB and download of query plus result

This is a submission for the Open Source AI Challenge with pgai and Ollama

What I Built

Main solution

A simple but complete webapp that facilitates development and testing of Ollama AI models through Timescale or other supported databases via a Retrieval Augmented Generation (RAG) widget for storing and retrieving embeddings generated by a specified Ollama model interacted with via its own widget in the webapp as well.

Extra benefits

It also doubles as a playground for thoroughly interacting with MySQL, PostgreSQL databases hosted on popular services like Neon.tech, Timescale with connection pooling support. All errors with queries and connections are caught and shown on the UI as a learning aid.

Perks

It can correctly receive and execute large volumes of SQL queries on connected databases as tested with the script for Timescale DB at https://app.chartdb.io/?ref=github_readme_2 under File > Import Database > PostgreSQL.

A encoding type of multipart/form-data along with a custom, efficient solution of less than 20 lines of code 😊 for parsing the request body when popular packages failed, did the trick. Check it out here: https://github.com/ogbotemi-2000/sqlUI_pinata/blob/main/utils.js#L11

Finally, it provides the option of saving a

{ 
  query: <RDBMS query>,
  response: <Result of query execution>

}

object as a direct download as a query-result.js local file or upload to IPFS via Pinata

UX Features

• Directly paste database connection string into input fields and configure web app immediately with the password protected.

• Switch immediately from MySQL to PostgreSQL database without hassle or storage of user data for uniqueness.

Demo

Hosted version

Here is a hosted version of the webapp.
https://sql-ui-pinata.vercel.app/

Remember to ollama pull <model>, ollama serve <model>, etc., before interacting with Ollama REST API via the web app

Local version

Similar to the hosted version but stores the provided database connection string locally and automatically bootstraps itself with it next time. Clone the repository

ogbotemi-2000 / sqlUI_pinata

A production-ready SQL UI for both MySQL and PostgreSQL databases with crash-proof error handling and support for uploading data to IPFS via Pinata

sqlUI_pinata

A production-ready SQL RDBMS (MySQL or PostgreSQL) UI webapp that accepts and stores database connection strings and executes queries, edited in a basic IDE by the user, in a crash-free manner whereby errors are intuitively shown to the user for correction or learning Databases can be changed on the fly by configuring them with the webapp, with support for connection pooling and support for uploading SQL queries and responses to the IPFS via Pinata

neon-starter-kit.mp4

Features

• A HTTP Node.js server with dynamic support for middlewares and routes to api/ endpoints
• Dynamic switching between MySQL and/or PostgreSQL databses without mixups as configured by the user anytime
• Compatibility with Vercel serverless environment
• Custom configuration that gets persisted until changed
• A user interface for complete interaction with database
• Option to enable pooled connection for database operations
• Thorough testing of database via queries, the server never crashes; it returns detailed errors instead
• Ready…

View on GitHub

Retrieval Augmented Generation Widget

The widget above receives its embedding from the Ollama API widget below before applying editable queries on the embedding and its associated metadata which is the JSON sent to the Ollama API in this case

Ollama REST API Interaction widget

Tools Used

pgvector

• Its functions are used in the SQL template queries provided by the RAG widget. Here is how it is used in a part of the backend

let { toSql }      = require('pgvector/pg'),
{ parseMultipart } = require('../utils'),
isMySQL;

module.exports = async function(request, response) {
  /** request.body is only undefined for enctype=multipart/form-data */
  let { local, pooled, query, setup, embedding, metadata } = request.body||=await new Promise(resolve=>{
    let buffer = [];
    request.on('data', chunk=>buffer.push(chunk)),
    request.on('end', function(data) {
      data = Buffer.concat(buffer).toString('utf-8'),
      resolve(parseMultipart(request, data))
    })
  }),
  config=fs.existsSync(file)&&require(file),
  stored = (config||{ }).CONNECTION_STRING, dB;

  embedding = toSql(embedding.replace(/\[|\]/g, '').split(','));

...

toSql is used to ensure that values in the embedding; either out of range or invalid are replaced with null.

• Using a more advanced toSql available in pgvector directly in the insert query templates for the RAG to either convert or pad vector embeddings to the length specified in the CREATE query ran prior.

Here is its schema below

toSql(embedding, vector_type i.e vector(float8), vector(int4) and vector(text), normalization options - l2_normalize or cosine_normalize, padding_value - eg 0, padding_length - 128, 256, 1536...)

And it is used as follows:

INSERT INTO data(metadata, toSql(embedding, vector(int4), l2_normalize, 0, 1536)) VALUES ($1, $2)

Here is alternative that uses a direct array_pad function in the INSERT query above if toSql triggers an error

INSERT INTO data (metadata, embedding)
VALUES (
    $1, 
    l2_normalize(
        (array_pad($2, 1536, 0))::vector(1536)
    )
);

The approach used at https://sql-ui-pinata.vercel.app is to create a database with an embedding column of variable length as follws:

CREATE TABLE IF NOT EXISTS data (id SERIAL PRIMARY KEY, metadata text, embedding vector);

Ollama

Created a minimalist but effective widget to make any call on the Ollama local REST API and specify options such as model, prompt, messages as well for dynamic generation of embeddings that are then used with customizable queries for RAG.

...
 fetch(url, {
    method: 'POST',
    body: meta
  }).then(res=>res.json()).then(res=>{
      result = res, flag&&(/*store embedding to be used sent to the server later*/account.embedding.value = embed = res.embedding)
  }).catch(err=>{result=err, flag=0})
  .finally(_=>{
    window.metadata.textContent = both.format((page.metadata = meta).slice(0, 100)+('...'.repeat(meta.length>100))),
    window.embedding.textContent = (page.embedding = embed).slice(0, 100)+('...'.repeat(embed.length>100))
...
})
...

pgai & pgvectorscale

Both pgai and pgvectorscale were not used directly however, the environment in which they can be used to create or use vector embeddings was created and assorted with widgets for quick and clear development with RAG and custom Ollama models, endpoints and prompts.

Final Thoughts

Lack of documentation on packages

1. Using pgvector.toSql in SQL queries has little information - code samples, use cases, and this waters down its usefulness, on how to use it to convert or pad vector embeddings to a given length on both Timescale and its Github repo.

Issues with downloading Ollama

• The link to download the Ollama software on their homepage redirects to a githubusercontent domain after which the connection terminates. I was unable to download and run Ollama locally but I managed to learn about its API and built a widget for interacting with Ollama API, it may be bug-free but is untested. ### Early compromise
• I was going to create a chatbot that generates and refines SQL queries for vector embeddings before OpenAI put up a paywall that I couldn't jump over and had to compromise.

Curious results

• Curiously pgvector is not present in the result of the SQL query below but vector is, are the same?

SELECT * FROM pg_available_extensions;

Running the CREATE query below leads to the error below because of this

"could not open extension control file "/usr/share/postgresql/16/extension/pgvector.control": No such file or directory"

CREATE EXTENSION IF NOT EXISTS pgvector;

Prize Categories

Since I built a platform for generating embeddings from Ollama's API and storing them as vector embeddings, this submission qualifies for all, particularly

1. pgvector
2. Ollama, among others

Given that vector embeddings are much larger, byte for byte, than the original text they were generated from (i.e "Hello World" generated a vector embedding of length 4096), working with them requires a database service that handles large volumes well and considers chronology such as Timescale along with its packages for practical development and testing of LLMS.

Thanks to Timescale for such an opportunity to learn the nitty-gritty of what I used to call AI magic.