SQLite Studio to manage SQLite databases

In this blog post, we will discuss SQLite Studio, a great little management tool, that helps us to easily create and manage SQLite databases. It is a free, cross-platform, portable (no installation required), open-source tool, that can be used without licensing issues even commercially.

The SQLite Studio comes with a list of features like SQL code editor, export and import data to and from various formats (like SQL, XML, JSON, etc) work with multiple databases, encrypted databases, SQL and DDL history, Data population for testing, and many more.

In this post, we will refer to how to install, know about its standard menu, create a sample test database, add table and records, execute a query, export and import database and table records.

Installation – Download, Unzip and Execute

The SQLite Studio is a portable tool that requires no installation. The user can directly download, unzip the downloaded folder and start using the tool. The tool can be download from the https://sqlitestudio.pl/ page.

The unzipped folder contains an “SQLiteStudio.exe” executable on a Windows machine. SQLiteStudio on Linux machine. Run the executable to start the studio. I am running version 3.2.1 on a Windows machine.

SQLite Studio Interface
SQLite Studio – User Interface

Standard Menus of SQLite Studio

The SQLite Studio provides a simple user interface with 5 menu options (at the time of writing the current version is: 3.2.1). Let us see what options each of them provide for the user.

Menu – Database

The Database menu option provides functionality on the database level and provides the user with an option to perform major operations like adding (creating), editing, removing a database. Connecting to an existing database, export the connected database to SQL, XML, JSON, PDF, HTML formats as well as to convert the database from one format to another like from SQLite3 to SQLCipher, performing integrity test, etc.

SQLite Studio Database menu
SQLite Studio – Database menu

Menu – Structure

The Structure menu option allows the user to perform various database operations like creating, editing, deleting a table, index, trigger, and views.

SQLite Studio Structure Menu
SQLite Studio – Structure menu

Menu – View

The View menu provides options related to the Studio layouts, toolbar, and windows management.

SQLite Studio View menu
SQLite Studio – View menu

Menu – Tools

The Tools menu provides users with an option to open SQL editor, DDL history, SQL Function editor, Collations editor, Import table data, Export entire database, table, or a query result. As well as options to open Extension Manager and Configuration Editor where the user can configure the look and feel, shortcuts, code formatters details, and data editor values.

SQLite Studio Tools Menu
SQLite Studio – Tools menu

Menu – Help

The Help menu provides links to the User manual, SQLite documentation, links to Studio’s home page, and forums to request a new feature, report bugs, and an option to check for updates, etc.

SQLite Studio Help Menu
SQLite Studio – Help Menu

Add (Create) a database

A database can be added (created) in the SQLite Studio in three ways, first is through the Database menu (Database > Add a database), second by using the shortcut key Ctrl + O keys, and third by clicking the “Add a database” icon on the toolbar, on doing so the Database dialog is presented to the user.

SQLite Studio Add a database
SQLite Studio – Create database form

Here we have an option to select the Database type; available options in the current versions are SQLite 3, SQLite 2, SQLCipher, System.Data.SQLite and WxSQLite3. For our sample database, we will choose the default Database type i.e. SQLite3

SQLite Studio SQLite Database Type
SQLite Studio – Database types

Next is the File section, where we can either provide the name of the database file and click the Ok button to save (this will create a new database file, in the default SQLite Studio location) or we can browse by clicking the “+” button to select a location and save the file. We can also select an existing database. In either case, the database will be added to the Databases list window.

We can choose to provide an alias name for the database by providing a name in the Name (on this list) text box. This name will be used for display purposes in the Databases list window, the actual name of the database file will not be affected. Here we have given “test.db” as the database name.

If we want the database to be always available when the SQLite Studio has opened the check the “Permanent (keep it in configuration)” checkbox.

SQLite Studio Database dialog
SQLite Studio – Create new database

Here we have created a new database with the following values selected: SQLite3 as the Database Type, “test.db” as the File name, and test as the Name (on the list) as well as checking the Permanent (keep it in configuration) value checked.

After clicking on the OK button, the database file is created and displayed on the Databases list window. Double click the database name to load the artifacts.

SQLite Studio database list view
SQLite Studio Databases List Window

Adding a table

Now let us add a new table called Employee into our test database, with the following requirement.

  1. Id: Integer, Primary Key, and Autoincrement.
  2. FirstName: varchar(20)
  3. LastName: varchar(20) and Not Null

