SQLite is a popular embedded relational database. It can be used on both mobile devices and personal computers. This article will tell you how to install and manage it in Mac.
1. Install SQLite3 On Mac.
- Download the SQLite zip file from SQLite official website.
- Then unzip the download file into a local folder such as /Users/zhaosong/Documents/WorkSpace/tool/sqlite-tools.
- Now the SQLite server has been installed, it does not need any installation.
- For macOS X 10.4 or higher version, SQLite3 has been installed on macOS by default. You can run the command
which sqlite3to show the SQLite binary file path in the system PATH environment variable value.
$ which sqlite3 /Users/songzhao/opt/anaconda3/bin/sqlite3
- If the output sqlite3 binary path is not which you want, you can go to the macOS user home directory then edit
.bash_profilefile to add and export your SQLite3 binary file path at the beginning of the system environment variable PATH value like below.
$ pwd /Users/songzhao $ vim .bash_profile # add below line at the end of your .bash_profile file. export PATH=your-sqlite3-bin-path:$PATH
After you save above changes in
$ source .bash_profileto activate it.
- You can also run the command
$ brew install sqlite3to install SQLite3 on macOS, and run the command
$ brew upgrade sqlite3to upgrade SQLite3.
2. Start & Stop ( Quit / Exit ) SQLite Server.
- Open a terminal and CD to the SQLite file unzip folder.
sqlite3in command line, when you see below messages, the SQLite server has been started. You can interact with SQLite3 in the command line console, after you input one statement, you need to type a
; + Enterkey to terminate the current input.
192:sqlite-tools zhaosong$ sqlite3 SQLite version 3.23.1 2021-01-10 17:39:29 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite>
- If you want to stop ( quit ) SQLite3 database in command line console, you can first type
;to terminate the last statement input, then type
.exit+ Enter key. When you quit the SQLite3 database, the SQLite3 process is terminated also that means the SQLite server is stopped.
$ sqlite3 SQLite version 3.30.0 2019-10-04 15:03:17 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> cd ...> .exit ...> .quit ...> ; sqlite> .quit ( .exit ) (base) songs-MacBook-Pro:~ songzhao$
- Now click Finder —> Go —> Applications to open MacOS application folder. And click Utilities —> Activity Monitor to open the activity monitor. You can find the sqlite3 process in this window.
3. Install SQLite Studio.
SQLite save all data of one database in a
.db file. It is a binary file. So if you want to create more databases, you need to create more
SQLite Studio is just a visual tool that can help you to manage SQLite database, includes create table, view, etc.
- Go to SQLite Studio download page to download the latest version.
- Open the downloaded zip file and click the InstallSQLiteStudio-3.2.1.app icon to install it.
- But because the installer is not downloaded from App Store, so you may encounter errors which said can not open it.
- Click System Preferences —> Security & Privacy then click Open Anyway button to open the SQLite Studio installer. Below picture does not contain the button, because I have already click the button before. If you install SQLite Studio for the first time, the Open Anyway button will be shown.
- After the installation process, you can find the SQLiteStudio icon in Finder —> Go —-> Applications folder.
4. Use SQLite Studio To Manage SQLite Server Database.
4.1 Create SQLite Database File.
- Click MacOS Applications —> SQLite Studio app icon to open it.
- Click Database —> Add a database menu item to create a new SQLite database.
- Because SQLite uses one binary file to store one database data, so click the green plus icon in the popup window to add a new SQLite database file. To manage existing database file, just click the open folder button to browse the existing database file.
- Click Test connection button to test whether the database can be connected or not.
- If all tests passed, just click OK button to save the database file.
4.2 Create Table Use SQLite Studio.
- Right-click the SQLite database just created, and click Create a table menu item in the popup menu list.
- Input table name in the text box. And click Commit structure changes icon ( green checkbox button at the top bar ) to save the changes.
- Click the Add column (Ins) icon button to add a table column.
- Input the column name and select the column data type. Check whether the column is Primary Key, Unique, or Not NULL checkbox.
- If you want the table id column value auto-increment, you can click the Configure button after the Primary Key and check Autoincrement checkbox, click Apply button to save.