How to set up Payload with SQLite and Turso

Set up and use Payload with SQLite

In this guide, I’m going to show you how you can set up and use Payload with a SQLite database. I’ll cover why you might want to choose SQLite over other database options, discuss some deployment scenarios, and walk you through integrating with Turso to enable a serverless environment like Vercel.

Let’s dive in!

Database options in Payload

When starting a new Payload project, you have three main database options:

• MongoDB: The original NoSQL database Payload was built for.
• Postgres: Available through the new adapters.
• SQLite: Also available now via the Drizzle adapter.

In this video and guide, we’re focusing on SQLite.

Benefits of SQLite

SQLite is a pretty simple option. Here’s why you might choose it over something like MongoDB:

• Simplicity: There’s no need to install or spin up a massive database server on your local machine. SQLite is just a single file.
• Portability: It runs everywhere, making it super easy to back up – just copy the file.
• Performance: For small data sets and simple queries (think a basic marketing website), SQLite can perform very well.

Deployment scenarios for SQLite

There are two scenarios where SQLite really shines:

1. Local Reproductions
   
   If you find a bug in Payload, you can easily include your SQLite database file in your repo so others can pull your reproduction setup, install the necessary libraries, and run the development server.
   
2. Serverless Deployments
   
   On serverless platforms like Vercel, containers wipe local files on every deploy. This is where a third-party solution like Turso comes in handy—it lets you host your SQLite database remotely so your data stays intact between deployments.

Another option is to use a docker-based deployment (for instance, with Coolify) where you can mount the database file as persistent storage.

Creating a new Payload project with SQLite

Let’s get started by creating a fresh Payload project using SQLite:

1. Create your project
   Open your terminal and run the following command:

When prompted for the project name, type something like sqlite-payload.

For the project template, choose “blank”.

When asked for the database, select SQLite (instead of MongoDB).

Leave the SQLite connection string as-is for now.

2. Install and Open the Project
   
   Once the dependencies are installed, move into your new project directory: cd sqlite-payload
   
   Open the project in your code editor (like VS Code). You’ll notice there isn’t any SQLite database file yet—don’t worry, it will be automatically generated when the development server starts.

Running the local development server

To get things up and running:

1. Start your development server: npm run dev
2. Open your browser and navigate to localhost:3000.
   Append /admin to your URL (for example, localhost:3000/admin).
   
   You’ll be greeted with the Payload login screen. Sign in or create a new user by entering a password and clicking “create.”

Once you log in successfully, your new Payload application is fully functioning. Notice that the first time you run the server, Payload automatically generates the SQLite database file usually something like sqlite-payload.db.

Integrating with Turso for remote SQLite

For production deployments on platforms like Vercel, you can’t rely on a local file since containers are ephemeral. That’s where Turso comes in.

Turso provides a remote SQLite database, which means you can use SQLite even in serverless environments. I’m going to show you how to set up Turso with your Payload project.

Setting Up Turso CLI

1. Install the Turso CLI
   On a Mac, you can install it via Homebrew. For Linux or Windows, follow the instructions provided by Turso.
2. Sign up for Turso
   Open your terminal and run:
   turso auth signup

This will open a browser window where you can sign up with GitHub or your email.

Creating a Turso Database

Once the CLI is set up:

1. Create a new Turso database by running:
   turso db create sqlite-payload (or whatever name you want to give it)
2. When prompted, give your database a name. For example, you might name it sqlite-payload.

Turso will create your database in the nearest data center (for me, that might be in Europe West or another region based on your location).

Getting the Connection URL

In Turso's Quickstart guide, you'll want to go to step 5 and select TypeScript/JavaScript. You’ll need the connection URL to hook up your Payload application to Turso.

It asks us to run: turso db show --url <database-name>. In this case, it'll be turso db show --url sqlite-payload

This command returns the connection URL—copy it!

Next, return to the codebase and paste this URL into your environment variables. Look for the environment variable that Payload uses for the database URI (usually something like DATABASE_URI). Replace the default local file URI with your Turso connection URL.

Adding an authentication token to the Payload Config

In addition to the connection URL, you need an authentication token to secure your connection to Turso.

1. Open your Payload config (payload.config.ts )
2. Update the configuration to include an auth token from your environment variables (we'll generate this shortly). For example:

We also need to return to the environment variables and add:

Next, create that token. In your terminal, run: turso db tokens create sqlite-payload

This command will return an authentication token—copy it and add it as DATABASE_TOKEN in your environment variables.

Testing the remote database connection

With the connection string and the authentication token in place:

1. Delete the local SQLite file from your project (if it exists) because you’re now relying on the remote database.
2. Restart your development server: npm run dev
   
3. Navigate to your Payload admin UI (localhost:3000/admin).
   
   If everything is set up correctly, you should see the login screen and be able to sign in. Now when you create a user or save data, it writes to your remote Turso database rather than to a local file—so you will no longer have a .db file in your project.
   
   This means that even on serverless deployments (like on Vercel), your data will persist, simply by using the same environment variables.

Conclusion and Use Cases

I hope you now see how incredibly easy it is to set up Payload with a SQLite database and even connect it to a remote service like Turso. To recap:

• Choosing SQLite: It’s a simple, portable option with great performance for small datasets.
• Local Development: Payload automatically generates the SQLite database file, making it perfect for creating local reproductions or simple projects.
• Deployment Considerations: Since serverless environments wipe local files, integrating Turso ensures your SQLite data persists.
• Production Uses: Although I personally might favor MongoDB or Postgres in production, SQLite remains a handy option for many smaller projects and scenarios.