Querying Relational Databases with LLMs (Not Text-to-SQL)

Relational databases are the backbone of structured data management—they keep us sane in a world of messy data. But, when we need to see the bigger picture and understand the hidden relationships and connections across our datasets, tables and rows are not always revealing enough.

That's where cognee steps in and whispers, “Let me handle that for you.” With our platform, you get an end-to-end pipeline that loads, processes, and links all the datapoints, transforming a structured database into an interactive knowledge graph in which each row becomes a node, and your foreign keys become intuitive edges.

In this post, we’ll walk through how to:

1. Build a relational database with dlt (using the fun PokéAPI example).
2. Clarify and fix your schema to make foreign key relationships crystal clear.
3. Migrate your database with cognee, transforming rows into an explorable knowledge graph.
4. Query your new graph in plain English—no JOINs, just clear answers.

Why Move to a Knowledge Graph?

In relational databases, relationships are typically navigated through JOIN operations. While effective, this approach becomes cumbersome as the data complexity increases, forcing you into repetitive and taxing cross-references to answer even simple relational queries.

Migrating to a knowledge graph completely changes the game. It's as if you replace multiple interconnected tables with a single intuitive mind-map, where your data points (nodes) and their relationships (edges) are visually explicit. Suddenly, exploring connections becomes natural, intuitive, and significantly more efficient.

Clear Benefits of Knowledge Graphs:

• Simplified Complex Queries: Graph databases naturally handle queries involving numerous relationships without cumbersome JOIN statements.
• Intuitive Visualizations: Data represented as interconnected nodes and edges provide immediate insights, eliminating mental overhead. And, let’s admit it—we all love visually appealing graphs.
• Improved Efficiency: Direct traversal between nodes often boosts query speed and performance.

How cognee Does Database Migration

Turning a relational database into a knowledge graph might sound like a huge leap—but with cognee, it’s surprisingly smooth. Here’s how the migration process works under the hood:

1. Extract the Schema

   cognee begins by calling extract_schema(), scanning your database to identify tables, primary keys, and the foreign keys that connect them.

2. Create Nodes

   Every table row becomes a node. For example, a user with id = 264674 from the users table might become a node named users:264674.

3. Create Edges

   cognee maps out relationships between tables based on foreign keys. For instance, if contributors.user_id references users.id, it creates an edge from the contributors node to the users node.

4. Embed and Index Your Data

   Finally, cognee computes vector embeddings for your data—numerical representations that capture semantic meaning. This enables powerful search capabilities over your new graph.

⚠️ Migration Tips for Best Results

To ensure smooth migration and meaningful graph construction:

• Define Foreign Keys Explicitly:

  cognee relies on foreign keys to build graph relationships. If your schema doesn’t clearly define them, nodes won’t be connected properly.

• Check Database Compatibility:

  At the moment, cognee supports PostgreSQL and SQLite. Make sure your .env file has the correct settings for the database you’re using.

Example Walkthrough: Building a Pokémon Knowledge Graph with dlt and cognee 🐱‍👤

When you pair cognee’s knowledge graph capabilities with dlt’s flexible data ingestion, your data pipeline quickly moves from scattered data to powerful, connected insights.

dlt (Data Load Tool) is a friendly Python library that lets you fetch, transform, and load data into your chosen database. With its SQLAlchemy destination, dlt makes inserting data into databases like SQLite or PostgreSQL painless.

Here's how cognee and dlt team up in practice:

Step-by-Step Migration Process

1. Set Up Your Environment 🛠️

Begin by copying the provided .env.template, rename it to .env, then fill in your database connection details and other required fields.

2. Collect and Load Data Using dlt + SQLite 📦

Here's a quick, straightforward example of how you might ingest Pokémon data from the PokéAPI into SQLite:

@dlt.resource(write_disposition="replace")
def pokemon_list(limit: int = 5):
    response = requests.get("https://pokeapi.co/api/v2/pokemon",
                            params={"limit": limit})
    yield response.json()["results"]

