Connect Structured data

Let’s connect PromptQL to a PostgreSQL database.

Make sure you have the alpha version of the CLI to use this example.

ddn update-cli --version v2.12.0-alpha.2

Add the PostgreSQL connector

ddn connector init mypostgres -i

Select hasura/postgres
Skip port, CONNECTION_URI, CLIENT_CERT, CLIENT_KEY, ROOT_CERT, etc. Press “return”.

The CLI will output text similar to this:

HINT To access the local Postgres database:
- Run: docker compose -f app/connector/mypostgres/compose.postgres-adminer.yaml up -d
- Open Adminer in your browser at http://localhost:9822 and create tables
- To connect to the database using other clients use postgresql://user:[email protected]:8105/dev

To enable the pgvector extension, you’ll need to use a PostgreSQL Docker image that includes pgvector, as postgres:latest doesn’t support it out-of-the-box. We recommend replacing the image in your compose.postgres-adminer.yaml file with pgvector/pgvector:pg16.

Copy the first command from the output above and paste it in your terminal to run the PostgreSQL instance.

Populate the database

We’ll populate the database with the SQL schema and data from the following files.

01_ecommerce_schema.sql
02_ecommerce_data.sql

First, let’s create our schema locally:

curl -o ./app/connector/mypostgres/01_ecommerce_schema.sql https://gist.githubusercontent.com/robertjdominguez/4f9aae5334ecae49f5ca71b5575ff746/raw/901e6ed3e2b84dab63b08b80a2e0f62f12b60e23/01_ecommerce_schema.sql

Then, the seed data:

curl -o ./app/connector/mypostgres/02_ecommerce_data.sql https://gist.githubusercontent.com/robertjdominguez/fb6316a1545bfb8960a74ea41bfa37e0/raw/8b38c468572918b77bcbc52896968f6bf74b87c9/02_ecommerce_data.sql

You can execute these scripts using the following commands:

cat app/connector/mypostgres/01_ecommerce_schema.sql | docker exec -i mypostgres-postgres-1 psql -U user -d dev
cat app/connector/mypostgres/02_ecommerce_data.sql | docker exec -i mypostgres-postgres-1 psql -U user -d dev

Introspect your data source

Introspect your data source to create a set of configuration files describing your data source in a format which the connector specifies.

ddn connector introspect mypostgres

Add your resources

Add your resources to create metadata for models, commands, and relationships in your supergraph.

ddn model add mypostgres '*'
ddn command add mypostgres '*'
ddn relationship add mypostgres '*'

Build your supergraph for the local engine

ddn supergraph build local

Create a Hasura DDN project to get PromptQL running, even if you are on local dev

ddn project init

Start your supergraph locally

ddn run docker-start

Head to your local DDN console

Run the following from your project’s directory:

ddn console --local

Talk to your data

Ask any questions you wish about your PostgreSQL data!

> Which product is most popular?
> Which customers generate the most revenue?

Connecting APIs directly Connecting Unstructured Data