Next.js SQLite: Using SQLite in Next.js Projects

Welcome to the world of using SQLite with Next.js! By the time you finish reading this guide, you’ll have a solid grasp of setting up and integrating an SQLite database into your Next.js applications.

Whether you’re building a simple side project, an MVP, or a full-blown web app, having the ability to store and retrieve data is crucial. SQLite is a highly portable, zero-configuration database that will allow you to get up and running quickly. The steps we cover make it a breeze to connect your Next.js frontend to a SQLite backend.

So without further ado, let’s dive in!

Why Use SQLite in Next.js?

Next.js is a popular React framework for building server-rendered apps that scale. It handles routing, server-side rendering, code splitting automatically for you.

SQLite is a self-contained, file-based SQL database engine. It’s lightweight, fast, and requires zero configuration.

Benefits of using Next.js with SQLite:

• Simple setup – SQLite databases are just simple file stores. No need to run complex database servers.
• Client-side querying – Execute queries directly from your Next.js application without needing a backend server.
• Offline persistence – Data remains even when the user goes offline temporarily.
• Portability – The database file can be easily shared across environments and platforms.
• Scalability – SQLite handles most simple querying needs. And you can switch to heavier databases later.

For rapid prototyping, SQLite enables you to bypass setting up a separate database server just to test core functionality. As your web app evolves, you always have the flexibility to migrate to Postgres, MySQL or other DBs.

Now let’s jump into some code!

Installing Dependencies

To use SQLite in Next, we’ll need to install a few key dependencies:

npm install sqlite3
npm install @mapbox/node-sqlite3
npm install sqlite

This gives us everything we need on the server-side and client-side to work with an SQLite database.

Creating the Database

First, we need an actual database file. Inside pages/api, create a db.js file with the following:

// pages/api/db.js

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

// Open SQLite database connection
export async function openDb() {
  return open({
    filename: './mydb.db',
    driver: sqlite3.Database
  })  
}

This creates a connection to a SQLite database named mydb.db in the root folder of our project.

If the file does not exist, SQLite will automatically create it when opening a connection.

Note: This openDb() method provides access to the database connection from anywhere in our app.

Defining Tables

Before working with data, we need to define tables to store it.

Under pages/api, create a seed.js file responsible for setting up the database schema:

// pages/api/seed.js

import { openDb } from './db' 

async function setup() {
  // Open SQLite connection
  const db = await openDb()

  // Define table schema
  await db.exec(`
    CREATE TABLE posts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,  
      title TEXT,
      content TEXT  
    );
  `)

  // Insert dummy data
  await db.run(
    'INSERT INTO posts (title, content) VALUES (?, ?)',
    'Hello World', 
    'My first blog post!'
  )
  
  // Close connection
  await db.close()  
}

setup()
  .catch(err => {
    console.error(err.message)
  })  

This creates a posts table with some dummy seed data.

The table has id, title, and content fields. id auto-increments to give each record a unique identifier.

Run this with:

node pages/api/seed.js

Our SQLite database and table are now ready!

Fetching Data

In Next.js, server-side data fetching occurs in the getServerSideProps lifecycle method.

Under pages/index.js, let’s query the posts from the database and pass them to our UI component:

// pages/index.js

import { openDb } from './api/db'

export async function getServerSideProps() {

  // Open database
  const db = await openDb()

  // Get posts from database  
  const posts = await db.all('SELECT * FROM posts')

  // Pass posts as prop to component
  return {
    props: {
      posts
    }
  }
}

function Home({ posts }) {
  // Render UI with posts
}

export default Home

That’s it! Our UI can now render the posts fetched from SQLite.

Performing CRUD Operations

Let’s expose APIs for CRUD operations:

Create

To insert new records:

// pages/api/posts/create.js

import { openDb } from '../db'

async function handler(req, res) {

  // Get post data from request body  
  const { title, content } = req.body

  // Insert post into database
  const db = await openDb()
  const result = await db.run(
    'INSERT INTO posts (title, content) VALUES (?, ?)', 
    [title, content]
  )
  await db.close()
  
  // Return result to client
  res.status(201).json(result)
}

export default handler

Read

To get a specific record by id:

// pages/api/posts/[id].js 

import { openDb } from '../../db'

async function handler(req, res) {

  // Get post ID from request URL
  const id = req.query.id

  // Get matching post from database
  const db = await openDb()
  const post = await db.get('SELECT * FROM posts WHERE id = ?', [id])

  // Return result to client  
  res.status(200).json(post)

}

export default handler

Update

To modify existing records:

// pages/api/posts/update.js   

import { openDb } from '../db'

async function handler(req, res) {
  
  // Get ID and new title/content from body
  const { id, title, content } = req.body

  // Update matching record in database
  const db = await openDb()
  await db.run(
    `UPDATE posts SET 
     title = ?, 
     content = ?  
     WHERE id = ?`,
    [title, content, id]
  )
  await db.close()
  
  // Return result to client
  res.status(200).json({ message: 'Post updated' })

}

export default handler  

Delete

To remove records:

// pages/api/posts/delete.js

import { openDb } from '../db'

async function handler(req, res) {

  // Get ID from request body  
  const { id } = req.body

  // Delete matching record from database
  const db = await openDb()
  await db.run('DELETE FROM posts WHERE id = ?', [id])
  await db.close()

  // Return result to client
  res.status(200).json({ message: 'Post deleted' })

}

export default handler

And that’s the full CRUD workflow!

These API handlers allow Next.js to interface with the SQLite database for persistent storage.

Fetching Data on Client-Side

For client-side data fetching with SWR, React Query or RTK Query, expose a /api/posts endpoint:

// pages/api/posts.js

import { openDb } from './db'

async function handler(req, res) {

  const db = await openDb()  
  const posts = await db.all('SELECT * FROM posts')

  res.status(200).json(posts)

}

export default handler

Now from any client component:

useEffect(() => {

  async function fetchPosts() {
    const res = await fetch('/api/posts') 
    const posts = await res.json()

    // Update component state  
  }

  fetchPosts()  

}, [])

And that’s it! The SQLite data is now available for client-side consumption.

Take your Next.js skills to the next level

As you can see, combining Next.js and SQLite is incredibly powerful for building data-driven web applications.

The benefits don’t stop there. Mastering this stack also sets you up for success by teaching core concepts that translate to other frameworks.

Once you understand server-side rendering with Next.js, you can more easily pick up frameworks like Nuxt.js. And if you know how to leverage SQLite for simple schemas, transitioning to more complex databases like MongoDB becomes much easier.

So don’t just learn Next.js and SQLite in isolation – use them as springboards to accelerate your overall learning.

The web development landscape moves rapidly. But fundamentals like server-side rendering, database access patterns, REST API principles remain consistent.

If you take the time now to really nail down Next.js and SQLite, you’ll establish a foundation of core concepts for becoming a well-rounded, adaptable web developer no matter how the technology evolves.

So get out there and build something! With the power of Next.js and SQLite at your fingertips, you have everything you need to bring your ideas to life.

I can’t wait to see what you create next!