Time-Series Data Meets Blockchain: Storing Time-Series Data with Solidity, Ganache and Python

I was browsing x.com the other day and came across a tweet in Spanish from Mariano, a member of SysArmy. Just for fun and as a challenge, I decided to build a time-series data storage system using smart contracts. I can't help but laugh as I write this because, against all odds, it actually works pretty well.

Y? Como van esos proyectos con Blockchain?
— mariano.sh (@aragunde) October 17, 2024

A Few Things to Keep in Mind

Using a public blockchain probably isn’t the most cost-effective solution for this, mainly because of the gas fees required for every transaction. It’s also not the fastest option. However, using a private blockchain makes more sense for this particular use case. And of course, I did it for the classic reason all tech enthusiasts understand: not because it’s the best solution, but because I could do it.

Let’s Get Started

Here’s the plan: we’ll use Ganache to set up an internal Ethereum network, write and deploy smart contracts using Solidity and Truffle, and use Python to interact with the blockchain.

But before we start typing commands into the console, let's define a few things.

Smart Contracts as Databases: Conceptually, we’ll treat each smart contract as a database.
Storing Data Points: Using Solidity, we'll create a contract that lets us add data points with a timestamp and valueto various measurements (like "temperature" or "humidity").

I’m a huge fan of InfluxDB’s line protocol and how simple it is, so we’ll aim to replicate that schema. If you’re not familiar with it, here’s a quick example: "temperature", 42, 1729783306.

Deploying Ganache

Now that we’ve got a basic plan, let’s deploy Ganache. This tool will create a local Ethereum network on your computer and provide accounts with Ethereum addresses and private keys. You’ll use one of these accounts to deploy the contract and interact with the blockchain.

You can download Ganache from here:

Ganache - Truffle Suite

Quickly fire up a personal Ethereum blockchain which you can use to run tests, execute commands, and inspect state while controlling how the chain operates.

Truffle Logo

Also, if you have nvminstalled, you can run in your terminal this:

npm install -g ganache-cli

Once is installed, run Ganache doing this:

ganache-cli

You will see something like this:

ganache v7.9.2 (@ganache/cli: 0.10.2, @ganache/core: 0.10.2)
Starting RPC server

Available Accounts
==================
(0) 0xfC91D2E9b4fc7dD6258c0886f9C110BD353B50a6 (1000 ETH)
(1) 0xefbA0CB44F5d53aaBeFbbde41FFDcF151cfCF33C (1000 ETH)
(2) 0x709c88fD11dDba44aeD07Ca956059271450AE958 (1000 ETH)
(3) 0xa34c65e1EA5DA9a041621878B39f3334CFB7796f (1000 ETH)
(4) 0x6495F34b084A350601F81E74CFec0eEe3603F2A7 (1000 ETH)
(5) 0xa7827f684f2D1EDfF127d8d9D7625fD543Db880E (1000 ETH)
(6) 0x85217E5CD47225042f287A42321a6a7c0133a259 (1000 ETH)
(7) 0x617E74C5Faa90D29a9B9EC572F11f581F485ac75 (1000 ETH)
(8) 0xc56E0568362F88D1A107529Aa914dEC64eBE013B (1000 ETH)
(9) 0x108cda52Fd5DD40521D70413DD6AEDA2e0382A3E (1000 ETH)

Private Keys
==================
(0) 0xb22bb380153b13e6e7d97b98c4d92c3514955d77f23c498933b4393c1af49b76
(1) 0x6f31460485db5761470c0766d7e95f5b541e05a0054cf1afc2b1fb3894016cd1
(2) 0x25b8f4281be5999c7d44ff5d58faafc93bf222f2e01a2f5c7b09cd0ae7cf2962
(3) 0x3c7829f018cd1a96768704eee3c8e8aea82f406c5e3c133b806b7f80eb1bf7a8
(4) 0xe98b0fb1e708c3fcbc5fa03f16bd06b4f4299641488c82741d8c9097823b3776
(5) 0x99c163dec2e45e3d01211823ca6845d3b9952a32deec5f32eb51f6a76bb441af
(6) 0x7e5c1e58283be7f554cff8495745719ba6c3b34169aabd39051db6d6bcbcecd9
(7) 0x095134a44a8ef6f65f28f8ac02488825a80dbee0859ad3c173e15e6952cd8d6c
(8) 0x72c442d743ffc881cd6dd13289a616e0ca8daaec1f91b319d4320f42b6b11a7f
(9) 0x9c02a1ffcebd377d2a363910740781c1f6a1f39457da6335e5afff327e9b5567

