Home
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.
sqlite-gui vs DB Browser for SQLite vs Sqlite Studio
| Feature | sqlite-gui | DB4S | Sqlite Studio |
|---|---|---|---|
| Syntax highlighting | + | + | + |
| Autocomplete | + | + | + |
| Edit table structure | -/+ | + | + |
| Charts | + | + | - |
| Edit multiple databases at the same time | +/- | + | + |
| Parallel queries | + | +/- | +/- |
| User-defined functions | +/- | - | + |
| Encrypted database support | + | + | + |
| Multi-languages | - | + | + |
| Cross-platformed | - | + | + |
| Code auto-formatting | +/- | - | + |
| Data generator | + | - | + |
| Query history | + | - | + |
| Parametrized queries | + | - | + |
| Edit data in resultset | - | + | + |
| BLOB support | + | + | + |
| Extensions | + | + | + |
| Unique features |
|
|
ℹ️ The major disadvantages of sqlite-gui are bolded.
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 increase in settings. - DB4S wraps an original query to
with t as (...) limit N offset Kto get data only for a visible part. Plus executes an query to get row countwith 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(withoutorder-clause) it's a fastest solution. - SQLiteStudio uses the same technique as DB4S for paggination by
1000rows.
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, NULLKeep in mind
- The text columns are ignored
- Dates should have
YYYY-MM-DDorYYYY-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 with a simple preparation.
Open refs-table in prefs.sqlite and click by it in an editor.
| 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. Check video to see it in action.
refname-column and the query-column should returns only necessary data.
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 %}
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;
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.
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.
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>returnssucceeded-status without real deleting data. - An table could be referenced as
<tblname>$B23that means worksheet<tblname>and cellB23. -
SQLExecDirectthat 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:
- Open Google console.
- Create a new project and select it as Active.
- Open
API and Servicesand push+ ENABLE APIS AND SERVICES. - Find
Google Sheets API, open it and enable it. - Return on
API and Servicesand chooseCredentials. - Push
+ Create credentialsand chooseAPI key. - Additional step: push
Editand chooseRestrict keywithGoogle Sheets APIonly 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
-
attach,detachandpragmacommands 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 useTEMP2-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.
| 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.
| 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 |
| 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.
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 |
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.
- 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
or use Code::Blocks 17 with installed mingw64 (MingW-W64-builds). To support Windows 10
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-staticlinker flag is required.
Get into contact with me by email lb-im@ya.ru