Getting To Know The Database

Pool Lookups With SQLAlchemy & Simple Pathfinding

The database integration is now available in the degenbot 0.5 beta. Instead of tossing it out there and hoping people figure it out, I’ll demonstrate and discuss some ways it can be used.

I encourage you to try out the beta release in a virtual environment and build a minimal database of V2 pools on Base mainnet, which is fast to populate with a fairly simple set of commands:

degenbot activate base_aerodrome_v2
degenbot activate base_pancakeswap_v2
degenbot activate base_sushiswap_v2
degenbot activate base_swapbased_v2
degenbot activate base_uniswap_v2

# pause here to make sure an RPC is defined in config.toml and to update # database schema

degenbot pool update

The V3 / V4 updaters will take much longer to run, and can be done later once you’re comfortable.

SQLAlchemy

I’ve used SQLAlchemy as the object-relational mapper (ORM) to translate the information in the database to our running applications. The classes are available under the degenbot.database.models module. Any class that derives from Base defines a database table.

These classes have fairly straightforward names that suggest the information contained in each.

Examples:

• Erc20TokenTable holds information for ERC-20 tokens

  • Chain ID

  • Address

  • Decimals

  • Name

  • Symbol

• LiquidityPoolTable is a polymorphic table class that holds a subset of information common to all liquidity pools, and a link to other subclasses with exchange-specific info as needed

  • Chain ID

  • Address

  • Kind ­— a label which maps different liquidity pools associated with a particular exchange to separate tables

    • An entry with kind “uniswap_v2” identifies this pool as Uniswap V2, and each entry in UniswapV2PoolTable will refer back to this base table with a foreign key.

  • Token0 ID & Token1 ID — both map to entries in Erc20TokenTable so that token specific data can be looked up via foreign key instead of duplicated in pool tables

  • Fee

• LiquidityPositionTable holds information about Uniswap V3 liquidity positions

  • Pool ID — a foreign key mapping the position to a given pool

  • Tick

  • Liquidity (Net)

  • Liquidity (Gross)

• UniswapV4PoolTable holds information about Uniswap V4 liquidity pools. These are all managed by a single Pool Manager contract, so they all have a common address. Thus they are tracked independently of the pools defined in LiquidityPoolTable, which are uniquely identified by their address

  • Pool Hash — referred to as the Pool ID by Uniswap, this is a 32 byte hash that uniquely identifies the pool at the Pool Manager

  • Hooks — an address that identifies the various hooks enabled by the pool

  • Currency0 ID & Currency1 ID — like the ERC-20 references from above

  • Fee

  • Tick spacing

  • Kind — a label to identify the particular exchange

• ManagedPoolLiquidityPositionTable holds liquidity positions for managed pools. Essentially identical to LiquidityPositionTable except that it points to managed pools in ManagedLiquidityPoolTable

• PoolManagerTable which holds information about individual Pool Manager contracts

  • Chain ID

  • Address

  • Kind — a label to identify the particular exchange

Relationships

A clear win from using an ORM is that links between tables can be expressed as a relationship and accessed like an attribute, instead of having to write complex JOIN queries.