SQLite3 Editor

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

Feature Overview

This extension can be used to 📊 view and edit database contents, ⚙ view and edit table schema, and ▶️ 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 for browsing records, which should be more intuitive than pagination. At the same time, only the data that is currently visible on the screen is retrieved from the database, so it loads fast even on large databases. In addition, the data is automatically reloaded when the table is modified by another process. It also comes with 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

You can edit table schemas as shown in the video. In addition to tables, you can also edit a view's name and definition.

▶️ Executing SQL Queries

You can run arbitrary queries in the query editor. The editor supports context-aware 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.

Table of Contents

• Creating a Database
• Editing Table Schema - Checking Table Schema
• Editing Table Schema - Changing Column Definition
• Editing Table Schema - Changing Column Order
• Editing Table Schema - Creating a Table
• Editing Table Schema - Creating an Index
• Editing Table Schema - Dropping a Table
• Editing Table Schema - Renaming a Table
• File Associations
• Importing and Exporting Data in CSV, JSON, or SQL
• In-Memory Databases
• Opening the Database in a Command Line Shell
• Query Editor - Overview
• Query Editor - Document Formatting
• Query Editor - Executing a Part of Common Table Expression (CTE)
• Query Editor - Syntax Validation
• Table View - Auto Reloading
• Table View - Conditional Formatting
• Table View - Displaying and Jumping to the Definition of Foreign Keys
• Table View - Drop Multiple Tables
• Table View - Editing GENERATED Columns
• Table View - Entity-Relationship Diagram
• Table View - Filtering and Sorting Data
• Table View - Finding Foreign Keys Referencing a Value
• Table View - Git-Diff Support
• Table View - Group by Value
• Table View - Inserting Data from CSV
• Table View - Jumping to a Table or a Row
• Table View - Multi-Selection
• Table View - Pop-Out Editor
• Table View - Resizing Columns
• Table View - Resizing Rows
• Table View - Selecting a Table
• Table View - Tabs
• Using Run-Time Loadable SQLite Extensions
• Using User Compiled SQLite

Creating a Database

To create a database file, 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.

The "Index" section displays an array of blue and gray boxes for each index. They represent which column the index applies to, with numbers indicating the order. For example, if you have CREATE TABLE table1(a, b, c); CREATE INDEX index1 ON table1 (c, a), then the coloring of the boxes would be blue, gray, blue, and the numbers would be 2, -, 1.

When you hover your cursor over an index, it highlights the corresponding columns. Conversely, when you hover your cursor over the dotted text in the columns, it highlights the associated indexes in the "Schema" pane.

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 complex 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 import and export features rely on a helper program bundled with this extension. You can check the available options by using <command> import --help or <command> export --help.

In-Memory Databases

You can connect to an in-memory database using SQLite3 Editor: Connect to In-Memory Database in the command palette. Alternatively, you can connect by editing the first line in a query editor to --database: :memory: (for details, refer to the "Query Editor" section). This feature should be useful to quickly test SQL statements.

Opening the Database in a Command Line Shell

This feature opens an integrated terminal in VSCode, initialized with the command sqlite3 <your-file>. It is available only if you have the sqlite3 command installed on your system. To use this feature, navigate to "Other Tools" then "Open in Command Line Shell".

Query Editor - Overview

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

The line comment at the first line of the query editor indicates the linked database. If it is deleted, the query editor will disconnect from the database.

Any files that start with -- database: ... will be recognized as a query editor. This allows you to save a query editor as a file for future use.

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

• To run a single statement, use Shift+Enter (or ⇧ return on Mac) 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.

The "Execute" button shown above a transaction (BEGIN; ... END; block) executes all the statements in the transaction. If the transaction is not successfully executed, the extension will perform a ROLLBACK.

Due to the limitation of the current mechanism to reload the table, there may be 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 (or ⇧ ⌥ F on Mac).

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)

In the query editor, "Select" buttons are displayed for each CTE. This enhances the ease of creating complex queries involving CTEs.

Query Editor - Syntax Validation

The query editor identifies syntax errors in SQL statements by preparing each statement in an isolated database connection and catching compilation errors. However, due to the nature of this method, certain errors might not be detected, such as those within PRAGMA statements or references to non-existent tables.

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 view the source record of a cell in a column that is under a foreign key constraint by hovering the cursor over the cell. If you want to jump to the source record, use the context menu item.

Table View - Drop Multiple Tables

You can quickly drop multiple tables with the multiselect mode in the table selector. You can enter the multiselect mode by right-clicking on a table name in the dropdown menu or by pressing Alt+Click (or ⌥ click on Mac) on a dropdown item.

Table View - Editing GENERATED Columns

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

