Home / Java & Spring Boot / Adding a Real Database with SQLite

Lesson 4 of 6

Adding a Real Database with SQLite

Estimated time: 2.5–3 hours

What You Will Learn

  • Understand what a database is and why your application needs one to store data permanently
  • Install SQLite on your computer and use it from the command line
  • Learn basic SQL commands like SELECT and INSERT to create and read data
  • Add SQLite support to your Spring Boot project using Maven dependencies
  • Convert your ContactMessage model class into a JPA entity that maps to a database table
  • Use a Spring Data repository to save and retrieve data without writing SQL by hand
  • Verify that your data survives server restarts — proving your database actually works

In the previous lesson, you built a contact form API that could receive messages and send them back as a list. It worked great — until you stopped the server. Every time you restarted your Spring Boot application, all of your messages vanished into thin air. That is because we were storing everything in an ArrayList inside your computer's memory. Memory is temporary. When a program stops running, its memory is wiped clean.

In this lesson, you are going to solve that problem for good. You will add a real database to your Spring Boot application so that every contact message is saved permanently to a file on your hard drive. Stop the server, restart your computer, come back a week later — your data will still be there. This is one of the most important skills in software development, and by the end of this lesson, you will have a fully functional full-stack application.

1. What is a Database?

A database is a program that is specifically designed to store, organize, and retrieve data. You can think of it as a super-powered spreadsheet. Just like a spreadsheet has rows and columns, a database stores data in tables with rows and columns. But unlike a regular spreadsheet, a database can handle millions of rows of data without slowing down, allow multiple users to read and write data at the same time, and keep your data safe even if the power goes out.

Let us break down the key vocabulary. In database language, a table is like a single spreadsheet. It holds one type of data. For example, you might have a table called contact_messages that stores all the messages from your contact form. Each row in the table is a single record — one contact message from one person. Each column is a field — a specific piece of information like the person's name, their email address, or their message text.

Here is what your contact messages would look like as a database table:

id name email message
1 Alice Johnson alice@example.com Love the website! Great work.
2 Bob Smith bob@example.com How do I sign up for classes?
3 Carol Lee carol@example.com Can I volunteer as a mentor?

Notice that first column: id. Every row in a database table needs a unique identifier — a number that distinguishes it from every other row. This is called the primary key. It is like a student ID number or a Social Security number. No two rows can have the same id. The database assigns these numbers automatically, starting at 1 and counting up.

Now think back to what happened in Lesson 13. You stored your contact messages in an ArrayList inside your Java code. That list lived in your computer's RAM (random access memory). RAM is incredibly fast, but it is volatile — meaning it only lasts as long as the program is running. The moment you stop your Spring Boot server, the Java process ends, its memory is released, and your list of messages disappears completely.

This is not a bug. This is simply how memory works. It is temporary by design. Your web browser, your music player, your text editor — they all lose their in-memory data when they close. The reason your documents and music files survive is that those programs save data to your hard drive (or solid-state drive). Data on a drive is persistent — it stays there even when the power is off.

A database writes your data to files on your hard drive. When you save a contact message, the database writes it to a file on disk immediately. When your Spring Boot server restarts and asks "what messages do we have?", the database reads from that file and gives back all the messages. Nothing is lost. This is why virtually every real application in the world — from a small blog to Instagram — uses a database.

You might be wondering: why not just write to a plain text file yourself? You could, but databases give you much more than simple file storage. They let you search through millions of records in milliseconds, they prevent data corruption if two users write at the same time, they let you define relationships between different tables, and they provide a powerful query language called SQL to ask complex questions about your data. Writing all of that yourself would take months. A database gives it to you for free.

The language you use to communicate with a database is called SQL, which stands for Structured Query Language. You will learn the basics of SQL in this lesson, but here is the exciting part: Spring Boot can actually generate most of the SQL for you automatically. You will write Java code, and Spring will translate it into database operations behind the scenes.

2. Why SQLite?

There are many databases in the world — PostgreSQL, MySQL, Oracle, MongoDB, and dozens more. Each has its strengths. For this lesson, we are going to use SQLite, and there is a very good reason for that: it is the simplest database that exists.

