DOCSEARCHbyAlgolia
Submit Docs

go-sqlcmd

repository·main·Indexed 20 days ago

https://github.com/microsoft/go-sqlcmd

A command-line tool and set of Go packages for interacting with Microsoft SQL Server, Azure SQL Database, and Azure Synapse. It provides modern features such as containerized instance management and Azure Active Directory (AAD) authentication, while maintaining backwards compatibility with the original ODBC sqlcmd utility.

Tokens
15.7K
Snippets
64
Records
84
Agent score
68%

What's inside go-sqlcmd

  1. Use ODBC sqlcmd flags and backwards compatibility

    main

    The sqlcmd tool maintains backwards compatibility with the original ODBC sqlcmd.

    • To view original ODBC flags (like -q, -Q, -i, -o), use sqlcmd -?.
    • To run a command using these flags within the current context, use sqlcmd -q "<query>".
    • If no current context is active, running sqlcmd without parameters reverts to the original behavior: attempting an interactive session on the default local instance (port 1433) using trusted authentication.
    sqlcmd -? 
sqlcmd -q "SELECT @@version"

  2. Differences between go-sqlcmd and ODBC sqlcmd

    main

    The go-sqlcmd project is a Go port of the original ODBC sqlcmd. Users should be aware of several behavioral changes:

    • Flag Syntax: Only - is accepted as a flag specifier; / is not supported. New POSIX-style flags are available (e.g., --input-file instead of -i). Note that --input-file requires comma-delimited filenames, whereas the original used spaces.
    • Ignored Switches: -R, -I, and -M are currently ignored.
    • Encryption (-N): The -N switch now accepts specific values: strict, true, mandatory, yes, 1, optional, no, 0, false, or disable.
      • If -N is provided without a value, true is used.
      • If -N is provided without -C, certificate validation is required.
      • To use strict encryption with a specific hostname, use -Ns -F <hostname>.
    • Input Files (-i):
      • To pass multiple files with a single -i, use a space: -i file1.sql file2.sql.
      • If a filename contains a comma, it must be triple-quoted: sqlcmd -i """select,100.sql""".
    • Interactive Mode: Commands must fit on one line. The tool does not check for open parentheses or quotes to prompt for successive lines like the ODBC version.

  3. Manage sqlcmd contexts and configuration

    main

    Each time sqlcmd create is used, a new context is created (e.g., mssql, mssql2). A context stores the endpoint and user configuration details.

    Context Commands

    • Switch context: sqlcmd config use <context-name>
    • View current context: sqlcmd config current-context
    • List all contexts: sqlcmd config get-contexts
    • View connection strings: sqlcmd config connection-strings (shows ODBC/ADO.NET/JDBC strings for the current context)
    • View all details: sqlcmd config view (shows user/endpoint details for all contexts in ~/.sqlcmd/sqlconfig)

    Custom Configuration Files

    Use the --sqlconfig flag to point to a custom YAML configuration file. This allows you to manage endpoints for specific projects.

    sqlcmd config --sqlconfig ./myproject.yaml add-endpoint --name ep1434 --address localhost --port 1434
sqlcmd config --sqlconfig ./myproject.yaml view
    sqlcmd config use <context-name>
sqlcmd config get-contexts
sqlcmd config connection-strings

  4. Enable console syntax coloring

    main

    You can enable syntax coloring for the output of :list and TSQL query results by setting the SQLCMDCOLORSCHEME environment variable or using :setvar. Valid values are names of Chroma styles.

    To see available styles and samples in interactive mode, run:

    :list color
    -- Inside sqlcmd interactive mode
:list color

  5. Build and test the sqlcmd project

    main

    Building the executable

    To build the sqlcmd binary, use the provided build scripts:

    Linux/macOS:

    ./build/build.sh

    Windows:

    .uilduild.cmd

    Running tests

    Tests require connection parameters via environment variables. Set SQLCMDSERVER, SQLCMDDATABASE, SQLCMDUSER, and SQLCMDPASSWORD before running:

    go test ./...

    Running tests in Docker (Windows): If developing on Windows, you can run tests in a Linux container:

    docker run -rm -e SQLCMDSERVER=<yourserver> -e SQLCMDUSER=<youruser> -e SQLCMDPASSWORD=<yourpassword> -v i:\git\go-sqlcmd:/go-sqlcmd -w /go-sqlcmd golang:1.16 go test ./...
    # Build
./build/build.sh

# Test
go test ./...

  6. Create and manage local SQL Server instances with sqlcmd

    main

    You can use sqlcmd to automate the creation of SQL Server instances using a local container runtime like Docker or Podman.

    Quickstart: Create instance with AdventureWorksLT

    To create a local instance, restore the AdventureWorksLT database, query it, and open Azure Data Studio, run:

    sqlcmd create mssql --accept-eula --using https://aka.ms/AdventureWorksLT.bak
sqlcmd query "SELECT DB_NAME()"
sqlcmd open ads

    Managing Instance Lifecycle

    • List available versions: sqlcmd create mssql get-tags
    • Create with specific version: sqlcmd create mssql --tag 2019-latest
    • Stop an instance: sqlcmd stop
    • Start an instance: sqlcmd start
    • Delete an instance: sqlcmd delete
    sqlcmd create mssql --accept-eula --using https://aka.ms/AdventureWorksLT.bak