HD Wallet
==================
Mnemonic:      chair vendor dial human olive dinner morning negative elevator alien catalog recipe
Base HD Path:  m/44'/60'/0'/0/{account_index}

Default Gas Price
==================
2000000000

BlockGas Limit
==================
30000000

Call Gas Limit
==================
50000000

Chain
==================
Hardfork: shanghai
Id:       1337

RPC Listening on 127.0.0.1:8545

This means that our network is up and running. As you can see, by default, we have ten accounts with their corresponding private keys.

Create a Truffle Project, Compile, and Deploy the Smart Contract

Now, let's create a project to write, compile, and deploy our smart contract to the network. Run the following commands:

mkdir blockchain-datastorage
cd blockchain-datastorage
truffle init

Let's modify the truffle-config.js file, should looks like this:

module.exports = {
  networks: {
    development: {
      host: "127.0.0.1",
      port: 8545,
      network_id: "*",
    },
  },
  compilers: {
    solc: {
      version: "0.8.0", // Solidity version to use
    },
  },
};

Create the Smart Contract Using Solidity

Now, let's create our smart contract using Solidity. Inside the contracts folder, create a file named Database.sol with the following content:

// SPDX-License-Identifier: MIT
pragma solidity ^0.8.0;

contract Database {
    struct DataPoint {
        uint256 timestamp;
        int256 value;
    }

    mapping(string => DataPoint[]) private measurements;

    function addDataPoint(string memory measurement, int256 value, uint256 timestamp) public {
        measurements[measurement].push(DataPoint(timestamp, value));
    }

    function getDataPoint(string memory measurement, uint256 index) public view returns (uint256, int256) {
        require(index < measurements[measurement].length, "Index out of bounds");
        DataPoint memory dataPoint = measurements[measurement][index];
        return (dataPoint.timestamp, dataPoint.value);
    }

    function getDataPointCount(string memory measurement) public view returns (uint256) {
        return measurements[measurement].length;
    }
}

As you can see here, we are defining the schema with three key components: the measurement (e.g., "temperature"), a value, and the timestamp for when the data point was recorded.

Compile the Contract

Now, let's compile the contract with the following command:

truffle compile

Deploy the Contract

Now, let's deploy the contract. First, create a new file in the migrations folder named 2_deploy_database.js with the following content:

const Database = artifacts.require("Database");

module.exports = function (deployer) {
  deployer.deploy(Database);
};

This script tells Truffle to deploy our Database contract to the network.

Once we have this saved, let's run this in the console:

truffle migrate --network development --reset

The output should look something like this:



Compiling your contracts...
===========================
> Everything is up to date, there is nothing to compile.


Starting migrations...
======================
> Network name:    'development'
> Network id:      1729783115705
> Block gas limit: 30000000 (0x1c9c380)


2_deploy_database.js
====================

   Deploying 'Database'
   --------------------
   > transaction hash:    0x395f273d8f96876f040dbda4ef4583cda15ecda8d6ce51fe6fcf15237922166d
   > Blocks: 0            Seconds: 0
   > contract address:    0xBA16d41238F91611caC04C958Da929cb78F2497B
   > block number:        1
   > block timestamp:     1729783252
   > account:             0xD410820FF4D7a00a03a0a58Bed49e81E4Ae29573
   > balance:             999.998579098
   > gas used:            421008 (0x66c90)
   > gas price:           3.375 gwei
   > value sent:          0 ETH
   > total cost:          0.001420902 ETH

   > Saving artifacts
   -------------------------------------
   > Total cost:         0.001420902 ETH

Summary
=======
> Total deployments:   1
> Final cost:          0.001420902 ETH

Save the contract address (0xYourContractAddressHere), as we’ll need it in a moment.

Writing and Retrieving Data Points Using Python