Most databases (like PostgreSQL or MySQL) run as a separate program on your computer. You have to install a database server, start it up, configure usernames and passwords, create a database through a special admin tool, and then connect your application to it. That is a lot of setup before you can even store your first piece of data. SQLite is completely different. It stores your entire database in a single file on your computer. There is no separate server to install or manage. Your Spring Boot application talks directly to the file. That is it.

Despite its simplicity, SQLite is one of the most widely used databases on the planet. It is built into every Android phone, every iPhone, every Mac, every copy of Chrome, Firefox, and Safari, and hundreds of thousands of other applications. When you search your text message history on your phone, you are querying a SQLite database. When your web browser remembers your bookmarks and history, that data is in a SQLite database. It has been estimated that there are over one trillion SQLite databases in active use worldwide.

SQLite is perfect for learning because you can see and touch your database. It is just a file. You can copy it, email it to a friend, back it up by dragging it to another folder. There is no mystery about where your data lives. And because SQLite uses standard SQL, everything you learn here — the SQL commands, the concepts of tables and rows and primary keys, the way JPA maps Java objects to database records — transfers directly to PostgreSQL, MySQL, or any other database you might use in the future.

Think of SQLite as training wheels that happen to be professional grade. You are learning real database skills with the easiest possible setup. When you are ready for a bigger project with many users, you can switch to PostgreSQL by changing a few lines of configuration. Your Java code stays exactly the same.

Let us get it installed on your computer.

3. Installing SQLite

Before we add SQLite to our Spring Boot project, let us install the SQLite command-line tool on your computer. This tool lets you create databases, run SQL commands, and inspect your data directly from the terminal. It is not strictly required for your Spring Boot app to work (the Java library handles that), but it is incredibly useful for debugging and for learning SQL.

First, check if SQLite is already installed. Open your terminal (Command Prompt on Windows, Terminal on Mac or Linux) and type:

sqlite3 --version

If you see a version number (like 3.43.2 or similar), you already have it and can skip ahead to the testing section below. If you see an error like "command not found" or "'sqlite3' is not recognized", you need to install it. Choose your operating system below:

winget install SQLite.SQLite --accept-package-agreements --accept-source-agreements
brew install sqlite
sudo apt install sqlite3

After the installation finishes, close your terminal completely and open a new one. This is important because your terminal needs to reload its list of available programs. Then verify the installation by running the version check again:

sqlite3 --version

You should now see a version number. If you do, SQLite is ready to go. If you are on Windows and still see an error, you may need to restart your computer so the system path updates.

Quick Test: Your First Database

Let us take SQLite for a spin and create a tiny test database right from your terminal. This will give you a feel for how databases work before we wire one into Spring Boot. Type the following command to create a new database file called test.db:

sqlite3 test.db

You should see a prompt that looks something like sqlite>. You are now inside the SQLite shell, talking directly to your database. Let us create a table, insert some data, and read it back. Type each of the following commands one at a time, pressing Enter after each:

CREATE TABLE greetings (id INTEGER PRIMARY KEY, message TEXT);

INSERT INTO greetings (message) VALUES ('Hello from SQLite!');

INSERT INTO greetings (message) VALUES ('Databases are cool!');

SELECT * FROM greetings;

After running the SELECT command, you should see output like this:

1|Hello from SQLite!
2|Databases are cool!

Look at that! You just created a database table, inserted two rows of data, and queried them back. Let us break down what each command did. CREATE TABLE made a new table called greetings with two columns: id (a number that auto-increments) and message (text). INSERT INTO added new rows. Notice we did not specify an id — SQLite assigned 1 and 2 automatically. SELECT * FROM greetings means "give me all columns from the greetings table" — the asterisk * means "everything."

To exit the SQLite shell, type:

.quit

If you look in your current folder, you will find a file called test.db. That file is your database. All the data you just created lives inside it. You can delete it if you like — it was just for practice. The real database we care about is the one we are about to create for our Spring Boot application.

4. Adding SQLite to Spring Boot

Now for the main event. We are going to connect our Spring Boot project to a SQLite database. This requires three things: adding the right dependencies to our project, configuring the database connection, and then updating our Java code to use JPA (Java Persistence API) instead of an in-memory list. Let us start with the dependencies.

Open your pom.xml file. This is the file that tells Maven (our build tool) which libraries our project needs. You already have some dependencies in there like spring-boot-starter-web. We need to add three more:

Add these three dependencies inside the <dependencies> section of your pom.xml, right next to your existing dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.45.1.0</version>
</dependency>

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-community-dialects</artifactId>
</dependency>