@dlt.transformer(data_from=pokemon_list)
def pokemon_details(pokemons):
    for pokemon in pokemons:
        yield requests.get(pokemon["url"]).json()

pipeline = dlt.pipeline(
    pipeline_name="pokemon_pipeline",
    destination=dlt.destinations.sqlalchemy(
        PatchedSqlalchemyCredentials("sqlite:///pokemon_data.db?timeout=15")
    ),
    dataset_name="main",
)
pipeline.run([pokemon_list, pokemon_details])

• Why dlt? One simple pipeline.run() call retrieves, structures, and stores API data—no need for manual pagination, retries, or insert handling.
• Why SQLite? It's lightweight, file-based, and perfect for quick demos. Prefer PostgreSQL? Simply adjust the connection string.

After this step, you'll have pokemon_data.db, neatly structured with your Pokémon data.

3. Patch Foreign Keys (a One-Time Tweak) 🔗

By design, dlt doesn't set formal FOREIGN KEY constraints. But cognee loves explicit foreign keys—they tell it precisely how to connect data.

Run this simple SQL command to fix that:

sqlite3 pokemon_data.db < examples/relational_db_with_dlt/fix_foreign_keys.sql

This script:

• Adds missing primary keys (_dlt_id columns).
• Turns columns like _dlt_parent_id into real FOREIGN KEY constraints.
• Enables foreign key enforcement (PRAGMA foreign_keys=ON).

✅ Already using PostgreSQL with defined foreign keys?

Great! You can skip this step.

4. Migrate Your Data: From Rows to a Knowledge Graph 🧠

Now let’s perform the migration with cognee:

engine = get_migration_relational_engine()
schema = await engine.extract_schema()           # ❶ read tables & keys

await create_relational_db_and_tables()          # ❷ prep metadata store
await create_pgvector_db_and_tables()            # ❸ prep vector store

graph_engine = await get_graph_engine()
await migrate_relational_database(graph_engine,  # ❹ convert rows → graph
                                  schema=schema)

Here's what's happening under the hood:

• Schema Extraction: Each table transforms into a node type, each foreign key into an edge type.
• Node Creation: Every row becomes a distinct node (table:primary_key, e.g., pokemon:25).
• Edge Linking: cognee translates every foreign key child → parent relationship into direct edges between nodes.
• Vector & Metadata Storage: Text fields become embedded vectors, enabling you to perform semantic searches later (e.g., “Which Pokémon can learn Water Pulse?”).

5. Visualize Your Graph (Optional but Recommended!) 🎨

After migration, you can generate an interactive HTML visualization, providing clarity on the relationships and structure of your data.

await visualize_graph()

6. Query in Plain English, Get Answers in Seconds 🚀

With cognee’s built-in embeddings, conversational graph querying is easy and intuitive:

search_results = await cognee.search(
    query_type=SearchType.GRAPH_COMPLETION,
    query_text="What kind of data do you contain?"
)
print(search_results)

A typical reply might be:

The data contains information about Pokémon, including their types, abilities, forms, game indices, held items, and statistics. It also tracks schema updates and loads in the database.

No complex JOINs or cumbersome SQL—just straightforward answers.

Unleash Your Data’s Hidden Potential with cognee

Once you see your relational data blossom into an interactive knowledge graph, you might never look at rows and tables the same way again. cognee doesn’t merely reorganize data—it opens up entirely new ways to explore, understand, and draw insights from complex information.

By turning your data into interconnected nodes and edges, you gain clarity, depth, and intuitive understanding that traditional databases struggle to match. It’s your gateway to advanced data exploration and smarter queries.

Ready to Try It Out?

1. Clone the cognee repository (and consider giving us a ⭐).
2. Point cognee at your PostgreSQL or SQLite database.
3. Ask your first natural-language question and watch your data come alive.

Still exploring and not quite ready for a demo? No worries! Learn more about what cognee does with this from-scratch post: "The Building Blocks of Knowledge Graphs: a Look at cognee's Approach".

Start thinking in graphs today—because interconnected data is more powerful data.