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.

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.

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

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.

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.

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.

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.

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.

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')

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.

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.

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.

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.

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 %}

{# 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 %}

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

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

{% 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}) %}

{% 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 %}

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

{# 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") %}

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

select 1;
select 2;
select 3;

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.

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.

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.

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.

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.

• 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.

• The driver works in read-only mode.

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.

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).

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.

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.

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

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.

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

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.

• 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.

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.

• 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.

Get into contact with me by email [email protected]