After saving pom.xml, your IDE (like IntelliJ or VS Code) should automatically download these libraries. If it does not, you can run ./mvnw dependency:resolve (or mvnw.cmd dependency:resolve on Windows) from your terminal inside the project folder to force the download.

Next, we need to tell Spring Boot where our database is and how to connect to it. Open the file src/main/resources/application.properties. This file holds configuration settings for your application. Add the following four lines:

# Database configuration
spring.datasource.url=jdbc:sqlite:contacts.db
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect
spring.jpa.hibernate.ddl-auto=update

Let us go through each line so you understand exactly what it does:

That last property — ddl-auto=update — is incredibly convenient for development. It means you can focus on writing Java code and let Spring handle the database structure. In a production application, you would use a more controlled approach called "database migrations," but for learning and development, update is perfect.

Save the file. Our Spring Boot project now knows about SQLite, but we have not told it what data to store yet. That is the next step: converting our Java model into something the database can understand.

5. Converting Your Model to a JPA Entity

In Lesson 13, you created a ContactMessage class with fields like name, email, and message. That class was a plain Java object — it held data in memory but had no connection to a database. Now we are going to add special annotations that tell Spring: "This class represents a table in the database. Each instance of this class is a row in that table."

In JPA terminology, a class that maps to a database table is called an entity. The word "entity" just means "a thing that exists" — in this case, a thing that exists in your database. You turn a regular Java class into an entity by adding a few annotations. Annotations in Java are those special keywords that start with @. They do not change what your code does directly — instead, they give instructions to frameworks like Spring about how to treat your class.

Here is the updated ContactMessage.java class. Pay attention to the new annotations at the top:

package com.example.demo.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "contact_messages")
public class ContactMessage {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String email;
    private String message;

    // JPA requires a no-argument constructor
    public ContactMessage() {
    }

    public ContactMessage(String name, String email, String message) {
        this.name = name;
        this.email = email;
        this.message = message;
    }

    // Getters
    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }

    public String getMessage() {
        return message;
    }

    // Setters
    public void setId(Long id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public void setMessage(String message) {
        this.message = message;
    }
}

Let us walk through each new piece:

Notice that we also added an empty constructor: public ContactMessage() {}. This is called a no-argument constructor (or "no-args constructor"), and JPA requires it. When JPA reads data from the database and creates Java objects from it, it needs to be able to create a blank ContactMessage first and then fill in the fields. Without this empty constructor, JPA would not know how to create the object. You can keep the other constructor too — it is still useful when you create objects in your own code.

We also changed the type of id from int to Long. Long is Java's way of storing large whole numbers, and it is the standard type for database primary keys. Using Long (with a capital L) instead of long (lowercase) means the value can be null, which is important because when you create a new ContactMessage that has not been saved yet, its id has not been assigned.

The other fields — name, email, and message — do not need any special annotations. JPA automatically maps them to columns in the table with the same names. A String field becomes a TEXT column, a Long becomes an INTEGER, and so on. JPA handles the translation between Java types and database types for you.

Creating the Repository

Now for the truly magical part of Spring Data JPA. We need a way to save entities to the database and read them back. In traditional Java programming, you would have to write a class with methods that open a database connection, construct SQL strings, execute queries, map the results back to Java objects, handle errors, and close the connection. That could easily be 50 to 100 lines of code for even basic operations.

With Spring Data JPA, you need one line of code. Seriously. Create a new file called ContactMessageRepository.java in your repository package:

package com.example.demo.repository;

import com.example.demo.model.ContactMessage;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ContactMessageRepository extends JpaRepository<ContactMessage, Long> {
}

That is it. That is the entire file. No methods, no SQL, no database connection code. Just an empty interface that extends JpaRepository.

So how does this work? The answer is that Spring is incredibly clever. When your application starts, Spring sees this interface and says: "This extends JpaRepository for the ContactMessage entity with a Long primary key. I will automatically create a class that implements this interface with all the standard database operations." Spring generates a complete implementation behind the scenes that includes methods like:

You get all of these methods for free, without writing a single line of implementation code. This is one of the reasons Spring Boot is so popular — it eliminates an enormous amount of tedious, repetitive code. The <ContactMessage, Long> part in angle brackets tells Spring which entity this repository manages and what type its primary key is. Spring uses this information to generate the correct SQL statements.

