SQL Server

Provides an API for accessing MSSQL databases

Technology

SQL Server
Golang
Project Forge

The sqlserver module provides Microsoft SQL Server database integration for Project Forge applications. This module extends the base database module with SQL Server-specific functionality, enterprise features, and optimizations for Microsoft SQL Server environments.

Overview

This module adds Microsoft SQL Server database support to Project Forge applications and requires the database module. It provides:

Key Features

SQL Server Compatibility

Enterprise Features

Performance

Security

Configuration

Environment Variables

The module reads configuration from environment variables (with optional prefix):

Windows Authentication

1
2
3
# Use Windows Authentication (omit user/password)
DB_HOST=localhost\\SQLEXPRESS
DB_DATABASE=MyDatabase

Usage

Basic Setup

1
2
3
4
5
6
7
8
9
// Load configuration from environment
params := SQLServerParamsFromEnv("")

// Open database connection
db, err := database.OpenSQLServerDatabase(params)
if err != nil {
    return err
}
defer db.Close()

Custom Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// Manual configuration
params := &SQLServerParams{
    Host:           "sqlserver.example.com",
    Port:           1433,
    User:           "app_user",
    Password:       "secure_password",
    Database:       "app_database",
    Schema:         "app_schema",
    MaxConnections: 25,
    Debug:          false,
}

db, err := database.OpenSQLServerDatabase(params)

Windows Authentication

1
2
3
4
5
6
7
8
// Windows Authentication (no username/password)
params := &SQLServerParams{
    Host:     "localhost\\SQLEXPRESS",
    Database: "MyDatabase",
    Schema:   "dbo",
}

db, err := database.OpenSQLServerDatabase(params)

With Environment Prefix

1
2
3
// Use custom environment variable prefix
// Reads MYAPP_DB_HOST, MYAPP_DB_PORT, etc.
params := SQLServerParamsFromEnv("MYAPP_")

SQL Server-Specific Features

Stored Procedures

1
2
3
4
5
6
7
8
// Execute stored procedure with parameters
_, err := db.Exec(`exec GetUsersByRole @Role = ?`, "admin")

// Stored procedure with output parameters
rows, err := db.Query(`
    declare @Count int
    exec GetUserCount @Role = ?, @Count = @Count output
    select @Count`, "admin")

Table-Valued Parameters

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// Create table type in SQL Server first:
// create type UserTableType as table (ID int, Name nvarchar(50))

// Use table-valued parameters for bulk operations
userTVP := mssql.TVP{
    TypeName: "UserTableType",
    Value: [][]interface{}{
        {1, "Alice"},
        {2, "Bob"},
    },
}

_, err := db.Exec(`exec BulkInsertUsers @Users = ?`, userTVP)

JSON Operations

1
2
3
4
5
6
7
8
// Insert JSON data
_, err := db.Exec(`insert into users (data) values (?)`, `{"name": "John", "preferences": {"theme": "dark"}}`)

// Query with JSON functions
rows, err := db.Query(`select json_value(data, '$.name') from users where json_value(data, '$.active') = 'true'`)

// JSON path operations
rows, err := db.Query(`select * from users where json_value(data, '$.preferences.theme') = 'dark'`)

Spatial Data Types

1
2
3
4
5
6
7
// Insert geometry data
_, err := db.Exec(`insert into locations (point) values (geometry::Point(?, ?, 4326))`, longitude, latitude)

// Spatial queries
rows, err := db.Query(`
    select name from locations 
    where point.STDistance(geometry::Point(?, ?, 4326)) < 1000`, userLong, userLat)

MERGE Operations (Upsert)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// SQL Server MERGE statement
_, err := db.Exec(`
    merge users as target
    using (values (?, ?)) as source (email, name)
    on target.email = source.email
    when matched then 
        update set name = source.name, updated_at = getdate()
    when not matched then
        insert (email, name, created_at) values (source.email, source.name, getdate());`,
    email, name)

Bulk Operations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// Bulk copy for large data inserts
// Uses SqlBulkCopy equivalent functionality
txn, err := db.Begin()
stmt, err := txn.Prepare(mssql.CopyIn("users", mssql.BulkOptions{}, "name", "email"))

for _, user := range largeUserList {
    _, err = stmt.Exec(user.Name, user.Email)
}

err = stmt.Close()
err = txn.Commit()

Dependencies

This module requires:

Production Considerations

Connection Pooling

Performance Tuning

High Availability

Licensing

Azure SQL Database

Special considerations for Azure SQL Database:

1
2
3
4
5
6
7
8
// Azure SQL Database connection
params := &SQLServerParams{
    Host:     "myserver.database.windows.net",
    Port:     1433,
    User:     "myuser@myserver",
    Password: "mypassword",
    Database: "mydatabase",
}

Azure-Specific Features

Source Code

See Also