Build a powerful, local crypto analytics database with DuckDB. This tutorial guides you through creating an ETL pipeline for Uniswap v3 data to run complex SQL queries instantly.
Why make thousands of slow, rate-limited API calls when you can run complex SQL queries instantly on your own machine? This tutorial introduces the most effective pattern for crypto data analysis: creating a local, high-performance copy of a complete on-chain dataset. By fetching the data once, you unlock unlimited, high-speed analytical capabilities.
Looking for other analytics solutions? Check out our full list of API Tutorials for more step-by-step guides.
Our “free tier” isn’t about delayed or incomplete data; it’s about providing a full, live, but scope-limited dataset for you to master. We’re giving you the tools, but it’s up to you to use them in the way that makes the most sense for your project.
The goal:
By the end of this guide, you will have a local uniswap_v3.db
file containing all pools and their recent trading history from Uniswap v3 on Ethereum. You will be able to:
Why this is a foundational skill:
Create a Python script to fetch complete Uniswap v3 pool and OHLCV data.
Populate your database and run complex SQL queries to find insights.
Connect your database to an AI assistant for natural language queries.
First, let’s create a Python script to act as our ETL (Extract, Transform, Load) pipeline. This script will fetch all pool data for Uniswap v3 on Ethereum and their recent trading history, then load it into a local DuckDB database file. It leverages two key endpoints: the Top Pools on a DEX endpoint to discover pools, and the Pool OHLCV Data endpoint to fetch historical price data.
Create a new file named build_uniswap_db.py
.
A simple, sequential script is great for learning, but real-world data fetching requires a more robust approach. Here is what we’ve used to make sure it runs reliably:
asyncio
and aiohttp
, the script can make many API requests concurrently instead of one by one. This means shorter time for completion.get_pool_ohlcv
function calculates how much data to request per API call so that it gets all the data for each pool.asyncio.Semaphore
, combined with carefully tuned BATCH_SIZE
and asyncio.sleep()
calls, makes sure we don’t hit the rate limit.fetch_with_retry
function automatically retries failed requests with an exponential backoff delay, making the pipeline resilient to temporary network issues.Before running the script, make sure you have the necessary Python libraries installed.
Now, execute the script from your terminal. It will fetch all Uniswap v3 pool data from Ethereum and their recent trading history, then create a uniswap_v3.db
file in a new dbs
directory. This may take several minutes, but it will be significantly faster than a purely sequential script.
Once the script completes, you have a powerful local database at your fingertips. You can now use any SQL client that supports DuckDB, or Python itself, to perform instant, complex analysis. In step 3, we will connect the database to an AI assistant for natural language queries.
If you want to query the database with a Python script, create a new file named query_duckdb.py
and paste the following code into it.
Now, execute the script from your terminal:
While you can use any SQL client to query your database, the real power comes from connecting it to an AI assistant. By using a Model Context Protocol (MCP) server, you can enable your assistant to directly query the uniswap_v3.db
file you created. This allows you to ask for insights in plain English instead of writing SQL.
For this, we will use mcp-server-duckdb
, an open-source MCP server for DuckDB.
You can install the server easily using npx
:
Next, you need to tell your AI assistant how to run the server. Add the following to your claude_desktop_config.json
file.
If you see a “Server disconnected” error after restarting your AI assistant, it means the application cannot find the uvx
or npx
command. This happens because the application doesn’t share the same PATH
environment variable as your terminal.
To fix this, you must use the full, absolute path to the command.
which uvx
or which npx
in your terminal./Users/yourname/.local/bin/uvx
or /opt/homebrew/bin/npx
).command
value in the JSON configuration below.The example below uses uvx
, which is recommended. Make sure to replace </path/to/your/project>
with the actual absolute path to your project directory.
Now, when you start your AI assistant, it will have the tools to query your local Uniswap V3 database. You can ask it things like:
By completing this tutorial, you have successfully transitioned from being a passive data consumer to an active data analyst. You’ve replaced the slow, restrictive pattern of making individual API calls with a fast, powerful, and scalable local analytics workflow.
Key achievements:
uniswap_v3.db
file is a valuable, reusable asset for any future analysis, dashboarding, or AI integration project.When your project grows and you need to explore other data solutions, check out our full list of API Tutorials for more advanced guides.
Build a powerful, local crypto analytics database with DuckDB. This tutorial guides you through creating an ETL pipeline for Uniswap v3 data to run complex SQL queries instantly.
Why make thousands of slow, rate-limited API calls when you can run complex SQL queries instantly on your own machine? This tutorial introduces the most effective pattern for crypto data analysis: creating a local, high-performance copy of a complete on-chain dataset. By fetching the data once, you unlock unlimited, high-speed analytical capabilities.
Looking for other analytics solutions? Check out our full list of API Tutorials for more step-by-step guides.
Our “free tier” isn’t about delayed or incomplete data; it’s about providing a full, live, but scope-limited dataset for you to master. We’re giving you the tools, but it’s up to you to use them in the way that makes the most sense for your project.
The goal:
By the end of this guide, you will have a local uniswap_v3.db
file containing all pools and their recent trading history from Uniswap v3 on Ethereum. You will be able to:
Why this is a foundational skill:
Create a Python script to fetch complete Uniswap v3 pool and OHLCV data.
Populate your database and run complex SQL queries to find insights.
Connect your database to an AI assistant for natural language queries.
First, let’s create a Python script to act as our ETL (Extract, Transform, Load) pipeline. This script will fetch all pool data for Uniswap v3 on Ethereum and their recent trading history, then load it into a local DuckDB database file. It leverages two key endpoints: the Top Pools on a DEX endpoint to discover pools, and the Pool OHLCV Data endpoint to fetch historical price data.
Create a new file named build_uniswap_db.py
.
A simple, sequential script is great for learning, but real-world data fetching requires a more robust approach. Here is what we’ve used to make sure it runs reliably:
asyncio
and aiohttp
, the script can make many API requests concurrently instead of one by one. This means shorter time for completion.get_pool_ohlcv
function calculates how much data to request per API call so that it gets all the data for each pool.asyncio.Semaphore
, combined with carefully tuned BATCH_SIZE
and asyncio.sleep()
calls, makes sure we don’t hit the rate limit.fetch_with_retry
function automatically retries failed requests with an exponential backoff delay, making the pipeline resilient to temporary network issues.Before running the script, make sure you have the necessary Python libraries installed.
Now, execute the script from your terminal. It will fetch all Uniswap v3 pool data from Ethereum and their recent trading history, then create a uniswap_v3.db
file in a new dbs
directory. This may take several minutes, but it will be significantly faster than a purely sequential script.
Once the script completes, you have a powerful local database at your fingertips. You can now use any SQL client that supports DuckDB, or Python itself, to perform instant, complex analysis. In step 3, we will connect the database to an AI assistant for natural language queries.
If you want to query the database with a Python script, create a new file named query_duckdb.py
and paste the following code into it.
Now, execute the script from your terminal:
While you can use any SQL client to query your database, the real power comes from connecting it to an AI assistant. By using a Model Context Protocol (MCP) server, you can enable your assistant to directly query the uniswap_v3.db
file you created. This allows you to ask for insights in plain English instead of writing SQL.
For this, we will use mcp-server-duckdb
, an open-source MCP server for DuckDB.
You can install the server easily using npx
:
Next, you need to tell your AI assistant how to run the server. Add the following to your claude_desktop_config.json
file.
If you see a “Server disconnected” error after restarting your AI assistant, it means the application cannot find the uvx
or npx
command. This happens because the application doesn’t share the same PATH
environment variable as your terminal.
To fix this, you must use the full, absolute path to the command.
which uvx
or which npx
in your terminal./Users/yourname/.local/bin/uvx
or /opt/homebrew/bin/npx
).command
value in the JSON configuration below.The example below uses uvx
, which is recommended. Make sure to replace </path/to/your/project>
with the actual absolute path to your project directory.
Now, when you start your AI assistant, it will have the tools to query your local Uniswap V3 database. You can ask it things like:
By completing this tutorial, you have successfully transitioned from being a passive data consumer to an active data analyst. You’ve replaced the slow, restrictive pattern of making individual API calls with a fast, powerful, and scalable local analytics workflow.
Key achievements:
uniswap_v3.db
file is a valuable, reusable asset for any future analysis, dashboarding, or AI integration project.When your project grows and you need to explore other data solutions, check out our full list of API Tutorials for more advanced guides.