6. Updating the Controller

Now that we have a JPA entity and a repository, we need to update our controller to use them. In Lesson 13, our ContactController used an ArrayList to store messages. We are going to replace that list with our new ContactMessageRepository.

Before we look at the code, we need to understand a very important concept called dependency injection. This sounds intimidating, but the idea is simple. Instead of our controller creating its own repository (like buying your own tools), Spring creates the repository and hands it to the controller (like the company providing tools for you). The controller says "I need a repository to do my job," and Spring says "Here you go, I made one for you."

In practice, this works through the constructor. When you add a ContactMessageRepository parameter to your controller's constructor, Spring sees that and automatically passes in the repository it created. You do not have to create the repository yourself or manage its lifecycle. Spring handles all of that.

Here is the updated ContactController.java:

package com.example.demo.controller;

import com.example.demo.model.ContactMessage;
import com.example.demo.repository.ContactMessageRepository;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/api/contact")
public class ContactController {

    private final ContactMessageRepository repository;

    public ContactController(ContactMessageRepository repository) {
        this.repository = repository;
    }

    @PostMapping
    public ContactMessage submitMessage(@RequestBody ContactMessage message) {
        return repository.save(message);
    }

    @GetMapping
    public List<ContactMessage> getAllMessages() {
        return repository.findAll();
    }
}

Compare this to the Lesson 13 version and notice what changed:

The keyword final on the repository field means the field can only be set once (in the constructor) and never changed after that. This is a good practice because you never want to accidentally replace the repository with something else during the controller's lifetime.

Notice how much cleaner this code is compared to the Lesson 13 version. And it does more — it saves data permanently instead of just keeping it in memory. This is the power of Spring Boot and JPA. The framework does the heavy lifting so you can focus on your application's logic.

Also notice that the save method returns the saved ContactMessage. This is useful because after saving, the entity now has its id field populated by the database. So when the API response goes back to the browser, it includes the id. The client can see exactly what was saved and can use the id to reference that specific message later.

Take a moment to let this sink in. You wrote an empty interface (the repository), added a single field and constructor parameter to your controller, and replaced two method calls. That is all it took to go from volatile in-memory storage to permanent database persistence. This is not a toy example — this is how real Spring Boot applications are built.

7. The Moment of Truth

Everything is in place. Let us test whether our data actually survives a server restart. This is the moment where you prove to yourself that your database is working. Start your Spring Boot application the way you normally do:

./mvnw spring-boot:run

Watch the console output carefully. You should see some new log messages from Hibernate about creating or updating the database table. Look for lines mentioning contact_messages or ddl. If you see errors, double-check your pom.xml dependencies and application.properties settings — a small typo can cause failures.

Once the server is running, open a new terminal window and submit a few contact messages using curl. Each command sends a POST request with JSON data to your API:

curl -X POST http://localhost:8080/api/contact \
  -H "Content-Type: application/json" \
  -d '{"name": "Alice", "email": "alice@example.com", "message": "Hello from the database!"}'

curl -X POST http://localhost:8080/api/contact \
  -H "Content-Type: application/json" \
  -d '{"name": "Bob", "email": "bob@example.com", "message": "SQLite is awesome!"}'

curl -X POST http://localhost:8080/api/contact \
  -H "Content-Type: application/json" \
  -d '{"name": "Carol", "email": "carol@example.com", "message": "My data will survive a restart!"}'

Each response should now include an "id" field that was not there before — this is the auto-generated primary key from the database. Verify all three messages are stored by fetching them:

curl http://localhost:8080/api/contact

You should see a JSON array with all three messages, each with an id. So far this looks the same as Lesson 13. Now here is the real test. Stop the server. Go to the terminal where Spring Boot is running and press Ctrl + C to shut it down. The server process ends. In Lesson 13, this would have erased all your data. Now, start the server again:

./mvnw spring-boot:run

Once it is running, fetch the messages again:

curl http://localhost:8080/api/contact

Your messages are still there. Alice, Bob, and Carol survived the restart. The data was written to the contacts.db file and read back when the server started again. This is the entire point of using a database — your data is permanent.

You can also verify this directly with the SQLite command-line tool. Without even running your Spring Boot server, open a terminal, navigate to your project folder, and run:

