By Gareth Dwyer, Matt Abrams and Vinayak Baranwal The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program. SQLite is a free, serverless, cross-platform SQL database engine. For developers who need a lightweight, zero-configuration way to work with SQL, SQLite is the right starting point: you install the CLI, create a file, and run queries without setting up a database server. In this tutorial you will install SQLite on Ubuntu, create a database, define tables, insert and query data, update and delete rows, join tables, and back up your database. You will also see when to use SQLite versus server-based systems like MySQL or PostgreSQL, and how to use common SQLite dot-commands. This tutorial focuses on installing and using SQLite from the command line. For a broader comparison of relational databases, see SQLite vs MySQL vs PostgreSQL: A Comparison of Relational Database Management Systems. For using SQLite in code, see How To Use the sqlite3 Module in Python 3 and How To Use SQLite with Node.js on Ubuntu 22.04. The How To Install and Use SQLite collection groups related tutorials. Ubuntu 20.04 has reached the end of its standard support period and no longer receives security updates under standard Ubuntu support. Security updates are still available via Ubuntu Pro/ESM if you enable it. The commands in this tutorial are valid on Ubuntu 20.04, but for new deployments use a supported LTS release such as Ubuntu 22.04 or 24.04. See Ubuntu’s release cycle for current support status. Installing the SQLite command-line tool on Ubuntu uses the standard package manager. Updating the package list first ensures you get the current sqlite3 package from the Ubuntu repositories. Update the package list: Install the SQLite CLI. This command is valid across Ubuntu LTS releases (20.04, 22.04, 24.04, and later): Verify the installation by checking the version: Output will vary by installed version. Ubuntu LTS releases ship with a stable SQLite build; you can install a newer build from source if you need a specific version. With SQLite installed, you can create a new database. Creating a database in SQLite means opening or creating a single file. Running sqlite3 with a filename creates that file if it does not exist, or opens it if it does. You will use a database named sharks.db for the rest of the tutorial. From your terminal, run: If sharks.db does not exist, SQLite creates it when you first run a statement that touches the database. If it already exists, SQLite opens a connection to it. You should see something like: The version string will differ depending on which SQLite build your Ubuntu release provides. Your prompt will show the sqlite> prefix: When you start SQLite with sqlite3 sharks.db, SQLite opens the database and creates the sharks.db file on disk immediately if it does not already exist, even if you then exit with .quit without running any commands. However, until you execute a statement that modifies the database (for example, CREATE TABLE ... or INSERT ...), the file will not contain any schema or data. Before relying on this database file, run at least one write statement—such as the CREATE TABLE command in the next step—and then exit with .quit. You will create a table and add data next. To store data in SQLite, you define a table with CREATE TABLE, specifying each column’s name, type, and constraints. The type tells SQLite what kind of data to expect; the constraints enforce what is required. Using NOT NULL on a column means every row must supply a value for it: the database rejects any INSERT that omits that column. This tutorial uses a convention of uppercase SQL keywords and lowercase names. Every SQL statement must end with a semicolon. SQLite uses type affinity rather than strict types. Declaring a column as integer or text tells SQLite how to store and compare values, but SQLite will not reject a string stored in an integer column. For predictable behavior, store values that match the declared type. See the SQLite datatype documentation for full details. Create a table with columns for ID, name, shark type, and average length in centimeters: After a successful CREATE TABLE, the prompt returns with no message. Insert three rows: Because every column is NOT NULL, you must supply a value for each. Omitting one causes an error. For example: You have created a table and inserted data. Next you will read from it. Reading data is done with SELECT. Using * returns all columns; WHERE restricts rows. You will list all sharks and then the row where id is 1. List all rows in sharks: Return only the row whose id is 1. Using WHERE id = 1 limits the result to that row: To return specific columns instead of all columns, list them by name after SELECT. This is more efficient than SELECT * when you only need part of a row: Next you will change the table structure and update values. You can add columns with ALTER TABLE and change values with UPDATE. Adding an age column and then setting it per row keeps the schema and data in sync. Add a column for age in years: Set age for each shark by matching on id. Using WHERE id = n ensures only that row is updated: Next you will delete rows based on a condition. DELETE FROM removes rows that match a WHERE clause. You will delete all sharks whose age is 200 or less, then confirm with SELECT. Always include a WHERE clause with DELETE FROM. Running DELETE FROM sharks; without a condition removes every row in the table instantly, with no confirmation prompt. SQLite does not have a recycle bin or undo for data manipulation statements. That removes Alyoshka and Himari. Confirm with: Only Sammy remains. Next you will add a second table and join it with INNER JOIN. When related data lives in two tables (for example, sharks and their conservation status), you combine them with a join. INNER JOIN returns only rows where both tables have a matching key — in this case, only sharks that have a corresponding entry in the endangered table. If you wanted all sharks regardless of whether they have a status, you would use LEFT JOIN, which returns every row from the left table and NULL for columns from the right table where no match exists. Create an endangered table that references sharks by id and holds a status: Join the two tables on sharks.id = endangered.id: To avoid duplicate id columns in the result, list the columns you want: To see the difference, try the same join with LEFT JOIN. Because only Sammy has an entry in endangered, the other sharks would show NULL for status if they existed: Since you deleted Alyoshka and Himari in Step 6, only Sammy appears. In a table with all three sharks, LEFT JOIN would return all three rows, with NULL in the status column for any shark not in endangered. Next you will back up and export the database. You can back up a SQLite database in three main ways: the .backup dot-command (recommended for a full copy), .dump (SQL text you can replay or archive), or copying the .db file when nothing is writing to it. This step uses the same sharks.db you have been editing. First exit the SQLite prompt so you can run backup commands from the shell. Type: From the shell, run SQLite and attach the current database, then run .backup to write a copy to a new file. This preserves the database format and is the preferred way to clone a database: List the file to confirm: Expected output (size and date will vary): Dumping produces a text file of SQL statements that recreate the schema and data. Use it for version control, migration, or importing into another SQL database: Inspect the start of the dump: Expected output (excerpt): You can copy the .db file with cp when no other process has the database open for writing. If an application or another sqlite3 session is writing, copy during a quiet period or use .backup from a read-only connection to avoid corruption. You now have three ways to back up or export your database. The next section explains when to choose SQLite over server-based databases. SQLite is a single-file, serverless SQL engine. It fits cases where you want embedded or local data, minimal setup, or a single writer. It is not a drop-in replacement for a shared, multi-writer server database. SQLite is not suitable for high-concurrency write workloads. Many simultaneous writers will contend for the same file and throughput will suffer. For read-heavy concurrent access, enabling WAL mode (Write-Ahead Logging) can improve read concurrency, but it does not turn SQLite into a multi-writer server database. For a deeper comparison, see SQLite vs MySQL vs PostgreSQL: A Comparison of Relational Database Management Systems. Dot-commands are SQLite CLI helpers. They start with a . and do not end with a semicolon. Run them at the sqlite> prompt. Example: turn on headers and column mode, then run a query: Column widths in .mode column are sized to fit the data in each column. Your output may be spaced differently depending on the values in your table. What is SQLite used for? SQLite is used as an embedded or file-based SQL database. Typical uses include local and edge applications, development and testing databases, mobile and desktop apps, CLI tools, and read-heavy caches or config stores where a single file and no server are desirable. How do I install SQLite on Ubuntu? Run sudo apt update, then sudo apt install sqlite3. Verify with sqlite3 --version. The same steps work across Ubuntu LTS releases. Does SQLite require a server? No. SQLite is serverless. The engine runs inside your process or the sqlite3 CLI; the database is a single file on disk. There is no separate database server to install or configure. Where are SQLite databases stored? A SQLite database is stored wherever you create it. When you run sqlite3 path/to/file.db, that path is the storage location. There is no default data directory; you choose the path and filename. Is SQLite suitable for production use? Yes, for the right workload. SQLite is widely used in production for embedded systems, edge devices, single-user or low-concurrency apps, and read-heavy use cases. It is not suitable for high-concurrency, multi-writer server workloads; for those, use MySQL, PostgreSQL, or another server RDBMS. How do I create a database in SQLite? Run sqlite3 filename.db. If the file does not exist, SQLite creates it when you run at least one SQL statement or dot-command. Then use CREATE TABLE and INSERT to define and populate tables. What is the difference between SQLite and MySQL? SQLite is serverless and file-based: one file per database, no separate server. MySQL is a client/server RDBMS: you run a server process and clients connect over the network. SQLite fits embedded and single-writer use; MySQL fits multi-user, multi-writer web and app back ends. See SQLite vs MySQL vs PostgreSQL for a full comparison. You have installed SQLite on Ubuntu, created a database and tables, inserted and queried data, updated and deleted rows, joined two tables with INNER JOIN, and backed up the database with .backup, .dump, and file copy. You also saw when SQLite is a good fit and how it compares to MySQL and PostgreSQL. Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases. Learn more about our products I'm a software engineer, writer, and mentor. On a mission to make high quality technical educational materials free for everyone. https://ritza.co Supporting the open-source community one tutorial at a time. Former Technical Editor at DigitalOcean. Expertise in topics including Ubuntu 22.04, Ubuntu 20.04, CentOS, and more. Building future-ready infrastructure with Linux, Cloud, and DevOps. Full Stack Developer & System Administrator. Technical Writer @ DigitalOcean | GitHub Contributor | Passionate about Docker, PostgreSQL, and Open Source | Exploring NLP & AI-TensorFlow | Nailed over 50+ deployments across production environments. This textbox defaults to using Markdown to format your answer. You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link! INSERT INTO sharks VALUES (4, “Faiza”, “Hammerhead Shark”); This command is throwing an error. After getting sqlite installed on a droplet can this database then be accessed from an App ? Nice and easy to understand, thank you! Please complete your information! Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation. Full documentation for every DigitalOcean product. The Wave has everything you need to know about building a business, from raising funding to marketing your product. Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter. New accounts only. By submitting your email you agree to our Privacy Policy Scale up as you grow — whether you're running one virtual machine or ten thousand. Sign up and get $200 in credit for your first 60 days with DigitalOcean.* *This promotional offer applies to new accounts only.