Home

Distributive versions

There are 4 different versions: 32 and 64-bit, with encryption support and without and 64-bit version with scripting/encription.
The encryption versions use modified sqlite3.dll from SQLite3 Multiple Ciphers. The no-encryption versions use standard library. Both sqlite3.dll libraries are built from source by mingw64-compiler.

Windows 10 has incompatible library (e.g. #25, #123, #125) for editor control. That is why msftedit.dll from Windows 7 was added to distributives.

Comparison with another SQLite editors

The most popular solutions are DB Browser for SQLite (DB4S) and Sqlite Studio. They both are great, robust and cross-platformed. If you need mostly to edit table data then DB4S is your choice. If you also work with queries, Sqlite Studio is more suitable for you. If you work with another databases e.g. PostgreSQL or MySQL then take a look at HeidiSQL, DBeaver and extremely powerfull and paided DataGrip.

But if you find UI of these editors are overcomplicated then try sqlite-gui. Despite the fact that sqlite-gui doesn't have some basic features (you can't edit table structure and UI supports only English), it compensates them by many others.

Each software use own way to work with data/resultset with over thousands rows

• sqlite-gui cuts rows by limit. The default value is 10 000. Can be increased in settings.
• DB4S wraps an original query to with t as (...) limit N offset K to get data only for a visible part. Plus executes an query to get row count with t as (...) select count(*) from t. If the original query is complex (with joins and materialized sub-results), it hit the performance by executing query multiple times. For a simple query e.g. select * from t where id between 30 and 6000 (without order-clause) it's a fastest solution.
• SQLiteStudio uses the same technique as DB4S for paggination by 1000 rows.

If sqlite-gui is not simple enought then try sqlite-x.

Charts

The query result can be viewed as a chart. There are several types of the chart: lines, dots, areas, histogram and bars. For example, the data below can be viewed as lines with sale_dates as axis-X and items and revenue as Y-values or as bars group by city and bars - items and revenue.

city	sale_date	items	revenue	comment
Moscow	2021-05-29	10	100	aaa
London	2021-05-30	7	200	bbb
Paris	2021-05-27	14	150	NULL
Berlin	2021-05-31	20	250	ccc
Cairo	2021-05-24	18	NULL	NULL

Try examples for yourself

select 'Moscow' city, '2021-05-29' sale_dates, 10 items, 100 revenue, 'aaa' comment
union all
select 'London', '2021-05-30', 7, 200, 'bbb'
union all
select 'Paris', '2021-05-27', 14, 150, NULL
union all
select 'Berlin', '2021-05-31', 20, 250, 'ccc'
union all
select 'Cairo', '2021-05-24', 18, NULL, NULL

Keep in mind

• The text columns are ignored
• Dates should have YYYY-MM-DD or YYYY-MM-DD HH:MI (the separator can be any: ., / or -) format.
• A chart is builded by a grid data and therefore is limited to 10 000 points.

Pinned resultsets

You can keep a resultset between queries or even app runs. Just pin a required resultset for it.
Use -- <caption> before an query to give the pinned tab a special name.

The data of resultsets is stored in prefs.sqlite on app closing.

Quick references

If you have id in table B that referenced to a row in an another table A then you can view this row by Alt + Click. You can set returned data in Database > Adjust references-dialog. If you want to set references for attached databases (non-main schemes) then you should edit refs-table in prefs.sqlite manually.

Column	Description
dbname	Database filename e.g. test.sqlite
schema	Schema of table B. Usually it is main.
tblname	Table B name
colname	Table B colname
refname	Hint title
query	Query that will be executed by Alt + Click. Commonly the query returns all columns from table A. Example: select * from "main"."customers" t where t."id" = ?1

Some of rows are builded automatically by foreign keys references.

⚠️ Since version 1.7.2 refs format has been slightly changed. The reference name was moved to refname-column and the query-column should returns only necessary data.

Alternative usage

Let suppose you have table T with column A. The column contains urls and you want to open them by Alt + Click. You should do the next two steps:

1. Go to Add-ons > SQLite extensions and install exec-extensions.
2. Go to Database > Adjust references and set for T.A the next query select exec('explorer ?1')

Query parameters

In an query literals may be replaced by a parameter that matches one of following templates: ?, ?N (e.g. ?1, ?2), :a, @b and $c.

select * from t where id > @id;
select * from t where name like '%' || ?txt || '%';

The binding dialog will be appeared on the query execution.

Query shortcuts