A new table can be added in three possible ways, first, from the Structure menu (Structure > Create a table), second by clicking the “Create a tableicon on the toolbar, and third by right-clicking on the Tables in the Databases list window.

SQLite Studio create table
SQLite Studio – Create a table

On selecting the Create a table option, we will be presented with the table details window, where we have an option to fill in the details using a form or by writing the DDL ourselves. Let us use the Form to fill in the details to create the table.

SQLite Studio create table form

In the form, we will provide the Table name as Employee. Now let us add the first column Id details by clicking the Add-column icon, and fill the column name as Id, Data Type: as INTEGER and check the Primary Key option and then click the Configure button to open the Edit constraint dialog.

SQLite Studio add column
Table primary key

Here we will check the Autoincrement checkbox as per the column requirement and click on Apply and then the OK button on the Column dialog.

SQLite Studio primary key autoincrement
SQLite Studio – Autoincrement

Next, we will add the second column FirstName again by clicking the Add column icon, with the following values, and click the OK button.

SQLite Studio add column to table

Next, is to add the last column LastName by following the same process.

SQLite Studio add a column to table not null
SQLite Studio – Column not null

All the column details will be displayed in the table Structure form. Next, we need to commit it to the database. The changes can be committed by clicking the “Commit structure changes icon.

SQLite Studio create new table
Employee table – Columns declaration

Once we click the “Commit structure changes” icon, we will be displayed the “Queries to be executed” dialog, displaying the DLL statement that will be executed against the database, here we will click the OK button to apply the changes. A status message will also be displayed for our verification purpose in the status window.

SQLite Studio create table DDL query
Create Employee table DDL Query

Once we apply the changes the table Employee will be added and displayed into the Test>Tables>Employee section in the Databases list window.

SQLite Studio databases list window
SQLite Studio – Databases list window

Add Records to Employee table

Now let us add a few records and we will do this by double-clicking the Employee table and selecting the Data tab. In this form, we will use the shortcut “Ins” button to insert a new record. (The user can choose to do the same using the “+” icon on the form or by selecting add multiple records at once from the drop-down next to the “+” Insert record icon.)

After inserting the required records, we commit the changes by pressing the “Commit” icon or by using the shortcut (Ctrl + enter)

SQLite Studio add records to table
Add Record to a Table

Executing SQL Queries

In order to execute a SQL query against any database, the SQLite Studio provides a SQL editor. The same can be open from the Tools menu (Tools > Open SQL editor) or by pressing the shortcut key “Alt + E“.

The user can use the same Query Editor window for multiple databases just by changing the active database in the drop-down list.

Let us run a simple query in the editor to check the output. To execute the query we will press the F9 shortcut.

SQLite Studio Query Editor
SQLite Studio – Query Editor

The output of the query is displayed in the Grid view and as a Form view, as well as the status of the query execution, is also displayed in the Status window.

Export database

Let us export the “Test.db” database as an SQL file. In order to export the database, we have three options first, is from the Tools menu (Tools > Export), second is to select “Export the database” from the Database menu and the third option is to right-click on the database name and select “Export the database” from the context menu.

SQLite Studio export the database
SQLite Studio – Export the database

On selecting the menu, we are presented with the Export dialog, select the all the table that needs to be exported. If we want to export the table data as well then have to select the “Export data from tables” option, and click the Next button.

SQLite Studio export data from tables
SQLite Studio – Export database and data from tables

In the next section, we have an option to select the format for the exported file, here we can choose from the various options like HTML, JSON, PDF, SQL, and XML, here we will select the default SQL option.

We can select the output to be a file or just to export the content to the clipboard, here are all the option we have selected, they are all self-explanatory. We have named the file to be DB.sql. Click the Finish button to export the file.

SQLite Studio export database form
SQLite Studio – Export database format and options

The content to the DB.sql is as follows:

--
-- File generated with SQLiteStudio v3.2.1 on Thu Dec 3 00:09:27 2020
--
-- Text encoding used: System
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: Employee
DROP TABLE IF EXISTS Employee;

CREATE TABLE Employee (
    Id        INTEGER      PRIMARY KEY AUTOINCREMENT,
    FirstName VARCHAR (20),
    LastName  VARCHAR (20) NOT NULL
);

