Skip to main content Link Search Menu Expand Document (external link)
© 2022, MakerDojo, Inc. All rights reserved

Reading the live state of a smart contract


One of the challenges of working with smart contracts is the need to use APIs and other specialized tools to interact with the contract and read its state. This can be time-consuming and may require a certain level of technical expertise, especially if you want to perform more complex queries or integrate the contract data into other systems.

Reading the live state of a smart contract using SQL is a powerful and convenient way to access and analyze contract data. By leveraging the familiar and powerful capabilities of SQL, you can easily integrate contract data into your existing workflow and perform advanced analysis such as data mining and machine learning.

Read contract state using SQL

To read the live state of a smart contract using SQL

  • you can use a SELECT statement with the live.chain_name.contract_call table, where chain_name is the name of the blockchain (we currently support ethereum, celo) and
  • Use the WHERE clause for passing contract address using the column contract_address
  • function signature you want to call using function_signature

For example, the following SQL query can be used to read the name of a contract on Celo with the address 0x67316300f17f063085Ca8bCa4bd3f7a5a3C66275 and the function signature name():

SELECT contract_name
FROM live.celo.contract_call
WHERE contract_address=lower('0x67316300f17f063085Ca8bCa4bd3f7a5a3C66275')
  AND function_signature='name()'

Read contract state by passing input params

If you want to call a contract function that takes input parameters, you can use the input_params column in the live.chain_name.contract_call table to pass the input parameters to the function. The input_params column expects a JSON value containing the input parameters for the function.

For example, the following query can be used to call the numRates function on a contract on Celo with the address 0xaf5D514bB94023C9Af979821F59A5Eecde0986EF, which takes a single address input parameter:

SELECT *
FROM live.celo.contract_call
WHERE 
  contract_address='0xaf5D514bB94023C9Af979821F59A5Eecde0986EF'
  AND function_signature='numRates(address)'
  AND input_params IN (
    '{"token":"0x765de816845861e75a25fca122bb6898b8b1282a"}',
    '{"token":"0xd8763cba276a3738e6de85b4b3bf5fded6d6ca73"}'
  )

This query will return the result of calling the numRates function with the two specified input parameters. You can use the json_extract function to extract specific values from the returned result, as shown in the previous example.

Parse read contract state results using JSON functions

This query will return the name of the contract as a string. If you want to call a function that returns multiple values or a more complex data structure, you can use the json_extract function to extract the specific values you are interested in.

For example, to extract the balance field from a contract that has a getBalance function that returns an object with a balance field, you can use the following query:

SELECT json_extract(result, '$.balance') as balance
FROM live.celo.contract_call
WHERE contract_address=lower('0x67316300f17f063085Ca8bCa4bd3f7a5a3C66275')
  AND function_signature='getBalance()'

Read contract state for unverified contracts using ABI

If you are working with a smart contract that is not verified or not part of the existing indexed contracts, you may need to provide the contract’s ABI (Application Binary Interface) in order to read its live state using SQL. The ABI is a JSON file that describes the contract’s functions, their inputs and outputs, and other information needed to interact with the contract.

To provide the contract’s ABI, you can use the contract_abi column in the live.chain_name.contract_call table. This column expects a base64 encoded string containing the contract’s ABI.

For example, the following query can be used to read the live state of a contract on Celo with the address 0x67316300f17f063085Ca8bCa4bd3f7a5a3C66275 and the function signature name(), using the contract’s ABI:

SELECT contract_name
FROM live.celo.contract_call
WHERE contract_address=lower('0x67316300f17f063085Ca8bCa4bd3f7a5a3C66275')
  AND contract_abi='<base 64 encoded ABI>'
  AND function_signature='name()'

Note that the contract_abi column is optional, and you only need to provide it if the contract is not verified or not part of the existing indexed contracts. If the contract is verified or part of the indexed contracts, you do not need to provide the ABI and can simply use the contract_address and function_signature columns to read the live state of the contract.