You can create shortcuts for commonly used queries in editor. There are three predefined shortcuts: Alt + F1 returns a table info, Ctrl + 1 - first 100 rows and Ctrl + 2 - row count. You can create new shortcuts for your own queries or redefine these by Query > Shortcuts in the main menu. After you hit a shortcut keys, application will get selection (or word under caret), substitute the selection instead of $SUB$ and execute the resulting query. Herewith, the text in the editor will not be changed.
Check the next video for SSMS with the similar feature, if you have any questions.

Cross database queries

Typically, if you want to work with data from non-SQLite sources, you should export the data as csv and then import it into a database. In some cases you can use virtual tables e.g. vsv for csv and tsv files or xlite for Excel and Open Document. sqlite-gui provides another way for it using odbc-extension. You should choose Database > Attach database by ODBC and specify ODBC-connection parameters (video example). Underhood the app attachs a new in-memory database, then scan ODBC source for a table list and finally create virtual tables one-by-one that present remote data as usual tables. No changes are made in the original database. You can attach several heterogeneous sources to execute queries over them all.

Before you attach an external source, you need to install an appropriate ODBC driver. The list of ODBC drivers can be found here - https://wiki.python.org/moin/ODBCDrivers. Usually Windows already has drivers for Excel, Access and MS SQL Server. The connection examples for various databases are here - http://connectionstrings.com

The main disadvantages are ODBC-slowness and simplicity odbc-extension that ignoring any source indexes and any other optimizations. Most likely, large queries over 100K+ rows will take a long time to complete. The solution is to create views (if it possible) that make all hard work on a source. Also due limitation of odbc-extension and ODBC-drivers themself, odbc virtual tables are read-only.

Custom functions

These functions are allowed to extend SQLite within the application. Each function has a name and a SQL code implementation (select-statement with one returning value). Use ?1, ?2, ... as function arguments e.g.

select max(?1, ?2) + max(?2, ?3)

You can use exec-extension to call a non-SQL code

select exec('powershell -nologo ""Get-Content ?1""')
select exec('python ?1.py ?2 ?3')

At this time only scalar functions are supported.

Jinja scripting

The scripting extends SQL by Jinja-like templating. This can be especially useful given that SQLite doesn't have any procedural extension. This feature requires inja-extension, which is distributed separately because experimental. Below are examples of how you can use the scripting (input the code as usual in SQL editor).