Now for the fun part—let's write some data points to our data warehouse (😆).

First, we need to install a package called web3:

python3 install web3

Python Code to Write and Retrieve Data Points

The code below will let us write and retrieve data points from our smart contract. For simplicity, everything is in a single script:

Note: Remember the smart contract address that I mentioned earlier? You’ll need it for this script. Additionally, you’ll need an account address and private key from one of the accounts displayed in the terminal running Ganache.

from web3 import Web3
import time

# Replace with the URL of your local Ganache instance
ganache_url = "http://127.0.0.1:8545"
w3 = Web3(Web3.HTTPProvider(ganache_url))

# Check if the connection is successful
if not w3.is_connected():
    raise ConnectionError(f"Failed to connect to the blockchain at {ganache_url}")

# Replace with your contract's ABI (copy this from Truffle build artifacts)
abi = [
    {
        "inputs": [
            {"internalType": "string", "name": "measurement", "type": "string"},
            {"internalType": "int256", "name": "value", "type": "int256"},
            {"internalType": "uint256", "name": "timestamp", "type": "uint256"}
        ],
        "name": "addDataPoint",
        "outputs": [],
        "stateMutability": "nonpayable",
        "type": "function"
    },
    {
        "inputs": [
            {"internalType": "string", "name": "measurement", "type": "string"},
            {"internalType": "uint256", "name": "index", "type": "uint256"}
        ],
        "name": "getDataPoint",
        "outputs": [
            {"internalType": "uint256", "name": "", "type": "uint256"},
            {"internalType": "int256", "name": "", "type": "int256"}
        ],
        "stateMutability": "view",
        "type": "function"
    },
    {
        "inputs": [{"internalType": "string", "name": "measurement", "type": "string"}],
        "name": "getDataPointCount",
        "outputs": [{"internalType": "uint256", "name": "", "type": "uint256"}],
        "stateMutability": "view",
        "type": "function"
    }
]

# Replace with your deployed contract address
contract_address = Web3.to_checksum_address("your_smart_contract_id")

# Create the contract instance using the checksum address
database_contract = w3.eth.contract(address=contract_address, abi=abi)

# Replace with your account address and private key
account_address = "your_account_address"
private_key = "your_private_key"

# Function to add a new data point and measure the time taken
def add_data_point(measurement, value, timestamp=None):
    if timestamp is None:
        timestamp = int(time.time())

    # Measure time for adding a data point
    start_time = time.time()
    
    transaction = database_contract.functions.addDataPoint(measurement, value, timestamp).build_transaction({
        'chainId': 1337,
        'gas': 2000000,
        'gasPrice': w3.to_wei('10', 'gwei'),
        'nonce': w3.eth.get_transaction_count(account_address),
    })

    signed_txn = w3.eth.account.sign_transaction(transaction, private_key=private_key)
    tx_hash = w3.eth.send_raw_transaction(signed_txn.raw_transaction)
    w3.eth.wait_for_transaction_receipt(tx_hash)

    end_time = time.time()
    elapsed_time = end_time - start_time
    print(f"Added data point to '{measurement}' with value {value}. Time taken: {elapsed_time:.4f} seconds")

# Function to retrieve a data point by index and measure the time taken
def get_data_point(measurement, index):
    start_time = time.time()

    timestamp, value = database_contract.functions.getDataPoint(measurement, index).call()

    end_time = time.time()
    elapsed_time = end_time - start_time
    print(f"Retrieved data point from '{measurement}' at index {index}. Time taken: {elapsed_time:.4f} seconds")
    return {'timestamp': timestamp, 'value': value}

# Function to retrieve the number of data points for a given measurement
def get_data_point_count(measurement):
    return database_contract.functions.getDataPointCount(measurement).call()

# Main function to insert and retrieve data for multiple measurements
def main():
    measurements = {
        "temperature": [42, 45, 47],
        "humidity": [30, 40, 50],
        "pressure": [1001, 1002, 1003]
    }

    # Add data points for each measurement
    for measurement, values in measurements.items():
        print(f"Adding data points to '{measurement}'...")
        for value in values:
            add_data_point(measurement, value)

    # Check the data point counts for each measurement
    for measurement in measurements.keys():
        print(f"\nChecking the data point count for '{measurement}'...")
        count = get_data_point_count(measurement)
        print(f"Data point count for '{measurement}': {count}")

        # Retrieve each data point for this measurement
        for i in range(count):
            data_point = get_data_point(measurement, i)
            print(f"{measurement.capitalize()} Data Point {i} - Timestamp: {data_point['timestamp']}, Value: {data_point['value']}")

