![hero](/static-v2/images/blog/select-from-the-browser/hero.jpg)

## Serverless SQL Queries

Frontend developers who write SQL have traditionally been required to sequester
their SQL queries to a backend server. The popular
[adage](https://stackoverflow.com/a/60332563/2044952) has more or less been
'don't run SQL directly from the frontend.'

For Splitgraph users, it turns out the old rules no longer apply. Thanks to the
[DDN HTTP API](https://www.splitgraph.com/docs/query/ddn-http), the browser can
work directly with raw SQL queries. In this post we demonstrate by building a
client-side only dashboard with taxi ride data.

## In a nutshell

Today we build a 100% client-side JavaScript app. We will:
- download official NYC taxi data and publish it to a Splitgraph repo
- write some SQL to roll up taxi zone activity across a 24h period
- clone and run a SPA to present taxi activity on an interactive map of NYC

## Dashboard goal: NYC Covid recovery followup
Whereas we previously focused on
[subway rides](./data-driven-app-powered-by-splitgraph), cab rides
are also a familiar sight to New Yorkers. Rendering taxi activity on a map of
NYC may give us additional indicators into how the Covid recovery is going.

I assume you have a Splitgraph account
([need one?](https://www.splitgraph.com/auth/sign_up)), Node.js, and the
[sgr](https://www.splitgraph.com/docs/sgr-advanced/getting-started/installation#installation-1)
CLI available.

## Download the data
Taxi ride datasets are published at
[nyc.gov](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page) on a
monthly basis. April is the most recent month available at the time of writing,
so let's download yellow cab records for April, across 2020, 2021, and 2022. It
looks something like this:

![April 2022](/static-v2/images/blog/select-from-the-browser/download.jpg)

Note: as of May 2022 nyc.gov started publishing this data in Parquet format, but
we need CSV. Thus please convert however you prefer.

<details>
<summary style={{display: "list-item"}}>parquet2csv.py helper (optional)</summary>

A [script](https://github.com/splitgraph/nyctaxis/master/) is available. It
depends on `pandas` and `fastparquet`.

```shell
pip install pandas fastparquet
```

</details>

However you make your CSVs once they're ready, please upload them via the `sgr` CLI.

```shell
$ sgr cloud upload $yourSplitgraphUser/nyctaxi yellow_tripdata_2020-04.csv yellow_tripdata_2021-04.csv yellow_tripdata_2022-04.csv
```
![Upload](/static-v2/images/blog/select-from-the-browser/upload.jpg)

Once the CSVs have been ingested your repo should look like this:
![Table](/static-v2/images/blog/select-from-the-browser/table.jpg)

Download and publish taxi data to Splitgraph: ✅

## SQL rollup

To populate the taxi zones with a 24 hour sample, let's
[roll up](https://github.com/splitgraph/nyctaxis/master/ddn-fetcher.ts#L6) all taxi
pickups and drop-offs that occurred during the given day.

<details>
<summary style={{display: "list-item"}}>
Want to see the query?
</summary>

<EmbeddedQuery
 minHeight={600}
 query={`WITH
pickups AS
(
SELECT
 "PULocationID" as location_id,
 COUNT("PULocationID") as pickup_count
FROM "paws/nyctaxi"."yellow_tripdata_2022-04"
WHERE
 tpep_pickup_datetime >= '2022-04-01'
AND
 tpep_pickup_datetime <= '2022-04-01'::date + INTERVAL '24 HOURS'
GROUP BY "PULocationID"
),
dropoffs AS
(
SELECT
 "DOLocationID" as location_id,
 COUNT("DOLocationID") as dropoff_count
FROM "paws/nyctaxi"."yellow_tripdata_2022-04"
WHERE
 tpep_dropoff_datetime >= '2022-04-01'
AND
 tpep_dropoff_datetime <= '2022-04-01'::date + INTERVAL '24 HOURS'
GROUP BY "DOLocationID"
),
joined AS
(
SELECT
 pickups.location_id, pickup_count, dropoff_count
FROM
 pickups LEFT JOIN dropoffs ON pickups.location_id = dropoffs.location_id
)
SELECT * from joined`}
 layout="hsplit"
/>
</details>

Roll up taxi activity: ✅

## Clone the React app

The [repo](https://www.github.com/splitgraph/nyctaxis) is available on GitHub.

<details>
<summary style={{display: "list-item"}}>
Want to step through the code tag by tag?
</summary>

- [Initial CRA](https://github.com/splitgraph/nyctaxis/tree/setup-cra) Tag: `setup-cra`
- [Add taxi zone data](https://github.com/splitgraph/nyctaxis/tree/taxizone-data) Tag: `taxizone-data`
- [Handle fetching + mapping](https://github.com/splitgraph/nyctaxis/tree/fetch-and-map) Tag: `fetch-and-map`

</details>

Since we use `mapbox-gl` for the interactive map, get yourself
a free API key at [mapbox.com](https://www.mapbox.com/) if you need one.

Changing the date and clicking 'load' will cause new data to be fetched and
rendered on the app.

Clone and run the SPA: ✅

## Conclusion
The DDN HTTP API offers a convenient way to move beyond the old rules of the
road, and let frontend developers run queries from their end user's browser.

Thanks for reading and keep following the Splitgraph blog for more posts.

##### Image credit
[Eduards Ceravs](https://flic.kr/p/b67zX6)

How Splitgraph's DDN HTTP API lets you run SQL queries directly from the browser, opening new possiblities for client-side data-driven apps.

SELECT directly from the browser

## Introduction
Data-driven apps like interactive dashboards offer a valuable way for users to more easily draw individual conclusions backed by data. However, delivering those benefits may require significant cross-discipline engineering skills: backend, frontend, data ingestion/pipeline, DevOps and potentially infra to run all of it.

In this post we illustrate how Splitgraph helps lower barriers for data-driven apps by addressing key pain points. The Splitgraph DDN integrates smoothly with anything that speaks Postgres, and oftentimes reduces the need to stand up and maintain infra.

Today we will:
- upload a CSV to a Splitgraph repo
- use SQL to query the repo via the DDN
- use Python to parameterize the queries and do basic aggregations
- tie it all together with [Streamlit](https://streamlit.io) in a rich web UI

## Dashboard idea: How is NYC's Covid recovery going?
To answer this question one might consider various year-over-year data, e.g. real estate [occupancy rates](https://papers.ssrn.com/sol3/papers.cfm?abstract_id=4124698), sales tax [revenue](https://comptroller.nyc.gov/reports/annual-state-of-the-citys-economy-and-finances/), or [Broadway](https://www.broadwayleague.com/research/grosses-broadway-nyc) ticket sales.

How about public transit? NYC has one of the world's largest metro systems, and it turns out turnstile activity is regularly [published](http://web.mta.info/developers/turnstile.html) by the MTA. While not every New Yorker uses the subway, many consider it vital, and such data could serve as a reasonable indicator of whether city life is back to pre-pandemic levels (spoiler: or not, as it turns out).

OK, we've got a dashboard goal! Let's plot data for NYC's most popular subway stations, by month, across 2020, 2021, and 2022.

I assume you have a Splitgraph account ([need one?](https://www.splitgraph.com/auth/sign_up)) and Python 3.x installed.

## Enter Splitgraph
<details>
<summary style={{display: "list-item"}}>Just want to see the code?</summary>

A self-contained [repo](https://www.github.com/splitgraph/nyc-subway-dashboard) is available on GitHub.

Each step has been tagged:
- [Connect to DDN](https://github.com/splitgraph/nyc-subway-dashboard/tree/sql-querying) Tag: `sql-querying`
- [Render the first graph via Streamlit](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/first-graph) Tag: `first-graph`
- [Year-over-year graphs](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/year-over-year-graphs) Tag: `year-over-year-graphs`

</details>


#### Upload the CSV
A [totals rollup CSV](https://static.observableusercontent.com/files/ad48776aa4a65719ef8b57810eb825aad469ee25f5b216efce578bfa69a6df47664590d2a58927ab0a32eac164428a5c750e88669aacdc4f1d4d22c3dbb5f51d?response-content-disposition=attachment%3Bfilename*%3DUTF-8%27%27mta_all_points%25402.csv) is a good starting point and what we're going to use. Go ahead and download it.


With the CSV in hand, you can drag it here (yes, right into this blog post):
<EmbeddedUpload />

If you prefer the [CLI](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-upload) that is also an option. A [previous blogpost](https://splitgraph.com/blog/csv-to-sql-upload-to-data-delivery-network-ddn) covers uploading in more detail.

![Drag and drop the CSV](/static-v2/images/blog/data-driven-app/upload-step-1.png)
_Drag and drop the CSV_

![I went with "nyc-transit" for a repo name](/static-v2/images/blog/data-driven-app/upload-step-2.png)
_I went with "nyc-transit" for a repo name_

![Repo page view](/static-v2/images/blog/data-driven-app/upload-step-3.png)
_Repo page view_

Upload a CSV to a Splitgraph repo: ✅

#### Let's query it!
With the repo now hosting the data, let's query it using SQL.

Here's a query for January 2020 turnstile activity:
<EmbeddedQuery
 query={`SELECT "START_DATE", "STATION", SUM("TOTAL")
FROM "paws/nyc-transit"."turnstile-activity"
WHERE to_date("START_DATE", 'MM/DD/YYYY') BETWEEN '1/1/2022' AND '2/1/2022'
GROUP BY "START_DATE", "STATION"`}
 layout="hsplit"
/>


#### DDN Time
For querying the repo in our app, we'll use the Splitgraph DDN. This feature makes repos conveniently available via `postgresql://data.splitgraph.com:5432/ddn`, and most Postgres clients should work. You will need a Splitgraph API key and secret to access the DDN; generate those by going to your [account settings](https://www.splitgraph.com/settings/sql-credentials) and set them aside.

The following example snippet will connect a Python app to the DDN. Swap user & password for the creds you just made.

```python
conn = psycopg2.connect(
 user = "myuser",
 password = "supersecret",
 host = "data.splitgraph.com"
 port = "5432"
 database = "ddn"
)
```

If you prefer you can check out a working example repo at this step. [Source](https://github.com/splitgraph/nyc-subway-dashboard/tree/sql-querying) - just [add the creds](https://docs.streamlit.io/streamlit-cloud/get-started/deploy-an-app/connect-to-data-sources/secrets-management) to `.streamlit/secrets.toml`

Use SQL to query the repo: ✅

## Enter Streamlit
<details>
<summary style={{display: "list-item"}}>Prefer an empty slate?</summary>

Streamlit has an [example](https://github.com/streamlit/streamlit-example) repo ready to clone/fork.

</details>

## Handling aggregations and data cleanup with Python
We have all this data available to work with from the DDN. Now, let's write some Python to clean it up and prepare it for plotting.
Let's do a simple aggregation so we can pass it into a Pandas DataFrame.


```python
data = {"DATE": [], "TOTAL": []}
for k, v in totals.items():
 data['DATE'].append(k)
 data['TOTAL'].append(v)

source = pd.DataFrame.from_dict(data)
line_chart = alt.Chart(source).mark_line().encode(
 alt.X('DATE:T', title='Time'),
 alt.Y('TOTAL:Q', title='Weekly rides'),
 ).properties(title="MTA total weekly turnstile activity (Jan 2020-May 2022)")
```
The [rest of the code](https://github.com/splitgraph/nyc-subway-dashboard/blob/master/streamlit_app.py#L45) is available.

Use Python to do basic aggregations: ✅


## How to integrate Splitgraph with Streamlit
Streamlit lets Python developers write interactive dashboards, with sliders, inputs, and graphs, all without having to write a single line of JavaScript.

For example `st.slider()` will render a slider on the page whose value is available as a variable.
```python
month = st.slider("Choose a month (Jan = 1, Feb = 2, etc.)", min_value=1, max_value=5)
```
Consider seeing the [tag](https://github.com/splitgraph/nyc-subway-dashboard/releases/tag/year-over-year-graphs) for a fleshed out example.

Tie it all together with Streamlit: ✅

## Conclusion
Is NYC back to business as usual? Well, take a look for yourself:
![January activity, year-over-year](/static-v2/images/blog/data-driven-app/jan-year-over-year.png)

How about April?
![April activity, year-over-year](/static-v2/images/blog/data-driven-app/apr-year-over-year.png)

In sum, while we can see popular stations are getting more traffic than the early days of lockdown, so far turnstile activity in 2022 hovers around 50% of the same month in 2020.

The actual [dashboard](https://share.streamlit.io/splitgraph/nyc-subway-dashboard) has mouseover with specific #s.

## Recap
We started with a simple dataset, uploaded it to a Splitgraph repo so we could run SQL queries.
Then, by writing some basic Python, we powered a mini dashboard by utilizing Streamlit's rich UI features.
We didn't need to stand up any new infra, and our users are empowered to draw their own conclusions.

## Shoutouts & Acknowledgements
Thank you MTA for regularly publishing [turnstile data](http://web.mta.info/developers/turnstile.html)

Thank you [@je2538](https://observablehq.com/@je2538/bouncing-back-where-nyc-subway-riders-have-returned-and-whe), which is how I learned that the MTA offers this, and who (I believe) rolled up the data in a suitable format for this exercise.

Thank you [Streamlit](https://streamlit.io/about) for making it easier to serve a rich client UI from Python.

We demonstrate how combining Splitgraph and Streamlit lets devs and data scientists more easily build data-driven apps. In this example we plot NYC subway turnstile data to try and glean how NYC's Covid recovery is going.

Building a data-driven app with Splitgraph and Streamlit

While waiting for/sitting on various types of transportation during the recent
spike in demand for travel, I killed time by solving
[Sudoku](https://en.wikipedia.org/wiki/Sudoku) puzzles.

A Sudoku puzzle has a simple premise. A 9✕9 grid (split into 3✕3 squares) has to
be filled with numbers from 1 to 9 such that each column, row and square only
contains one of each digit.

After solving about a dozen boards, I had an idea. Sudoku is a classic
constraint satisfaction problem. Solving it with a computer has been done many
times before, including with something as unorthodox as
[SQL](https://wiki.postgresql.org/wiki/Sudoku_solver) (please don't run this on
Splitgraph).

I had a more disgusting method in mind.

Package managers like Yarn/Poetry/Cargo do version range and conflict resolution
when generating a lockfile. In essence, they are designed for solving constraint
satisfaction problems.

So, is it possible to get a dependency resolver to solve Sudoku for me?

Turns out, it is. I put the code up
[on GitHub](https://github.com/mildbyte/poetry-sudoku-solver) and what follows
is the explanation of how it works. This is using
[Poetry](https://python-poetry.org/), a package and dependency manager for
Python projects.

## Encoding the board constraints

First, we need to encode the rules of Sudoku in such a way that a dependency
resolver can understand them.

We can represent each Sudoku board cell as a Python package with the name
`sudoku-cell{row}{col}`. Each package has 9 versions `{value}.0.0`,
corresponding to the value of that cell. Since in Python, a resolved dependency
tree only has one version of each package, this means that every cell can only
have one value, which is what we're after.

In addition, every package also has dependencies on other "cell" packages, with
version constraints encoding which values those "cells" can have.

For example, version 3.0.0 of the `sudoku-cell25` package represents an
assertion that the cell at row 2, column 5 of the board has the number 3 in it.
The `pyproject.toml` for this package has a list of all "cell" packages in the
same row, column or a 3x3 square as this cell:

```toml
[tool.poetry.dependencies]
python = "^3.6"
sudoku-cell14 = "!= 3.0.0"
sudoku-cell15 = "!= 3.0.0"
sudoku-cell16 = "!= 3.0.0"
sudoku-cell21 = "!= 3.0.0"
sudoku-cell22 = "!= 3.0.0"
sudoku-cell23 = "!= 3.0.0"
sudoku-cell24 = "!= 3.0.0"
sudoku-cell26 = "!= 3.0.0"
sudoku-cell27 = "!= 3.0.0"
sudoku-cell28 = "!= 3.0.0"
sudoku-cell29 = "!= 3.0.0"
sudoku-cell34 = "!= 3.0.0"
sudoku-cell35 = "!= 3.0.0"
sudoku-cell36 = "!= 3.0.0"
sudoku-cell45 = "!= 3.0.0"
sudoku-cell55 = "!= 3.0.0"
sudoku-cell65 = "!= 3.0.0"
sudoku-cell75 = "!= 3.0.0"
sudoku-cell85 = "!= 3.0.0"
sudoku-cell95 = "!= 3.0.0"
```

To use this version of the package ("put 3 in this cell"), we can't use the same
version of any of the conflicting packages ("can't put 3 in cells in the same
row, column or square").

I then set up a devpi instance and uploaded all 9✕9✕9 = 729 package versions to
it. Theoretically, one could upload them to the public PyPI (these rules are
only ever uploaded once, not every time we need to solve a board), but that
feels abusive.

## Encoding the problem

Now, we can represent an unsolved Sudoku board as another Poetry package:

```toml
[tool.poetry.dependencies]
python = "^3.6"
sudoku-cell11 = "*"
sudoku-cell12 = "2.0.0"
sudoku-cell13 = "*"
sudoku-cell14 = "8.0.0"
sudoku-cell15 = "*"
sudoku-cell16 = "9.0.0"
sudoku-cell17 = "*"
sudoku-cell18 = "*"
sudoku-cell19 = "*"
sudoku-cell21 = "3.0.0"
sudoku-cell22 = "7.0.0"
sudoku-cell23 = "*"
sudoku-cell24 = "6.0.0"
...
```

This `pyproject.toml` depends on all 81 "cell" packages, pinning known cells to
their values.

## Solving the problem

We can now solve the problem by simply running `poetry update --lock`. In order
to generate a lockfile for this package, Poetry has to find a version (value)
for each of the 81 packages (cells) in such a way that they don't conflict with
each other. Since we encoded the rules of Sudoku as inter-package dependencies,
the lockfile will contain a solution to this Sudoku board.

Running `poetry update` with `-vvv` even outputs the internal assertions Poetry
is deriving about the constraints:

```
125: conflict: sudoku-cell52 (3.0.0) depends on sudoku-cell55 (!=3.0.0)
 125: ! sudoku-cell52 (3.0.0) is partially satisfied by not sudoku-cell52 (5.0.0)
 125: ! which is caused by "sudoku-cell52 (5.0.0) depends on sudoku-cell51 (!=5.0.0)"
 125: ! thus: sudoku-cell52 (3.0.0 || 5.0.0) requires sudoku-cell55 (!=3.0.0) or sudoku-cell51 (!=5.0.0)
 125: ! not sudoku-cell51 (!=5.0.0) is partially satisfied by sudoku-cell51 (!=8.0.0)
 125: ! which is caused by "sudoku-cell11 (8.0.0) depends on sudoku-cell51 (!=8.0.0)"
 125: ! thus: if sudoku-cell52 (3.0.0 || 5.0.0) and sudoku-cell11 (8.0.0) then sudoku-cell55 (!=3.0.0) or sudoku-cell51 (<5.0.0 || >5.0.0,<8.0.0 || >8.0.0)
...
131: selecting sudoku-cell14 (6.0.0)
 131: Version solving took 269.771 seconds.
 131: Tried 131 solutions.
```

## Success

Finally, we can parse the lockfile (read which version of each package Poetry
has chosen) and emit it as a solved Sudoku board:

```
 ORIGINAL SOLUTION


 . . . | 6 4 1 | 9 . 5 3 2 8 | 6 4 1 | 9 7 5
 . . . | . . 9 | 4 . . 1 6 5 | 8 7 9 | 4 3 2
 . . 7 | 2 . . | . . . 9 4 7 | 2 3 5 | 1 6 8
-------|-------|------- -----------------------
 2 . . | . 5 . | . . . 2 7 4 | 1 5 6 | 8 9 3
 . . 1 | . . 7 | 6 . 4 8 5 1 | 3 9 7 | 6 2 4
 . 9 . | . . . | 5 . 7 6 9 3 | 4 8 2 | 5 1 7
-------|-------|------- -----------------------
 . 8 . | . . . | . . . 4 8 9 | 7 6 3 | 2 5 1
 . . 2 | . . . | . 8 6 5 3 2 | 9 1 4 | 7 8 6
 . . 6 | 5 2 . | . . . 7 1 6 | 5 2 8 | 3 4 9
```

## Failed attempt: Yarn

In the beginning, I tried a similar idea with Yarn, with `package.json` files
for each individual "cell" package encoding dependencies on other packages:

```json
{
 "name":"sudoku-cell26",
 "version":"6.0.0",
 "main":"index.js",
 "license":"MIT",
 "dependencies":{
 "sudoku-cell14":"1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0",
 "sudoku-cell15":"1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0",
 "sudoku-cell16":"1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0",
...
```

However, Yarn resolved dependencies using multiple versions for some packages:

```yaml
# This file is generated by running "yarn install" inside your project.
# Manual changes might be lost - proceed with caution!

__metadata:
 version: 6
 cacheKey: 8

? "sudoku-cell11@npm:*, sudoku-cell11@npm:1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 ||
 5.0.0 || 6.0.0 || 7.0.0 || 9.0.0, sudoku-cell11@npm:1.0.0 || 2.0.0 || 3.0.0 ||
 4.0.0 || 5.0.0 || 6.0.0 || 8.0.0 || 9.0.0, sudoku-cell11@npm:1.0.0 || 2.0.0 ||
 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0, sudoku-cell11@npm:1.0.0 ||
 2.0.0 || 3.0.0 || 4.0.0 || 6.0.0 || 7.0.0 || 8.0.0 || 9.0.0,
 sudoku-cell11@npm:1.0.0 || 2.0.0 || 3.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 8.0.0
 || 9.0.0, sudoku-cell11@npm:1.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0
 || 8.0.0 || 9.0.0, sudoku-cell11@npm:2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0
 || 7.0.0 || 8.0.0 || 9.0.0"
: version: 9.0.0
 resolution: "sudoku-cell11@npm:9.0.0"
 dependencies:
 sudoku-cell12:
 1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 8.0.0
 sudoku-cell13:
 1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 8.0.0
---
? "sudoku-cell11@npm:1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0
 || 8.0.0"
: version: 8.0.0
 resolution: "sudoku-cell11@npm:8.0.0"
 dependencies:
 sudoku-cell12:
 1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 9.0.0
 sudoku-cell13:
 1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 9.0.0
---
? "sudoku-cell12@npm:*, sudoku-cell12@npm:1.0.0 || 2.0.0 || 3.0.0 || 4.0.0 ||
 5.0.0 || 6.0.0 || 7.0.0 || 9.0.0, sudoku-cell12@npm:1.0.0 || 2.0.0 || 3.0.0 ||
 4.0.0 || 5.0.0 || 6.0.0 || 8.0.0 || 9.0.0, sudoku-cell12@npm:1.0.0 || 2.0.0 ||
 3.0.0 || 4.0.0 || 5.0.0 || 7.0.0 || 8.0.0 || 9.0.0, sudoku-cell12@npm:1.0.0 ||
 2.0.0 || 3.0.0 || 4.0.0 || 6.0.0 || 7.0.0 || 8.0.0 || 9.0.0,
 sudoku-cell12@npm:1.0.0 || 2.0.0 || 3.0.0 || 5.0.0 || 6.0.0 || 7.0.0 || 8.0.0
 || 9.0.0, sudoku-cell12@npm:2.0.0 || 3.0.0 || 4.0.0 || 5.0.0 || 6.0.0 || 7.0.0
 || 8.0.0 || 9.0.0"
```

I tried running `yarn dedupe` which is supposed to deduplicate overlapping
version ranges in the lockfile, but it ran out of memory and crashed. Yarn also
comes with a constraints system, but that's just Prolog so it felt like
cheating.

## Conclusion and future work

The code I used to generate the packages and upload them to devpi is on
[GitHub](https://github.com/mildbyte/poetry-sudoku-solver/). You can install it
with Poetry and run this experiment locally, for better or for worse.

As for what else can be done with this, I was thinking of using something faster
than devpi to feed packages to Poetry (the package upload step takes about 10
minutes and the actual solving process takes a couple of minutes). Maybe it's
possible to transpile a Prolog program into a set of Poetry packages. The sky's
the limit.

An unexpected use case for one of Python's most popular package managers.

Solving Sudoku with Poetry's dependency resolver

## What is Splitgraph?

Splitgraph is building the Unified Data Stack – an integrated and modern solution
for working with data without worrying about its infrastructure.

You can try Splitgraph now! Browse
[the public data catalog](https://www.splitgraph.com/explore) to discover
40,000+ repositories to query with SQL, through the
[web IDE](https://www.splitgraph.com/query)
or [any Postgres client](https://www.splitgraph.com/connect).

## Introduction

There is a trend in data engineering to apply best practices from software engineering, for example, version control and code reviews. Splitgraph itself was originally inspired by Git and Docker, both famous developer tools.

Another DevOps tool we like is Terraform: it lets you use a declarative configuration language to define your cloud infrastructure. Terraform then takes care of interpreting this configuration and calling cloud vendor-specific APIs to create/destroy resources and align the real state of the world with the desired configuration.

We built something similar for the data stack: [`splitgraph.yml`](https://www.splitgraph.com/docs/splitgraph.yml/introduction), or Splitgraph Cloud project files.

You can think of `splitgraph.yml` as a Terraform file that lets you programmatically configure a dataset in Splitgraph, including its:

- metadata (README, tags, description)
- data source settings (databases, SaaS services, flat files)
- ingestion schedule
- dbt transformations

We use this format ourselves to declare data ingestion pipelines with Airbyte, build our own data warehouse with dbt and organize them in our Splitgraph data catalog (see a [previous blog post](https://www.splitgraph.com/blog/airbyte-dbt-splitgraph) for more details!)

In this post, we'll talk about how you can use `splitgraph.yml` to define your own data pipeline and show an end-to-end example that ingests some [sample data](https://data.cityofnewyork.us/Transportation/For-Hire-Vehicles-FHV-Active/8wbx-tsch) from Socrata and transforms it using dbt.

## Example `splitgraph.yml` file

Here is a basic example that builds two Splitgraph repositories:

 - [splitgraph-demo/for-hire-vehicles](https://www.splitgraph.com/splitgraph-demo/for-hire-vehicles): List of active for-hire vehicles in New York City ([source](https://data.cityofnewyork.us/Transportation/For-Hire-Vehicles-FHV-Active/8wbx-tsch))
 - [splitgraph-demo/for-hire-vehicles-summary](https://www.splitgraph.com/splitgraph-demo/for-hire-vehicles-summary): dbt transformation that summarizes the data in the first repository

```yaml
repositories:
- namespace: splitgraph-demo
 repository: for-hire-vehicles
 # Catalog-specific metadata for the repository.
 metadata:
 readme:
 text: For more information, see https://www.splitgraph.com/cityofnewyork-us/for-hire-vehicles-fhv-active-8wbx-tsch
 description: New York Active For Hire Vehicles
 topics:
 - socrata
 - new york
 # Data source settings
 external:
 plugin: csv
 params:
 connection:
 connection_type: "http"
 url: "" # Will be overridden by the individual table
 tables:
 vehicles:
 options:
 url: "https://data.cityofnewyork.us/api/views/8wbx-tsch/rows.csv?accessType=DOWNLOAD"
 # Schema of the table (set to [] to infer)
 schema: []
 # Enable live querying for the plugin (creates a "live" tag in the
 # repository proxying to the data source).
 is_live: true

# dbt model that processes the previous repository
- namespace: splitgraph-demo
 repository: for-hire-vehicles-summary
 external:
 plugin: dbt
 credential: dbt-sample
 params:
 sources:
 # Map dbt source names to Splitgraph repositories
 - dbt_source_name: for_hire_vehicles
 namespace: splitgraph-demo
 repository: for-hire-vehicles
 hash_or_tag: latest
 git_branch: $THIS_SHA
 is_live: false
 tables: {}
 metadata:
 description: Sample dbt model
 readme:
 text: "## Sample dbt model\n\nThis is a model referencing data\
 \ from:\n\n * [/splitgraph-demo/for-hire-vehicles](/splitgraph-demo/for-hire-vehicles)"
```

The full project that uses this file is located on [our GitHub](https://github.com/splitgraph/splitgraph-yml-example). You can also query the resultant dataset on Splitgraph:

<EmbeddedQuery
 query={`SELECT
 "Base Name",
 "Base Type",
 "Count"
FROM "splitgraph-demo/for-hire-vehicles-summary:latest"."vehicles_summary"
ORDER BY "Count" DESC;`} />

## Writing `splitgraph.yml` files

### Quick method: generating a Splitgraph Cloud project from a seed

The simplest way to get started with `splitgraph.yml` is by using our new "project seeds" functionality. This will generate a GitHub repository for you with a `splitgraph.yml` file, a dbt model and a GitHub Action file that you can use as a starting point to define your own pipeline.

We've done some magic to make the process smooth for you, so you can select data sources, copy a repo on GitHub, and paste a seed value into a GitHub action, to set up all your boilerplate.

Click the button to get started:

<ButtonLink href="/connect/splitgraph-yml">Generate splitgraph.yml and GitHub Repository</ButtonLink>
 

Splitgraph supports over 100 sources. These include SaaS services (powered by the open Airbyte standard) as well as "live queries" to the most popular RDBMSs and data warehouses. The latter, powered by PostgreSQL Foreign Data Wrappers, lets you query data through Splitgraph without having to load it.

![Using the wizard to generate a splitgraph.yml file](/static-v2/images/blog/splitgraph-yml-terraform-data-pipeline/splitgraph-yml-wizard-selected.png)

The wizard will guide you through using our Splitgraph Cloud [template project](https://github.com/splitgraph/splitgraph-cloud-template) to generate a project from a "seed" (a representation of the data sources you chose).

![Using the Splitgraph Cloud template project to generate a splitgraph.yml file](/static-v2/images/blog/splitgraph-yml-terraform-data-pipeline/splitgraph-yml-project-seed.png)

After you go through the process, you will end up with a repository with Splitgraph Cloud project files:

 - A `splitgraph.yml` file that defines your chosen data
 - `build.moveme.yml` in the root with a GitHub Action definition used to ingest the data on a schedule or on-demand
 - Sample dbt model that references data from all the datasets you added

We created the [example project](https://github.com/splitgraph/splitgraph-yml-example) using this method.

You will need to perform some extra steps to finish the setup (see the README in the generated repository for more information):

 - Set up your Splitgraph API keys (we call them ["SQL credentials"](https://www.splitgraph.com/settings/sql-credentials), as you can also use them to connect to Splitgraph's PostgreSQL-compatible endpoint) as GitHub Secrets
 - Add credentials to `splitgraph.credentials.yml` (not required for our sample project, as the Socrata endpoint we're using to get the data is public)
 - Edit `splitgraph.yml` to set up the data source parameters
 - Set up GitHub Actions (copy `build.moveme.yml` to the `.github` directory)

### Advanced method: using `splitgraph.yml` with the `sgr` CLI

The example GitHub Action above calls the `sgr` CLI to interact with Splitgraph Cloud and manage datasets and pipelines.

You can run the same commands from your development machine or from another workflow orchestrator, like GitLab CI. Let's look at these commands in detail.

#### `sgr cloud stub`: generate a `splitgraph.yml` file

This command will generate a basic `splitgraph.yml` file for a specific plugin. You can use this file as a starting point [(documentation)](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-stub).

```bash
$ sgr cloud stub csv splitgraph-demo/csv
credentials:
 csv: # This is the name of this credential that "external" sections can reference.
 plugin: csv
 # Credential-specific data matching the plugin's credential schema
 data:
 s3_access_key: '' # AWS Access Key
 s3_secret_key: '' # AWS Secret Access Key
repositories:
- namespace: splitgraph-demo
 repository: csv
 # Catalog-specific metadata for the repository. Optional.
 metadata:
 readme:
 text: Readme
 description: Description of the repository
 topics:
 - sample_topic
 # Data source settings for the repository. Optional.
 external:
 # Name of the credential that the plugin uses. This can also be a credential_id if the
 # credential is already registered on Splitgraph.
 credential: csv
 plugin: csv
 # Plugin-specific parameters matching the plugin's parameters schema
 params:
 connection: # Connection. Choose one of:
 - connection_type: http # REQUIRED. Connection type. Constant
 url: '' # REQUIRED. URL. HTTP URL to the CSV file
 - connection_type: s3 # REQUIRED. Connection type. Constant
 s3_endpoint: '' # REQUIRED. S3 endpoint. S3 endpoint (including port if required)
 s3_bucket: '' # REQUIRED. Bucket name. Bucket the object is in
 s3_region: '' # S3 region. Region of the S3 bucket
 s3_secure: false # Secure. Whether to use HTTPS for S3 access
 s3_object: '' # S3 Object name. Limit the import to a single object
 s3_object_prefix: '' # S3 Object prefix. Prefix for object in S3 bucket
...
```

#### `sgr cloud load`: set up the catalog metadata

This command [(documentation)](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-load) uploads the README, tags, description as well as external data source settings and credentials to Splitgraph Cloud.

#### `sgr cloud sync`: run a data load

This command [(documentation)](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-sync) starts an ingestion job. If you previously ran `sgr cloud load`, you can use the existing data source settings uploaded to Splitgraph Cloud. You can also make this command use an existing `splitgraph.yml` file, which will run ingestion without Splitgraph Cloud storing your data source credentials.

#### Miscellaneous `sgr cloud` commands

There are other useful `sgr cloud` commands. See our [documentation](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-plugins) for a full list:

- [`sgr cloud upload`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-upload): upload a CSV file to Splitgraph Cloud
- [`sgr cloud download`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-download): export a SQL query result as a CSV file
- [`sgr cloud plugins`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-plugins): search for data source plugins
- [`sgr cloud search`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-search): search for repositories
- [`sgr cloud sql`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-sql): run SQL queries
- [`sgr cloud status`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-status): check the status of ingestion jobs

### `splitgraph.yml` reference

An in-depth reference for the `splitgraph.yml` format is available [here](https://www.splitgraph.com/docs/splitgraph.yml/introduction).

### In Alpha: Web UI

Configure datasets individually via the web GUI. You can try it here, but note
it's in alpha status and some configurations are not yet fully supported:

<ButtonLink href="/connect/data">Connect Data</ButtonLink>
 

![Upcoming Web UI to add data to Splitgraph](/static-v2/images/blog/splitgraph-yml-terraform-data-pipeline/splitgraph-yml-web-ui.png)

You can still store your catalog in the `splitgraph.yml` format with this method: use the [`sgr cloud dump`](https://www.splitgraph.com/docs/sgr/splitgraph-cloud/cloud-dump) command to dump existing repositories to `splitgraph.yml`.

## Conclusion

In this blog post, we showcased `splitgraph.yml`: a configuration format that lets you programmatically manage your datasets on Splitgraph. You can find an example project on [our GitHub](https://github.com/splitgraph/splitgraph-yml-example).

The example project also shows how to make the data repositories that you build private. You can then use our [sharing settings and invites functionality](/blog/data-sharing-settings-invites-private-repositories) to manage who has access to your datasets and invite collaborators.

Interested in tighter security guarantees or more team features? We also provide private versions of Splitgraph Cloud, deployed to a cloud vendor and region of your choice. Feel free to [try it out](https://www.splitgraph.com/demo)!

We showcase the splitgraph.yml format, which lets you programmatically manage your datasets on Splitgraph, change their data source settings and define dbt transformations.

We showcase the `splitgraph.yml` format, which lets you programmatically manage your datasets on Splitgraph, change their data source settings and define dbt transformations.

`splitgraph.yml`: Terraform for your data stack

splitgraph.yml: Terraform for your data stack

## Query Splitgraph from Public Observable Notebooks

[Observable](https://observablehq.com/@splitgraph) has native support for
connecting to [PostgreSQL](https://observablehq.com/@observablehq/databases)
from private notebooks and querying [SQLite](https://observablehq.com/@observablehq/sqlite)
files from public notebooks. Unfortunately, querying large amounts of data from
public notebooks has not been supported.

That's changed now thanks to
the [Splitgraph Observable Client](https://observablehq.com/@splitgraph/ddn),
which lets you query all the public data on the [Splitgraph Data Delivery Network (DDN)](/explore)
from any notebook.

## Import the client

To use the Splitgraph Observable Client in your own notebook, simply import it
from the `@splitgraph/ddn` notebook. Call the `splitgraph` function to send SQL
queries to the DDN over its HTTP API:

```javascript
import { splitgraph } from "@splitgraph/ddn"

all_cases = await splitgraph`
SELECT
 date,
 confirmed,
 recovered,
 dead,
 source
FROM "splitgraph/oxcovid19".epidemiology
WHERE countrycode = 'GBR' AND gid = '{"GBR"}' AND source = 'GBR_PHE'
ORDER BY date DESC`
```

## Example notebook

This [Planning a family vacation](https://observablehq.com/@splitgraph/planning-a-vacation-in-the-age-of-covid-19)
notebook showcases the Observable Splitgraph API:

<iframe width="100%" height="800" frameBorder="0"
 src="https://observablehq.com/embed/@splitgraph/planning-a-vacation-in-the-age-of-covid-19?cell=*">
</iframe>

Query public Splitgraph repositories from Observable notebooks, by importing the Splitgraph Observable Client to send SQL queries over HTTP to the Splitgraph Data Delivery Network (DDN).

Planning a vacation with Splitgraph and Observable

## Splitgraph helps teams collaborate on data

At Splitgraph, we're convinced there's better ways to collaborate on data than
sharing CSV files. The ability to
[query other databases](/blog/postgresql-fdw-aggregation-pushdown-multicorn-part-1)
using Postgres queries, import data from hundreds of data sources,
[transform it with dbt](/blog/airbyte-dbt-splitgraph),
and travel back in time to any previous version all serve the same
purpose: to give teams working with data a place to collaborate effectively.
That said, if you're working with CSV files, [we have that covered as well 😉](/blog/csv-to-sql-upload-to-data-delivery-network-ddn).

The quickest way to try Splitgraph is to simply sign up to Splitgraph.com, the
public data portal open to everyone. You can import or connect your own data
into private or public repositories. And you can query over 40,000 public data
tables within 60 seconds, just by
[pointing your Postgres client to `data.splitgraph.com:5432`](https://www.splitgraph.com/connect/query).

## Dedicated Data Portals

For organizations, we also provide **Dedicated Data Portals**, which are private
deployments of Splitgraph to the cloud infrastructure of your choice. You get
all the same features of Splitgraph.com, except within a dedicated single-tenant
instance that you can invite your teammates to.

Dedicated Data Portals are currently in beta. Starting today, you can provision
a demo environment free for 7 days with no credit card required. If you want to
continue experimenting, just get in touch with us and we'll extend the trial.
Once you're ready to buy, schedule a meeting to discuss your requirements and pricing.

## Try Splitgraph in a single-tenant environment

In addition to the features available on the public [Splitgraph.com](https://www.splitgraph.com),
a Dedicated Data Portal enables you to:

- Store your data in a single-tenant environment.
- Get your own Splitgraph subdomain.
- Invite colleagues, collaborators and customers.
- Run Splitgraph in your cloud provider's region to minimize latency and transfer costs.
- Integrate with your company's OAuth/SSO (e.g. Okta).
- Gain in-depth control over sharing and unlimited namespaces.

## Deploy now

Launch your own demo Dedicated Data Portal with just a few
clicks (signup, but no credit card required).

<ButtonLink href="/demo">Deploy Demo</ButtonLink>

## What to expect

After you signup and deploy the demo, we'll send you an email in about 20 minutes,
once your deployment is ready. It will look like this:

<img src="/static-v2/images/blog/demo/demo-welcome-email.png" />

Just click the button in the email to get started with your Dedicated Data Portal.

Deploy a demo instance of a Dedicated Data Portal, so you can experiment with Splitgraph features in a single-tenant environment. Try it today, free for 7 days, no credit card required.

Get your own private Splitgraph data portal

## What is Splitgraph?

Splitgraph is building the Unified Data Stack – an integrated and modern solution
for working with data without worrying about its infrastructure.

You can try Splitgraph now! Browse
[the public data catalog](https://www.splitgraph.com/explore) to discover
40,000+ repositories to query with SQL, through the
[web IDE](https://www.splitgraph.com/query)
or [any Postgres client](https://www.splitgraph.com/connect).

Splitgraph is powered by open standards and simple abstractions, like
[data images](https://www.splitgraph.com/docs/concepts/images) – immutable tables
that you can push and pull, or query on the fly.

We're not replacing SQL as our favorite query language anytime soon 😄, but we rely on [GraphQL](https://graphql.org/) to implement many of Splitgraph's internal APIs. Thanks to GraphQL schema stitching, we were able to add new backend services which extend the set of fields published by older services. Clients don't need to specify which service provides which field, this is determined automatically by the schema stitching logic.

## How we use GraphQL at Splitgraph

Projects like [Apollo Client](https://www.apollographql.com/docs/react/), [graphql-playground](https://github.com/graphql/graphql-playground) and [PostGraphile](https://www.graphile.org/postgraphile/) have made working with GraphQL a smooth experience, but the declarative query language has proven to be even more important than the tooling. It's given us the flexibility to develop services which can be used independently, but also as components implementing the following unified API:

```graphql
type Query {
 """Returns a single `Repository` identified
 by it's `namespace` and `repositoryName`."""
 metadataRepository(
 namespace: String!,
 repositoryName: String!): Repository
 authorizationRepository(
 namespace: String!,
 repositoryName: String!): Repository
 """Returns a single `User` based on their username."""
 authorizationUser(username: String!): User
 metadataUser(username: String!): User
}

type Repository {
 namespace: String!,
 repositoryName: String!,
 description: String,
 url: String,
 """Lists all users who have permission to read this repository."""
 usersWithReadAccess: [User!]!
}

type User {
 username: String!,
 fullName: String,
 """Lists all repositories this user has permission to read."""
 repositories: [Repository!]!
}
```

The "dashboard" page which greets users upon login is one of the many clients of this API. It needs to display the current user's name as well a link to each repository they have access to. A single GraphQL query can fetch the required fields:

```graphql
query Dashboard($username: String!) {
 metadataUser(username: $username) {
 fullName,
 repositories {
 description,
 url
 }
 }
}
```

## Schema stitching in the gateway service

Two separate services store the data requested by the `Dashboard` query. The client sends the query to the `gateway` service, which uses [schema stitching](https://www.graphql-tools.com/docs/schema-stitching/stitch-combining-schemas#combining-schemas) to combine the APIs of the backing `metadata` and `authorization` services into the unified schema shown above. As the `gateway` service executes the `Dashboard` query, it queries the backing services.

### Metadata service

The `metadata` service is powered by [PostGraphile](https://www.graphile.org/postgraphile/) and predates the `authorization` service. Before developing the latter, Splitgraph's internal GraphQL API Schema looked similar to the following:

```graphql
type Repository {
 namespace: String!
 repositoryName: String!
 description: String
 url: String
}

type User {
 username: String!
 fullName: String
}

type Query {
 metadataRepository(
 namespace: String!,
 repositoryName: String!): Repository
 metadataUser(username: String!): User
}
```

### Authorization service

We built the `authorization` service based on [Ory Keto](https://github.com/ory/keto) to keep track of user-repository permissions.
The `authorization` service's schema extends the types introduced by the `metadata` service:

```graphql
"""This Repository type is merged with the Repository
 type declared in the metadata service schema."""
type Repository {
 """namespace and repositoryName are the key fields
 by which the repository types in the two
 subschemas can be merged."""
 namespace: String!
 repositoryName: String!
 usersWithReadAccess: [User!]!
}

"""Similar to Repository, User will be merged
 with the metadata service's User type."""
type User {
 """username is the key field of User."""
 username: String!
 repositories: [Repository!]!
}

type Query {
 """authorization service specific fields for retrieving
 User and Repository objects."""
 authorizationRepository(
 namespace: String!,
 repositoryName: String!): Repository
 authorizationUser(username: String!): User
}
```

## What is schema stitching?

The main idea is simple: the different fields of a type may be distributed among several services as long as the corresponding subschemas all use the same type name. By default, the "stitched" schema just combines all the fields of any given type from all subschemas, but accidental type merging [can be avoided](https://www.graphql-tools.com/docs/schema-stitching/stitch-combining-schemas#duplicate-types).
Single-service GraphQL schemas operate under the closed world assumption that all fields belonging to a type are declared in the schema. Stitching leads to an open world model where any type may be extended with additional fields declared in a new subschema.

At first, the doubled fields in the `Query` type may seem superfluous. They have the same return types after all: `User` and `Repository`.
Why they're necessary becomes apparent as soon as we examine how the `Dashboard` query is processed by the `gateway` service.

## Querying stitched schemas

The `gateway` service uses the [merging flow](https://www.graphql-tools.com/docs/schema-stitching/stitch-type-merging#merging-flow) to execute queries and delegate to the other services.
With the proper [subschema configuration](https://www.graphql-tools.com/docs/schema-stitching/stitch-combining-schemas#subschema-configs), the schema stitching code can determine which service should be queried for a particular field.

Consider the steps required to compute results for the `Dashboard` query for user `mrDorp`.
1. The client submits the `Dashboard` query to the gateway service, which selects the `fullName` and `repositories` fields on the result of `metadataUser`.
 <img src="/static-v2/og/blog/dashboard-query-1.svg" />

1. Since `metadataUser` comes from the `metadata` service's subschema, the `gateway` queries it for the `fullName` field.
 <img src="/static-v2/og/blog/dashboard-query-2.svg" />

 ```graphql
 metadataUser(username: "mrDorp") {
 __typename
 fullName
 username
 }
 ```
 The `username` field is added implicitly since it's the key field used to join the `User` types in the two services. `__typename` is always added by the schema stitching code. The response to the query is:
 ```json
 {
 "data": {
 "metadataUser": {
 "__typename": "User",
 "username": "mrDorp",
 "fullName": "Ralph Dorp"
 }
 }
 ```

1. The `repositories` field of the `User` type belongs to the `authorization` service, so the `gateway` queries it next. The subschema configuration specifies that the top-level `authorizationUser` field may be queried to get the `User` fields declared in the service's subschema.
 <img src="/static-v2/og/blog/dashboard-query-3.svg" />

 ```graphql
 authorizationUser(username: "mrDorp") {
 username
 repositories {
 __typename
 namespace
 repositoryName
 }
 }
 ```
 Note that the `Dashboard` query selected the `description` and `url` fields of each `Respository` object, but these fields are declared by the `metadata` service schema. In the request headed for the `authorization` service, all that can be queried are the key fields. Just as in the previous query, the `gateway` service implicitly adds the `username` key field to the selection set. The response is the following:

 ```json
 {
 "data": {
 "authorizationUser": {
 "__typename": "User",
 "username": "mrDorp",
 "repositories": [
 {
 "__typename": "Repository",
 "namespace": "austintexas-gov",
 "repositoryName": "austin-high-school-graduation-rates-xeb7-q8v3"
 },
 {
 "__typename": "Repository",
 "namespace": "bts-gov",
 "repositoryName": "county-transportation-profiles-qdmf-cxm3"
 }
 ]
 }
 }
 ```
1. Having obtained the repositories' key fields, the `gateway` may query additional `Repository` fields from the `metadata` service. It consults the stitching configuration for the top-level field to query - in this case `metadataRepository`.
 <img src="/static-v2/og/blog/dashboard-query-4.svg" />

 ```graphql
 metadataRespository(
 namespace: "austintexas-gov",
 repositoryName: "austin-high-school-graduation-rates-xeb7-q8v3") {
 __typename
 namespace
 repositoryName
 description
 url
 }

 metadataRespository(
 namespace: "bts-gov",
 repositoryName: "county-transportation-profiles-qdmf-cxm3") {
 __typename
 namespace
 repositoryName
 description
 url
 }
 ```

 Just as with the query for the `authorizationUser` field, the key fields (`namespace` and `repositoryName`) and `__typename` are implicitly added to the query to allow merging of result objects.
 We created a PostGraphile plugin using GraphQL's [DataLoader](https://www.graphql-tools.com/docs/connectors#dataloader-and-caching) to combine the two queries above into a single GraphQL query ([drop us a line](mailto:support@splitgraph.com) if you're interested in using it). Unfortunately, the current schema does not allow for such optimization, so each repository's metadata fields are queried separately. The `metadata` service responds with:
 ```json
 {
 "data": {
 "metadataRepository": {
 "__typename": "Repository",
 "namespace": "austintexas-gov",
 "repositoryName": "austin-high-school-graduation-rates-xeb7-q8v3"
 "description": "Graduation rates for Austin high schools for years 2012 to 2016 provided by the Texas Education Agency.",
 "url": "https://www.splitgraph.com/austintexas-gov/austin-high-school-graduation-rates-xeb7-q8v3"
 }
 }
 }

 {
 "data": {
 "metadataRepository": {
 "__typename": "Repository",
 "namespace": "bts-gov",
 "repositoryName": "county-transportation-profiles-qdmf-cxm3"
 "description": "Profiles of transportation features of U.S. counties",
 "url": "https://www.splitgraph.com/bts-gov/county-transportation-profiles-qdmf-cxm3"
 }
 }
 }
 ```

1. The `gateway` has all the fields required to respond to the `Dashboard` query. Key fields which weren't selected in the `Dashboard` query are discarded once the objects have been merged.
 ```json
 {
 "data": {
 "metadataUser": {
 "__typename": "User",
 "fullName": "Ralph Dorp",
 "repositories": [
 {
 "__typename": "Repository",
 "description": "Graduation rates for Austin high schools for years 2012 to 2016 provided by the Texas Education Agency.",
 "url": "https://www.splitgraph.com/austintexas-gov/austin-high-school-graduation-rates-xeb7-q8v3"
 },
 {
 "__typename": "Repository",
 "description": "Profiles of transportation features of U.S. counties",
 "url": "https://www.splitgraph.com/bts-gov/county-transportation-profiles-qdmf-cxm3"
 }
 ]
 }
 }
 }
 ```

## Configuring subschemas to stitch

While processing the `Dashboard` query, the `gateway` had to decide which service to query for each requested field. This is determined by the [subschema configuration](https://www.graphql-tools.com/docs/schema-stitching/stitch-combining-schemas#subschema-configs).
Below is the simplified code for the `gateway` service, which is based on the TypeScript examples for [remote schemas](https://www.graphql-tools.com/docs/remote-schemas#graphql-over-http-executor-with-cross-undici-fetch) and [schema stitching](https://www.graphql-tools.com/docs/schema-stitching/stitch-combining-schemas#getting-started):

```typescript
import { stitchSchemas } from "@graphql-tools/stitch";
import { fetch } from "cross-fetch";
import { print, GraphQLSchema } from "graphql";
import { introspectSchema, wrapSchema } from "@graphql-tools/wrap";
import type { AsyncExecutor } from "@graphql-tools/utils/executor";
import { SubschemaConfig } from "@graphql-tools/delegate";

const authorizationServiceUrl = "http://api.splitgrph.com/authorzation/graphql";
const metadataServiceUrl = "http://api.splitgrph.com/metadata/graphql";

const makeRemoteExecutor: (serviceUrl: string) => AsyncExecutor = (
 serviceUrl: string
) => async ({ document, variables }) => {
 const query = print(document);
 const result = await fetch(serviceUrl, {
 method: "POST",
 headers: {
 "Content-Type": "application/json",
 },
 body: JSON.stringify({ query, variables }),
 });
 return result.json();
};

const defineSubSchema = async (
 serviceUrl: string,
 merge?: SubschemaConfig["merge"]
): Promise<SubschemaConfig> => {
 const executor = makeRemoteExecutor(serviceUrl);
 const schema = wrapSchema({
 schema: await introspectSchema(executor),
 executor,
 });
 return { executor, schema, merge };
};

export const makePublicSchema = async (): Promise<GraphQLSchema> =>
 stitchSchemas({
 subschemas: [
 await defineSubSchema(authorizationServiceUrl, {
 User: {
 fieldName: "authorizationUser",
 selectionSet: "{ username }",
 args: ({ username }) => ({ username }),
 },
 Repository: {
 fieldName: "authorizationRepository",
 selectionSet: "{ namespace repositoryName }",
 args: ({ namespace, repositoryName }) => ({
 namespace,
 repositoryName,
 }),
 },
 }),
 await defineSubSchema(metadataServiceUrl, {
 User: {
 fieldName: "metadataUser",
 selectionSet: "{ username }",
 args: ({ username }) => ({ username }),
 },
 Repository: {
 fieldName: "metadataRepository",
 selectionSet: "{ namespace repositoryName }",
 args: ({ namespace, repositoryName }) => ({
 namespace,
 repositoryName,
 }),
 },
 }),
 ],
 });
```

The `GraphQLSchema` object returned by `makePublicSchema` can be used to create an [HTTP GraphQL endpoint](https://www.graphql-tools.com/docs/server-setup#setup-an-http-server).

In order to query a subschema, it's URL must be known. Conveniently, a GraphQL query can be used to obtain an endpoint's schema. This is what `defineSubSchema()` does when it calls `introspectSchema()`. The object passed to `defineSubSchema()` is the [merge configuration](https://www.graphql-tools.com/docs/schema-stitching/stitch-type-merging#type-merging). Consider the fragment,

```javascript
 await defineSubSchema(authorizationServiceUrl, {
 User: {
 fieldName: "authorizationUser",
 selectionSet: "{ username }",
 args: ({ username }) => ({ username }),
 }
```

It declares: when fields of the `User` type are queried which were declared by the `authorization` service schema, they can be obtained by passing the `username` field of the pre-existing `User` object as the argument to the top-level `authorizationUser` field.

This was employed in step 2 of the query process described earlier, when the `User.repository` field was merged with the `User` object obtained from the `metadata` service in the previous step.

`fieldName` specifies the field of the top-level `Query` type to consult. `selectionSet` defines the set of fields to be selected from the existing `User` object to get `authorizationUser`'s arguments, typically the key field or fields. `selectionSet` could contain fields not yet available on the current `User` instance. In such a case, an additional subschema query would be made before the field referred to by `fieldName` is queried.

The `args` function transforms the existing fields on the `User` object to be used as arguments to `authorizationUser`. In most cases, this is the identity function, but it can be useful for things like string to number conversion, especially when stitching schemas for APIs outside of our control. For example, one could extend the types in the official Shopify GraphQL Schema with custom fields routed to an internal service.

It would also be possible to start by querying the `authorization` service:

```graphql
query Dashboard2($username: String!) {
 authorizationUser(username: $username) {
 fullName,
 repositories {
 description,
 url
 }
 }
}
```

The steps to executing the `Dashboard2` query would be:
1. Query the `authorization` service for the `username` key field and the `repositories` field.
1. Merge the `User.fullName` field to the existing `User` object instance by querying `metadataUser`.
1. Merge `url` and `description` fields with the existing `Repository` objects using `metadataRepository`.

## Conclusion

Schema stitching has enabled us to extend an existing service's GraphQL API with new fields served by a new service. The "stitching" is seamless in the sense that clients don't need to know which field belongs to which service's subschema. We evolved our API without affecting earlier queries.

Read about we use GraphQL schema stitching to provide a single coherent schema for accessing several services with shared types from overlapping GraphQL schemas.

SELECT directly from the browser

Building a data-driven app with Splitgraph and Streamlit

Solving Sudoku with Poetry's dependency resolver

`splitgraph.yml`: Terraform for your data stack

Planning a vacation with Splitgraph and Observable

Get your own private Splitgraph data portal

Combining multiple GraphQL backends with schema stitching

Product

Support

Company

Splitgraph

SELECT directly from the browser

Building a data-driven app with Splitgraph and Streamlit

Solving Sudoku with Poetry's dependency resolver

splitgraph.yml: Terraform for your data stack

Planning a vacation with Splitgraph and Observable

Get your own private Splitgraph data portal

Combining multiple GraphQL backends with schema stitching

Product

Support

Company

Community

Splitgraph

`splitgraph.yml`: Terraform for your data stack