SQL Tools JSON-RPC Protocol

The SQL Tools JSON-RPC API provides a host-agnostic interface for the SQL Tools Service functionality. The API provides easily consumable operations that allow simple integration into tools applications.

Launching the Host Process

From your host process, launch Microsoft.SqlTools.ServiceLayer(.exe) using an host-native process API that allows you to read and write this process' standard in/out streams. All communication with the host process occurs via this channel.

It is recommended that the process I/O be dealt with as a byte stream rather than read as a string since different parts of the message format could be sent with different text encodings (see next section).

It is expected that an editor will launch one instance of the host process for each SQL 'workspace' that the user has opened. Generally this would map to a single top-level folder which contains all of the user's SQL script files for a given project.

Messages Overview

The SQL Tools Service implements portions of the language service protocol defined by VS Code. Some portions of this protocol reference have been duplicated here for convenience.

It additionally implements several other API to provide database management services such as connection management or query execution.

This document provides the protocol specification for all the service's JSON-RPC APIs.

Connection Management

• ↩️ connection/connect
• ↩️ connection/cancelconnect
• ➡️ connection/connectionchanged
• ➡️ connection/complete
• ➡️ connection/disconnect

Query Execution

• ↩️ query/execute
• ↩️ query/subset
• ↩️ query/saveCsv
• ↩️ query/saveJson

Language Service Protocol

Documentation for the Language Service Protocol is available at language service protocol. The SQL Tools Service implements the following portion Language Service Protocol.

• ↩️ initialize
• ↩️ shutdown
• ➡️ exit
• ➡️ workspace/didChangeConfiguration
• ➡️ workspace/didChangeWatchedFiles
• ⬅️ textDocument/publishDiagnostics
• ➡️ textDocument/didChange
• ➡️ textDocument/didClose
• ➡️ textDocument/didOpen
• ➡️ textDocument/didSave
• ↩️ textDocument/completion
• ↩️ completionItem/resolve
• ↩️ textDocument/hover
• ↩️ textDocument/signatureHelp
• ↩️ textDocument/references
• ↩️ textDocument/definition

Message Protocol

A message consists of two parts: a header section and the message body. For now, there is only one header, Content-Length. This header, written with ASCII encoding, specifies the UTF-8 byte length of the message content to follow. The host process expects that all messages sent to it will come with an accurate Content-Length header so that it knows exactly how many bytes to read. Likewise, all messages returned from the host process will be sent in this manner.

Message Schema

The body of a message is encoded in JSON and conforms to a specific schema. There are three types of messages that can be transmitted between editor and host process: Request, Response, and Event.

Common Fields

The common fields shared between all message types are as follows:

• seq: A sequence number that increases with each message sent from the editor to the host. Even though this field shows up on all message types, it is generally only used to correlate response messages to the initial request that caused it.
• type: The type of message being transmitted, either request, response, or event.

Request Fields

A request gets sent by the editor when a particular type of behavior is needed. In this case, the type field will be set to request.

• command: The request type. There are a variety of request types that will be enumerated later in this document.
• arguments: A JSON object containing arguments for the request, varies per each request command.

NOTE: Some request types do not require a matching response or may cause events to be raised at a later time

Response Fields

A response gets sent by the host process when a request completes or fails. In this case, the typefield will be set to response.

• request_seq: The seq number that was included with the original request, used to help the editor correlate the response to the original request
• command: The name of the request command to which this response relates
• body: A JSON object body for the response, varies per each response command.
• success: A boolean indicating whether the request was successful
• message: An optional response message, generally used when success is set to false.

Event Fields

An event gets sent by the host process when

• event: The name of the event type to which this event relates
• body: A JSON object body for the event, varies per each event type

Base Protocol

The base protocol consists of a header and a content part (comparable to HTTP). The header and content part are separated by a '\r\n'.

Header Part

The header part consists of header fields. Each header field is comprised of a name and a value, separated by ': ' (a colon and a space). Each header field is terminated by '\r\n'. Considering the last header field and the overall header itself are each terminated with '\r\n', and that at least one header is mandatory, this means that two '\r\n' sequences always immediately precede the content part of a message.

