Testing

ASTQL queries are testable without a database connection. This guide covers testing patterns and best practices.

Testing Query Output

Basic Output Testing

func TestUserQuery(t *testing.T) {
    instance := setupTestInstance()

    result, err := astql.Select(instance.T("users")).
        Fields(instance.F("username"), instance.F("email")).
        Where(instance.C(instance.F("active"), astql.EQ, instance.P("is_active"))).
        Render(postgres.New())

    if err != nil {
        t.Fatalf("unexpected error: %v", err)
    }

    expected := `SELECT "username", "email" FROM "users" WHERE "active" = :is_active`
    if result.SQL != expected {
        t.Errorf("SQL mismatch\ngot:  %s\nwant: %s", result.SQL, expected)
    }

    if len(result.RequiredParams) != 1 || result.RequiredParams[0] != "is_active" {
        t.Errorf("params mismatch: got %v", result.RequiredParams)
    }
}

Test Instance Setup

Create a dedicated schema for tests:

func setupTestInstance() *astql.ASTQL {
    project := dbml.NewProject("test")

    users := dbml.NewTable("users")
    users.AddColumn(dbml.NewColumn("id", "bigint"))
    users.AddColumn(dbml.NewColumn("username", "varchar"))
    users.AddColumn(dbml.NewColumn("email", "varchar"))
    users.AddColumn(dbml.NewColumn("active", "boolean"))
    project.AddTable(users)

    posts := dbml.NewTable("posts")
    posts.AddColumn(dbml.NewColumn("id", "bigint"))
    posts.AddColumn(dbml.NewColumn("user_id", "bigint"))
    posts.AddColumn(dbml.NewColumn("title", "varchar"))
    posts.AddColumn(dbml.NewColumn("published", "boolean"))
    project.AddTable(posts)

    instance, err := astql.NewFromDBML(project)
    if err != nil {
        panic(err)
    }
    return instance
}

Table-Driven Tests

Testing Multiple Queries

func TestQueries(t *testing.T) {
    instance := setupTestInstance()

    tests := []struct {
        name     string
        query    func() (*astql.QueryResult, error)
        wantSQL  string
        wantParams []string
    }{
        {
            name: "simple select",
            query: func() (*astql.QueryResult, error) {
                return astql.Select(instance.T("users")).
                    Fields(instance.F("username")).
                    Render(postgres.New())
            },
            wantSQL:    `SELECT "username" FROM "users"`,
            wantParams: nil,
        },
        {
            name: "select with where",
            query: func() (*astql.QueryResult, error) {
                return astql.Select(instance.T("users")).
                    Where(instance.C(instance.F("id"), astql.EQ, instance.P("id"))).
                    Render(postgres.New())
            },
            wantSQL:    `SELECT * FROM "users" WHERE "id" = :id`,
            wantParams: []string{"id"},
        },
        {
            name: "select with order and limit",
            query: func() (*astql.QueryResult, error) {
                return astql.Select(instance.T("users")).
                    OrderBy(instance.F("username"), astql.ASC).
                    Limit(10).
                    Render(postgres.New())
            },
            wantSQL:    `SELECT * FROM "users" ORDER BY "username" ASC LIMIT 10`,
            wantParams: nil,
        },
    }

    for _, tt := range tests {
        t.Run(tt.name, func(t *testing.T) {
            result, err := tt.query()
            if err != nil {
                t.Fatalf("unexpected error: %v", err)
            }
            if result.SQL != tt.wantSQL {
                t.Errorf("SQL mismatch\ngot:  %s\nwant: %s", result.SQL, tt.wantSQL)
            }
            if !slicesEqual(result.RequiredParams, tt.wantParams) {
                t.Errorf("params mismatch\ngot:  %v\nwant: %v", result.RequiredParams, tt.wantParams)
            }
        })
    }
}

func slicesEqual(a, b []string) bool {
    if len(a) != len(b) {
        return false
    }
    for i := range a {
        if a[i] != b[i] {
            return false
        }
    }
    return true
}

Testing Validation

Testing Invalid Input

func TestInvalidTable(t *testing.T) {
    instance := setupTestInstance()

    defer func() {
        if r := recover(); r == nil {
            t.Error("expected panic for invalid table")
        }
    }()

    instance.T("nonexistent")
}

func TestInvalidField(t *testing.T) {
    instance := setupTestInstance()

    defer func() {
        if r := recover(); r == nil {
            t.Error("expected panic for invalid field")
        }
    }()

    instance.F("nonexistent")
}

Testing Try Variants

func TestTryT(t *testing.T) {
    instance := setupTestInstance()

    // Valid table
    table, err := instance.TryT("users")
    if err != nil {
        t.Errorf("unexpected error for valid table: %v", err)
    }
    if table.Name != "users" {
        t.Errorf("wrong table name: %s", table.Name)
    }

    // Invalid table
    _, err = instance.TryT("nonexistent")
    if err == nil {
        t.Error("expected error for invalid table")
    }
}

