Working with SQLiteStudio

Slashdot it! Delicious Share on Facebook Tweet! Digg!
Alexander Kirch, 123RF

Alexander Kirch, 123RF

Data Workshop


SQLiteStudio, a compact graphical tool, lets you complete everyday tasks relating to SQLite databases quickly and easily.

In many desktop scenarios, using a RDBMS server proves to be overkill and can even cause security problems in case of misconfiguration. If only one user with one application needs access to the data, the SQLite database gives you an option, without having to do without the benefits of the query language (see box "SQLite").


SQLite is simply a 1MB program library that implements a relational database system. The library can be integrated directly into applications and extends them to include database functions; additional server software is not needed.

SQLite supports most SQL92 commands, including transactions, subqueries, views, triggers, and user-defined functions. However, it lacks functions for managing object permissions and a client-server architecture; the entire database is located in a single file.

Many well-known applications rely on SQLite because of its benefits, including the Mozilla Firefox, Google Chrome, and Apple Safari browsers; the Skype VoIP software; and mobile operating systems like Android, iOS, Symbian, and Windows phone. Additionally, Mac OS X uses SQLite.

SQLiteStudio [1] turns out to be a compact and useful tool, giving users the ability to easily accomplish many daily tasks related to SQLite databases. To discover how to set up the practical application quickly, refer to the "Installing SQLiteStudio" box.

Installing SQLiteStudio

Many distributions have SQLiteStudio in their software repositories; you can generally set up the program conveniently with the respective package manager.

However, SQLiteStudio is conspicuously absent from Ubuntu. SQLiteStudio is available via the project website [2] in the form of 32-bit or 64-bit binaries for Linux, Mac OS X, and Windows, as well as a source tarball.

Download the binary package, and then unpack it with:

tar xvf sqlstudio-X.Y.Z.tar.xz

where X.Y.Z is the version number – 3.0.7 at the time of writing – and SQLiteStudio is ready to use.

You can run it from within its own directory by typing


at the command line.

When you first start, SQLiteStudio prompts you for the desired localization. There are localizations for Chinese, French, Polish, Russian, and Slovenian, or you can leave the default value of American English . All the information in this article assumes you did the latter.

Creating a Database

To create a new database, launch SQLiteStudio and then click Database | Add a database or simply type Ctrl+O. In the dialog box that appears (Figure 1), choose SQLite 3 as the Database type . In File , click on the green plus sign to the right of the input box to create a data directory.

Figure 1: SQLiteStudio dialog for creating a database.

If the Generate automatically box is checked for Name (on the list) , the tool creates a database name from the directory name. If you keep the Permanent (keep it in configuration) box checked in Options , SQLiteStudio will remember this database in your next session.

Finally, click on Test connection . If the SQLite database is operational, you will see a green checkmark as confirmation. Clicking OK then quits the dialog.

Before you can create a table, you first need to connect to the database. To do so, select Database |Connect to the database , or press the icon with the two connectors on the far left. If multiple databases are available later on, you will need to select the required database below Databases on the left.

After successfully connecting, SQLiteStudio shows you the database in a tree structure on the left edge of the program window.

Creating Tables

The dialog for creating a database table can be opened via the Structure | Create a table menu item, by right-clicking on Tables in the tree display of the database on the left or via the table icon with the plus sign in the toolbar.

Enter the table name in the Table Name field – in our example, it is staff (see Table 1). To create a new table column, click on the appropriate icon in the toolbar of this window or press Ins . This opens the window for creating the column (Figure 2).

Table 1


name surname born
Joe Miller 03/04/1976
Henry Mitchell 01/02/1993
Laura Smith 09/09/1990
The content of the snum field is created automatically.
Figure 2: After naming the table, create a table field.

In the resulting dialog, you can set all of the properties for the table column. In each table, one of the fields must have a primary or foreign key. In our example, the snum column is used as the primary key (Figure 3), and its value is automatically incremented (AutoIncrement ).

Figure 3: Use this dialog box to set up the primary key.

After creating all the required columns in the table – the structure of the sample table staff is shown in Figure 4 – press the green checkmark icon in the tool bar. This will show the SQL command that will create the table in your database. The toolbar also contains all the tools necessary for further editing of the table, including editing the properties of a column or deleting columns.

Figure 4: Structure of the example table staff.

Buy this article as PDF

Express-Checkout as PDF

Pages: 7

Price $0.99
(incl. VAT)

Buy Ubuntu User

Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • Getting started with LibreOffice Base

    Creating database applications with wizards and graphic editors – without SQL and programming – that's what the LibreOffice program Base, modeled after Microsoft Access, is all about.

  • Visualizing complex structures using Graphviz

    Network plans, nested dependencies, or binary trees – with Graphviz, you can visualize complex relationships in a simple way.

  • LibreOffice Base

    A database without a search function is like a car without an engine. The second part of our Base series is dedicated to implementing a database search.

  • Convenient private cloud with Seafile

    After the Prism and Tempora revelations, the writing's on the wall: Your data stored on public clouds isn't safe from the clutches of security agencies. Help can come only from a self-administered solution – yet Seafile provides a simple and ingenious approach.

  • Installing and testing Nextcloud

    Leading ownCloud developers, including the project founder Frank Karlitschek, became dissatisfied with the direction of the project, so they started Nextcloud, a fork of the code and a new company. The goal is to create a better balance among the company, clients, and users. We take a look at how Nextcloud is faring.