SQLite3 Editor

This extension allows you to edit SQLite 3 files without having to write SQL queries.

Update: Previously this extension required Python, but starting from v1.0.91 it no longer requires Python.

Feature Overview

This extension can be used to 🔴 view and edit database contents, 🟢 view and edit table schema, or 🔵 execute SQL queries.

Most of these features are not present in other VSCode extensions for SQLite, such as alexcvzz/SQLite and SQLite Viewer.

Here are some videos of the main features:

🔴 Viewing Database Contents

This extension uses scrolling instead of pagination for browsing records, by only querying the visible area. The data is automatically reloaded when the table is modified by another process. It also comes with some fancy features, such as conditional formatting, image BLOBs preview, foreign key hover, etc.

🔴 Editing Database Contents

In addition to simple UPDATEs and INSERTs shown in the recording above, you can bulk-set a value to a selection, delete multiple rows by dragging over row numbers, set a value to a cell in a foreign key column using a dropdown, edit a table via a view, etc.

🟢 Editing Table Schema

In addition to tables, you can also edit a view's name and definition.

🔵 Executing SQL Queries

The editor supports auto-completion, syntax highlighting, hover documentation, signature help, and syntax validation.

Feature Documentation

The arrangement of the features listed in this section does not reflect any particular order. Feel free to explore each feature as per your interest.

Creating a Database

To create a database, simply create a file with a supported file extension, such as .db, .sqlite, or .sqlite3. The extension will automatically initialize the file as a database if it is empty and has one of the supported extensions.

Editing Table Schema - Checking Table Schema

To view the schema of the active table, click on the "Schema" button located at the top of the editor. This will open a panel displaying the table schema, indexes, and triggers. This feature supports both tables and views.

Editing Table Schema - Changing Column Definition

You can change the type affinity and the constraints of a column with this feature.

Editing Table Schema - Changing Column Order

You can change the order of table columns by dragging the table header.

We've tested this feature extensively, but we won't take any responsibility for possible data loss. Please ensure you check the generated query and/or create a backup before using this feature, especially if you're working with a critical database.

Editing Table Schema - Creating a Table

You can access this feature by clicking on the statement at the bottom of the editor, then selecting "CREATE TABLE".

The tables that can be created by this feature are limited to simpler ones. To create a table such as ones with GENERATED columns or COLLATE clauses, use the query editor.

Editing Table Schema - Creating an Index

You can access this feature by either clicking the SQL statement at the bottom of the editor, right-clicking the table name in the SELECT statement, or clicking "Schema" then "Index > + Add".

Editing Table Schema - Dropping a Table

You can access this feature by either clicking on the SQL statement at the bottom of the editor, or by right-clicking the table name in the SELECT statement, and then selecting "DROP TABLE".

Editing Table Schema - Renaming a Table

To rename a table, right-click the table name in the SELECT statement, and then select "Rename Table".

File Associations

This extension recognizes .db, .db3, .sdb, .s3db, .sqlitedb, .mddata, .sqlite, .sqlite3, .sl3, .vscdb, and .sq3 files as database files. To open other files, right-click the file in the explorer and select Open with… then SQLite3 Editor.

Importing and Exporting Data in CSV, JSON, or SQL

By clicking the "Other Tools..." button, you can access various features, including CSV, JSON, and SQL imports and exports. These features require you to install the sqlite-utils package.

This feature pre-fills a command in a terminal. You can check the options the executable takes by using <command> import --help or <command> export --help.

In-Memory Databases

Sometimes, you may want to write a SQL query for use in source code with code completions enabled. However, this extension requires a database connection to enable its IDE features. In such cases, you can connect to an in-memory database using SQLite3 Editor: Connect to In-Memory Database in the command palette or by connecting to a database with --database: :memory:.

Opening the Database in a Command Line Shell

You can open the database with the command line shell from the "Other Tools > Open in Command Line Shell" menu item when sqlite3 (for Linux, OSX) or sqlite3.exe (for Windows) is available in the PATH.