Testing Error Cases

Builder Errors

func TestBuilderErrors(t *testing.T) {
    instance := setupTestInstance()

    // Fields on non-SELECT query
    _, err := astql.Insert(instance.T("users")).
        Fields(instance.F("username")).
        Render(postgres.New())

    if err == nil {
        t.Error("expected error for Fields on INSERT")
    }

    // SET on non-UPDATE query
    _, err = astql.Select(instance.T("users")).
        Set(instance.F("username"), instance.P("value")).
        Render(postgres.New())

    if err == nil {
        t.Error("expected error for Set on SELECT")
    }
}

Testing Complex Queries

Join Tests

func TestJoinQuery(t *testing.T) {
    instance := setupTestInstance()

    result, err := astql.Select(instance.T("users", "u")).
        Fields(
            instance.WithTable(instance.F("username"), "u"),
            instance.WithTable(instance.F("title"), "p"),
        ).
        LeftJoin(instance.T("posts", "p"), astql.CF(
            instance.WithTable(instance.F("id"), "u"),
            astql.EQ,
            instance.WithTable(instance.F("user_id"), "p"),
        )).
        Render(postgres.New())

    if err != nil {
        t.Fatalf("unexpected error: %v", err)
    }

    // Check key parts of the query
    if !strings.Contains(result.SQL, "LEFT JOIN") {
        t.Error("missing LEFT JOIN")
    }
    if !strings.Contains(result.SQL, `u."id" = p."user_id"`) {
        t.Error("missing join condition")
    }
}

Subquery Tests

func TestSubquery(t *testing.T) {
    instance := setupTestInstance()

    subquery := astql.Sub(
        astql.Select(instance.T("posts")).
            Fields(instance.F("user_id")).
            Where(instance.C(instance.F("published"), astql.EQ, instance.P("is_pub"))),
    )

    result, err := astql.Select(instance.T("users")).
        Where(astql.CSub(instance.F("id"), astql.IN, subquery)).
        Render(postgres.New())

    if err != nil {
        t.Fatalf("unexpected error: %v", err)
    }

    // Check parameter namespacing
    if !strings.Contains(result.SQL, ":sq1_is_pub") {
        t.Error("subquery parameter not namespaced")
    }
}

Snapshot Testing

For complex queries, consider snapshot testing:

func TestComplexQuery_Snapshot(t *testing.T) {
    instance := setupTestInstance()

    result, err := buildComplexQuery(instance)
    if err != nil {
        t.Fatalf("unexpected error: %v", err)
    }

    golden := filepath.Join("testdata", t.Name()+".golden.sql")

    if *update {
        os.WriteFile(golden, []byte(result.SQL), 0644)
        return
    }

    expected, err := os.ReadFile(golden)
    if err != nil {
        t.Fatalf("failed to read golden file: %v", err)
    }

    if result.SQL != string(expected) {
        t.Errorf("SQL mismatch with golden file\ngot:\n%s", result.SQL)
    }
}

Best Practices

1. Test Query Structure, Not Exact Strings

For complex queries, test for required components:

func TestQueryContains(t *testing.T) {
    result, _ := buildQuery()

    checks := []string{
        `FROM "users"`,
        `WHERE "active"`,
        `ORDER BY`,
        `LIMIT`,
    }

    for _, check := range checks {
        if !strings.Contains(result.SQL, check) {
            t.Errorf("missing: %s", check)
        }
    }
}

2. Test Parameter Lists

Always verify required parameters:

func TestParams(t *testing.T) {
    result, _ := buildQuery()

    expected := []string{"user_id", "status", "limit"}
    for _, param := range expected {
        found := false
        for _, p := range result.RequiredParams {
            if p == param {
                found = true
                break
            }
        }
        if !found {
            t.Errorf("missing param: %s", param)
        }
    }
}

3. Isolate Test Schemas

Each test file or package should have its own schema setup:

// users_test.go
func setupUsersTestInstance() *astql.ASTQL { ... }

// orders_test.go
func setupOrdersTestInstance() *astql.ASTQL { ... }

4. Test Edge Cases

func TestEdgeCases(t *testing.T) {
    instance := setupTestInstance()

    // Empty fields (SELECT *)
    result, _ := astql.Select(instance.T("users")).Render(postgres.New())
    if !strings.Contains(result.SQL, "SELECT *") {
        t.Error("empty fields should produce SELECT *")
    }

    // Multiple WHERE calls
    result, _ = astql.Select(instance.T("users")).
        Where(instance.C(instance.F("active"), astql.EQ, instance.P("a"))).
        Where(instance.C(instance.F("id"), astql.EQ, instance.P("b"))).
        Render(postgres.New())
    if !strings.Contains(result.SQL, "AND") {
        t.Error("multiple WHERE should combine with AND")
    }
}