if __name__ == "__main__":
    main()

In the script, you'll notice that we're hardcoding values for this demo, but it’s easy to see how you could integrate this with a Node-RED client to push real data. Now, let's run the script and see what we get:

python3 main.py

In this example, we are adding that from temperature, humidty and pressure so, the output looks like this:

Adding data points to 'temperature'...
Added data point to 'temperature' with value 42. Time taken: 0.0096 seconds
Added data point to 'temperature' with value 45. Time taken: 0.0087 seconds
Added data point to 'temperature' with value 47. Time taken: 0.0090 seconds
Adding data points to 'humidity'...
Added data point to 'humidity' with value 30. Time taken: 0.0096 seconds
Added data point to 'humidity' with value 40. Time taken: 0.0092 seconds
Added data point to 'humidity' with value 50. Time taken: 0.0097 seconds
Adding data points to 'pressure'...
Added data point to 'pressure' with value 1001. Time taken: 0.0105 seconds
Added data point to 'pressure' with value 1002. Time taken: 0.0110 seconds
Added data point to 'pressure' with value 1003. Time taken: 0.0091 seconds

Checking the data point count for 'temperature'...
Data point count for 'temperature': 12
Retrieved data point from 'temperature' at index 0. Time taken: 0.0040 seconds
Temperature Data Point 0 - Timestamp: 1729786885, Value: 42
Retrieved data point from 'temperature' at index 1. Time taken: 0.0036 seconds
Temperature Data Point 1 - Timestamp: 1729786885, Value: 45
Retrieved data point from 'temperature' at index 2. Time taken: 0.0039 seconds
Temperature Data Point 2 - Timestamp: 1729786885, Value: 47
Retrieved data point from 'temperature' at index 3. Time taken: 0.0038 seconds
Temperature Data Point 3 - Timestamp: 1729786887, Value: 42
Retrieved data point from 'temperature' at index 4. Time taken: 0.0044 seconds
Temperature Data Point 4 - Timestamp: 1729786887, Value: 45
Retrieved data point from 'temperature' at index 5. Time taken: 0.0044 seconds
Temperature Data Point 5 - Timestamp: 1729786887, Value: 47
Retrieved data point from 'temperature' at index 6. Time taken: 0.0041 seconds
Temperature Data Point 6 - Timestamp: 1729786888, Value: 42
Retrieved data point from 'temperature' at index 7. Time taken: 0.0035 seconds
Temperature Data Point 7 - Timestamp: 1729786888, Value: 45
Retrieved data point from 'temperature' at index 8. Time taken: 0.0037 seconds
Temperature Data Point 8 - Timestamp: 1729786888, Value: 47
Retrieved data point from 'temperature' at index 9. Time taken: 0.0037 seconds
Temperature Data Point 9 - Timestamp: 1729787175, Value: 42
Retrieved data point from 'temperature' at index 10. Time taken: 0.0033 seconds
Temperature Data Point 10 - Timestamp: 1729787175, Value: 45
Retrieved data point from 'temperature' at index 11. Time taken: 0.0040 seconds
Temperature Data Point 11 - Timestamp: 1729787175, Value: 47

