Nuxt Experimental Features: Connect your Turso DB natively with db0 and Drizzle ORM

Introduction

This article is a continuation of my earlier Turso Article. You can go through that article and the code first, but it's not required for reading or taking over changes from this article if you want to code along.

✨ Improved Features

useDatabase() is now globally available, no need to create it yourself.
useDatabase() is pre-configured through your nuxt.config.ts or nitro.config.ts file.
Enabling the experimental database layer will automagically add a local dev sqlite db at ./.data/db.sqlite3.
You can use the experimental Nitro Tasks to perform scheduled migrations or perform any other Ops on your db.
It is way cleaner compared to the previous approach.

😌 Easy Setup and Requirements

Update your `package.json`

Include the following versions:

package.json

{
  "dependencies": {
    "nuxi": "npm:nuxi-nightly@latest",
    "nuxt": "npm:nuxt-nightly@latest"
  }
}

Delete your package-lock file after version update and before re-installing!

Install additional dependency

Install the better-sqlite3 dependency for the local dev database layer:

npm install better-sqlite3

You can find my working experimental version at the nightly branch of the previous article.

🛠 Configuration and Implementation

1. Getting started

If you're new and tagging along, make sure you have your Turso DB API token at hand. You'll need them in your .env file:

.env

devDatabase=true
TURSO_DB_URL=
TURSO_DB_AUTH_TOKEN=

2. Nuxt Configuration

Update your nuxt.config.ts file to enable the new experimental features:

nuxt.config.ts

export default defineNuxtConfig({
  devtools: { enabled: true },
  nitro: {
    experimental: {
      tasks: true,
      database: true,
    },
    database: {
      devDatabase: {
        connector: 'sqlite',
        options: { name: 'devDb' }
      },
      default: {
        connector: 'turso',
        options: {
          url: process.env.TURSO_DB_AUTH_TOKEN,
          authToken: process.env.TURSO_DB_AUTH_TOKEN,
        }
      },
    }
  },
})

3. Usage

Simplify your database initialization:

server/utils/db.ts

import { drizzle } from "db0/integrations/drizzle/index"; 
export const orm = drizzle(useDatabase());

4. Database Migration with Nitro Tasks

Create a basic migration function:

server/utils/db.ts

import { drizzle } from "db0/integrations/drizzle/index";
export const orm = drizzle(useDatabase())

export function migrateDatabase() {
  const db = useDatabase();
  db.sql`CREATE TABLE IF NOT EXISTS tickets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    subject TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )`;
}

Set up a Nitro Task to run the migration:

server/tasks/db/migrate.ts

export default defineNitroPlugin(async () => {
  if (process.env.devDatabase) {
    console.group('> Task', 'db:migrate')
    const task = await runTask('db:migrate')
    console.group(task.result)
  }
})

Create a Nitro server plugin to fire off the task:

server/plugins/init.ts

export default defineTask({
  async run() {
    if (process.env.devDatabase) migrateDatabase()
    return { result: 'ok' }
  }
})

5. Drizzle Configuration

Your drizzle.config.ts can remain largely unchanged:

drizzle.config.ts

import { join } from 'pathe';
import type { Config } from "drizzle-kit";

export default {
  out: 'server/database/migrations',
  schema: 'server/database/schema.ts',
  driver: process.env.devDatabase ? "better-sqlite" : "turso",
  dbCredentials: {
    url: process.env.devDatabase 
      ? join(process.cwd(), './.data/db.sqlite3') 
      : process.env.TURSO_DB_URL as string,
    authToken: process.env.devDatabase 
      ? "" 
      : process.env.TURSO_DB_AUTH_TOKEN as string,
  },
} satisfies Config;

6. Nuxt / Nitro API Example

Here's an example of how to use the ORM in an API route:

server/api/v1/tickets/index.ts

import { tickets, InsertTicket } from "~/server/database/schema";

export default defineEventHandler(async (event) => {
  try {
    const body = await readBody(event);
    const newTicket: InsertTicket = {
      ...body
    }
    const result = orm.insert(tickets).values(newTicket).run();
    return { newTicket : result}
  } catch (e: any) {
    throw createError({
      statusCode: 400,
      statusMessage: e.message,
    });
  }
})

Conclusion

The experimental features in Nuxt's nightly build significantly enhance the developer experience when working with databases. By leveraging db0 and Drizzle ORM, you can achieve a more streamlined and efficient database setup in your Nuxt projects. The unjs team is dropping bangers!

Coolify - Self Hosting on Steroids

Coolify is an open-source, self-hosted alternative to Vercel, Heroku, and Netlify that allows developers to host their applications on their own infrastructure.

User Auth and Session Management in Nuxt with Sidebase and Strapi v4

Learn how to implement user authentication and session management in a Nuxt application using Sidebase Nuxt-Auth module and Strapi v4 as the backend.