Skip to content

Tutorial

This doc has examples how to use workspace toolset to develop SQLite databases and fill them with data. As well explore, query and visualise.

To start open Quickstart page for quick access to all the tools

Workspace collage

Use workspace terminal to execute CLI commands

codeserver-demo

Workspace conntains example SQLite database, the file /home/static-server/sqlite-viewer/examples/Chinook_Sqlite.sqlite

Sqlite

SQLite3 is installed. Open terminal and execute

sqlite3

Note: check out a better CLI litecli

Sqlite extensions

The following compiled extensions are present in the folder /home/sqlite-extensions

  • crypto: secure hashes
  • fileio: read and write files
  • fuzzy: fuzzy string matching and phonetics
  • ipaddr: IP address manipulation
  • re: regular expressions
  • stats: math statistics
  • text: string functions
  • unicode: Unicode support
  • uuid: Universally Unique IDentifiers

Example start SQLite, and load extension:

sqlite3
.load /home/sqlite-extensions/stats

Upload & download SQLite databases

No matter if workspace is used locally, in cloud or kubernetes you can upload and download database files using file browser

filebrowser

Sqlite-web

sqlite-web - is a web-based SQLite database browser. It can serve any SQLite database.

There are 2 SQlite database served by default:

  • 'Main' database. Empty SQlite3 database. Sqlite-web 'Main'
  • 'Example/test' database. Filled with test "Chinook" database.

Serve on-demand SQlite database

One port 8034 is reserved for serving on-demand database with Sqlite-web. Serve it with the following command

sqlite_web <path-to-your-sqlite-database-file> --host 0.0.0.0 --port 8034

Example, start sqlite-web on port 8034 for SQLite database file /home/static-server/sqlite-viewer/examples/Chinook_Sqlite.sqlite

sqlite_web /home/static-server/sqlite-viewer/examples/Chinook_Sqlite.sqlite --host 0.0.0.0 --port 8034 

and with extensions loaded:

sqlite_web /home/static-server/sqlite-viewer/examples/Chinook_Sqlite.sqlite --host 0.0.0.0 --port 8034  -e /home/sqlite-extensions/text.so -e /home/sqlite-extensions/unicode.so -e /home/sqlite-extensions/uuid.so

You can open on-demand database served with Sqlite-web from the Quickstart page.

Sqlite-viewer

sqlite-viewer - explore and query SQLite databases.

Upload SQLite database file (from your local machine) and explore.

sqlite-viewer

Web-GUI-for-SQLite

Web-GUI-for-SQLite - explore and query SQLite databases.

Upload SQLite database file (from your local machine) and explore.

sqlite-web

Litecli

Litecli - is a command-line client for SQLite databases that has auto-completion and syntax highlighting. Useful if you prefer CLI.

Open Terminal and try with example database:

litecli /home/static-server/sqlite-viewer/examples/Chinook_Sqlite.sqlite

litecli1

litecli2

Load extensions

.load /home/sqlite-extensions/stats

Tbls

Generate docs for the example database

tbls doc sqlite:////home/static-server/sqlite-viewer/examples/Chinook_Sqlite.sqlite /home/static-server/sql-tbls

and view SVG files with Static File Server

tbls

DB designer

With DB designer you can visually create ERD diagrams, and generate DDL SQL scripts

dbdesigner

Advanced

SQLite Json

Examples

CREATE TABLE jsontest   (
  id INTEGER NOT NULL PRIMARY KEY,
  jss JSON
);

INSERT INTO jsontest  VALUES 
(1, json(' { "key1" : "my key 1", "key2": [ "test" ] } ')),
(2, json(' { "key1" : "my new key", "key2": [ "test", "test" ] } ')),
(3, json(' { "key1" : "my yet another key", "key2": [ "test", "test", "test2" ] } '));

SELECT * FROM jsontest;

Links: - Complete SQlite JSON docs

Rclone

rclone is a command line program to manage files on cloud storage. It is a feature rich alternative to cloud vendors' web storage interfaces.

Copy dump to S3:
Create file ~/.config/rclone/rclone.conf with the following content

[remote]
type = s3
provider = AWS
access_key_id = XXXXXXXXXXXXXXXXXXXXXX
secret_access_key = XXxxXXxXXXxxxXXxXXXxxXXXxxxXXXxXXXX
region = xx-xxxx-x

Use Rclone to copy to S3 and delete from local

rclone move /home/project/sqlite-db remote:my-s3-bucket/sqlite/

Restore from S3 to local: When there is a need to get database from S3 to local folder.

# copy 
rclone copy remote:my-s3-bucket/sqlite/ /home/sqlite-db/
# mount S3 without copying
rclone sync remote:my-s3-bucket/sqlite/ /home/sqlite-db/