SQL Server Connector

sqlserver://[username[:password]@]host[:port]/database[?options]

# Standard connection
sqlserver://sa:YourPassword123@localhost:1433/mydb?sslmode=disable

# Named instance
sqlserver://user:pass@localhost:1433/mydb?instanceName=SQLEXPRESS

# NTLM authentication
sqlserver://domain_user:pass@server:1433/db?authentication=ntlm&domain=MYDOMAIN

# Azure AD authentication
sqlserver://user:[email protected]:1433/mydb?authentication=azure-active-directory-access-token

# No SSL (development only)
sqlserver://user:pass@localhost:1433/db?sslmode=disable

# SSL without certificate verification (recommended for most use cases)
sqlserver://user:pass@host:1433/db?sslmode=require

# SSL with certificate verification (default)
sqlserver://user:pass@host:1433/db

# Connect to SQLEXPRESS instance
sqlserver://user:pass@localhost:1433/db?instanceName=SQLEXPRESS

# Connect to custom named instance
sqlserver://user:pass@server:1433/db?instanceName=INSTANCE1

[[sources]]
id = "sqlserver_db"
dsn = "sqlserver://user:pass@host:1433/db"
connection_timeout = 30  # Seconds to establish connection
query_timeout = 60       # Seconds for query execution

sqlserver://domain_user:password@server:1433/db?authentication=ntlm&domain=MYDOMAIN

sqlserver://[email protected]:[email protected]:1433/mydb?authentication=azure-active-directory-access-token

SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY SCHEMA_NAME;

// Default schema (dbo)
await connector.getTables();

// Specific schema
await connector.getTables('myschema');

EXEC sp_addextendedproperty 
  @name = N'MS_Description',
  @value = N'User email address',
  @level0type = N'SCHEMA', @level0name = 'dbo',
  @level1type = N'TABLE', @level1name = 'users',
  @level2type = N'COLUMN', @level2name = 'email';

const schema = await connector.getTableSchema('users', 'dbo');
// Returns: [
//   {
//     column_name: 'email',
//     data_type: 'varchar',
//     is_nullable: 'NO',
//     column_default: null,
//     description: 'User email address'
//   }
// ]

const indexes = await connector.getTableIndexes('users', 'dbo');
// Returns: [
//   {
//     index_name: 'PK_users',
//     column_names: ['id'],
//     is_unique: true,
//     is_primary: true
//   },
//   {
//     index_name: 'IX_users_email',
//     column_names: ['email'],
//     is_unique: true,
//     is_primary: false
//   }
// ]

// Get all routines
await connector.getStoredProcedures('dbo');

// Only functions
await connector.getStoredProcedures('dbo', 'function');

// Only procedures
await connector.getStoredProcedures('dbo', 'procedure');

const proc = await connector.getStoredProcedureDetail('sp_GetUsers', 'dbo');
// Returns: {
//   procedure_name: 'sp_GetUsers',
//   procedure_type: 'procedure',
//   language: 'sql',
//   parameter_list: '@user_id int IN',
//   definition: 'CREATE PROCEDURE [dbo].[sp_GetUsers]...'
// }

EXEC sp_addextendedproperty 
  @name = N'MS_Description',
  @value = N'Application user accounts',
  @level0type = N'SCHEMA', @level0name = 'dbo',
  @level1type = N'TABLE', @level1name = 'users';

const comment = await connector.getTableComment('users', 'dbo');
// Returns: 'Application user accounts'

await connector.executeSQL(
  "SELECT * FROM users WHERE email = @p1 AND active = @p2",
  { maxRows: 100 },
  ['[email protected]', true]
);

await connector.executeSQL(
  "SELECT * FROM large_table",
  { maxRows: 100 }
);
// Executes: SELECT TOP 100 * FROM large_table
// Or for queries with ORDER BY: ... OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

await connector.executeSQL(`
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  SELECT * FROM accounts WHERE id IN (1, 2);
`, { maxRows: undefined });

# SQL Server Express
export DSN="sqlserver://sa:YourPassword123@localhost:1433/mydb?instanceName=SQLEXPRESS&sslmode=disable"

# Standard SQL Server
export DSN="sqlserver://sa:YourPassword123@localhost:1433/mydb?sslmode=disable"

[[sources]]
id = "local_sqlserver"
dsn = "sqlserver://sa:YourPassword123@localhost:1433/mydb?instanceName=SQLEXPRESS&sslmode=disable"

[[sources]]
id = "azure_sql"
dsn = "sqlserver://username@servername:[email protected]:1433/mydb?sslmode=require"
connection_timeout = 30

[[sources]]
id = "azure_ad_sql"
dsn = "sqlserver://[email protected]:[email protected]:1433/mydb?authentication=azure-active-directory-access-token"

[[sources]]
id = "windows_auth"
dsn = "sqlserver://DOMAIN\\username:password@server:1433/mydb?authentication=ntlm&domain=DOMAIN"

[[sources]]
id = "remote_sqlserver"
dsn = "sqlserver://dbuser:dbpass@internal-server:1433/mydb?sslmode=disable"
ssh_host = "bastion.example.com"
ssh_user = "admin"
ssh_key = "~/.ssh/id_rsa"

-- Create schema
CREATE SCHEMA app;
GO

-- Create table in schema
CREATE TABLE app.users (
  id INT PRIMARY KEY,
  name NVARCHAR(100)
);

await connector.getTables('app');
await connector.getTableSchema('users', 'app');

-- Query JSON columns
SELECT 
  JSON_VALUE(data, '$.name') as name,
  JSON_QUERY(data, '$.tags') as tags
FROM events
WHERE JSON_VALUE(data, '$.type') = 'purchase';

-- FOR JSON to output as JSON
SELECT id, name, email
FROM users
FOR JSON PATH;

SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) as recency_rank
FROM transactions;

WITH source AS (
  SELECT id, name, email FROM staging_users
)
MERGE users AS target
USING source ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
  INSERT (id, name, email) VALUES (source.id, source.name, source.email);

-- Create temporal table
CREATE TABLE users (
  id INT PRIMARY KEY,
  name NVARCHAR(100),
  email NVARCHAR(255),
  ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
  ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

-- Query historical data
SELECT * FROM users FOR SYSTEM_TIME AS OF '2024-01-01 00:00:00'
WHERE id = 123;

-- Create full-text index
CREATE FULLTEXT INDEX ON articles(title, content)
KEY INDEX PK_articles;

-- Search with CONTAINS
SELECT title, [RANK]
FROM articles
WHERE CONTAINS((title, content), 'sql server')
ORDER BY [RANK] DESC;