Table View - Entity-Relationship Diagram

If the database contains foreign keys or views, the table selector will display the entity-relationship diagram alongside the list of tables. Clicking on an entity opens the corresponding table.

You can display the columns and values in the first row of a table by right-clicking on the table name or pressing Ctrl+Click (or ⌘ click on Mac).

Table View - Filtering and Sorting Data

The find widget shown in the image below allows you to quickly filter records by searching for text matches across all columns, just like the one in VSCode. For more advanced usage, you can also use the regular expression, whole word, and case-sensitivity switches. You can access this feature by pressing the "Find" button in the editor or pressing Ctrl+F (or ⌘ F on Mac).

Column filters allow you to filter records based on a specific value in the chosen column. To use this, right-click on a table column header.

Alternatively, you can also set a filter via the context menu on cells.

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 - Finding Foreign Keys Referencing a Value

To find the foreign keys referencing a value, select a cell and click Find All References in the context menu or press Shift+F12 (or ⇧ F12 on Mac).

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 Value

You can group records by a column with this feature. To group a column, right-click on a column header then select "Group by Value". 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 (or ⌘ G on Mac) 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.

You can perform actions other than editing data by right-clicking on the selection.

Table View - Pop-Out Editor

This feature is available when the configuration sqlite3-editor.ui.experimental.popOutEditor.enabled is set to true. The default setting is false.

When enabled, you can open an embedded textarea in a new tab by either:

• Clicking the button displayed at the top-left corner of the currently focused textarea.
• Pressing Alt+P (or ⌥P on Mac).

The Alt+P shortcut selects an editor based on the following rules:

1. If an editor is focused, then it is chosen.
2. If there are editors in the bottom or side pane, then the topmost one among them is chosen.
3. Otherwise, the topmost editor is chosen.

Setting sqlite3-editor.ui.popOutEditor.enabled to true also adds the Ctrl + S keybinding to the UI for consistency with the pop-out editors. When pressed, it asks if the changes should be committed.

Table View - Resizing Columns

You can resize columns by dragging the right edge of the column header. Alternatively, to adjust the width to the content, you can use the "Autofit Column Width" button in the context menu.

Table View - Resizing Rows

You can resize rows by dragging the lower edge of a row's number.

Table View - Selecting a Table

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

Table View - Tabs

Adding "sqlite3-editor.ui.alwaysDisplayTabs": true to VSCode's settings enables the table tab feature. Its behavior resembles that of VSCode's tabs; in the preview mode, the tab closes when you switch to another table, but after double-clicking the preview tab, the tab remains open until you click the close button. Certain actions, such as selecting "Go to Source Record" on foreign key columns, activate the tab UI regardless of the value of the sqlite3-editor.ui.alwaysDisplayTabs setting.

Using Run-Time Loadable SQLite Extensions

The configuration setting "sqlite3-editor.connection.runtimeLoadableExtensions" allows you to specify run-time loadable extensions that will be loaded with every SQLite connection opened by this extension.

For example, to load SpatiaLite, follow these instructions:

1. Install SpatiaLite:

   # Ubuntu/Debian
   sudo apt install libsqlite3-mod-spatialite
   
   # macOS
   brew install libspatialite
   
   # Windows
   Download a binary from https://www.gaia-gis.it/gaia-sins/, extract it, and add the directory containing mod_spatialite.dll to the PATH environment variable. You may need to reopen the VSCode window.
   

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

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

   Run-time loadable extensions can be associated with specific files by matching them with a regex pattern. To match any file, use ".*".

Using User Compiled SQLite

Since this extension statically links SQLite to a component built with Rust, you need to rebuild the component to use a non-standard version of SQLite.

For example, if you want to add the compilation flag SQLITE_OMIT_DEPRECATED to SQLite to exclude deprecated features, follow these instructions:

1. First, you need to install Rust and Git.
2. Then, build the Rust component with the following commands. Note that this project uses the "bundled" feature of the libsqlite3-sys crate, and libsqlite3-sys uses the "LIBSQLITE3_FLAGS" environment variable when compiling 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
   

3. Finally, specify the absolute path to ./target/release/sqlite3-editor in the sqlite3-editor.executablePath setting of VSCode.

Please note that making changes like this could cause issues with future updates to the extension. Therefore, we recommend turning off automatic updates for this extension.

Frequently Asked Questions

• Q: How can I input NULL quickly?
  A: Select a cell, press Shift+Alt+N (or ⇧ ⌥ N on Mac), then press Ctrl+Enter (or ⌘ ↩ on Mac).
• Q: How can I delete all the rows in a table quickly?
  A: There is no button to do that in the UI. Execute DELETE FROM <table-name>; in the query editor.