INSERT INTO Employee (Id, FirstName, LastName) VALUES (1, 'Lindsay', 'Ferguson');
INSERT INTO Employee (Id, FirstName, LastName) VALUES (2, 'Mathew', 'Hammer');
INSERT INTO Employee (Id, FirstName, LastName) VALUES (3, 'John', 'Doe');

COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

Export Table

We can choose to export a single table as well without exporting the complete database. In order to export a table, right-click on the table name to be exported and select “Export the table” from the context menu.

SQLite Studio export table
SQLite Studio – Export the table

The Export dialog will appear, we can select to export table data, indexes, and triggers. By default, all these options are checked.

SQLite Studio export table form
SQLite Studio – Table to export

So we will continue by clicking the Next button, and move the next window which is similar to the export database option. Keeping all the options as below, we will click the Finish button.

SQLite Studio export table form dialog
SQLite Studio – Export Data format and option

As the name provided is DB.sql, following is the output of the exported file.

--
-- File generated with SQLiteStudio v3.2.1 on Thu Dec 3 00:24:00 2020
--
-- Text encoding used: System
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: Employee
DROP TABLE IF EXISTS Employee;

CREATE TABLE Employee (
    Id        INTEGER      PRIMARY KEY AUTOINCREMENT,
    FirstName VARCHAR (20),
    LastName  VARCHAR (20) NOT NULL
);

INSERT INTO Employee (Id, FirstName, LastName) VALUES (1, 'Lindsay', 'Ferguson');
INSERT INTO Employee (Id, FirstName, LastName) VALUES (2, 'Mathew', 'Hammer');
INSERT INTO Employee (Id, FirstName, LastName) VALUES (3, 'John', 'Doe');

COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

Import Data into a Table

The SQLite Studio also provides an option to import data into an existing table from a CSV file or using a Regular expression. Here will use a CSV file to import data into our Employee table. The content of the CSV file is as follows. Please note my CSV file contain the header information as well.

Id,LastName,FirstName
4,'Edwards', 'George'
5,'Howell', 'Rachel'
6,'Funke', 'Tobias'

In order to import data into a table, right-click on the table name and select the “Import into the table” option or select the Tools menu and Import.

SQLite Studio Import data to table
SQLite Studio – Import data into table

The import dialog is presented with the database and the table name selected. Click the Next button to continue.

SQLite Studio Import table form
SQLite Studio Import Data Form

Here in the dialog we will select Data source type as CSV, browse and point to our input file, check the “First line represents CSV column names” option as our input file contain the column name and we want to ignore them, and finally select the Field separator as, (comma), and then click the Finish button. This will import all the data into the Employee tables. (Assuming all the data available in the file are correct)

SQLite Studio data source form
SQLite Studio Import data – Data Source

Let us refresh the Employee table data. Here all the data from the CSV file are imported successfully.

SQLite Studio import data from csv file
Imported data from CSV file

Style Configurator

The SQLite Studio provides an option to change its look and feel, as well as to customize its shortcuts, Fonts, colors, Style (theme). The Style configuration dialog can be open from the Tools menu (Tools > Open configuration dialog) or by using the shortcut F2 key.

SQLite Studio style configurator
Style configurator dialog

Conclusion

The SQLite Studio comes with a great number of features like populating a table with random data, function editor, DDL history, Extension manager. Exploring them would be of great help. All in all, it a great tool to manage SQLite databases with ease and is a must-have tool for the team.

The source code and other tools related details can be found on GitHub: https://github.com/pawelsalawa/sqlitestudio

I hope you found this post helpful. Thanks for visiting. Cheers !!!

[Further Readings: Adapter Design Pattern in C# |  How to use Blazor EditForm for Model Validation |  How to add a new profile in Windows Terminal |  How to easily Customize Global Settings in Windows Terminal |  How to add Git Bash to Windows Terminal Application |  How to customize Windows Terminal Application |  How to customize Windows Terminal Key Bindings |  How to Install Windows Terminal in Windows 10 |  Important Debugging Shortcuts of Visual Studio 2019 |  How to publish a Blazor Server Application to IIS |  Top 7 Visual Studio 2019 extensions for Web Projects |  The difference in Blazor Server and WebAssembly Application ]  

0 0 votes
Article Rating
Subscribe
Notify of
guest
1 Comment
oldest
newest most voted
Inline Feedbacks
View all comments
1
0
Would love your thoughts, please comment.x
()
x