sqlite3 contacts.db "SELECT * FROM contact_messages;"

This command opens the contacts.db file and runs a SQL query against it. You will see your three messages printed out directly from the database file. This proves that the data lives in that file, independent of your Spring Boot application. Even if you deleted your entire Java project, the contacts.db file would still contain the data.

Take a moment to appreciate what you have accomplished. You started with a simple contact form in Lesson 13 that lost its data every time the server restarted. Now you have a fully persistent application backed by a real database. This is a fundamental milestone in your journey as a software developer.

8. What Just Happened (The Big Picture)

Let us step back and look at the complete architecture of what you have built. When a user fills out the contact form on your website and clicks "Submit," here is the full journey that data takes:

  1. The browser sends an HTTP POST request containing the form data as JSON. The request travels over the network to your Spring Boot server running on localhost:8080.
  2. Spring Boot receives the request and routes it to your ContactController based on the URL path (/api/contact) and HTTP method (POST).
  3. The controller processes the request. Spring automatically converts the JSON body into a ContactMessage Java object using Jackson (the JSON library). The controller then passes this object to the repository.
  4. The repository saves the data to SQLite. Spring Data JPA generates an INSERT INTO contact_messages SQL statement and executes it. The SQLite JDBC driver writes the data to the contacts.db file on your hard drive.
  5. The response is sent back to the browser. The saved entity (now with its auto-generated id) is converted back to JSON and sent as the HTTP response. The browser receives confirmation that the message was saved.

When someone visits the page and the browser sends a GET request to fetch all messages, the journey is similar but in reverse: the controller asks the repository for all messages, the repository runs a SELECT * FROM contact_messages query, SQLite reads from the file, the results are mapped to Java objects, converted to JSON, and sent to the browser.

Think about the layers of your application. You have a frontend (HTML, CSS, and JavaScript in the browser), a backend API (Spring Boot handling HTTP requests), and a database (SQLite storing data to disk). This three-layer architecture — often called a "three-tier architecture" — is the foundation of virtually every web application in the world. Facebook, Twitter, Amazon, your bank's website — they all follow this same basic pattern, just at a much larger scale.

You have built a real full-stack application. Not a toy, not a simulation — a real application that accepts data from users over HTTP, stores it permanently in a database, and serves it back on demand. The technologies you used (Spring Boot, JPA, SQLite) are used by professional developers every day. The patterns you learned (entities, repositories, dependency injection) are industry-standard practices. You should feel genuinely proud of reaching this milestone.

Test Your Knowledge

1. What makes SQLite different from databases like PostgreSQL or MySQL?

Correct! SQLite stores the entire database in a single file on your computer. Unlike PostgreSQL or MySQL, which run as separate server processes that you must install and manage, SQLite requires no server at all. Your application reads and writes directly to the database file. Despite this simplicity, SQLite uses standard SQL and is one of the most widely deployed databases in the world.

2. What does the @Entity annotation tell Spring about a Java class?

That's right! The @Entity annotation tells JPA (Java Persistence API) that this class represents a database table. Each instance of the class corresponds to a row in the table, and each field corresponds to a column. Spring and Hibernate use this annotation to know which classes should be mapped to the database and how to generate the appropriate SQL statements.

3. How many lines of code do you need to write inside a Spring Data JPA repository to get basic save, find, and delete operations?

Correct! Spring Data JPA is remarkably powerful. You only need to declare an interface that extends JpaRepository with your entity type and primary key type. Spring automatically generates a full implementation with methods like save(), findAll(), findById(), deleteById(), and more. You write zero lines of implementation code — Spring creates it all for you at application startup.

4. What does the property spring.jpa.hibernate.ddl-auto=update do?

That's right! When ddl-auto is set to update, Hibernate examines your @Entity classes each time the application starts and compares them to the actual database tables. If a table does not exist, Hibernate creates it. If you have added new fields to your entity, Hibernate adds the corresponding columns. This makes development very convenient because your database schema stays in sync with your Java code automatically.

Excellent work completing this lesson! You have crossed a major threshold in your development journey. Your application now has a real database, which means it behaves like a real production application. In the next lesson, you will learn how to add validation to protect your data and implement full CRUD (Create, Read, Update, Delete) operations to give users complete control over their data.

Finished this lesson?

← Previous: Building the Contact Form API Next: Validation & CRUD →