• Q: The UI is too compact.
  A: You can increase the value of "Sqlite3-editor › UI: Spacing" ("sqlite3-editor.ui.spacing") in the VSCode settings.
• Q: How can I save a query I wrote in the query editor?
  A: You can save the editor content as a file with Ctrl+S (or ⌘ S on Mac). You may want to change the file path on the first line of the file to a relative path, such as -- database: ./database.sqlite, to ensure it works when the parent folder is renamed or moved. If you frequently use a SELECT statement, also consider creating a view.
• Q: Can I open the result of a SELECT statement, which I executed in the query editor, in another editor tab?
  A: Currently, there is no direct way to do that. However, the query result is shown in the most recently active UI tab. Therefore, you can duplicate the editor tab for the database file using the "Split Editor Right" button at the top right corner, click on the newly opened editor tab's title to make it active, then execute the query to send the result to that new editor tab.
• Q: What is 'rowid = ...' in the tooltip on row numbers? It shows a different number than the row number.
  A: It is the rowid in ROWID tables.

Configurations

This document does not cover all of the configuration options. Please search "sqlite3-editor" in the VSCode's settings tab for further information.

sqlite3-editor.connection.setupQueries

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

The keys are case-sensitive regular expressions used to match the file URI, and the values are corresponding SQL statements to be executed. In case multiple patterns match the same URI, only the first item will be used.

Commands that are available in the SQLite's CLI, including .load, are not supported. To load run-time loadable extensions, use the sqlite3-editor.connection.runtimeLoadableExtensions configuration instead.

For example, to execute PRAGMA foreign_keys = ON; and PRAGMA busy_timeout = 1000; on all SQLite 3 connections, you can specify the following configuration:

"sqlite3-editor.connection.setupQueries": {
    ".*": "PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 1000;"
}

sqlite3-editor.connection.runtimeLoadableExtensions

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

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.connection.setupQueries.

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.connection.runtimeLoadableExtensions": {
  ".*": ["/home/user/sqlean/crypto.so"]
}

sqlite3-editor.pragma_foreign_key

This configuration controls whether the enforcement of foreign key constraints is enabled. This configuration has the same effect as setting "sqlite3-editor.connection.setupQueries": { ".*": "PRAGMA foreign_keys = <value>;" }, except changes are applied to open database connections immediately.

sqlite3-editor.maxHistoryEntries

By default, this extension stores the last 500 SQL queries executed in the query editor. 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.comment.filepath

By default, the "comment" feature, accessible from the schema pane, writes descriptions about database tables to a file with the extension ".docs.md" located next to the database table.

This configuration changes the file path of the comment file.

Syntax

The value can begin with one of the following prefix variables. If the value lacks these prefix variables, it is interpreted as an absolute path.

• ${fileDirname}: The parent directory of the database file (e.g., /foo/bar for /foo/bar/db.sqlite).
• ${fileWorkspaceFolder}: The currently opened folder.
• ${userHome}: The user's home directory on the computer.

After the prefix or the first segment (e.g., "C:\" or "/") of the absolute path, the following variables can be used anywhere and any number of times:

• ${fileBasename}: The basename of the database file (e.g., db.sqlite for /foo/bar/db.sqlite).
• ${fileBasenameNoExtension}: The basename of the database file without its extension (e.g., db for /foo/bar/db.sqlite).
• ${fileExtname}: The file extension of the database file (e.g., .sqlite for /foo/bar/db.sqlite).
• ${fileDirnameBasename}: The name of the parent directory of the database file (e.g., bar for /foo/bar/db.sqlite).

The configuration value must resolve to a filename that ends with ".md" after variable replacement. Slash and backslash characters ("/", "\") will be replaced with appropriate ones for the current platform.

Supported Environments

Minimum Supported VSCode Version: 1.70.0 (July 2022)

Supported Operating Systems:

• Windows, x64, Windows 7+
• Windows, ia32, Windows 7+
• Windows, arm64, Windows 8+
• Linux, x64, glibc 2.17+
• Linux, arm64, glibc 2.17+
• Linux, armhf, glibc 2.29+
• Linux, x64 *1
• Linux, arm64 *1
• Mac, x64, 10.15+ (Catalina, Darwin 19)
• Mac, arm64, 11.0+ (Big Sur, Darwin 20)

*1 are fallbacks for when glibc is not available, such as on Alpine Linux, but they do not support SQLite run-time loadable extensions because libc (musl) is statically linked.

License

Open Source Licenses

The SQLite3 Editor: Open Source Licenses command in the command palette displays the licenses of the open-source libraries used by this software.

Notice

SQLite3 Editor Copyright (C) 2024 https://github.com/yy0931

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.