How To Install SQLite3 On Mac

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 on macOS.

1. Install SQLite3 On Mac.

1. Download the SQLite zip file from SQLite’s official website.
2. Then unzip the download file into a local folder such as /Users/zhaosong/Documents/WorkSpace/tool/sqlite-tools.
3. Now the SQLite server has been installed, it does not need any installation.
4. For macOS X 10.4 or higher version, SQLite3 has been installed on macOS by default. You can run the command which sqlite3 to show the SQLite binary file path in the system PATH environment variable value.

   $ which sqlite3
   /Users/songzhao/opt/anaconda3/bin/sqlite3

5. If the output sqlite3 binary path is not which you want, you can go to the macOS user home directory then edit .bash_profile file 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

6. After you save the above changes in .bash_profile file, run $ source .bash_profile to activate it.
7. You can also run the command $ brew install sqlite3 to install SQLite3 on macOS, and run the command $ brew upgrade sqlite3 to upgrade SQLite3.

2. Start & Stop ( Quit / Exit ) SQLite Server.

1. Open a terminal and CD into the SQLite file unzipped folder.
2. Execute sqlite3 in the command line, when you see the 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 ; + Enter key 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>

3. If you want to stop ( quit ) SQLite3 database in the command line console, you can first type ; to terminate the last statement input, then type .quit / .exit + Enter key. When you quit the SQLite3 database, the SQLite3 process is terminated also, which 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$

4. Now click Finder —> Go —> Applications menu item on the macOS top menu bar to open the macOS /Applications folder.
5. Then click Utilities —> Activity Monitor to open the activity monitor. You can find the sqlite3 process in the Activity Monitor ( All Processes ) window.

3. Install SQLite Studio.

1. SQLite saves 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 .db files.
2. SQLite Studio is just a visual tool that can help you to manage SQLite databases, such as creating tables, views, etc.
3. Go to the SQLite Studio download page to download the latest version.
4. Open the downloaded zip file and click the InstallSQLiteStudio-3.2.1.app icon to install it.
5. But because the installer is not downloaded from App Store, so you may encounter errors that said can not open it.
6. Click the Mac logo( on the top left corner of macOS desktop ) —> System Preferences… menu item to open the System Preferences window.
7. Click the Security & Privacy icon to open the Security & Privacy window, then click the Open Anyway button to open the SQLite Studio installer.
8. If you can not find the Open Anyway button, that is because you may have already click the button before. If you install SQLite Studio for the first time, the Open Anyway button will be shown.
9. After the installation process, you can find the SQLiteStudio icon in the Finder —> Go —-> Applications folder.

4. Use SQLite Studio To Manage SQLite Server Database.

4.1 Create SQLite Database File.

1. Click MacOS Applications —> SQLite Studio app icon to open it.
2. Click Database —> Add a database menu item on the top menu bar to create a new SQLite database, it will popup the Database window.
3. Because SQLite uses one binary file to store one database data, so click the green plus icon ( you can find it after the File text box) in the popup window to add a new SQLite database file.
4. To manage the existing database file, just click the open folder button to browse the existing database file.
5. Click the Test connection button to test whether the database can be connected or not.
6. If all tests passed, just click the OK button to save the database file.

4.2 Create Table Use SQLite Studio.

1. In the SQLiteStudio manager window, right-click the SQLite database just created, and click Create a table menu item in the popup menu list.
2. Input the table name in the text box. And click the Commit structure changes icon ( green checkbox button at the top bar ) to save the changes.
3. Click the Add column (Ins) icon button to add a table column.
4. Input the column name and select the column data type. Check the checkbox Primary Key, Unique, or Not NULL if you need.
5. 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.

5. Question & Answer.

5.1 How To Fix Failed To Build Gem Native Extension Error When Install SQLite3 On macOS.

5.1.1 Question1.

1. When I install SQLite3 on my macOS (OS X Mojave ), I meet the below 2 errors, ERROR: Error installing sqlite3-ruby: ERROR: Failed to build gem native extension.
2. This error wastes me a lot of time, below is the detailed message for this error, how to fix it. Thanks.

   Building native extensions.  This could take a while... 
   ERROR:  Error installing sqlite3-ruby:
   ERROR: Failed to build gem native extension.
   
   /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/bin/ruby
   extconf.rb mkmf.rb can't find header files for ruby at
   /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/ruby.h

5.1.2 Answer1.

1. The reason may be you do not install XCode version 4 or higher. I meet the same error, I find my XCode version is version 3, after I upgrade to XCode version 4, the error is fixed.
2. But I think for most macOS versions, the SQLite3 is installed by default, then you can run the command brew install sqlite3 to install the latest version, or you can run the command brew upgrade sqlite3 to upgrade your sqlite3 to the latest version.
3. But you also need to export the new version SQLite binary folder in your macOS PATH environment variable to make the macOS use the new SQLite version, otherwise, you may find it still uses the old version when you run the command sqlite3.
4. Run the command cd ~ in the terminal to go to your home directory, then edit the .bash_profile file, add the below code in it.

   export PATH="/< new-sqlite-install-folder >/bin:$PATH"

5. Then run the command $ source .bash_profile in terminal to enable your changes.