How to Install SQLite on Windows 10, 2016, 2019, 2022 (SQLite3)
How to Install SQLite on Windows 10, 2016, 2019, 2022 (SQLite3 Command). SQLite is popular tool due to its great feature and zero configuration needed. It is small and self contained database engine that has a lot of APIs for a variety of programming languages. This article will take you through the process of setting up SQLite on Windows 10, 2016, 2019, 2022.
What is SQLite
Structured Query Language or SQL is a programming language primarily used in database management. Specifically, it provides access to and management of all the data contained in a tabular RDBMS.
SQLite itself is an RDBMS (relational database management system). In addition, it is in the public domain, which means you are free to use its code for commercial or noncommercial use. As a result, you will not be legally restricted in terms of utilizing, modifying, or even distributing the platform.
As opposed to server/client SQL systems, like MySQL, SQLite has been optimized for simplicity, economy and requires relatively little configuration. In the context of that, it does not compete with server/client solutions.
SQLite is a very popular database management system because of the fact that it is lightweight and easy to manage.
With SQLite all data and the data objects are stored in a single file that can be accessed directly by any application. The file is stored on the file system and no further admin required to run SQLite.
However, SQLite is only capable of handling low to moderate volume HTTP requests and also the database size is usually limited to 2GB. Even SQLite has its limitations, its advantages have gained the attention of more users. The tools in SQLite in particular the SQLite3 Command Line CLI we examine here, work the same from one environment to the next.
Sqlite3
The SQLite project has simple command line program named sqlite3 or sqlite3.exe on Windows, which allows the user to manually enter and execute SQL statements against an SQLite database or a ZIP archive. In this article we go through steps how to use the sqlite3 program in Windows server.
In the next part of our article How to Install SQLite on Windows 10, 2016, 2019, 2022 (SQLite3 Command) we will point out SQLite advantages:
Benefits of SQLite
Improves Performance
SQLite is extremely small and lightweight RDBMS, it is self contained in a small footprint. Also, it is serverless and file based. Any other RDBMS like MySQL needs a separate server for functionality, also called server/client architecture.
In fact, SQLite may take up as little as 600KB of space, depending on the system you install it on. You do not have to worry about installing any dependencies in order to make it work.
The SQLite database itself is modest by nature. Objects composing them – such as tables, indexes, and schema – are centralized within a single OS file. The combination of all these factors can lead to solid performance, in addition to high levels of dependability and stability. More specifically, SQLite runs between 10 to 20 times faster than PostgreSQL, and two times faster than MySQL. Low traffic websites will notice the difference the most.
Easy Setup and Administration
SQLite requires no configuration or requirements from the beginning. It is serverless and requires no configuration files. This means there isn’t any installation process involved. Downloading the requisite SQLite Libraries is all you need to get started with your database. Your computer is under no significant strain because it uses a small amount of memory.
SQLite is also transactional. This will allow you to roll back changes in case of a program crash or if an editing attempt does not succeed. SQLite’s rollback function allows you to return a database’s version that existed before the edits were applied. Therefore, you can rest assured that your changes won’t cause irreversible damage.
SQLite offers simplicity as well as easy administration. Ultimately, this means you may not need a DBA to keep everything running smoothly.
SQLite Is Versatile, Portable, and Flexible
A platform that provides versatility is one of the most beneficial things when it comes to software. SQLite is a cross platform database engine that is known for its robust portability and functionality.
You can copy and transfer data between 32 bit and 64 bit operating systems, as well as between big endian and little endian architectures. Basically, SQLite can be installed, compiled, and run on a number of different platforms, including Linux, Windows, and Mac OS X.
Furthermore, since the SQLite database consists of only one file and not a collection of separate files, it is far more portable when it comes to flexibility. As a result, SQLite is suitable for a wider variety of computing platforms.
SQLLite reduces cost and complexity
License free and serverless. No need for lengthy and error prone procedural queries. SQLite can be easily extended in in future releases just by adding new tables and/or columns. It also preserve the backwards compatibility.
Follow this post, we will show you how how to Install SQLite on Windows 10, 2016, 2019, 2022 (SQLite3 Command).
Install SQLite on Windows 10, 2016, 2019, 2022 (SQLite3 Command)
Install SQLite on Windows
First, go to SQLite’s official website download page and download precompiled binaries from Windows section. Once the download is completed, you should see the downloaded file in the Windows Downloads directory.
Next, right click on the downloaded file and extract it inside the C:\sqlite directory.
The window will look like this and you extract Compressed (Zipped) Folders and find a destination file as below
Next, open the Windows CMD and change the directory to C:\sqlite using the following command:
cd C:\sqlite
Next, verify the SQLite version with the following command:
sqlite3.exe
You will get the SQLite version in the following output:
SQLite version 3.38.5 2022-05-06 15:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
Create a Database in SQLite
The basic syntax to create a database in SQLite is shown below:
sqlite3.exe database-name
Now, let’s create a database named employee.db using the following command:
sqlite3.exe employee.db
You can also verify your created database with the following command:
.database
You should see your created database in the following output:
main: C:\sqlite\employee.db r/w
To get a list of all SQLite options, run the following command:
.help
You should see the list of all SQLite options in the following output:
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.connection [close] [#] Open or close an auxiliary database connection
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump ?OBJECTS? Render database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in spreadsheet
.exit ?CODE? Exit this program with return-code CODE
.expert EXPERIMENTAL. Suggest indexes for queries
Create a Table in SQLite
SQLite databases are organized into tables and tables store information in rows and columns. If you want to create a table named engineer with some columns, run the following command:
CREATE TABLE engineer(id integer NOT NULL, name text NOT NULL, persontype text NOT NULL, length integer NOT NULL);
To verify the created table, run the following command:
.tables
You should see the following output:
engineer
Now, insert some data into the table using the following command:
INSERT INTO engineer VALUES (1, "Ram", "Junagadh", 528);
INSERT INTO engineer VALUES (2, "Vikash", "Surat", 734);
INSERT INTO engineer VALUES (3, "Raj", "London", 1200);
Next, verify the inserted data using the following command:
SELECT * FROM engineer;
You should see the content of a table in the following output:
1|Ram|Junagadh|528
2|Vikash|Surat|734
3|Raj|London|1200
To view the data based on ID, use the following command:
SELECT * FROM engineer WHERE id IS 2;
You should see the following output:
2|Vikash|Surat|734
Update a Table in SQLite
You can use the ALTER TABLE command to change the table. For example, to add a new column named age, run the following command:
ALTER TABLE engineer ADD COLUMN age integer;
Next, add the new age value for each rows using the following command:
UPDATE engineer SET age = 80 WHERE id=1;
UPDATE engineer SET age = 50 WHERE id=2;
UPDATE engineer SET age = 90 WHERE id=3;
Next, verify the table data using the following command:
SELECT * FROM engineer;
You should see the following output:
1|Ram|Junagadh|528|80
2|Vikash|Surat|734|50
3|Raj|London|1200|90
To display the table data in a table format, you will need to enable the headers in SQLite. You can enable it using the following command:
.headers ON
.mode column
You can now see the table data in the table format using the following command:
SELECT * FROM engineer;
You should see the following output:
id name persontype length age
-- ------ ---------- ------ ---
1 Ram Junagadh 528 80
2 Vikash Surat 734 50
3 Raj London 1200 90
To exit from the SQLite shell, run the following command:
.quit
Backup and Restore SQLite Database
To backup an SQLite database to a text file, run the following command:
sqlite3 employee.db .dump > employee.dump.sql
If you want to create a backup in sqlite format, run the following command:
sqlite3 employee.db ".backup employee.backup.sqlite"
To restore a backup from the text formated dump file, run the following command:
sqlite3 employee.db < employee.dump.sql
To restore a backup from the SQLite formatted dump file, run the following command:
sqlite3 employee.db ".restore employee.backup.sqlite"
Great! We have learned How to Install SQLite on Windows 10, 2016, 2019, 2022 (SQLite3 Command).
How to Install SQLite on Windows 10, 2016, 2019, 2022 (SQLite3) Conclusion
In this post, we explained how to install SQLite on a Windows server. We also explained how to interact with the SQLite shell to create and manage databases and tables in SQLite. I hope you can now easily use SQLite in the development environment.
