SigNoz is an open-source, OpenTelemetry-native observability platform for your team and their AI agents. Get logs, metrics, and traces in one tool with features like APM, distributed tracing, log management, infra monitoring, etc. Combined with SigNoz MCP and a native AI teammate (in SigNoz Cloud) it helps you build more resilient apps.
TypeScript apmapplication-monitoringdistributed-tracinggogood-first-issuejaegerloglogsmetricsmonitoringnextjsobservabilityopen-sourceopentelemetryprometheusreactreactjsself-hostedtracingtypescript
package sqlschema
import (
"testing"
"github.com/stretchr/testify/assert"
"github.com/uptrace/bun/schema"
)
func TestIndexToCreateSQL(t *testing.T) {
testCases := []struct {
name string
index Index
sql string
}{
{
name: "Unique_1Column",
index: &UniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"id"},
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_users_id" ON "users" ("id")`,
},
{
name: "Unique_2Columns",
index: &UniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"id", "name"},
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_users_id_name" ON "users" ("id", "name")`,
},
{
name: "Unique_3Columns_Named",
index: &UniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"id", "name", "email"},
name: "my_index",
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "my_index" ON "users" ("id", "name", "email")`,
},
{
name: "Unique_Functional_SingleExpression",
index: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_users_72852951" ON "users" (LOWER(email))`,
},
{
name: "Unique_Functional_MixedColumnsAndExpressions",
index: &UniqueIndexWithExpressions{
TableName: "tag",
Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_tag_a36c51df" ON "tag" (org_id, kind, LOWER(key), LOWER(value))`,
},
{
name: "Unique_Functional_ComplexExpression",
index: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(TRIM(first_name) || ' ' || TRIM(last_name))"},
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_users_37e845f3" ON "users" (LOWER(TRIM(first_name) || ' ' || TRIM(last_name)))`,
},
{
name: "Unique_Functional_Named",
index: &UniqueIndexWithExpressions{
TableName: "tag",
Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
name: "uq_tag_org_kind_lower_key_lower_value",
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_tag_org_kind_lower_key_lower_value" ON "tag" (org_id, kind, LOWER(key), LOWER(value))`,
},
{
name: "PartialUnique_1Column",
index: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_users_email_94610c77" ON "users" ("email") WHERE "deleted_at" IS NULL`,
},
{
name: "PartialUnique_2Columns",
index: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"org_id", "email"},
Where: `"deleted_at" IS NULL`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_users_org_id_email_94610c77" ON "users" ("org_id", "email") WHERE "deleted_at" IS NULL`,
},
{
name: "PartialUnique_Named",
index: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
name: "my_partial_index",
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "my_partial_index" ON "users" ("email") WHERE "deleted_at" IS NULL`,
},
{
name: "PartialUnique_WhereWithParentheses",
index: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `("deleted_at" IS NULL)`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_users_email_94610c77" ON "users" ("email") WHERE ("deleted_at" IS NULL)`,
},
{
name: "PartialUnique_WhereWithQuotedIdentifier",
index: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"order" IS NULL`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_users_email_14c5f5f2" ON "users" ("email") WHERE "order" IS NULL`,
},
{
name: "PartialUnique_WhereWithQuotedLiteral",
index: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `status = 'somewhere'`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_users_email_9817c709" ON "users" ("email") WHERE status = 'somewhere'`,
},
{
name: "PartialUnique_WhereWith2Columns",
index: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email", "status"},
Where: `email = '[email protected]' AND status = 'active'`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_users_email_status_e70e78c3" ON "users" ("email", "status") WHERE email = '[email protected]' AND status = 'active'`,
},
// postgres docs example
{
name: "PartialUnique_WhereWithPostgresDocsExample_1",
index: &PartialUniqueIndex{
TableName: "access_log",
ColumnNames: []ColumnName{"client_ip"},
Where: `NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255')`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_access_log_client_ip_5a596410" ON "access_log" ("client_ip") WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255')`,
},
// postgres docs example
{
name: "PartialUnique_WhereWithPostgresDocsExample_2",
index: &PartialUniqueIndex{
TableName: "orders",
ColumnNames: []ColumnName{"order_nr"},
Where: `billed is not true`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_orders_order_nr_6d31bb0e" ON "orders" ("order_nr") WHERE billed is not true`,
},
// sqlite docs example
{
name: "PartialUnique_WhereWithSqliteDocsExample_1",
index: &PartialUniqueIndex{
TableName: "person",
ColumnNames: []ColumnName{"team_id"},
Where: `is_team_leader`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_person_team_id_c8604a29" ON "person" ("team_id") WHERE is_team_leader`,
},
// sqlite docs example
{
name: "PartialUnique_WhereWithSqliteDocsExample_2",
index: &PartialUniqueIndex{
TableName: "purchaseorder",
ColumnNames: []ColumnName{"parent_po"},
Where: `parent_po IS NOT NULL`,
},
sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_purchaseorder_parent_po_dbe2929d" ON "purchaseorder" ("parent_po") WHERE parent_po IS NOT NULL`,
},
}
for _, testCase := range testCases {
t.Run(testCase.name, func(t *testing.T) {
fmter := Formatter{schema.NewNopFormatter()}
sql := testCase.index.ToCreateSQL(fmter)
assert.Equal(t, testCase.sql, string(sql))
})
}
}
func TestIndexEquals(t *testing.T) {
testCases := []struct {
name string
a Index
b Index
equals bool
}{
{
name: "PartialUnique_Same",
a: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
},
b: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
},
equals: true,
},
{
name: "PartialUnique_NormalizedPostgresWhere",
a: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
},
b: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `(deleted_at IS NULL)`,
},
equals: true,
},
{
name: "PartialUnique_DifferentWhere",
a: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
},
b: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"active" = true`,
},
equals: false,
},
{
name: "PartialUnique_NotEqual_Unique",
a: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
},
b: &UniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
},
equals: false,
},
{
name: "Unique_NotEqual_PartialUnique",
a: &UniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
},
b: &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
},
equals: false,
},
{
name: "Unique_Functional_Same",
a: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
b: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
equals: true,
},
{
name: "Unique_Functional_CaseInsensitiveEqual",
a: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
b: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"lower(email)"},
},
equals: true,
},
{
name: "Unique_Functional_QuotedSimpleIdentifierEqualsUnquoted",
a: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
b: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{`LOWER("email")`},
},
equals: true,
},
{
name: "Unique_Functional_UnquotedMixedCaseEqualsLower",
a: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(Email)"},
},
b: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
equals: true,
},
{
name: "Unique_Functional_QuotedMixedCaseNotEqualUnquoted",
a: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{`LOWER("Email")`},
},
b: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
equals: false,
},
{
name: "Unique_Functional_DifferentExpressions",
a: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
b: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"UPPER(email)"},
},
equals: false,
},
{
name: "Unique_Functional_NotEqualToPlainSameColumns",
a: &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
},
b: &UniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
},
equals: false,
},
}
for _, testCase := range testCases {
t.Run(testCase.name, func(t *testing.T) {
assert.Equal(t, testCase.equals, testCase.a.Equals(testCase.b))
})
}
}
func TestUniqueIndexFunctionalName(t *testing.T) {
t.Run("autogen uses uq_<table>_<hash>", func(t *testing.T) {
idx := &UniqueIndexWithExpressions{
TableName: "tag",
Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
}
assert.Equal(t, "uq_tag_a36c51df", idx.Name())
})
t.Run("same expressions produce the same name", func(t *testing.T) {
a := &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
}
b := &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
}
assert.Equal(t, a.Name(), b.Name())
})
t.Run("different expressions produce different names", func(t *testing.T) {
a := &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
}
b := &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"UPPER(email)"},
}
assert.NotEqual(t, a.Name(), b.Name())
})
t.Run("expressions in different order produce different names", func(t *testing.T) {
a := &UniqueIndexWithExpressions{
TableName: "tag",
Expressions: []string{"org_id", "LOWER(key)"},
}
b := &UniqueIndexWithExpressions{
TableName: "tag",
Expressions: []string{"LOWER(key)", "org_id"},
}
assert.NotEqual(t, a.Name(), b.Name())
})
t.Run("functional autogen differs from plain autogen for same columns", func(t *testing.T) {
plain := &UniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
}
functional := &UniqueIndexWithExpressions{
TableName: "users",
Expressions: []string{"LOWER(email)"},
}
assert.Equal(t, "uq_users_email", plain.Name())
assert.NotEqual(t, plain.Name(), functional.Name())
})
t.Run("Named() override wins over hash", func(t *testing.T) {
idx := (&UniqueIndexWithExpressions{
TableName: "tag",
Expressions: []string{"org_id", "LOWER(key)"},
}).Named("my_functional_index")
assert.Equal(t, "my_functional_index", idx.Name())
})
}
func TestNormalizeExpressions(t *testing.T) {
testCases := []struct {
name string
expressions []string
output []string
}{
{
name: "Empty",
expressions: nil,
output: nil,
},
{
name: "PlainColumnsUnchanged",
expressions: []string{"org_id", "kind"},
output: []string{"org_id", "kind"},
},
{
name: "FunctionNameCaseFolded",
expressions: []string{"LOWER(key)", "LOWER(value)"},
output: []string{"lower(key)", "lower(value)"},
},
{
name: "PostgresRenderedMatchesDeclared",
expressions: []string{"lower(key)"},
output: []string{"lower(key)"},
},
{
name: "WhitespaceCollapsedPerExpression",
expressions: []string{"LOWER( key )", "org_id"},
output: []string{"lower( key )", "org_id"},
},
{
name: "RedundantOuterParenthesesStripped",
expressions: []string{"(LOWER(key))"},
output: []string{"lower(key)"},
},
{
name: "QuotedSimpleIdentifierUnquoted",
expressions: []string{`LOWER("email")`},
output: []string{"lower(email)"},
},
{
name: "QuotedIdentifierCaseByIdentifierPreserved",
expressions: []string{`LOWER("Key")`},
output: []string{`lower("Key")`},
},
{
name: "StringLiteralCasePreserved",
expressions: []string{"COALESCE(status, 'Deleted')"},
output: []string{"coalesce(status, 'Deleted')"},
},
{
name: "NormalizedIndependentlyPerElement",
expressions: []string{"UPPER(a)", "b", "LOWER(c)"},
output: []string{"upper(a)", "b", "lower(c)"},
},
}
for _, testCase := range testCases {
t.Run(testCase.name, func(t *testing.T) {
assert.Equal(t, testCase.output, normalizeExpressions(testCase.expressions))
})
}
}
func TestPartialUniqueIndexName(t *testing.T) {
a := &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"deleted_at" IS NULL`,
}
b := &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `(deleted_at IS NULL)`,
}
c := &PartialUniqueIndex{
TableName: "users",
ColumnNames: []ColumnName{"email"},
Where: `"active" = true`,
}
assert.Equal(t, "puq_users_email_94610c77", a.Name())
assert.Equal(t, a.Name(), b.Name())
assert.NotEqual(t, a.Name(), c.Name())
}