trufnetwork · MicBun · May 29, 2025 · May 29, 2025 · May 29, 2025
diff --git a/internal/migrations/011-get-database-size.sql b/internal/migrations/011-get-database-size.sql
@@ -0,0 +1,39 @@
+CREATE OR REPLACE ACTION get_database_size() PUBLIC VIEW RETURNS TABLE(
+  database_size BIGINT
+) {
+  RETURN WITH RECURSIVE
+  table_stats AS (
+    SELECT 
+      'streams' AS table_name,
+      (SELECT COUNT(*) FROM streams) AS row_count,
+      128 AS avg_row_bytes,
+      100 AS avg_pk_index_bytes,
+      0 AS avg_other_index_bytes  -- Only PK exists
+    UNION ALL
+    SELECT 
+      'taxonomies',
+      (SELECT COUNT(*) FROM taxonomies),
+      258,
+      48,  -- PK index (UUID)
+      100  -- FK index to streams
+    UNION ALL
+    SELECT 
+      'primitive_events',
+      (SELECT COUNT(*) FROM primitive_events),
+      179,
+      120,  -- Composite PK index
+      100   -- FK index to streams
+    UNION ALL
+    SELECT 
+      'metadata',
+      (SELECT COUNT(*) FROM metadata),
+      230,  -- Average of your estimate
+      48,   -- PK index (UUID)
+      100   -- FK index to streams
+  )
+
+
+  SELECT
+    SUM(row_count * (avg_row_bytes + avg_pk_index_bytes + avg_other_index_bytes))::BIGINT
+  FROM table_stats;
+}
diff --git a/tests/streams/database_size_test.go b/tests/streams/database_size_test.go
@@ -0,0 +1,76 @@
+package tests
+
+import (
+	"context"
+	"fmt"
+	"testing"
+
+	kwilTesting "github.com/kwilteam/kwil-db/testing"
+	"github.com/pkg/errors"
+	"github.com/stretchr/testify/assert"
+	"github.com/trufnetwork/node/internal/migrations"
+	testutils "github.com/trufnetwork/node/tests/streams/utils"
+	"github.com/trufnetwork/node/tests/streams/utils/procedure"
+	"github.com/trufnetwork/node/tests/streams/utils/setup"
+	"github.com/trufnetwork/sdk-go/core/types"
+	"github.com/trufnetwork/sdk-go/core/util"
+)
+
+var (
+	streamName = "primitive_stream"
+	streamId   = util.GenerateStreamId(streamName)
+	deployer   = util.Unsafe_NewEthereumAddressFromString("0x0000000000000000000000000000000000000123")
+)
+
+func TestDatabaseSize(t *testing.T) {
+	kwilTesting.RunSchemaTest(t, kwilTesting.SchemaTest{
+		Name:        "database_size_test",
+		SeedScripts: migrations.GetSeedScriptPaths(),
+		FunctionTests: []kwilTesting.TestFunc{
+			WithTestDatabaseSizeSetup(testDatabaseSize(t)),
+		},
+	}, testutils.GetTestOptions())
+}
+
+func WithTestDatabaseSizeSetup(testFn func(ctx context.Context, platform *kwilTesting.Platform) error) kwilTesting.TestFunc {
+	return func(ctx context.Context, platform *kwilTesting.Platform) error {
+		// Set the platform signer
+		platform = procedure.WithSigner(platform, deployer.Bytes())
+
+		// Create the composed stream
+		err := setup.CreateStream(ctx, platform, setup.StreamInfo{
+			Locator: types.StreamLocator{
+				StreamId:     streamId,
+				DataProvider: deployer,
+			},
+			Type: setup.ContractTypePrimitive,
+		})
+		if err != nil {
+			return errors.Wrap(err, "error setting up stream")
+		}
+
+		return testFn(ctx, platform)
+	}
+}
+
+func testDatabaseSize(t *testing.T) func(ctx context.Context, platform *kwilTesting.Platform) error {
+	return func(ctx context.Context, platform *kwilTesting.Platform) error {
+		streamLocator := types.StreamLocator{
+			StreamId:     streamId,
+			DataProvider: deployer,
+		}
+
+		result, err := procedure.GetDatabaseSize(ctx, procedure.GetDatabaseSizeInput{
+			Platform:      platform,
+			Locator: streamLocator,
+			Height:        0,
+		})
+		if err != nil {
+			return errors.Wrap(err, "error getting database size")
+		}
+
+		assert.Equal(t, int64(2118), result, fmt.Sprintf("Actual: %d, Expected: 2118", result))
+
+		return nil
+	}
+}
diff --git a/tests/streams/utils/procedure/execute.go b/tests/streams/utils/procedure/execute.go
@@ -4,6 +4,7 @@ import (
 	"context"
 	"fmt"
 
+	trufTypes "github.com/trufnetwork/sdk-go/core/types"
 	"github.com/trufnetwork/sdk-go/core/util"
 
 	"github.com/kwilteam/kwil-db/common"
@@ -556,3 +557,40 @@ func ListStreams(ctx context.Context, input ListStreamsInput) ([]ResultRow, erro
 
 	return processResultRows(resultRows)
 }
+
+type GetDatabaseSizeInput struct {
+	Platform *kwilTesting.Platform
+	Locator  trufTypes.StreamLocator
+	Height   int64
+}
+func GetDatabaseSize(ctx context.Context, input GetDatabaseSizeInput) (int64, error) {
+	deployer, err := util.NewEthereumAddressFromBytes(input.Platform.Deployer)
+	if err != nil {
+		return 0, errors.Wrap(err, "failed to create Ethereum address from deployer bytes")
+	}
+
+	txContext := &common.TxContext{
+		Ctx:          ctx,
+		BlockContext: &common.BlockContext{Height: input.Height},
+		Signer:       input.Platform.Deployer,
+		Caller:       deployer.Address(),
+		TxID:         input.Platform.Txid(),
+	}
+
+	engineContext := &common.EngineContext{
+		TxContext: txContext,
+	}
+	var databaseSize *int64
+	r, err := input.Platform.Engine.Call(engineContext, input.Platform.DB, "", "get_database_size", []any{}, func(row *common.Row) error {
+		databaseSize = safe(row.Values[0], nil, int64PtrConverter);
+		return nil
+	})
+	if err != nil {
+		return 0, err
+	}
+	if r.Error != nil {
+		return 0, errors.Wrap(r.Error, "error in get_database_size")
+	}
+
+	return *databaseSize, nil
+}