Currently the following header fields are supported:

The header part is encoded using the 'ascii' encoding. This includes the '\r\n' separating the header and content part.

Content Part

Contains the actual content of the message. The content part of a message uses JSON-RPC to describe requests, responses and notifications. The content part is encoded using the charset provided in the Content-Type field. It defaults to 'utf8', which is the only encoding supported right now.

Example:

Content-Length: ...\r\n
\r\n
{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "textDocument/didOpen", 
    "params": {
        ...
    }
}

Base Protocol JSON structures

The following TypeScript definitions describe the base JSON-RPC protocol:

Abstract Message

A general message as defined by JSON-RPC. The language server protocol always uses "2.0" as the jsonrpc version.

interface Message {
    jsonrpc: string;
}

RequestMessage

A request message to describe a request between the client and the server. Every processed request must send a response back to the sender of the request.

interface RequestMessage extends Message {

    /**
     * The request id.
     */
    id: number | string;

    /**
     * The method to be invoked.
     */
    method: string;

    /**
     * The method's params.
     */
    params?: any
}

Response Message

Response Message sent as a result of a request.

interface ResponseMessage extends Message {
    /**
     * The request id.
     */
    id: number | string;

    /**
     * The result of a request. This can be omitted in
     * the case of an error.
     */
    result?: any;

    /**
     * The error object in case a request fails.
     */
    error?: ResponseError<any>;
}

interface ResponseError<D> {
    /**
     * A number indicating the error type that occurred.
     */
    code: number;

    /**
     * A string providing a short description of the error.
     */
    message: string;

    /**
     * A Primitive or Structured value that contains additional
     * information about the error. Can be omitted.
     */
    data?: D;
}

export namespace ErrorCodes {
    export const ParseError: number = -32700;
    export const InvalidRequest: number = -32600;
    export const MethodNotFound: number = -32601;
    export const InvalidParams: number = -32602;
    export const InternalError: number = -32603;
    export const serverErrorStart: number = -32099;
    export const serverErrorEnd: number = -32000;
    export const serverNotInitialized: number = -32001;
}

Notification Message

A notification message. A processed notification message must not send a response back. They work like events.

interface NotificationMessage extends Message {
    /**
     * The method to be invoked.
     */
    method: string;

    /**
     * The notification's params.
     */
    params?: any
}

Example JSON-RPC Message Format

See the language service protocol for more details on the protocol formats for these Language Service events. Below is an example of the JSON-RPC message format for the textDocument/didChange message.

textDocument/didChange

This request is sent by the editor when the user changes the contents of a SQL file that has previously been opened in the language service. Depending on how the request arguments are specified, the file change could either be an arbitrary-length string insertion, region delete, or region replacement.

It is up to the editor to decide how often to send these requests in response to the user's typing activity. The language service can deal with change deltas of any length, so it is really just a matter of preference how often change requests are sent.

Request

The arguments for this request specify the absolute path of the file being changed as well as the complete details of the edit that the user performed. The line/endLine and offset/endOffset (column) numbers indicate the 1-based range of the file that is being replaced. The insertString field indicates the string that will be inserted. In the specified range.

    {
      "seq": 9,
      "type": "request",
      "command": "change",
      "arguments": {
        "file": "c:/Users/UserName/Documents/test.sql",
        "line": 39,
        "offset": 5,
        "endLine": 39,
        "endOffset": 5,
        "insertString": "Test\r\nchange"
      }
    }

Response

No response is needed for this command.

Database Management Protocol

The follow section describes the message protocol format for the common database management functionality provided by the SQL Tools Service. The message formats are described as C# classes. These classes are packaged inside the common message structures documented above and serialized to JSON using JSON.Net.

Connection Management

connection/connect

Establish a connection to a database server.

Request

    public class ConnectParams
    {
        /// <summary>
        /// A URI identifying the owner of the connection. This will most commonly be a file in the workspace
        /// or a virtual file representing an object in a database.         
        /// </summary>
        public string OwnerUri { get; set;  }
        /// <summary>
        /// Contains the required parameters to initialize a connection to a database.
        /// A connection will identified by its server name, database name and user name.
        /// This may be changed in the future to support multiple connections with different 
        /// connection properties to the same database.
        /// </summary>
        public ConnectionDetails Connection { get; set; }
    }