Checking the data point count for 'humidity'...
Data point count for 'humidity': 12
Retrieved data point from 'humidity' at index 0. Time taken: 0.0036 seconds
Humidity Data Point 0 - Timestamp: 1729786885, Value: 30
Retrieved data point from 'humidity' at index 1. Time taken: 0.0038 seconds
Humidity Data Point 1 - Timestamp: 1729786885, Value: 40
Retrieved data point from 'humidity' at index 2. Time taken: 0.0046 seconds
Humidity Data Point 2 - Timestamp: 1729786885, Value: 50
Retrieved data point from 'humidity' at index 3. Time taken: 0.0038 seconds
Humidity Data Point 3 - Timestamp: 1729786887, Value: 30
Retrieved data point from 'humidity' at index 4. Time taken: 0.0041 seconds
Humidity Data Point 4 - Timestamp: 1729786887, Value: 40
Retrieved data point from 'humidity' at index 5. Time taken: 0.0035 seconds
Humidity Data Point 5 - Timestamp: 1729786887, Value: 50
Retrieved data point from 'humidity' at index 6. Time taken: 0.0038 seconds
Humidity Data Point 6 - Timestamp: 1729786888, Value: 30
Retrieved data point from 'humidity' at index 7. Time taken: 0.0036 seconds
Humidity Data Point 7 - Timestamp: 1729786888, Value: 40
Retrieved data point from 'humidity' at index 8. Time taken: 0.0039 seconds
Humidity Data Point 8 - Timestamp: 1729786888, Value: 50
Retrieved data point from 'humidity' at index 9. Time taken: 0.0036 seconds
Humidity Data Point 9 - Timestamp: 1729787175, Value: 30
Retrieved data point from 'humidity' at index 10. Time taken: 0.0036 seconds
Humidity Data Point 10 - Timestamp: 1729787175, Value: 40
Retrieved data point from 'humidity' at index 11. Time taken: 0.0033 seconds
Humidity Data Point 11 - Timestamp: 1729787175, Value: 50

Checking the data point count for 'pressure'...
Data point count for 'pressure': 12
Retrieved data point from 'pressure' at index 0. Time taken: 0.0038 seconds
Pressure Data Point 0 - Timestamp: 1729786885, Value: 1001
Retrieved data point from 'pressure' at index 1. Time taken: 0.0035 seconds
Pressure Data Point 1 - Timestamp: 1729786885, Value: 1002
Retrieved data point from 'pressure' at index 2. Time taken: 0.0034 seconds
Pressure Data Point 2 - Timestamp: 1729786885, Value: 1003
Retrieved data point from 'pressure' at index 3. Time taken: 0.0035 seconds
Pressure Data Point 3 - Timestamp: 1729786887, Value: 1001
Retrieved data point from 'pressure' at index 4. Time taken: 0.0036 seconds
Pressure Data Point 4 - Timestamp: 1729786887, Value: 1002
Retrieved data point from 'pressure' at index 5. Time taken: 0.0035 seconds
Pressure Data Point 5 - Timestamp: 1729786887, Value: 1003
Retrieved data point from 'pressure' at index 6. Time taken: 0.0033 seconds
Pressure Data Point 6 - Timestamp: 1729786888, Value: 1001
Retrieved data point from 'pressure' at index 7. Time taken: 0.0035 seconds
Pressure Data Point 7 - Timestamp: 1729786888, Value: 1002
Retrieved data point from 'pressure' at index 8. Time taken: 0.0033 seconds
Pressure Data Point 8 - Timestamp: 1729786888, Value: 1003
Retrieved data point from 'pressure' at index 9. Time taken: 0.0033 seconds
Pressure Data Point 9 - Timestamp: 1729787175, Value: 1001
Retrieved data point from 'pressure' at index 10. Time taken: 0.0034 seconds
Pressure Data Point 10 - Timestamp: 1729787175, Value: 1002
Retrieved data point from 'pressure' at index 11. Time taken: 0.0034 seconds
Pressure Data Point 11 - Timestamp: 1729787175, Value: 1003

So, basically, we're writing data in 9.6 milliseconds and reading it in an average of 3.5 milliseconds—not bad at all!

To conclude

This experiment was a lot of fun. I’ve never come across using Blockchain for a time-series use case like this, and now my mind is racing with questions. How feasible would this be on a private blockchain network where data is saved across multiple nodes, offering some level of redundancy? How expensive would it be in terms of resources to, let’s say, push 1 million metrics per second? Could a real data storage system be built from this? Instead of storing data directly on the filesystem, could we write it to something like S3?

This project sparked a lot of curiosity and questions, pushing me to think about how much deeper we could go and what else we could build. While I’m not convinced this makes sense for a real-world solution, I might give it a shot for the sake of learning.

What do you think about this project? What other use cases come to mind for this "Data Warehouse"?