How to Use DB Browser SQLite for Database Management
Evidence Nwangwa 
Open & Create Databases in DB Browser, Create Tables & Views, Add data to tables, Open more than one database, Run SQL Queries in DB Browser SQLite.
A comprehensive beginners guide to using DB Browser SQLite for Database management.
In a previous article, I talked about how to install SQLite on Windows and Mac computers
Now, let’s discuss SQLite usage. Specifically in the context of using DB Browser for SQLite
The article below assumes you are using DB Browser for SQLite. Here’s a link to install it.
Here are the things covered
- How to open a database in DB Browser for SQLite
- How to Browser data in DB Browser for SQLite
- How to create a new database in DB Browser for SQLite
- How to Create a New Table in DB Browser for SQLite
- How to Execute and Save SQL Queries in DB Browser for SQLite
- How to Add data to Tables in DB Browser for SQLite
- How to Open and Save Projects in DB Browser for SQLite
- How to Open more than one database at a time in DB Browser for SQLite
- Additional “Extra” Stuff You need to know about DB Browser for SQLite
If you prefer to watch a video instead of reading the blog, here's a video covering the same topic. Enjoy!!!
How to Open a Database in DB Browser for SQLite
To open in a database, just click
Open Database —> Find the database you are looking for that ends in .db or .sqdpro
and select it.
If you want a .db database to work with in DB Browser for SQLite, you can get it here in my FREE Technical Resources Page
Or you can get them from my github repo for Databases but the FREE Technical Resources page has a lot more databases and resources than my github page.
How to Browse data in DB Browser for SQLite
After the database is open, you can browse it’s content by clicking “browse data”
If you don’t see the “browse data” tab, go to view —> browse data to toggle on this option.
How to Create a New Database in DB Browser for SQLite
Sometimes you want to create a new database instead of opening one. Here’s how.
Click on
New Database —> Choose a folder for your database —> Enter file name —> Hit Enter
It will bring you back to DB Browser.
From here, you can
Enter the table names and fields manually using the screen presented or you can click cancel and add the tables later.
Go to the next section to learn how to create tables.
How to Create a New Table in DB Browser for SQLite
Note: You need to have a database before you can add a table.
To add a table to a database, you have 2 options.
Option 1 - Do it with the user interface
Option 2 - Do it with SQL Query
Option 1 - Adding a table using the GUI
After your database is open, click on edit —> create table
Enter table name and some fields to create the table
Click OK and the table will be added to your database
To save the changes you made to your newly created database, be sure to hit the “write changes” button
Option 2 - Create a table using SQL Queries.
Go to the next section to learn about executing SQL Queries
How to Execute and Save SQL Queries in DB Browser for SQLite
To execute SQL Queries
Click on “Execute SQL”. If you don’t see this option, click on View —> Execute SQL
Write your SQL Query like so
CREATE TABLE "testing_102" (
"First Name" TEXT,
"Last Name" TEXT,
"phone" INTEGER,
"address" INTEGER,
"Born_In_Seattle" REAL
"Country of Origin" TEXT
)
Highlight and click the “play” button/icon above to execute the query
This creates a new table and adds it to the database
Be sure to hit “Write Changes” to save the changes you made to the database
To save your SQL Queries in DB Browser, you have 2 options
Click the “save icon” to save the SQL file independent of the project
or click “Save Project” to save the SQL Query as part of the project.
Saving the SQL independent of the project allows you to re-open and re-use the SQL Queries without having to open the DB Browser Project with DB Browser.
How to Add data to Tables in DB Browser for SQLite
Here’s how to add data to a table in SQLite
You have 2 Options.
Option 1 - Add data to table using the GUI
Option 2 - Add data to table using SQL
Option 1 - How to add data to a table using the UI in SQLite
- Click on Browse data (View —> browse data)
- Find the table you want to insert data into from the dropdown menu
- Click the Insert record Icon to insert rows
3. Add data to tables in DB Browser for SQLite - Start Entering values for the table in the Browse data page
- Click the “Save Icon” to save it as a view or export it as a csv
3b. Add data to tables in DB Browser for SQLite. Export as View or CSV - Click on “Write Changes” to save those changes to the database.
3b. Add data to tables in DB Browser for SQLite: Write Data Changes to Database.
Option 2 - Add data to SQLite table using SQL Queries
You could also insert data into the table using SQL Statement
INSERT INTO "testing_102" (
"First Name",
"Last Name" ,
"phone" ,
"address" ,
"Born_In_Seattle" ,
"Country of Origin"
) VALUES ("Evidence", "Nwangwa", 123, "Seattle", "False", "Nigeria");
And the data will be available in the table
Remember to click “Write Changes” to save the changes to the database.
After adding your tables, you may want to add data to the tables.
How to Open and Save Projects in DB Browser for SQLite
Ok. You have made all these changes, but you really need to use the bathroom or take your dog to the vet, so you want to pause and come back later without loosing your progress.
Here’s how to save your projects.
Just click on “SAVE PROJECT” and save it to local computer. It will save as a .sqbpro file
If you are ready to start working again, you have a couple of options.
Option 1 - Find the .sqbpro file saved before and double click it, that should open it in DB Browser.
Option 2 - Go to DB Browser, Click on “Open Project”, find the .sqbpro file saved above, and open that.
.db file is just the database. If you open that, it’s like opening a new project from scratch. .sqbpro file is the actual DB Browser file and opening it will allow you to continue with where you left off including the SQL Files and SQL queries and Databases.
How to Create a View in DB Browser for SQLite
There are 2 ways to create a view in DB Browser SQLite
Option 1 - Create it using the User Interface
Option 2 - Create it using SQL Query.
Option 1: You can create a view in SQLite by
Browse data —> Filter the data if desired —> Click the save icon —> Save as view —> Give it a name and it shows up under “views” right below “tables”
Option 2: Write a View statement and execute it in DB Browser
For Example
Create view view_102_test_2 as
select * from testing_102 where Born_In_Seattle = "False"
And the views show up in the DB Schema page
Be sure to click “Write Changes” to save the Views to the database
How to Open more than one database simultaneously in DB Browser for SQLite
There are times when you want to open more than one database at a time in DB Browser.
To do so, click on “Attach Database”
Find the .db file you want to open, open it and it should show up in the list of tables under DB Schema like so.
I opened the chinook database
Since you now have more than one database present, you will need to specify the database and fully qualify the objects when making SQL Queries. This way, the DB Engine will know which Database you are referring to.
For example, under Browse data in the image below, do you see how chinook database tables is prefixed with chinook.<table_name> ? That’s what I mean by fully qualifying the name.
Additional “Extra” Stuff you need to know about DB Browser for SQLite
To add primary and foreign key constraints or to modify the table structure or to export a table as CSV in DB Browser SQLite
Click the table name —> database structure —> Write click the table and choose the option of interest. Specifying the database constraints is found under “modify table”. Database structure us under “View” Option.
If You can’t see DB Schema which is where you have your list of Tables and Views, this can be found under View —> DB Schema
You have additional options under “VIEW”
And “Tools”
I hope you liked this quick introduction to using DB Browser for SQLite
------------------------------------------------------------------------------------------------
Go here to learn how to download DB Browser for SQLite on Windows and Mac
Feel free to get access to my FREE Technical Resources where I give away stuff worth 10X more than this article for FREE including access to many databases that you can download and use in DB Browser for SQLite
Go to my youtube channel below to watch a video on how to do everything I described above.
Follow me on Youtube
Follow me on Linkedin
Follow me on Github
Follow me on Twitter
If you want to contact me, speak to me here
Want me to create a similar technical content for your brand? Contact me here.
Want to get updates everytime I publish new content? Join my FREE Technical Newsletter here
Categories: : Data Base Management System (DBMS), SQL, SQLite