Response

    bool

connect/cancelconnect

Cancel an active connection request.

Request

    public class CancelConnectParams
    {
        /// <summary>
        /// A URI identifying the owner of the connection. This will most commonly be a file in the workspace
        /// or a virtual file representing an object in a database.         
        /// </summary>
        public string OwnerUri { get; set;  }
    }

Response

    bool

connection/connectionchanged

Connection changed notification

Request

    public class ConnectionChangedParams
    {
        /// <summary>
        /// A URI identifying the owner of the connection. This will most commonly be a file in the workspace
        /// or a virtual file representing an object in a database.         
        /// </summary>
        public string OwnerUri { get; set; }
        /// <summary>
        /// Contains the high-level properties about the connection, for display to the user.
        /// </summary>
        public ConnectionSummary Connection { get; set; }
    }

connection/complete

Connection complete event.

Request

    public class ConnectionCompleteParams
    {
        /// <summary>
        /// A URI identifying the owner of the connection. This will most commonly be a file in the workspace
        /// or a virtual file representing an object in a database.         
        /// </summary>
        public string OwnerUri { get; set;  }

        /// <summary>
        /// A GUID representing a unique connection ID
        /// </summary>
        public string ConnectionId { get; set; }

        /// <summary>
        /// Gets or sets any detailed connection error messages.
        /// </summary>
        public string Messages { get; set; }

        /// <summary>
        /// Error message returned from the engine for a connection failure reason, if any.
        /// </summary>
        public string ErrorMessage { get; set; }

        /// <summary>
        /// Error number returned from the engine for connection failure reason, if any.
        /// </summary>
        public int ErrorNumber { get; set; }

        /// <summary>
        /// Information about the connected server.
        /// </summary>
        public ServerInfo ServerInfo { get; set; }

        /// <summary>
        /// Gets or sets the actual Connection established, including Database Name
        /// </summary>
        public ConnectionSummary ConnectionSummary { get; set; }
    }

connection/disconnect

Disconnect the connection specified in the request.

Request

    public class DisconnectParams
    {
        /// <summary>
        /// A URI identifying the owner of the connection. This will most commonly be a file in the workspace
        /// or a virtual file representing an object in a database.         
        /// </summary>
        public string OwnerUri { get; set; }
    }

Response

    bool

connection/listdatabases

Return a list of databases on the server associated with the active connection.

Request

    public class ListDatabasesParams
    {
        /// <summary>
        /// URI of the owner of the connection requesting the list of databases.
        /// </summary>
        public string OwnerUri { get; set; }
    }

Response

    public class ListDatabasesResponse
    {
        /// <summary>
        /// Gets or sets the list of database names.
        /// </summary>
        public string[] DatabaseNames { get; set; }
    }

Query Execution

query/execute

Execute a SQL script.

Request

    public class QueryExecuteParams
    {
        /// <summary>
        /// The selection from the document
        /// </summary>
        public SelectionData QuerySelection { get; set; }

        /// <summary>
        /// URI for the editor that is asking for the query execute
        /// </summary>
        public string OwnerUri { get; set; }
    }

Response

    public class QueryExecuteResult
    {
        /// <summary>
        /// Informational messages from the query runner. Optional, can be set to null.
        /// </summary>
        public string Messages { get; set; }
    }

query/subset

Retrieve a subset of a query results.

Request

    public class QueryExecuteSubsetParams
    {
        /// <summary>
        /// URI for the file that owns the query to look up the results for
        /// </summary>
        public string OwnerUri { get; set; }

        /// <summary>
        /// Index of the batch to get the results from
        /// </summary>
        public int BatchIndex { get; set; }

        /// <summary>
        /// Index of the result set to get the results from
        /// </summary>
        public int ResultSetIndex { get; set; }

        /// <summary>
        /// Beginning index of the rows to return from the selected resultset. This index will be
        /// included in the results.
        /// </summary>
        public int RowsStartIndex { get; set; }

        /// <summary>
        /// Number of rows to include in the result of this request. If the number of the rows 
        /// exceeds the number of rows available after the start index, all available rows after
        /// the start index will be returned.
        /// </summary>
        public int RowsCount { get; set; }
    }