sqlcmd create mssql get-tags
sqlcmd create mssql --tag 2019-latest

  7. Set up a manual development environment for sqlcmd

    main

    To develop or build sqlcmd manually, follow these steps:

    1. Install Go: Ensure Go 1.24 or higher is installed.
    2. Clone the repository: Clone the microsoft/go-sqlcmd repository.
    3. SQL Server: Set up a SQL Server instance (version 2017 or later).
    4. Configure Environment Variables: Set the following variables to simplify connection management:
      • SQLCMDSERVER: Server hostname (e.g., localhost)
      • SQLCMDUSER: Username (e.g., sa)
      • SQLCMDPASSWORD: Password
      • SQLCMDDATABASE: Database name (optional)
    5. Build: Use the Go toolchain to build the modern implementation.

    Once configured, you can verify the build by checking the version.

    go build -o sqlcmd ./cmd/modern
./sqlcmd --version

  8. Configure output formats (ASCII, Vertical, Horizontal)

    main

    You can control how query results are displayed using command line options or scripting variables:

    • Vertical Format: Use the --vertical flag or set the SQLCMDFORMAT variable to vertical.
    • ASCII Table Format: Use the --ascii flag or set SQLCMDFORMAT to ascii (e.g., -v SQLCMDFORMAT=ascii). This format uses borders and determines column widths based on content. Note that SQLCMDCOLWIDTH and -w still control maximum screen width and wrapping.
    • Horizontal Format: This is the default (space separated, no borders).
    # Using command line flags
sqlcmd --vertical
sqlcmd --ascii

# Using scripting variables
sqlcmd -v SQLCMDFORMAT=ascii

  9. Quick Start with Dev Containers

    main

    The fastest way to develop and test sqlcmd is using the included Dev Container, which pre-configures Go 1.24, SQL Server 2025, and the sqlcmd binary in your PATH.

    Using VS Code

    1. Install the Dev Containers extension.
    2. Open this repository in VS Code.
    3. Click "Reopen in Container".

    Using GitHub Codespaces

    1. Click the "Code" button on the GitHub repository page.
    2. Select "Create codespace".

    Commands inside the container

    Once the container is running, use these helper commands:

    • ginstall: Build sqlcmd from source.
    • gtest: Run the test suite.
    • sql: Connect to SQL Server (e.g., sql -Q "SELECT @@VERSION").
    # Build sqlcmd from source
ginstall

# Run the test suite
gtest

# Connect to SQL Server
sql -Q "SELECT @@VERSION"

  10. Install the sqlcmd CLI

    main

    The sqlcmd command line tool is available via various package managers depending on your operating system.

    Windows

    • WinGet: winget install sqlcmd or winget upgrade sqlcmd
    • Choco: choco install sqlcmd or choco upgrade sqlcmd
    • Manual: Download .msi or .zip from the releases page.

    macOS

    • Homebrew: brew install sqlcmd or brew upgrade sqlcmd
    • Note for Apple Silicon (M1/M2): You must enable Rosetta for x86/amd64 emulation in Docker Desktop settings (under "Features in development") to use local container runtimes effectively.

    Linux

    • Linuxbrew/Homebrew: brew install sqlcmd or brew upgrade sqlcmd
    • Package Managers: Available via apt-get, yum, and zypper.
    winget install sqlcmd
brew install sqlcmd
brew install sqlcmd

  11. Authenticate with Azure Active Directory (AAD)

    main

    The Go version of sqlcmd supports multiple AAD authentication models via the --authentication-method flag or the -G switch.

    Authentication Methods

    • ActiveDirectoryDefault: Uses azidentity to attempt various mechanisms (Client Secret, Managed Identity, etc.). Best for scripts running in both local and Azure environments. Requires AZURE_TENANT_ID and AZURE_CLIENT_ID environment variables.
    • ActiveDirectoryPassword: Uses a username and password. Does not support MFA. Use -U <user> -P <pass> or environment variables.
    • ActiveDirectoryInteractive: Launches a web browser for user authentication.
    • ActiveDirectoryManagedIdentity: Used for Azure VMs. If using a user-assigned identity, set the username to the identity ID; otherwise, leave username empty.
    • ActiveDirectoryServicePrincipal: Authenticates a service principal ID as the username and a client secret as the password. Username format: <service principal id>@<tenant id>. Use SQLCMDPASSWORD for the secret.

    Key Environment Variables

    • AZURE_TENANT_ID: The tenant ID of the server.
    • AZURE_CLIENT_ID: Required for ActiveDirectoryDefault.
    • AZURE_CLIENT_SECRET: Used by DefaultAzureCredential to select ClientSecretCredential.
    • AZURE_CLIENT_CERTIFICATE_PATH: Path to a certificate file.
    • SQLCMDCLIENTID: Set this for ActiveDirectoryInteractive and ActiveDirectoryPassword to specify an authorized application ID.
    # Using DefaultAzureCredential (requires env vars like AZURE_CLIENT_ID)
sqlcmd --authentication-method=ActiveDirectoryDefault -S myserver.database.windows.net

# Using Interactive login
sqlcmd -G -S myserver.database.windows.net

# Using Service Principal
export SQLCMDPASSWORD="your-client-secret"
sqlcmd --authentication-method=ActiveDirectoryServicePrincipal -U "<sp-id>@<tenant-id>" -S myserver.database.windows.net

  12. Restore a database from a URL during installation

    main

    You can automate the restoration of a SQL Server backup file during the installation process using the --using flag.

    Requirements:

    • The URL must use http or https.
    • The URL must point to a .bak file.

    Behavior: The tool will download the .bak file into the container and perform a RESTORE DATABASE operation. The database name will be derived from the filename (the part before .bak) unless otherwise specified via the --user-database flag.