DuckDB SQL Tools Preview extension adds basic DuckDB SQL support to VS Code IDE, and provides database schema and SQL query API and user interfaces for the popular SQL Tools extension, SQL query editor, SQL language server, and data processing tools.
DuckDB is an in-process SQL OLAP database management system that uses vecotrized data engine for optimized analytics and parallel query processing with extensive SQL support and direct Parquet, CSV, and JSON data loading and query capabilites for local and remote data sources.
DuckDB SQL Tools Peview extension v1.1.0 adds the following basic DuckDB SQL capabilities to VS Code IDE for working with DuckDB instances:
- Connect to a local DuckDB v0.7.1 instance
- Create new in-memory DuckDB instance
- View DuckDB v0.7.1 tables, columns, and views
- Run SQL queries on open DuckDB connections
- Attach SQLite database files to in-memory DuckDB instances
- Query remote
CSVandParquetdata files with DuckDB HTTPFS extension - Create in-memory DuckDB tables from remote data sources and query results
- Manage DuckDB connections in SQL Tools Connections Explorer
- Autocomplete SQL keywords, table names, column names, and view names on open database connections in VS Code SQL editor
- Save named SQL query Bookmarks
- Use SQL Query History
- Export SQL query results in
CSVandJSONdata formats
See SQL Tools documentation for a comprehensive list of SQL Tools extension features contributed to VS Code IDE.
Install DuckDB SQL Tools Preview extension from VS Code marketplace in your browser by clicking on Install button in the extension info page header.
Alternatively, you can install this extension directly in VS Code IDE from Extensions tab (ctrl+shift+x) by searching for DuckDB.
Users of VS Codium and other VS Code based IDEs can install DuckDB SQL Tools Preview extension using duckdb-sql-tools-x.x.x.vsix extension package from the Assets section in published releases of this extension on GitHub. Follow install from .vsix instructions in your VS Code extensions compatible IDE or online container service to install it.
Note: DuckDB SQL Tools use DuckDB Node.js API and require a local Node.js installation to query DuckDB instances.
Download and install Node.js from the official node.js downloads page. Node.js is used as a local web server to host local data and DuckDB files. Node.js will install npm tool we use to install duckdb-async library to establish DuckDB connections.
We use Node.js DuckDB library instead of the limited DuckDB WASM browser JS library other similar DuckDB data tools use to enable faster data imports and loading via multi-threaded node-gyp DuckDB native API interface.
The following sections provide a summary of the popular SQL Tools VS Code extension, database drivers, our public Random Fractals Inc. Data Tools extensions, and new premium Pro Data Tools available to our Pro sponsors on github.
SQL Tools VS Code extension provides connections to many commonly used databases, and includes database connection management tree view with database schema info and objects display, basic table viewer for SQL query results display, and SQL language server for editing SQL in VS Code with SQL syntax highlighting and auto-completion.
SQL Tools support many popular databases via the official and community database drivers you can install and use in VS Code IDE from VS Code Marketplace SQL Tools drivers page.
Our Random Fractals Inc. Data Tools 🛠️ is a collection of public data visualization extensions, viewers, notebook renderers, and code snippets for devs and data scientists using VS Code IDE, published under our Random Fractals Inc. ☂️ org.
Pro Data Tools is a new premium set of of custom query and data view VS Code extensions created for our monthly Pro sponsors on github, and can be viewed as an extension pack to enhance SQL development and runtime workflow connected to the different database management systems via SQL Tools extension and database plugins.
Pro Data Tools include DuckDB Pro Tools extension that adds advanced DuckDB SQL, connection features, and DuckDB v0.8 support to VSCode IDE.
The latest version of DuckDB Pro Tools extension adds the following capabilities to VS Code IDE for working with DuckDB v0.8.0 instances:
- Connect to a local DuckDB v0.8.0 instance
- Create new in-memory DuckDB instance
- Import local and remote
CSV,JSONandParquetdata files into in-memory DuckDB instance for exploratory data analysis (EDA) - View DuckDB v0.8.0 databases, schemas, tables, columns, views, indexes, sequences, extensions, settings, functions, types and keywords in SQL Tools Connections Explorer
- Run SQL queries on active DuckDB connections
- Attach SQLite database files to in-memory DuckDB instances to run analytical queries
- Query remote
CSV,Parquet, andJSONdata files with DuckDB HTTPFS extension and new DuckDB JSON extension - Create in-memory DuckDB tables from remote data sources and query results
- Manage DuckDB v0.8.0 connections in SQL Tools Connections Explorer
- Auto-complete SQL keywords, DuckDB instance table names, column names, and view names for active DuckDB connections in VS Code SQL editor
- Save named SQL query Bookmarks
- Use SQL Query History
- Export DuckDB query results in
CSVandJSONdata formats - Use PRQL Code Lens from our new PRQL Pro Tools collection to generate and run SQL queries on active DuckDB connection
- Explore new
employees.duckdbdemo data, PRQL and SQL sample queries - Run sample
chicago-crimesandgbif-observationsPRQL and SQL queries on Github and AWS S3 hostedparquetdata files - Use new
DuckDB Toolsviews and metadata shortcut commands from VS CodeCommand Palette...
Our Pro sponsors on github also get access to the premium PRQL Pro Tools VS Code extension. The initial private beta release of PRQL Pro Tools comes with custom PRQL Code Lens SQL Tools plugin that allows you to run PRQL queries for the supported PRQL target SQL dialects directly using any of the supported SQL Tools extension drivers for the different database management systems.
Recently released private beta of Markdown SQL Pro Tools comes with Select SQL code, Execute SQL statement and Execute All SQL statements code lenses. Our Markdown SQL Code Lenses allow you to run SQL queries from your .md markdown documents and sql code blocks in those documents directly against any of the supported SQL Tools database management systems.
Future versions of Markdown SQL Pro Tools will include new custom Data Notebook extension integrated with SQL Tools extension, supported database management systems, our Data Table Renderers and VS Code Notebooks user interface to view and run SQL queries from .sql files and .md markdown documents with sql code blocks using native VS Code Notebook View similar to this PRQL notebook example.
DuckDB egnine also provides a number of Extensions you can install and load to work with remote CSV and Parquet data files over HTTPFS, enable Full Text Search, attach SQLite database with SQLite Scanner, or attach PostgreSQL database instance with DuckDB Postgres Scanner.
You can check the list of core and installed DuckDB extensions by running the following SQL query on an open database connection:
select * from duckdb_extensions();
DuckDB SQL Tools VS Code extension installs and loads HTTPFS DuckDB extension by default for all the open database connections. You can add other DuckDB Extensions to the active DuckDB connection by running INSTALL and LOAD extension SQL statements.
Example of loading trimmed down Chicago crimes data reported in 2022 into an in-memory DuckDB instance from a .parquet data file hosted in our Chicago Crimes data and analytical tools demo GitHub repository:
This example uses implicitly loaded DuckDB HTTPFS extension to query reported Chicago crimes parquet data file with over 210K recorded crime reports, creates a CrimeReports table in a new DuckDB :memory: instance from remote parquet data file, and queries imported data.
SQLite database users can use DuckDB SQL Tools VS Code extension and DuckDB SQLite Scanner extension to add data from SQLite database to in-memory DuckDB instance.
Run the following SQL statements to add SQLite Scanner DuckDB extension 8000 to an open database connection:
INSTALL sqlite;
LOAD sqlite;With the loaded SQLite Scanner DuckDB extension you can attach SQLite database file to a DuckDB database instance. Attached SQLite database tables will show up as views in DuckDB instance.
Run the following SQL CALL function to attach SQLite database instance:
CALL sqlite_attach('E:\\projects\\data\\tools\\duckdb-tools\\data\\chinook\\sqlite\\chinook.sqlite');
Note: DuckDB SQL Tools extension uses DuckDB NodeJS Client API. In order to work with local data files, you need to specify full path to your local database or data files in SQL statements that reference local file paths.
Future versions of this extension might simplify local file path references by deducing absolute file path from the local DuckDB file connection string or open VS Code project workspace folder path, and replacing relative database or data file references with the corresponding absolute path in an open VS Code project workspace.
Also, note in the sqlite_attach() SQL function call statement above we are escaping \ file path delimiters on Windows OS by using \\ characters sequence.
DuckDB SQL Tools extension documentation repository contains sample /data folder with chinook.duckdb and chinook.sqlite database files, csv, json and parquet data files you can download to get started using DuckDB with our SQL Tools VS Code extension.
The cninook/duckdb demo data folder also has sample SQL query files you can try running on this well-known sample database with our VS Code extension.
Due to the limited time and minimal development effort invested into building this Free Trial DuckDB SQL Tools extension, our Preview version of this extension comes with the following known limitations and supported usage scenarios.
DuckDB SQL Tools Preview v1.1.0 release of this extension supports only local database instances created with DuckDB v0.7.1 engine. Database instances and files created with other versions of DuckDB are not supported as they use different compression and storage formats and the structure of .duckdb file has been changing as DuckDB engine is evolving.
Use DuckDB CLI to export data from the older database file versions and create new .duckdb file using the latest DuckDB storage implemenation. Read Announcing DuckDB 0.7.0 blog post for more information about DuckDB v0.7.1 storage improvements.
You can use prior v1.0.2 of this free DuckDB Sql Tools Preview extension to work with the older DuckDB v0.6.1 files.
The Premium DuckDB Pro Tools extension version, available to our Pro sponsors on GitHub, supports the latest DuckDB v0.8 files and features.
DuckDB SQL Tools Preview extension opens .duckdb database files in read-only mode.
The Premium DuckDB Pro Tools extension version, available to our Pro sponsors on GitHub, supports opening .duckdb files in write mode, provides DuckDB v0.8 support, extended DuckDB connection, schemas, systems objecs, and views display.
You can experiment with writable :memory: DuckDB instances in this DuckDB SQL Tools Preview extension version. In-memory DuckDB instances function similar to In-Memory SQLite Databases. You can use DuckDB Import Data and Atach features available via DuckDB SQL statements, and use Export Database SQL statements to export created in-memory DuckDB instances.
DuckDB SQL Tools Preview extension lets you create in-memory database instances by specifying :memory: keyword in the Database File field of the new DuckDB connection in SQL Tools Connection Assistant, as demonstrated in DuckDB HTTPFS and SQLite Scanner extension usage examples above.
Note: only :memory DuckDB database instances are open in read/write mode in this DuckDB SQL Tools Preview extension version.
Sign up for the Premium Pro Data Tools on GitHub to get access to the DuckDB Pro Tools and work with the latest versions of DuckDB files in read and write modes.
You can adjust the amount of RAM allocated to VS Code IDE to enable opening large files and load more data into memory. Go to File -> Preferences -> Settings and type files.maxMemoryForLargeFilesMB in the Setting search field to change it. For example, users with 64GB of RAM can change it to 49152 MB to allow VS Code use 48GB of available memory.
SQL Tools extension provides many configuration Settings users can toggle to change database connection and tree view display options, sql formatting, and results display.
The following SQL Tools Settings were used while creating and testing this DuckDB SQL Tools extension and are recommended for working with DuckDB instances efficiently. We suggest you set these preferences in your User Settings in VS Code by navigating to File -> Preferences -> Settings -> User -> Extensions -> SQLTools, or adding them to your global VS Code settings.json config file using the JSON code snippet below:
{
...
"sqltools.useNodeRuntime": true,
"sqltools.disableNodeDetectNotifications": true,
"sqltools.autoOpenSessionFiles": false,
"sqltools.results.limit": 10000,
"sqltools.results.location": "current",
}| Setting | Description |
|---|---|
"sqltools.useNodeRuntime": true |
Enable Node runtime in order to use DuckDB NodeJS API DuckDB SQL Tools extension depends on. |
"sqltools.disableNodeDetectNotifications": true |
Disable Node runtime detection notifications after initial SQL Tools extension installation to prevent Node runtime information message display on every new VS Code session start. |
"sqltools.autoOpenSessionFiles": false |
Prevent auto open of new session SQL editor instance after connecting to the database instance. |
"sqltools.results.limit": 10000 |
Maximum number of records to return in results. SQL Tools defaults to displaying only 50 records in query results view. Changing this limit setting to 1000 or 10000 will show more data rows to inspect in result views. |
"sqltools.results.location": "current" |
Defines the editor group to use for result table views. SQL Tools display all results in the next editor group to show results on the side next to the active SQL query editor. Changing this setting to current will display results in the same editor group and display more result columns. |
Please use our public DuckDB SQL Tools GitHub Discussions portal to submit your feedback, share examples of how you are using DuckDB SQL Tools VS Code extension, or request new trivial and premium features. Our goal with this extension and DuckDB Pro Tools is to make DuckDB more accessible and easier to use in VS Code IDE.
Become a Fan or a Pro Sponsor of our dev work on this and other Random Fractals, Inc. code and data viz extensions if you find them useful, educational, or enhancing your daily dataViz dev code workflows and exploratory data analysis experience.
☕️ https://ko-fi.com/dataPixy 💖 https://github.com/sponsors/RandomFractals