Response

    public class QueryExecuteSubsetResult
    {
        /// <summary>
        /// Subset request error messages. Optional, can be set to null to indicate no errors
        /// </summary>
        public string Message { get; set; }

        /// <summary>
        /// The requested subset of results. Optional, can be set to null to indicate an error
        /// </summary>
        public ResultSetSubset ResultSubset { get; set; }
    }

query/saveCsv

Save a resultset as CSV to a file.

Request

    public class SaveResultsRequestParams
    {
        /// <summary>
        /// The path of the file to save results in
        /// </summary>
        public string FilePath { get; set; }

        /// <summary>
        /// Index of the batch to get the results from
        /// </summary>
        public int BatchIndex { get; set; }

        /// <summary>
        /// Index of the result set to get the results from
        /// </summary>
        public int ResultSetIndex { get; set; }

        /// <summary>
        /// URI for the editor that called save results
        /// </summary>
        public string OwnerUri { get; set; }

        /// <summary>
        /// Start index of the selected rows (inclusive)
        /// </summary>
        public int? RowStartIndex { get; set; }

        /// <summary>
        /// End index of the selected rows (inclusive)
        /// </summary>
        public int? RowEndIndex { get; set; }

        /// <summary>
        /// Start index of the selected columns (inclusive)
        /// </summary>
        /// <returns></returns>
        public int? ColumnStartIndex { get; set; }

        /// <summary>
        /// End index of the selected columns (inclusive)
        /// </summary>
        /// <returns></returns>
        public int? ColumnEndIndex { get; set; }

        /// <summary>
        /// Check if request is a subset of result set or whole result set
        /// </summary>
        /// <returns></returns>
        internal bool IsSaveSelection
        {
            get
            {
                return ColumnStartIndex.HasValue && ColumnEndIndex.HasValue
                       && RowStartIndex.HasValue && RowEndIndex.HasValue;
            }
        }
    }

    public class SaveResultsAsCsvRequestParams: SaveResultsRequestParams
    {
        /// <summary>
        /// Include headers of columns in CSV
        /// </summary>
        public bool IncludeHeaders { get; set; }
    }

Response

    public class SaveResultRequestResult
    {
        /// <summary>
        /// Error messages for saving to file. 
        /// </summary>
        public string Messages { get; set; }
    }

query/saveJson

Save a resultset as JSON to a file.

Request

    public class SaveResultsRequestParams
    {
        /// <summary>
        /// The path of the file to save results in
        /// </summary>
        public string FilePath { get; set; }

        /// <summary>
        /// Index of the batch to get the results from
        /// </summary>
        public int BatchIndex { get; set; }

        /// <summary>
        /// Index of the result set to get the results from
        /// </summary>
        public int ResultSetIndex { get; set; }

        /// <summary>
        /// URI for the editor that called save results
        /// </summary>
        public string OwnerUri { get; set; }

        /// <summary>
        /// Start index of the selected rows (inclusive)
        /// </summary>
        public int? RowStartIndex { get; set; }

        /// <summary>
        /// End index of the selected rows (inclusive)
        /// </summary>
        public int? RowEndIndex { get; set; }

        /// <summary>
        /// Start index of the selected columns (inclusive)
        /// </summary>
        /// <returns></returns>
        public int? ColumnStartIndex { get; set; }

        /// <summary>
        /// End index of the selected columns (inclusive)
        /// </summary>
        /// <returns></returns>
        public int? ColumnEndIndex { get; set; }

        /// <summary>
        /// Check if request is a subset of result set or whole result set
        /// </summary>
        /// <returns></returns>
        internal bool IsSaveSelection
        {
            get
            {
                return ColumnStartIndex.HasValue && ColumnEndIndex.HasValue
                       && RowStartIndex.HasValue && RowEndIndex.HasValue;
            }
        }
    }

Response

    public class SaveResultRequestResult
    {
        /// <summary>
        /// Error messages for saving to file. 
        /// </summary>
        public string Messages { get; set; }
    }