
From Splitgraph's inception back in 2018, we have always been a Postgres-first company. Our serverless SQL API, `sgr`, the Splitgraph Data Delivery Network, the managed data platform &mdash; PostgreSQL and its rich ecosystem of extensions has been at the core of our products.

This is why we could not pass up the opportunity to join one of the top contributors to PostgreSQL. We are pleased to announce that Splitgraph has been acquired by [EDB](https://www.enterprisedb.com). The senior leadership at EDB is excited about our experience in OLAP and the modern data stack and believe that we will be a big part of their plans to grow the company.

From all of us at Splitgraph, thanks for being part of our journey these past five years. We can't wait to share more as we continue it under EDB's flag.

<center><ButtonLink href="https://www.enterprisedb.com/news/edb-acquires-splitgraph">Read the press release on EDB's website</ButtonLink></center>
<br/>


Splitgraph is joining EDB, the leader in accelerating Postgres in the enterprise.

EDB acquires Splitgraph

We built a ChatGPT plugin for querying Splitgraph without writing SQL. Along the way, we discovered a better way to write LLM-powered applications.

How we built a ChatGPT plugin for Splitgraph


Seafowl has supported UDFs [for some time now](https://www.splitgraph.com/blog/seafowl-wasm-udfs), and they continue to offer an exciting way for Seafowl users to significantly extend functionality in a fully sandboxed environment. Recently, in response to user request, we added a Golang example.

<ButtonLink href="https://github.com/splitgraph/seafowl-udf-go/">
  See the code
</ButtonLink>

<br />

[See the UDF docs](https://seafowl.io/docs/guides/custom-udf-wasm).

To learn more about compiling Golang to WASM, read on.

## Handling UDF requirements
There are certain requirements incumbent on the UDF implementer when it comes to running UDFs in Seafowl, and Golang is included. Today we cover a partial list:
- reading data in from the host through linear raw memory
- returning the computed result as a msgpack-encoded buffer
- working around Golang default syntax (e.g. TitleCase function exports)
- compiling Golang into WASM

## Reading data in
As discussed previously, when you register a Seafowl UDF it's required to [specify certain parameters](https://github.com/splitgraph/seafowl-udf-go/blob/2e49dd7a110213a7c49b19845cd537a59164c034/create_udf.sh#L11-L16) including e.g. `wasm_export` (e.g. addi64), `return_type` (e.g. BIGINT), `input_types` (e.g. BIGINT).

This is important because raw memory is the medium for passing data between Seafowl and the UDF. By specifying the parameter types upfront at UDF registration time, both Seafowl host and UDF know how to properly encode/decode the data they share with each other. That's why the function [`addi64`](https://github.com/splitgraph/seafowl-udf-go/blob/master/main.go#L12), expects a _pointer to a buffer_ vs conventional Go function params.

## Writing data out
Conversely, when we return the result to Seafowl, we need to return a buffer that includes the length of the result [first](https://github.com/splitgraph/seafowl-udf-go/blob/2e49dd7a110213a7c49b19845cd537a59164c034/udf.go#L115), with the actual msgpack-encoded result appended [afterwards](https://github.com/splitgraph/seafowl-udf-go/blob/2e49dd7a110213a7c49b19845cd537a59164c034/udf.go#L14).

This allows Seafowl's query engine to return the result of the UDF in the typical way.

## TitleCase function export workaround
Golang conventionally uses TitleCase to indicate when a function should be exported from its module scope. Some may find it [cute](https://youtu.be/M3BM9TB-8yA?t=879) and this convention is presumably fine in traditional Golang, but in certain contexts like UDFs, we are required to provide `alloc`/`dealloc` functions _in lower case_ - which poses a fun hurdle.

Fortunately, while researching how `tinygo` works, I learned it supports annotating functions regardless of casing via
`//export $functionName`

For example:
```go
//export alloc
func alloc(size uintptr) unsafe.Pointer {
	...
}
```

And Seafowl will be able to call this function in the required way.

## Compiling Golang into WASM
In August 2023 the Golang team shipped WASI/WASM [support](https://go.dev/blog/go1.21#a-new-port-to-wasi), so the following approach we used may no longer be strictly necessary these days.

When we tackled this project, Golang did not yet support WASM natively so looking elsewhere, we found [Tinygo](https://tinygo.org/docs/concepts/faq/what-is-tinygo/).

It turns out we can generate a WASM module ready for import into Seafowl via:
`tinygo build -o seafowl-udf-go.wasm -target=wasi`

NOTE: it's necessary to include `-target=wasi`.

## Recap

In this post we reviewed some of the steps taken to port the [Rust-based UDF](https://github.com/splitgraph/seafowl-udf-rust) into Golang. This was my first Golang project and I appreciated the opportunity to work at the lower level, learn about byteslice manipulation, msgpack serialization, WASI and more.

If you build a cool UDF, please consider letting us know!


#### Image credits
<ValeIgnore>[liantomtit](https://unsplash.com/@liantomtit), [evanescentlight](https://unsplash.com/@evanescentlight)</ValeIgnore>


We return to the wide world of WASM. In our previous round we implemented a UDF using Rust; today we're porting it to Golang.

Writing UDFs in Golang

[The Splitgraph Console](https://www.splitgraph.com/splitgraph/domestic_us_flights/-/console)
now analyzes the SQL query on the fly and displays the referenced tables in the
sidebar for easier query comprehension. The feature is powered by
[tree-sitter](https://tree-sitter.github.io/tree-sitter/) running in WASM.

<ButtonLink href="https://www.splitgraph.com/query?sqlQuery=SELECT%0A++++%22EmployeeId%22%2C%0A++++%22LastName%22%2C%0A++++%22FirstName%22%2C%0A++++%22Title%22%2C%0A++++%22ReportsTo%22%2C%0A++++%22BirthDate%22%2C%0A++++%22HireDate%22%2C%0A++++%22Address%22%2C%0A++++%22City%22%2C%0A++++%22State%22%2C%0A++++%22Country%22%2C%0A++++%22PostalCode%22%2C%0A++++%22Phone%22%2C%0A++++%22Fax%22%2C%0A++++%22Email%22%0AFROM%0A++++%22demodemo123%2Fchinook-from-sqlite%3A0df8bfcc3c837a68d20be83830af20781f6f6d4515ee0da60b06cc9850c31918%22.%22Employee%22%0ALIMIT+100%3B&id=006102fd-c5ef-47e7-ada3-40a7040006d6&flavor=splitgraph&initial-namespace=demodemo123">
  Try it out live
</ButtonLink>

<br />

Interested in how we got it working? Read on!

[The Splitgraph Console](https://www.splitgraph.com/splitgraph/domestic_us_flights/-/console)
offers an IDE-like experience for interacting with Splitgraph and the data it
exposes. One fundamental feature is
[the query editor](https://www.splitgraph.com/query?sqlQuery=SELECT%0A++++%22EmployeeId%22%2C%0A++++%22LastName%22%2C%0A++++%22FirstName%22%2C%0A++++%22Title%22%2C%0A++++%22ReportsTo%22%2C%0A++++%22BirthDate%22%2C%0A++++%22HireDate%22%2C%0A++++%22Address%22%2C%0A++++%22City%22%2C%0A++++%22State%22%2C%0A++++%22Country%22%2C%0A++++%22PostalCode%22%2C%0A++++%22Phone%22%2C%0A++++%22Fax%22%2C%0A++++%22Email%22%0AFROM%0A++++%22demodemo123%2Fchinook-from-sqlite%3A0df8bfcc3c837a68d20be83830af20781f6f6d4515ee0da60b06cc9850c31918%22.%22Employee%22%0ALIMIT+100%3B&id=006102fd-c5ef-47e7-ada3-40a7040006d6&flavor=splitgraph&initial-namespace=demodemo123).
It resembles a code editor and is used for editing SQL (PostgreSQL, in fact)
queries, rather than files.

The query editor has smart features like:

- autocomplete

  We use [`pgcli`](https://pypi.org/project/pgcli/) on the backend to retrieve
  the completions.

- syntax highlighting

  We use
  [the SQL Monaco language configuration](https://github.com/microsoft/monaco-editor/blob/38e1e3d097f84e336c311d071a9ffb5191d4ffd1/src/basic-languages/sql/sql.ts),
  which uses
  [the Monarch library](https://microsoft.github.io/monaco-editor/monarch.html)
  under the hood.

One interesting feature we wanted to have was to introspect the query and
display the tables it references. This would let the user quickly understand and
interact with a subset of repositories and tables, rather than all repositories
and tables within some namespace.

We implemented this feature using
[tree-sitter](https://tree-sitter.github.io/tree-sitter/) compiled to WASM
([web-tree-sitter](https://github.com/tree-sitter/tree-sitter/tree/master/lib/binding_web))
so the parsing happens within the user's browser. The rest of this article
outlines the problems faced and the decisions we took to get the feature
working.

## Parsing in the browser or on the server

The first question we had to answer was whether the parsing process should
happen in the browser, like our syntax highlighting, or on the server, like our
autocomplete.

There are pros and cons to both approaches:

- parsing in the browser has a higher initial cost (fetching and initializing
  the parser), but offers faster feedback during editing, and transfers less
  data during long editing sessions

- the browser can only execute JavaScript or WASM. The parser would have to be
  compiled to either of these languages. This limits our choices, as we cannot
  run arbitrary existing parsers.

- either the browser or the server have to do more work to parse the query. For
  in-browser parsing, this could be slow for low-end devices. For server
  parsing, we could DDoS our servers by having many users ask for parsing of
  their queries.

At this point, either approach would work. We had a slight preference for doing
in-browser parsing for its lower latency and being able to use the parsed AST
for more features in the future.

## Existing SQL parsers

Parsing SQL is a popular problem which has many solutions in various languages.

### libpg_query and its bindings

[libpg_query](https://github.com/pganalyze/libpg_query) is a C library for
parsing PostgreSQL. It uses the same code that the PostgreSQL server uses. This
makes the parsing the most accurate and spec-compliant.

There are bindings for using libpg_query in other languages:

- [pgsql-parser](https://github.com/pyramation/pgsql-parser) for Node.js
- [pg-query-emscripten](https://github.com/pganalyze/pg-query-emscripten) for
  WASM (available in the browser)
- [pg_query.rs](https://github.com/pganalyze/pg_query.rs) for Rust

  Trivia: this is what
  [Supabase's postgres_lsp](https://github.com/supabase/postgres_lsp) uses to
  parse pgSQL.

#### The importance of syntax error recovery

One major downside behind using any library based on `libpg_query` is that it
has no syntax error recovery when the query contains syntax errors. If the
parser encounters an error, it does not attempt to parse the rest of the code.
It all comes down to
[libpg_query having a single `error` field per parse tree](https://github.com/pganalyze/libpg_query/blob/9b21e3295402a0d0ee9a50c468d426c2dbb73ee6/src/pg_query_internal.h#L14).

This makes it unsuitable for usage in code editors. When editing, most of the
time the code has a syntax error, because the user is still typing the word, or
is editing some more complex statement.

For example, given the following query:

```sql
SELECT
    "gid",
    "state"
FROM
    "splitgraph/election-geodata:79724cbf5dcd2b260ac0d60e58135123d527ff4d2e1dc709f6da47fa8f4ee71f"."nation"
LIMIT 100;
```

When the user wants to select one more column in a `SELECT` statement:

```sql
SELECT
    "gid",
    ,
    -- look, a syntax error. There cannot be a trailing comma above
    "state"
FROM
    "splitgraph/election-geodata:79724cbf5dcd2b260ac0d60e58135123d527ff4d2e1dc709f6da47fa8f4ee71f"."nation"
LIMIT 100;
```

we should still be able to parse the query and know it references
[splitgraph/election-geodata](https://www.splitgraph.com/splitgraph/election-geodata/-/console)
despite the query having a syntax error.

This makes syntax error recovery a must-have for our parser.

### sql-parser from sql-language-server

[sql-parser](https://github.com/joe-re/sql-language-server/tree/release/packages/sql-parser)
is a JavaScript parser implemented using [Peggy](https://peggyjs.org/) (a
JavaScript parser generator). It is part of the
[sql-language-server](https://github.com/joe-re/sql-language-server).

Lack of syntax error recovery disqualifies this library. It also did not support
using schemas in table names (e.g. `"splitgraph/election-geodata"."nation"` was
considered a syntax error, but `"splitgraph/election-geodata"` was not).

### tree-sitter

[tree-sitter](https://tree-sitter.github.io/tree-sitter/) is a parser generator.
The generated parsers include syntax error recovery. When there is a syntax
error, only a few AST nodes get marked as `ERROR`, but the rest of the code is
parsed like normal. It also supports incremental parsing. This means doing a
re-parse of the code after editing it is more efficient than parsing from
scratch if only we provide tree-sitter the previous AST and the description of
the modified range of code.

Both of these qualities make tree-sitter well-suited for code editors. In fact,
it is used in [neovim](https://neovim.io/doc/user/treesitter.html),
[Atom](https://neovim.io/doc/user/treesitter.html),
[Emacs](https://lists.gnu.org/archive/html/emacs-devel/2022-11/msg01443.html),
and [Helix](https://helix-editor.com/), to name a few. Tree-sitter also powers
basic IDE operations in web versions of VSCode (like <https://vscode.dev/>)
thanks to
[the vscode-anycode extension](https://github.com/microsoft/vscode-anycode). It
also powers parsing of bash in
[the bash-language-server](https://github.com/bash-lsp/bash-language-server/blob/f2c7447929bd0ab20f37ec5a44719430aa111d14/server/src/analyser.ts#L74).

See
[Introduction to tree-sitter](https://teknologiumum.com/posts/introductory-to-treesitter)
for a more detailed description of what tree-sitter can do.

Tree-sitter has bindings in many languages. The ones relevant to us are:

- [web-tree-sitter](https://github.com/tree-sitter/tree-sitter/tree/master/lib/binding_web)
  for WASM,
- [node-tree-sitter](https://github.com/tree-sitter/node-tree-sitter) for
  Node.js.

As for the grammar,
[tree-sitter-sql](https://github.com/DerekStride/tree-sitter-sql/) is a
well-maintained SQL grammar used in Neovim. In our testing, it successfully
parsed the pgSQL we threw at it, and the parts it stumbled on were fixed in just
a handful of days.

All of the above led us to decide tree-sitter is the right choice for parsing
pgSQL in the Console.

## Extracting referenced tables from the query

Tree-sitter parses the query into an AST.
[The corpus tests of the grammar](https://github.com/DerekStride/tree-sitter-sql/blob/main/test/corpus/select.txt)
show the stringified AST node types. Now the challenge becomes extracting the
referenced tables from that AST.

Neovim's `:InspectTree` command is extremely useful at understanding the AST
while interactively editing code.

![Neovim with the source query in the left window and the parsed AST in the
right window. The `relation` AST node is selected in the AST window and the
table reference is highlighted in the query.](/static-v2/images/blog/parsing-pgsql-with-tree-sitter/tree-sitter-inspect-tree-parsed-ast.png)

Here we can also see the AST parsed from a query that has a syntax error.



![Neovim with the source query in the left window and the parsed AST in the
right window. There is a syntax error in the query, but the parsed AST still contains a `relation`.](/static-v2/images/blog/parsing-pgsql-with-tree-sitter/tree-sitter-inspect-tree-error-recovery.png)



Tree-sitter also supports
[declaratively querying the AST](https://tree-sitter.github.io/tree-sitter/using-parsers#pattern-matching-with-queries)
using queries written as
[S-expressions](https://en.wikipedia.org/wiki/S-expression). It takes away the
burden of walking the AST and looking for subtrees that match a certain shape.
We ended up with the following query to capture the referenced tables:

```txt
(
  relation (
    (
      object_reference
      schema: (identifier)
      name: (identifier)
    ) @reference
  )
)
```



![Neovim with the source query in the left window, the parsed AST in the
right-top window, and the tree-sitter query playground in the right-bottom. The matched `relation` AST node and source code is highlighted.](/static-v2/images/blog/parsing-pgsql-with-tree-sitter/tree-sitter-playground-relation-query.png)



This query ended up capturing the table reference we care about.

### Extracting parts of a table reference

Tree-sitter gives us back the table reference as a string like
`"splitgraph/election-geodata:79724cbf5dcd2b260ac0d60e58135123d527ff4d2e1dc709f6da47fa8f4ee71f"."nation"`.
Now it is our job to extract the namespace, repository name, image tag, and the
table name from this reference.

This felt like a good job for a regular expression. We ended up with the
following one and wrote a bunch of test cases to make sure it also tolerates
errors (e.g. a missing table name at the end when the user is still typing it):

```ts
const tableReferenceRegExp = /^"((?<namespace>[^/]+)?)(\/((?<repositoryName>[^":]+)?)(:((?<imageTag>[^"]+)?))?"?(\."?((?<tableName>[^"]+)?)"?)?)?$/;
```

It uses
[named capturing groups](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Regular_expressions/Named_capturing_group)
to make extracting the reference parts easier.

## Result

Tying these pieces together, the Splitgraph Console query editor now supports
displaying referenced tables in the sidebar as a tree. It updates nearly
instantly. We added a debounce to avoid updating the UI so often.

![Splitgraph Console query editor with referenced tables shown in the sidebar.](/static-v2/images/blog/parsing-pgsql-with-tree-sitter/splitgraph-console-with-referenced-tables.png)

We found that the tree-sitter parsing process usually takes less than 1ms on a
laptop. Using 6x CPU throttling in Chrome still leads to a usable UI.

<video width="100%" height="360" controls>
  <source
    src="/static-v2/demo-videos/query-editor-parsing-referenced-tables.mp4"
    type="video/mp4"
  />
</video>

## Check it out

Boost your SQL writing experience with live query introspection.

<ButtonLink href="https://www.splitgraph.com/query?sqlQuery=SELECT%0A++++%22EmployeeId%22%2C%0A++++%22LastName%22%2C%0A++++%22FirstName%22%2C%0A++++%22Title%22%2C%0A++++%22ReportsTo%22%2C%0A++++%22BirthDate%22%2C%0A++++%22HireDate%22%2C%0A++++%22Address%22%2C%0A++++%22City%22%2C%0A++++%22State%22%2C%0A++++%22Country%22%2C%0A++++%22PostalCode%22%2C%0A++++%22Phone%22%2C%0A++++%22Fax%22%2C%0A++++%22Email%22%0AFROM%0A++++%22demodemo123%2Fchinook-from-sqlite%3A0df8bfcc3c837a68d20be83830af20781f6f6d4515ee0da60b06cc9850c31918%22.%22Employee%22%0ALIMIT+100%3B&id=006102fd-c5ef-47e7-ada3-40a7040006d6&flavor=splitgraph&initial-namespace=demodemo123">
  Try it now
</ButtonLink>

<br />

---

## Bonus: supabase/postgres_lsp discussion

As we were working on integrating tree-sitter in the Splitgraph Console,
Supabase was working on the
[postgres_lsp](https://github.com/supabase/postgres_lsp), a language server for
PostgreSQL.

Creating a language server involves much more work than just the parser. It can
provide diagnostics, autocomplete, semantic highlighting, code actions, "Go to
definition", and
[many other features](https://microsoft.github.io/language-server-protocol/specifications/lsp/3.17/specification/#languageFeatures).

The parser is still at the core of a language server. Omitting error recovery
from the parser used in a language server can lead to frustrating experience for
the users.

For example, with semantic highlighting, the user will have a jarring experience
if the query stops being highlighted each other keystroke due to intermittent
parsing errors.

Autocomplete also is problematic to get working when suggestions are only
available when the query is valid. Most of the time the user will request
suggestions for a half-written query, which, most likely, is not a valid query
yet. If the server does not implement syntax error recovery during parsing, it
will keep yielding empty or wrong suggestions.

TypeScript server (`tsserver`) which is used in the TypeScript Language Server,
has advanced syntax error recovery which offers stellar TypeScript authoring
experience. Syntax error recovery is heavily integrated into the parser, for
example:

- [nodes are annotated when surrounding nodes have errors](https://github.com/microsoft/TypeScript/blob/b4cd9d8fdd1a3a9a8324069a7a6f4336538aa688/src/compiler/parser.ts#L1578),
- [missing nodes are created when there are errors in the AST](https://github.com/microsoft/TypeScript/blob/b4cd9d8fdd1a3a9a8324069a7a6f4336538aa688/src/compiler/parser.ts#L2582),
- [parsing functions are aware whether the parser is in "error recovery" mode](https://github.com/microsoft/TypeScript/blob/b4cd9d8fdd1a3a9a8324069a7a6f4336538aa688/src/compiler/parser.ts#L2785)
  and adjust their behavior.

At the time of writing this article (2023-08-09), postgres_lsp employs some
measures to handle syntax errors returned by
[pg_query.rs](https://github.com/pganalyze/pg_query.rs)
([source](https://github.com/supabase/postgres_lsp/blob/1250f5ed14a0e86b2b7fa581214284c67b960621/crates/parser/src/statement.rs#L123)).
Their LSP is still at a very early stage and only supports semantic highlighting
(and keeps crashing quite often), which makes it hard to assess. We are very
interested to see if using a parser with no syntax error recovery as a base will
be a viable solution.

That said, we are extremely excited that there is development in this space.

We used tree-sitter to show the tables referenced in the query inside the Splitgraph Console.

Parsing pgSQL with tree-sitter in WASM

A discussion of approaches to keep Apollo cache consistent with the API data after invoking GraphQL mutations.

Keeping Apollo Cache up-to-date after mutations


## Introduction

It seems like ChatGPT and similar large language models are appearing everywhere these days, from [web search](https://blogs.bing.com/search/march_2023/Confirmed-the-new-Bing-runs-on-OpenAI%E2%80%99s-GPT-4) to [coding](https://github.com/features/copilot), even in the [public sector](https://www.bloomberg.com/news/articles/2023-06-07/microsoft-offers-powerful-openai-technology-to-us-government-cloud-customers?leadSource=uverify%20wall).
One reason language models are so appealing is their ability to succinctly summarize and answer questions about large amounts of information such as <ValeIgnore>Wikipedia</ValeIgnore> articles.

That got us thinking: what if we could use a language model to answer questions about data in the [Splitgraph DDN](/docs/query/ddn)?
With over 40,000 public repositories, the DDN has a wealth of information available to anyone who can write SQL queries.
It turns out OpenAI's GPT is surprisingly good at writing SQL.
Does this mean Splitgraph can be used conversationally instead of writing queries?
The answer is yes, with a few important caveats.
We successfully built a proof-of-concept ([available on GitHub](https://github.com/splitgraph/splitgraph-llm-demo)), which can answer questions such as "How many public libraries operate in Chicago?" by prompting GPT to generate SQL queries which are then executed on Splitgraph.
Read on for the details!

## Introducing LangChain

In isolation, language models are only capable of generating or completing text.
In order to interact with an end user the way ChatGPT does or to use external data sources like <ValeIgnore>Bing's</ValeIgnore> web search index, OpenAI's GPT must be integrated with other systems.

The open source [LangChain](https://python.langchain.com/en/latest/) project aims to simplify the development of language-model powered applications.
Using LangChain, developers create pipelines (called "chains") which send text prompts to — and receive generated text from — language models.

We're not the first to try getting an AI to write SQL queries for us: LangChain includes an "SQL Chain" specifically designed for this purpose.
The [SQL Chain demo](https://python.langchain.com/en/latest/modules/chains/examples/sqlite.html) shows GPT is pretty good at figuring out which
tables to use in their query and composing a query to answer the original question in the prompt. The question is can it scale to meet Splitgraph's needs?

## The context is the bottleneck

**GPT** originally stands for [Generative Pre-trained Transformer](https://en.wikipedia.org/wiki/Generative_pre-trained_transformer).
Without diving into too much detail, transformers are a type of artificial neural network which process their entire input all at once.
Since their introduction in a [2017 paper by Google](https://arxiv.org/abs/1706.03762), transformers have taken the world by storm.

But processing all input "at once" would require arbitrarily large neural nets for arbitrarily large input.
For this reason, there is an upper bound on how large the input can be, called the maximum context length.

In order to process longer documents, they must first be segmented into text fragments which stay within this size limit.
The language model can operate on a single fragment at any time.
The LangChain SQL demo uses a database containing about a dozen tables. The entire database schema fits into the LLM's
prompt without reaching the maximum context length, meaning a single text fragment is enough!

With 40,000 repositories, each potentially containing several tables, there is no way to fit the DDN's contents into OpenAI's 4097 word (technically token) context.

Fortunately, having a huge collection of documents doesn't rule out using language models, after all Google or Microsoft integrated their entire web search engine indexes.

## Retrieval-Augmented Generation to the rescue!

[Retrieval-Augmented Generation](https://simonwillison.net/2023/Jan/13/semantic-search-answers/) is a technique for using LLMs with large collections
of documents. It uses a two-step process to answer a user's question:

1. First, it finds the documents most relevant to the original question.
2. Next, it generates an LLM prompt which includes the original question along with the selected documents (or their summary).

The trick is that the document search in step 1 doesn't require all the documents (or their summaries) to fit into the LLM's context.
Instead, search is performed by computing the vector similarity of the **embeddings** for the prompt and each document.

## Embeddings

An [embedding](https://writings.stephenwolfram.com/2023/02/what-is-chatgpt-doing-and-why-does-it-work/#the-concept-of-embeddings) is a vector of floating point numbers
generated by the transformer for a given input. The input could be a single token or a longer text as long as it's not longer than the maximum context length.

OpenAI's models use 1536 dimensional embeddings. In a sense, an embedding is like a hash function which computes
a list of floats for any text. Inputs whose computed embedding vectors are close together generally belong to text referring to similar concepts.

There are several vector databases which can be used to efficiently find the closest document embeddings to a given prompt's embedding.
Fortunately, we don't have to replace SQL, as the <ValeIgnore>[pgvector](https://github.com/pgvector/pgvector)</ValeIgnore> extension turns PostgreSQL into a vector database.

Needless to say, to compute the vector distance of document embeddings, we need to know the embeddings for each document -or rather each document fragment of maximum context size.
So prior to querying the set of all repositories, they must be indexed.

## Indexing repositories

The [indexing code](https://github.com/splitgraph/splitgraph-llm-demo/blob/main/index_repos.py) is fairly simple. Using the GraphQL API used by the Splitgraph frontend,
it's easy to get the data for each repository in a namespace. Since the README's for each repository use Markdown,
the entire description of the repository including the table schemas is generated in Markdown as well. LangChain has [document loaders](https://python.langchain.com/en/latest/modules/indexes/document_loaders.html) for lots of different text formats
including Markdown.

## Querying
OpenAI recommends using [cosine similarity](https://en.wikipedia.org/wiki/Cosine_similarity) when comparing embeddings, which `pgvector` supports with the `<=>` operator which is applicable to the vector type.
Once GPT generates an SQL query, it must be executed on the DDN.
Splitgraph implements the PostgreSQL network protocol, but some of PostgreSQL's introspection tables aren't available.
Normally, LangChain would read the database schema from these tables, but —lacking these tables— the [demo code](https://github.com/splitgraph/splitgraph-llm-demo/blob/main/langchain_demo/splitgraph_db.py#L93) uses the table schema returned by the GraphQL API instead.

## Results
We only tried querying repositories from one namespace, <ValeIgnore>[cityofchicago](https://www.splitgraph.com/cityofchicago)</ValeIgnore> during testing.
Occasionally, the SQL query returned by GPT can be invalid, the prompt can grow beyond the maximum context size for tables with lots of columns.
Still, overall, the generated queries are surprisingly good. Some examples:

```bash
> python3 query.py cityofchicago 'Which residential neighborhood of Chicago is the most expensive?'
SQL query generated by LLM:
  SELECT
    location_address,
    zip_code,
    units,
    property_name,
    property_type,
    community_area,
    address,
    community_area_number
  FROM
    "cityofchicago/affordable-rental-housing-developments-s6ha-ppgi"."affordable_rental_housing_developments"
  ORDER BY
    units ASC
  LIMIT
    5;
```

```bash
> python3 query.py cityofchicago 'How many public libraries operate in Chicago?'
SQL query generated by LLM:
  SELECT
    COUNT(*)
  FROM
    "cityofchicago/libraries-2020-circulation-by-location-v5qm-a67f"."libraries_2020_circulation_by_location"
```

One interesting thing about LLMs is that —depending on the temperature setting— their output can differ significantly
even if the input doesn't change.

## Conclusion

Large language models are capable of performing many tasks which were previously hard to automate —including the translation of natural language questions into valid SQL queries.
The LLM-generated SQL isn't always perfect, but it works surprisingly well surprisingly often.

Check out the [code on GitHub](https://github.com/splitgraph/splitgraph-llm-demo).
If you're excited about using Splitgraph with LLMs, [let us know](mailto:hello@splitgraph.com?subject=splitgraph%20llms)!


Follow along as we build a GPT-powered bot capable of answering natural language questions by finding relevant Splitgraph repositories and querying them via automatically generated SQL.

Building a GPT-powered agent to answer questions using data from Splitgraph


## Introduction

[Seafowl](https://seafowl.io) is a new analytics database based on [DataFusion](https://arrow.apache.org/datafusion/) and [delta-rs](https://github.com/delta-io/delta-rs), designed for running "at the edge" and serving queries over HTTP with cache-optimized responses. Because it separates storage from compute, it's an ideal candidate for running in "serverless" environments. In this post, we'll explore the architecture behind Seafowl and demonstrate how to deploy it to Google Cloud Run (GCR) as a true "scale-to-zero" serverless database that doesn't require any persistent compute resources, and can query data stored in Google Cloud Storage (GCS), while minimizing cold-start times.

## Why do we need a serverless database?

The advantage of serverless applications is that each instance of an application can execute in a region close to the user requesting it, without the need to provision a server in every region ahead of time. But there's a catch: for most use cases, the application is bottlenecked by the database, which _does_ require provisioning a persistent server ahead of time. This can mean sacrificing the main benefit of serverless, as every cache miss needs to query a single master database in an arbitrary region, regardless of how close to the user the HTTP server is executing.

Seafowl solves this problem for analytical workloads, by accepting certain tradeoffs that might be less acceptable for transactional databases, in exchange for fast and cacheable responses to read queries. For example, as an OLAP database, Seafowl assumes that data is loaded into it periodically, and it's generally okay to return stale data while loading new data into it.

## Why Seafowl?

Seafowl separates compute and storage of the data it queries. It makes data in [object storage](https://seafowl.io/docs/guides/scaling-multiple-nodes#postgresql-as-a-catalog-minio-as-s3-storage) queryable by a Docker [image](https://hub.docker.com/r/splitgraph/seafowl) ([binary](https://github.com/splitgraph/seafowl/releases) also available), and maintains its internal catalog by writing to a SQLite file. This makes it easy to deploy as a serverless function to any platform that can run Docker images, like Google Cloud Run[^2]. With Seafowl you can expect a fast initialization time (e.g. 10ms) and configurable dependencies keep you in the driver's seat for deciding which tradeoffs make sense for your situation.

Another perk: because Seafowl offers a [CDN-friendly HTTP story](https://seafowl.io/docs/guides/querying-cache-cdn), if your data is suitable for caching your end users will likely further benefit if you choose to front with e.g. CloudFlare or similar CDN providers.

## What's the challenge?

The challenge is avoiding cold starts, while also avoiding the need for a persistent node for storing metadata. The Seafowl architecture is divided into three parts: compute (e.g. the binary, deployed in e.g. a Docker image); storage (the blob data in Object Storage); and a catalog (the metadata and information schema for the database). Compute and storage map naturally to services like GCR and GCS. But for the catalog, it's less clear. Seafowl supports writing catalog metadata to either a Postgres database, or a SQLite file. A Postgres catalog supports multiple writers, making each Seafowl instance able to act as a reader and a writer. This comes at the cost of requiring a persistent node (options like Neon[^1] exist, but in our testing, the latency was unacceptable as it caused cold starts of multiple seconds). A SQLite catalog can be bundled with the image, but at the cost of being able to actually persist Seafowl writes.


Is there a way we can get the best of both worlds? With GCS FUSE, we can come close. That's what we'll explore in this post. We're going to deploy Seafowl to GCR, and mount its catalog as a SQLite "file" on the FUSE filesystem backed by GCS. The end result is a serverless database that can respond to read queries from any region, with start times on the order of 10ms.

## Goal
In today's post we show you how to stand up serverless Seafowl on [Cloud Run](https://cloud.google.com/run).

GCP offers an "always free" tier which may cover both your [storage](https://cloud.google.com/free/docs/.free-cloud-features#storage) and [compute](https://cloud.google.com/free/docs/free-cloud-features#cloud-run) needs. This HOWTO seeks to stay within those limits, so you are likely to have low or possibly zero costs. Either way, this guide sequesters all resources we create here into a separate GCP project for easy cleanup.

## Requirements

- Access to [GCP](https://console.cloud.google.com/getting-started) (e.g. a Google account[^3])
- The `gcloud` CLI (aka Cloud SDK. [Need it?](https://cloud.google.com/sdk/docs/install-sdk))
- Choosing a unique-to-GCP
  - project name (`$PROJECT_NAME`)
  - bucket name (`$BUCKET_NAME`)

Ready? Let's begin!

1. After installing `gcloud`, login to your Google account.
```shell
gcloud auth login
```

2. Create a new project (optional, suggested).
```shell
gcloud projects create $PROJECT_NAME
Create in progress for [https://cloudresourcemanager.googleapis.com/v1/projects/seafowl-gcsfuse].
Waiting for [operations/cp.6073745129322302465] to finish...done.
Enabling service [cloudapis.googleapis.com] on project [seafowl-gcsfuse]...
Operation "operations/acat.p2-124549755242-c3e8de2b-a6fa-4a73-aaa1-ad949f26e7cb" finished successfully.
```
ℹ️ In case you get an "already in use" error, please choose a unique name. Consider appending some randomness and use `export PROJECT_NAME=YourName` to refer to it through the rest of the commands).

3. Set your project and region

Creating resources in the same region gives best performance as well as helps avoid cross-regional networking charges.
```shell
gcloud config set project $PROJECT_NAME
Updated property [core/project].
```

```shell
gcloud config set run/region $REGION # e.g. us-east1
Updated property [run/region].
```

4. Create a bucket

This bucket will be mounted as a pseudo-filesystem inside Seafowl's container.
```shell
gsutil mb -l us-east1 gs://$BUCKET_NAME
Creating gs://$BUCKET_NAME/...
```
ℹ️ Cloud Storage bucket names _share a global namespace_. Similarly to the project name, in case you encounter e.g. "The requested bucket name is not available", please pick a [unique name](https://cloud.google.com/storage/docs/buckets#naming) and `export BUCKET_NAME=MyBucket`.

5. Setup up Seafowl's config + credentials
- Seafowl can be configured via `seafowl.toml`. We persist it in GCP's [Secret Manager](https://console.cloud.google.com/security/secret-manager) so it can be mounted in our function.
```
[object_store]
type = "gcs"
bucket = "$BUCKET_NAME"
google_application_credentials = "seafowl-data/seafowl-gcsfuse.json"
[catalog]
type = "sqlite"
dsn = "seafowl-data/seafowl.sqlite"
[frontend.http]
bind_host = "0.0.0.0"
write_access = "efe507120a88bdb1b7a2e0479bdc4c163bc31c2d83787240dbccd8b95115d92a"
```
Replace with your real bucket name, then save the above config as `seafowl.toml`, and then run:

```shell
gcloud secrets create --data-file seafowl.toml seafowl_toml
```
(Allow the API to be enabled, if it asks)

- Upload the credential file into the bucket via:
```shell
gcloud storage cp seafowl-gcsfuse.json gs://$BUCKET_NAME/seafowl-gcsfuse.json
Copying file://seafowl-gcsfuse.json to gs://$BUCKET_NAME/seafowl-gcsfuse.json
  Completed files 1/1 | 2.3kiB/2.3kiB
```
NOTE: The [Secret Manager](https://console.cloud.google.com/security/secret-manager) is also suitable for this file.
In the interest of keeping a lid on costs we only save one secret. By mounting `seafowl-gcsfuse.json` via GCS Fuse, it's provided to Seafowl before it starts.

7. Establish least privilege permissions
We create a service account so that minimum needed permissions are sequestered into their own identity.
```shell
gcloud iam service-accounts create seafowl-gcsfuse-identity
```
Add a binding for object storage and accessing secrets.
```
gcloud projects add-iam-policy-binding $PROJECT_NAME \
     --member "serviceAccount:seafowl-gcsfuse-identity@$PROJECT_NAME.iam.gserviceaccount.com" \
     --role "roles/storage.objectAdmin" \
     --role="roles/secretmanager.secretAccessor"
Updated IAM policy for project [$PROJECT_NAME].
bindings:
- members:
  - user:user@example.com
  role: roles/owner
- members:
  - serviceAccount:seafowl-gcsfuse-identity@$PROJECT_NAME.iam.gserviceaccount.com
  role: roles/storage.objectAdmin
etag: BwX8Ze-ob4Y=
version: 1
```

8. Almost done! Now we can deploy the function:
```shell
gcloud run deploy seafowl-gcsfuse \
  --image splitgraph/seafowl-gcsfuse:latest \
  --execution-environment gen2 \
  --allow-unauthenticated \
  --service-account seafowl-gcsfuse-identity \
  --update-secrets=/app/config/seafowl.toml=seafowl_toml:latest \
  --update-env-vars BUCKET=$BUCKET_NAME

API [run.googleapis.com] not enabled on project [814934642383]. Would you like to enable and retry (this will take a few minutes)? (y/N)?  y

Enabling service [run.googleapis.com] on project [814934642383]...
```
[Dockerfile](https://github.com/splitgraph/seafowl-gcsfuse/blob/master/Dockerfile) available.

8. Check it with `curl`

Just swap your endpoint for the sample one.

### Read
```shell
curl -i -H "Content-Type: application/json" \
-X POST "https://seafowl-gcsfuse-YourEndpointHere.a.run.app/q" -d@- <<EOF
{"query": "
SELECT now()
"}
EOF
```
Should return something like:
```shell
HTTP/2 200
content-type: application/octet-stream
vary: Content-Type, Origin, X-Seafowl-Query
x-cloud-trace-context: 2f378466d69425e60793db49b4f406a4
date: Fri, 19 May 2023 14:57:37 GMT
server: Google Frontend
content-length: 43

{"now()":"2023-05-24T14:57:37.809652290Z"}
```

### Write
```shell
curl -i -H "Content-Type: application/json" \
-H "Authorization: Bearer Fc8yA8SZ0On70pg7znAu7s6EEHnDgUzP" \
-X POST "https://seafowl-gcsfuse-YourEndpointHere.a.run.app/q" -d@- <<EOF
{"query": "
CREATE TABLE holditright (id int)
"}
EOF
```

Should return
```shell
HTTP/2 200
content-type: application/octet-stream
vary: Content-Type, Origin, X-Seafowl-Query
x-cloud-trace-context: 8f75d4895dac499dbeef7eabd6fdfcba;o=1
date: Fri, 19 May 2023 16:36:09 GMT
server: Google Frontend
content-length: 0
```

### Verify
If you look at your bucket using the web console, you should see something roughly similar to this:
![Bucket](/static-v2/seafowl/blog/deploying-serverless-seafowl/bucket-example.jpg)

### Tear down
The function you just deployed is likely to be within the always free tier, but in case you'd like to clean up the project, run:
```
gcloud run services delete $SERVICE_NAME # e.g. seafowl-gcsfuse
gcloud config set project $PROJECT_NAME
gcloud config set run/region $REGION
```
Here's how to delete the [bucket](https://cloud.google.com/storage/docs/deleting-bucket) and the [service account](https://cloud.google.com/iam/docs/service-accounts-delete-undelete?hl=en#deleting).


## Conclusion
Congrats! 🎉 You have a scale to zero, web (HTTP)-first analytical database, deployed to the edge.
Ready to load up with whatever data you want, and ready to be fronted by a CDN.

![Low cloud bills are tasty](/static-v2/seafowl/blog/deploying-serverless-seafowl/rainbow-unsplash.com-@jorgefdezsalas.jpeg)


## Optional Follow Up
Two bonus ideas for consideration:
- For public & production deployments, a common next step is controlling who can write vs read. Consider deploying two functions: read-only for public access and write-enabled which is only available to trusted parties. Setting read-only can be done by providing [env var](https://seafowl.io/docs/reference/seafowl-toml-configuration#using-environment-variables) `SEAFOWL__FRONTEND__HTTP__WRITE_ACCESS=off` and `RUN_SEAFOWL_READ_ONLY=true` [info](https://github.com/splitgraph/seafowl-gcsfuse/blob/21376efa28c6ab7b7198e5394c3657b9d78002a1/gcsfuse_run.sh#L11).
- If your data is public, and your users would benefit from better latency, consider [fronting the Cloud Run endpoint with CloudFlare](https://seafowl.io/docs/getting-started/tutorial-fly-io/part-4-2-cdn-with-cloudflare). The CloudFlare CDN offers a convenient and powerful way to cache query results.


#### Image credits
[nikkotations](https://unsplash.com/@nikkotations), [jorgefdezsalas](https://unsplash.com/@jorgefdezsalas)

[^1]:
    More details [here](https://neon.tech/blog/quicker-serverless-postgres)

[^2]:
    Not your preferred cloud provider? Let us know if you'd like to see a [Lambda](https://aws.amazon.com/lambda/) or [Container Instance](https://azure.microsoft.com/en-us/products/container-instances) version of this post.

[^3]:
    This is roughly what you should see if you visit the [GCS console](https://console.cloud.google.com/) for the first time.

    <img src="/static-v2/seafowl/blog/deploying-serverless-seafowl/setup-gcs.png" />



Learn how to combine Seafowl with GCS FUSE to achieve true scale to zero. Serve users at the edge with a web (HTTP)-first analytical database that works on GCP Cloud Run, including within the "always free" tier.

Splitgraph has been acquired by EDB! Read the blog post.

EDB acquires Splitgraph

How we built a ChatGPT plugin for Splitgraph

Writing UDFs in Golang

Parsing pgSQL with tree-sitter in WASM

Keeping Apollo Cache up-to-date after mutations

Building a GPT-powered agent to answer questions using data from Splitgraph

Deploying a serverless Seafowl DB to Google Cloud Run using GCS FUSE and SQLite

Product

Support

Company

Splitgraph

Splitgraph has been acquired by EDB! Read the blog post.

EDB acquires Splitgraph

How we built a ChatGPT plugin for Splitgraph

Writing UDFs in Golang

Parsing pgSQL with tree-sitter in WASM

Keeping Apollo Cache up-to-date after mutations

Building a GPT-powered agent to answer questions using data from Splitgraph

Deploying a serverless Seafowl DB to Google Cloud Run using GCS FUSE and SQLite

Product

Support

Company

Community

Splitgraph