{# 1. Share parameters between queries #}
{% set id = 10 %} 
select * from books where id = {{ id }};
select * from orders where book_id = {{ id }};

{# 2. Data generation #}
{% for i in range(20) %}
    insert into t (id, value) values ({{ i }}, abs(random() % 10)); 
{% endfor %}

{# 3. Logic implementation #}
{% for row in query("select id, fullname, position from employees") %}
     {% if row.position == "manager" %}
          -- {{ row.fullname }}
          select * from orders where employee_id = {{ row.id }};
     {% endif %}
{% endfor %}

Dive deeper into scripting

Popular tool dbt for data transformation already uses this way to extends SQL-scripts. It looks like more convenient than lua-scripting in SQlite Manager or Tcl-scripting in SQLite Studio. The jinja-like templates are widely distributed in many products because the syntax is a very simple and intuitive but expandable.

There are only three operators which use {% ... %} braces: set, for and if.

{# Set local variable; Use double-quote to quote string #}
{% set name = "Alex" %} 

{# And then use {{ ... }} to substitute a variable value #}
select '{{ name }}'; --> select 'Alex'

{# Loop over array or object #}
{% for i in [3, 4, 5] %} ... {% endfor %}
{% for key, value in {"x": 10, "y": 20} %} ... {% endfor %}

{# Conditional #}
{% if x == 10 %} ... {% else %} ... {% endif %}

The fourth operator is a macro and it can be used as custom function e.g.

{% macro f (a, b) %} {{ a }} + {{ b }} * 2 {% endmacro %}
select {{ f(1, 2) }}; --> select 5;
select {{ f(2, 3) }}; --> select 8;

Unfortunately inja-extension has only a basic support of Jinja templates by a library limitation and it doesn't support macros, filters and some another features yet. But inja-extension provides a special function call(code, data) for it:

{% set f = "{{ a }} + {{ b }} * 2" %}
select {{ call(f, {"a": 1, "b": 2}) }}; --> select 5;
select {{ call(f, {"a": 2, "b": 3}) }}; --> select 8;

There are 3 another extension-specific functions to fetch data from SQLite to variable

{% set val = query_value("select 12 + 3;") %}
select {{ val }}; --> select 15;
select {{ query_value("insert into t (name) values ('AAA') returning id;") }}; --> select <auto-id>;

{% set row = query_row("select * from books where id = " + 10) %} {# returns an object #}
select {{ row.id}}, '{{ row.title }}' as title; --> 10, Catherine Ryan Hyde

{% set dataset = query("select * from books") %} {# returns an array #}
{% for row in dataset %}
    update books set price = price + 10 where id = {{ row.id }};
{% endfor %}

-- Arguments passing
{% set val = query_value("select ?1 + ?2;", 12, 3) %}
{% set row = query_row("select ?1 + ?2 as val;", [12, 3]) %}
{% set rows = query("select * from books where id = @id", {"@id": 5}) %}

Also there are 3 helper functions tojson, fromjson and quote

{% set row = query_row("select ?1 + ?2 as x, ?1 - ?2 as y;", [12, 3]) %}
select {{ quote(tojson(row)) }}; --> {"x":"15","y":"9"}

{% set books = fromjson(query_value("select books from v_json_orders where id = ?1", 1)) %}
{% for row in books %}
select {{ quote(row.title) }} title;
{% endfor %}

And one function raise_error to stop the script and show a message

{% if value < 10 %}
{{ raise_error("Too small") }}
{% endif %}

There is no magic in how it works. The app reads a template from an editor, passes the template to inja-function that generating a plain SQL-code and this code is running as usual. Therefore if you need to execute any DML-command that depending on previous steps then you should use query, query_row and query_value instead of plain SQL insertions.

{# Incorrect usage #}
insert into t (id) values (1);
{% set id = query_value("select id from t where id = 1") %} {# id will be empty! #}

{# Correct usage #}
{% set id = query("insert into t (id) values (1) returning id") %}

Scripting in a terminal mode

In this case the script has a different behaviour. It will be evaluated and outputed without executing e.g.

{# Use additional "-" to remove white-space characters in a result #}
{% for i in range(3) -%}
select {{i}};
{%- for %}

Returns

select 1;
select 2;
select 3;

Data generator

You can define your own "type" in a dropdown box on the right side of column name e.g. animal with values cat, dog, hare and fox. Open generators-table in prefs.sqlite. You need to add four rows with type equals to animal and value one of cat, dog, hare and fox. That's all.

Add-ons

SQLite extensions

The simplest way to extend SQLite is a using extensions. The application has an extension manager which uses own repository https://github.com/little-brother/sqlite-extensions. The repository provides binaries, help/info files and optionally source codes.

Virus warnings for extension binaries

Some antiviruses don't like a small exe/dlls. These are a known issues 1, 2.
Below example for standard SQLite extensions are builded by gcc -I ../include -shared x.c -o x.dll -s

• series.dll - 15 warnings
• uuid.dll - 37 warnings

This problem occurs for default Code::Block 17 gcc compiler (5.1.0). To build dlls less/without warnings the compiler needs to be updated to latest version (9.2.0). But sometimes that's not enough: there is opinion that antiviruses don't like a non-msvcrt (gcc) entry point.
It is worth noting: dll with a new unique name makes VirusTotal less suspicious. The next check will use a file hash.

Value viewer plugins

These plugins are using to preview values in more human-readable form e.g. to show json as a tree. All plugins are stored in https://github.com/little-brother/sqlite-gui-value-viewers. The repository provides binaries, sources and info files.

Check plugin Wiki to get more details.

Column modifier plugins

Sometimes you may need to slightly transform values in a resultset before analyzing it. Some transformation easily can be implemented as a part of an query, but others aren't e.g. splitting each 3 digits in a number 1234567 to 1 234 567. The solution is a column modifier. It doesn't change an original dataset and can be reverted at any time. Modifiers can change a text, text and background colors and even entire cell rendering. All plugins are stored in https://github.com/little-brother/sqlite-gui-column-modifiers. The repository provides binaries and sources.

Any custom function with a single argument can be used as a column modifier.

Check plugin Wiki to get more details.

Export/Import data via ODBC

The app provides easy way to work with external data through ODBC-extension.The extension uses 32-bit drivers and supports only two data types text and numeric (double). Also each ODBC driver has own oddity/limitation that couldn't be fixed easily.

When configuring a 32 bit ODBC datasource for an Excel file created in Microsoft Office 2010 or above you need to have the driver for .xlsx files. The 32 bit version of this driver is not installed by default with Office 2013 or above. To add this driver you need to install the Microsoft Access Database Engine 2010 Redistributable.

Excel

• delete from <table> returns succeeded-status without real deleting data.
• An table could be referenced as <tblname>$B23 that means worksheet <tblname> and cell B23.
• SQLExecDirect that is called in app raises run-time error if the document is opened.
• Don't forget remove "Read-only" flag in connection parameters.

CSV

• The driver works in read-only mode.

Import Google Spreadsheets

You need a Google account and Google API key to obtain data from Google Sheets. With this key you can import any public sheets. Import non-public sheets requires OAuth2 authorization and it is not currently supported.

Follow the next steps to create API key:

1. Open Google console.
2. Create a new project and select it as Active.
3. Open API and Services and push + ENABLE APIS AND SERVICES.
4. Find Google Sheets API, open it and enable it.
5. Return on API and Services and choose Credentials.
6. Push + Create credentials and choose API key.
7. Additional step: push Edit and choose Restrict key with Google Sheets API only checked.

Multithreading to execute queries in parallel

SQLite supports three different threading modes: single-thread, serialized and multi-thread. The first two imply that an application uses a one (shared) connection to access to a database and all queries are executed sequentially. That is not a problem for simple queries that are usually used. The most popular SQLite tools are using one connection. So their interface will be blocking when you try to execute a longer query and do something else at once e.g.

with t(x) as (select 1 union all select x + 1 from t limit 10000), t2(x) as (select x from t order by random())
select avg(t.x * t2.x) from t, t2;

The using multi-thread mode (when each tab uses own connection) allowed to:

• execute queries in parallel
• interrupt queries independently
• make UI more responsive

⚠️ The app uses a one auxilary connection to fetch meta data and to work in tools and a separate connection for each editor tab (include the teminal tab). There are issues related with it:

• attach, detach and pragma commands will only affect the tab that called them.
• each tab (connection) has own temp-schema. So if you want share in-memory data between tabs you should use TEMP2-schema instead.

The another one option to more parallel opportunities is a using Write-Ahead Logging (WAL). WAL provides more concurrency as readers do not block writers and a writer does not block reader (reading and writing can proceed concurrently).

Database encryption

The app supports encryption through a vfs-extension provided by SQLite3 Multiple Ciphers. This extension is built into sqlite3.dll. If you don't need encryption, use *-no-ciphers distributive version with original sqlite3.dll.

REST API web server

This feature allows to manipulate (do CRUD operations) in SQLite database via http-requests. It can be useful for those who need to emulate site backend or a microservice. Available links are built automatically based on metadata. The feature is disabled by default in Settings. After turning on, the server will be available at http:\\127.0.0.1:3000.
Limitation: A table should have a single column primary key for CRUD.

Method	Url	Description
GET	/api/:table?:tail	Returns all rows by query select * from :table :tail
GET	/api/:table/:id	Get row from :table with primary key equals to :id
GET	/api/:table/:id/:fktable	Get linked rows in :fktable by foreign key
POST	/api/:table	Insert a new row by a request body. Keys without existent columns will be ignored.
PUT	/api/:table/:id	Update row. Keys without existent columns will be ignored.
DELETE	/api/:table/:id	Delete row by :id

POST, PUT and DELETE methods return an object id e.g. {"id": 10} on success.

Request examples

Method	Url	Body
GET	/api/books?limit 100
GET	/api/books?where price > 10
GET	/api/books/10
GET	/api/employees/7/orders
POST	/api/books	{"title": "ABC", "price": 5}
PUT	/api/books/10	{"title": "NEW"}
DELETE	/api/books/10

prefs.sqlite

Table	Description
prefs	User preferences such a window position, font attributes and etc.
recents	Recently opened databased.
history	Succeeded queries
gists	User saved queries
generators	Values for a data generator
functions	Custom functions
refs	Quick references. Some of rows are builded automatically by foreign keys.
disabled	All disabled index and triggers are stored here
encryption	Encryption profiles for databases
cli	Succeeded queries in a terminal
diagrams	Table's positions on diagrams
query_params	The values used for parametrized queries early
search_history	Last 20 search strings in Search dialogs
shortcuts	Query shortcuts
functions	Custom functions
help	Strings for popup help.

You can add own strings to help table. But don't change existing rows because the table will be updated automatically by new release and you'll lose your changes. The best way to change them or add something is just let me know.

Number values of prefs-table are stored in memory and overrided on exit. So their editing does not affect.
You can change prefs-values via terminal.

Advanced settings

There are couple settings which can be changed only trougth the terminal by .set-command e.g. .set cli-font-size 10.

Option	Default	Description
cli-font-size	8	The terminal's output font size
cli-row-limit	10	Row limit in query result in the terminal output
backup-prefs	0	Backup prefs.sqlite on start
use-highlight	1	Enable/Disable text highlighting in editors
use-legacy-rename	0	Equals to pragma legacy_alter_table = ?1
max-column-width	400	Max column width in a resultset. Set to 0 to turn off this limitation.
last-update-check	0	Last check date in YYMMDD-format
http-server-debug	0	Debug REST API web server to http-debug.txt
format-keyword-case format-function-case	1	Define how query formatter works with keywords and functions 0 - don't change 1 - use lower case 2 - use upper case
ignore-readonly-prefs	0	Don't alert user if prefs.sqlite is read only
extended-query-plan	0	Show a full execution plan for an query

Command line arguments

sqlite-gui [--profile <file>.sqlite] [--readonly] [database [<table or view>]] [--csv-import <input>.csv <tablename>] [--csv-export <output>.csv <query>]

The order of the individual commands is not important.

Examples

• Open the specified database at startup

  sqlite-gui D:/databases/bookstore.sqlite 
  

• Open only the specified table/view for editing at startup

  sqlite-gui D:/databases/bookstore.sqlite books
  

• Import CSV file with default settings

  sqlite-gui D:/databases/bookstore.sqlite --import-csv "D:/data/my file.csv" my_table > import-result.txt
  

• Export query result to CSV file with default settings

  sqlite-gui D:/databases/bookstore.sqlite --export-csv D:/export/books.csv "select * from books" > export-result.txt
  

Use quotes if a database path or a table name contains a space or another special symbols.

Debugging

Some operations e.g. an query formatting, can crush the application. You can enable Use logger.exe for debugging in settings to catch the last before a crush. This text will be especially usefull as a bug report.

How to build

• sqlite3.dll + sqlite3.def

  // original
  gcc -shared -Wl,--output-def=sqlite3.def sqlite3.c -o sqlite3.dll -DSQLITE_ENABLE_DBSTAT_VTAB -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_THREADSAFE=2 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_GEOPOLY -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_MATH_FUNCTIONS -s -O2 -static
  
  // SQLite3 Multiple Ciphers version, mingw64-32 is required
  gcc -shared sqlite3mc.c -o sqlite3.dll -DSQLITE_SECURE_DELETE -DSQLITE_ENABLE_DBSTAT_VTAB -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_THREADSAFE=2 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_GEOPOLY -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_MATH_FUNCTIONS -D_WINDOWS -DWIN32 -DNDEBUG -DSQLITE_API=__declspec(dllexport) -m32 -s -O2 -static -msse4 -maes
  

• libsqlite3.a

  cd include 
  dlltool -d sqlite3.def -l libsqlite3.a -D sqlite3.dll
  copy libsqlite3.a lib/libsqlite3.a
  

• Application x64

  mkdir bin
  mkdir obj
  
  set PATH=C:\mingw64\mingw64\bin;%PATH%
  del bin\sqlite-gui.exe
  
  windres.exe -J rc -O coff -i src\resource.rc -o obj\resource.res
  g++ -Wall -std=c++11 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0600 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\dbutils.cpp -o obj\dbutils.o
  g++ -Wall -std=c++11 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0600 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\dialogs.cpp -o obj\dialogs.o
  g++ -Wall -std=c++11 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0600 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\http.cpp -o obj\http.o
  g++ -Wall -std=c++11 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0600 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\main.cpp -o obj\main.o
  g++ -Wall -std=c++11 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0600 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\prefs.cpp -o obj\prefs.o
  g++ -Wall -std=c++11 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0600 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\tools.cpp -o obj\tools.o
  g++ -Wall -std=c++11 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0600 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\utils.cpp -o obj\utils.o
  g++ -Llib -o bin\sqlite-gui.exe obj\dbutils.o obj\dialogs.o obj\http.o obj\main.o obj\prefs.o obj\tools.o obj\utils.o obj\resource.res -static -m32 -s -static -m64  -lgdi32 -luser32 -lkernel32 -lcomctl32 -lgdiplus -lcomdlg32 -lshlwapi -lmsimg32 -lwininet -lws2_32 -lole32 -luuid lib\x64\libsqlite3.a -mwindows
  

  or use Code::Blocks 17 with installed mingw64 (MingW-W64-builds). To support Windows 10 -static linker flag is required.

Are you need more topics?

Get into contact with me by email lb-im@ya.ru