Query Editor - Overview

To access the query editor, click the "Custom Query" button.

The line comment at the first line of the query editor indicates which database the editor is connected to, and should not be deleted, or the query editor will be disconnected from the database. The comment serves the purpose of enabling the user to save the content of the query editor as a file and reuse it later.

There are three ways to execute statements in the query editor:

• To run a single statement, use Shift+Enter or click the "Execute" button above the statement. The result will only be displayed if it is a SELECT statement. To display the result of PRAGMA statements, use the pragma functions (e.g. use SELECT * FROM pragma_table_list(); instead of PRAGMA table_list;).
• To execute the entire file, use the ▷ button located in the top right corner of the editor.
• To execute a part of the file, select the text then click "Execute Selection" in the context menu.

Uncommitted transactions (BEGIN; ...; COMMIT/END; blocks) and transactions whose inner statement raised an error are rolled back.

The extension checks the database's mtime every second to automatically reload the table, which may result in a slight delay between the execution of a statement and the updates to the table.

Query Editor - Document Formatting

You can format queries by selecting "Format Document" in the command palette or by pressing Shift+Alt+F.

This feature employs sql-formatter to format queries. You can adjust formatting options with the configurations under sqlite3-editor.format, and they are mostly compatible with Prettier SQL VSCode.

Query Editor - Executing a Part of Common Table Expression (CTE)

To make it easier to create a complex query involving CTEs, the extension adds buttons that enable you to display the output of each CTE.

Query Editor - Syntax Validation

The extension finds syntax errors by preparing (or compiling) each statement in the editor and catching compilation errors. However, some PRAGMAs do their work when the statement is prepared [1], so we do not check them for syntax errors.

[1] "Some PRAGMA statements do ..." in https://www.sqlite.org/lang_explain.html#explain_operates_at_run_time_not_at_prepare_time

Table View - Auto Reloading

When the 'Auto Reload' button is activated, the editor will automatically refresh the table whenever there are updates.

Table View - Conditional Formatting

Conditional formatting colorizes table cells by their values. You can use this feature by right-clicking a table column then "Colorize (Conditional Formatting)".

Table View - Displaying and Jumping to the Definition of Foreign Keys

You can display and jump to the definition of foreign keys by hovering your mouse over them and using context menus.

Table View - Editing GENERATED Columns

GENERATED columns can be edited just like any other columns. Committing a change updates the table schema.

Table View - Filtering and Sorting Data

This extension also includes a find widget that enables you to filter records using regex, whole word, and case-sensitivity switches, making data searching faster than using the query editor to write WHERE clauses.

To sort records by a specific column, simply click on the column name. You can also sort the table from the context menu on the column name.

Table View - Git-Diff Support

When you open a database from the source control view, you will see a side-by-side comparison of two versions of the database.

To display the differences between the two versions, click the "Compare with Local File" button. Depending on the size of the database, it may take several seconds or minutes for the differences to appear. If locking the databases is a problem for your use-case, you should avoid using this feature.

Table View - Group by Column

You can group records by a column with this feature. To group a column, right-click on a column header then select "Group by Column". To cancel the grouping, click the icon on the column header.

Table View - Inserting Data from CSV

You can insert CSV into an existing table by clicking the SQL statement at the bottom of the editor then selecting "Bulk Insert".

This feature inserts CSV data into the table. Each CSV column is encoded as TEXT, but if the table column has a data type (column affinity), SQLite will cast it to the appropriate type. For example, if a column is defined as "column1 INTEGER," then the CSV values in that column will be cast as an integer.

Table View - Jumping to a Table or a Row

When the extension's editor is active, you can use the Go to Table/Row command to navigate to a specific table and/or row. The Ctrl+G keybinding is assigned to this command.

Table View - Multi-Selection

