DOCSEARCHbyAlgolia
Submit Docs

SQLTools for VS Code

website·Indexed 33 days ago

https://vscode-sqltools.mteixeira.dev/en/home/

A VS Code extension for managing database connections and executing queries. It supports multiple database drivers including MySQL, PostgreSQL, SQLite, MariaDB, CockroachDB, TiDB, and Microsoft SQL Server/Azure. Key features include query bookmarks, Codelens for query execution, session management for multiple connections, and a built-in formatter.

Tokens
12.7K
Snippets
60
Records
106
Agent score
67%

What's inside SQLTools

  1. Overview of SQLTools features

    SQLTools is a VS Code extension suite for managing SQL databases. Key features include:

    • SQL code beautifier and formatter
    • Query runner with history and bookmarks
    • Connection explorer for database navigation
    • Automatic generator for INSERT queries
    • Pluggable driver architecture to support various database engines

  2. Build vscode-sqltools from source

    To build the project from source, ensure you have Node.js v20 and Yarn v1 installed. Navigate to the project root and run the installation and start commands. To debug, open the vscode-sqltools.code-workspace file in VS Code and execute the 'Launch Extension and LS' task from the Debug panel.
    cd vscode-sqltools
yarn # installs dependencies and runs scripts
yarn run start # or yarn run dev:watch

  3. Use workspace-relative paths for SQLite databases

    SQLite relative paths are resolved based on the VSCode variable ${workspaceFolder}, allowing database paths to be relative to the current open project workspace.

  4. Install and configure SQLite driver prerequisites in VS Code

    To use the SQLite driver, ensure Node.js (version 14 or later) and npm are installed. The driver requires the node-sqlite3@5.1.1 package, which the extension will prompt to install upon first use.

    You must configure the sqltools.useNodeRuntime setting in VS Code. This can be set to true for auto-detection or a direct path to the Node executable. On Windows, using PowerShell as the default terminal shell is recommended for successful auto-detection.

    // settings.json example
{
  "sqltools.useNodeRuntime": true
}

  6. Preview documentation changes locally

    To preview documentation changes, use the VS Code Debug view. Select Development server (docs) from the dropdown and run it. Once the Astro server starts, a local URL (typically http://localhost:3000/) will be printed in the Terminal tab. Ctrl/Cmd+Click this link to open the preview in your browser.
    Debugger attached.
   astro  v1.2.1 started in 818ms
  
  ┃ Local    http://localhost:3000/
  ┃ Network  use --host to expose

  7. Install official database drivers for SQLTools

    To connect to a database using SQLTools, you must install the corresponding driver extension from the VS Code Marketplace. The following official drivers are available:

    • PostgreSQL & CockroachDB: Use the mtxr.sqltools-driver-pg extension.
    • MySQL, MariaDB, & TiDB: Use the mtxr.sqltools-driver-mysql extension.
    • Microsoft SQL Server / Azure: Use the mtxr.sqltools-driver-mssql extension.
    • SQLite: Use the mtxr.sqltools-driver-sqlite extension.

  11. Execute SQL query blocks using Codelens

    Since v0.17.15, SQLTools supports VS Code's Codelens feature, allowing you to group multiple SQL statements into a single executable block. To define a block, use the -- @block comment followed by a name. All statements following this comment until the next block definition are executed together. You can specify a specific connection for a block using the -- @conn ConnectionName comment at the start of the block, enabling the use of multiple different connections within a single file.
    -- @block create company table
-- @conn PGSQL
CREATE TABLE IF NOT EXISTS COMPANY(
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL
);

INSERT INTO COMPANY VALUES (1, 'Name', 12, 'address', 0.2);

-- @block create department table
CREATE TABLE IF NOT EXISTS DEPARTMENT(
    ID INT PRIMARY KEY NOT NULL,
    DEPT CHAR(50) NOT NULL,
    EMP_ID INT NOT NULL
);

  12. Set up the local testing environment for SQLTools

    To test changes in a local branch, you must use a Linux-compatible environment (bash shell) and have Docker installed, as target DBMS platforms are run in containers.

    Follow these steps to initialize the testing environment:

    1. Open the multi-root workspace file test/test-vscode-sqltools.code-workspace in VS Code.
    2. Install the recommended extensions: the SQLTools extension, the required driver extensions for testing targets, and optionally ms-azuretools.vscode-containers.
    3. Open the NPM Scripts view in VS Code.
    4. Run the up script to launch all containerized services. Alternatively, run up:* scripts to launch individual services for specific drivers.
    5. Verify that the containers are running via the VS Code Containers activity bar.
    6. Use the SQLTools activity bar to connect to the test targets (most connect via exposed ports, except SQLite which uses a local .db file).