UPDATE and DELETE statements support multi-selection. To select multiple cells, you can either drag over the cells, click a cell with the alt key pressed, or press the Shift + Arrow keys while a cell is selected.

Dragging on row numbers is not currently supported, and you need to use the alt + click instead.

Table View - Resizing Columns

You can resize columns by dragging the right edge of the column header, or by using the "Autofit Column Width" button to adjust the width according to the content.

Table View - Switching Tables

You can switch between tables by clicking a table name in either the editor or the explorer.

When the sqlite3-editor.ui.nativeTableSelector configuration option is set to true, the quick pick widget will be displayed instead of a drop-down menu. It renders many table names faster, but it might be less intuitive to use.

Configurations

NOTE: This document does not cover all of the configuration options. Search "sqlite3-editor" in the VSCode's settings tab for other configurations.

sqlite3-editor.connectionSetupQueries.debug

This configuration allows you to view which setup queries were used. If enabled, the extension displays the URI matched against the regex patterns in connectionSetupQueries and the result of the match for each pattern when an editor is opened.

sqlite3-editor.connectionSetupQueries.driver.sqlite

This configuration specifies the SQL statements that are executed immediately after connecting to a database.

The keys are case-sensitive regular expressions used to match the file URI (e.g. file:///path/to/database.db), and the values are corresponding SQL statements to be executed. If multiple item's patterns match the same URI, only the first item will be used.

This configuration is similar to the "sqlite.setupDatabase" configuration in alexcvzz/sqlite but differs in that it uses regular expressions for path comparison.

Commands that are available in the SQLite's CLI, including .load, are not supported. Most runtime-loadable extensions can be loaded with SELECT load_extension(...);, but to load runtime loadable extensions that modify or delete existing functions, you need to use the sqlite3-editor.runtimeLoadableExtensions.driver.sqlite3 configuration instead.

For example, to execute PRAGMA foreign_keys = ON;, PRAGMA busy_timeout = 1000;, and SELECT load_extension('/home/user/sqlean/crypto.so'); on all SQLite 3 connections, use the following configuration:

"sqlite3-editor.connectionSetupQueries.driver.sqlite3": {
    ".*": "PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 1000; SELECT load_extension('/home/user/sqlean/crypto.so');"
}

sqlite3-editor.maxHistoryEntries

By default, this extension saves the last 500 SQL queries in ExtensionContext.globalState and you can view them using SQLite3 Editor: Show History and clear them using SQLite3 Editor: Clear History from the command palette. This setting specifies the number of queries to save. If set to 0, history will not be saved.

sqlite3-editor.runtimeLoadableExtensions.driver.sqlite3

This configuration loads run-time loadable extensions immediately after connecting to a database, using the sqlite3_load_extension call in the SQLite C interface.

Most run-time loadable extensions can be loaded with SELECT load_extension(...);, but some run-time loadable extensions need the sqlite3_load_extension call, according to the documentation:

load_extension(X), load_extension(X,Y)

• The load_extension() function will fail if the extension attempts to modify or delete an SQL function or collating sequence. The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the sqlite3_load_extension() C-language API.

https://www.sqlite.org/lang_corefunc.html#load_extension

The keys are case-sensitive regular expressions used to match the file URI, and the values are the list of runtime-loadable extensions to be loaded. In case multiple patterns match the same URI, only the first item will be used. The runtime-loadable extensions are loaded before executing the setup queries specified in sqlite3-editor.connectionSetupQueries.driver.sqlite3.

For example, to load the crypto module in sqlean, download and extract a release of sqlean, and specify the extracted path as follows:

"sqlite3-editor.runtimeLoadableExtensions.driver.sqlite3": {
  ".*": ["/home/user/sqlean/crypto.so"]
}

sqlite3-editor.ui.nativeTableSelector

The drop-down in the top left corner, used to select a table, has performance problems when the number of tables is large (> 1000). To address this issue, this extension displays a notification to recommend enabling this configuration when a large database is opened.

When this configuration set to true, mouse clicks on a table name in the top left corner will open a VSCode's quick pick widget instead of the drop-down menu. This configuration provides faster rendering, but it may be less intuitive to use.

Using Non-Standard SQLite

Run-Time Loadable SQLite Extensions

SpaciaLite

1. Install SpaciaLite:

   # Ubuntu/Debian
   sudo apt install libsqlite3-mod-spatialite
   
   # macOS
   brew install libspatialite
   

2. Configure the extension (F1 or Ctrl(Cmd) + Shift + P -> Preferences: Open User Settings (JSON)):

   "sqlite3-editor.runtimeLoadableExtensions.driver.sqlite3": {
       ".*": ["mod_spatialite"]
   }
   

User-Compiled SQLite

SQLite is statically linked to the component of this extension, which is implemented in Rust. You can build it with necessary modifications and then specify the path of the resulting binary in the VSCode settings sqlite3-editor.executablePath. Please note that if you do this, updating the extension may cause compatibility issues, so we recommend disabling automatic updates for this extension.

For instance, if you want to add the compilation flag SQLITE_OMIT_DEPRECATED, you must first install Rust and Git. Then, run the following commands in a terminal. The resulting binary will be located at ./target/release/db-driver-rs. The Rust source uses the "bundled" feature of the libsqlite3-sys crate, and libsqlite3-sys uses the "LIBSQLITE3_FLAGS" environment variable to compile the SQLite source.

git clone https://github.com/yy0931/sqlite3-editor --single-branch --branch rust-backend
cd sqlite3-editor
cargo clean
LIBSQLITE3_FLAGS='-DSQLITE_OMIT_DEPRECATED' cargo build --release --features sqlite

Supported Environments

• Windows, x64, Windows 7+
• Windows, ia32, Windows 7+
• Windows, arm64
• Linux, x64, glibc 2.17+
• Linux, arm64, glibc 2.17+
• Linux, armhf, glibc 2.29+
• Linux, x64, musl
• Linux, arm64, musl
• Mac, x64, 10.7+, Lion+
• Mac, arm64, 11.0+, Big Sur+

Monitoring and Reporting

Error Reporting

This extension displays a "Send error report" button on error notifications. When clicked, it sends the error message with sensitive information removed to the extension's author using Microsoft/vscode-extension-telemetry, or opens a GitHub issue, depending on the user's telemetry settings. If vscode-extension-telemetry is used, the system information (i.e. the "common properties" of Microsoft/vscode-extension-telemetry) will also be included.

Keep in mind that the bugs may not be fixed if the reports do not include enough information or the developer is busy, but your help in reporting errors is greatly appreciated.

Telemetry Data

This extension collects anonymous telemetry data using Microsoft/vscode-extension-telemetry if telemetry.telemetryLevel is "all" and sqlite3-editor.telemetry is "allow". It is disabled by default.

The purpose of this telemetry is to:

• Determine whether the extension is being used and if continued development and releases are necessary.
• Improve the compatibility and performance of the extension based on frequently used environments.
• Improve the extension based on frequently used features.
  Note: We believe that this extension is primarily used for three purposes: viewing data, editing data, and executing SQL statements. We would like to know on which area we should focus to develop further.

The following is the list of data collected, and no sensitive or personal information will be collected or transmitted:

• The type of the editor tab (file, git-diff, or other).
• The startup time of the editor tab.
• Usage of internal commands that are registered with vscode.commands.registerCommand.
• Usage of each element on the UI.
• Usage of each keyboard shortcut.
• The type (such as table, view, etc.) and size of the active table (rounded to the nearest power of 2).
• The "common properties" of Microsoft/vscode-extension-telemetry.

The data collected is encrypted and will be deleted after 90 days, which is the default setting in Microsoft Azure Monitor.

The data collected will only be used to improve the extension and will not be shared with any third parties.

Please note that the telemetry data collected by this extension may change in future releases.