Posted by db - Published Fri Mar 15 2024 - Updated Wed May 15 2024

TL:DR

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:

  • useDatabase() is now globally available, no need to create it yourself.
  • useDatabase() is pre-configured through your nuxt. | 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.

Requirements:

  • You need to update your package.json to include the following versions: Note: Delete your package-lock file after version update and before re-installing!
    "nuxi": "npm:nuxi-nightly@latest",
    "nuxt": "npm:nuxt-nightly@latest",
  • You need to install the better-sqlite3 dependency for the local dev database layer. You can find my working experimental version at the nightly branch of previous article.

Going over the changes

In this article I will be going over some amazing new experimental features introduced inside the nightly build of Nuxt and Nitro. Introducing db0, a new lightweight module to connect with your SQL databases natively through Nitro, how awesome is that!

It uses Drizzle ORM internally and offers a bunch more ORM's soon (check the integrations). Since we are already focused on Drizzle, this seemed like a win-win to me.

I'll be taking you step by step on what the database layer adds and what has been improved in terms of setting up your database inside your Nuxt 3 projects.

  1. Getting started
  2. Nuxt Configuration
  3. Usage
  4. Next up Nitro Tasks: Database Migration Example
  5. What about my drizzle.config.ts and typed SQL schema?
  6. Nuxt / Nitro API Example
  7. References

1. Getting started

If you are new and tagging along, make sure you have your Turso DB API token at hand. You'll need them in your .env file otherwise the database layer will kick off the local dev db.

devDatabase=true
TURSO_DB_URL=
TURSO_DB_AUTH_TOKEN=

Upgrade your project

If you are thinking of upgrading one of your existing Nuxt 3 projects like I did, then do this first.

  1. **Install better-sqlite3, drizzle-kit and drizzle-orm.
pnpm add better-sqlite3 drizzle-kit drizzle-orm
  1. Adopt the nightly version of Nuxt 3 in your package.json file:
// for Nuxt 3
"nuxi": "npm:nuxi-nightly@latest",
"nuxt": "npm:nuxt-nightly@latest",
  1. Delete your npm/yarn/pnpm package-lock file.
  2. Reinstall your project npm i / yarn i / pnpm i.

or, clean install the nightly release

  1. Open terminal and start a nuxi-nightly CLI build, use the following CLI command:
npx nuxi-nightly@latest init your-app-name
  1. **Install better-sqlite3, drizzle-kit and drizzle-orm.
pnpm add better-sqlite3 drizzle-kit drizzle-orm

2. Nuxt Configuration

Let's update the nuxt.config.ts file to enable the new experimental features by adding the nitro.experimental and nitro.database object.

  1. Enable the experimental database and tasks layer inside your nuxt.config.ts:
// https://nuxt.com/docs/api/configuration/nuxt-config
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,
            }
        },
        /* 
        // we're not using this and not in repo
		///////////////////////
		users: {
		  connector: 'postgresql',
		  url: 'postgresql://username:password@hostname:port/database_name'
		}
	    */
     }
  },
})
  1. The new database layer automagically generates a new better-sqlite3 database for us on the go at ./.data/db.sqlite3. Note: You guessed it right, including the devDatabase object automagically overrides all your other databases connectors even the default connector (yes, you can have multiple, at the same time).
devDatabase: {
   connector: 'sqlite',
   options: { name: 'devDb' }
},

Nitro difference

You configure Nitro the same way as Nuxt and the above nuxt.config example, however for Nitro, remove the nitroparent object. Like so:

export default defineNitroConfig({
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,
		}
	},
	 /*
     // we're not using this and not included in the example repo
	///////////////////////
	users: {
	  connector: 'postgresql',
	  url: 'postgresql://username:password@hostname:port/database_name'
	}
	*/
  }
})

3. Usage

In the previous article we initialized our database inside ./server/utils/db.ts, lets revisit the code.

import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";

export const database = createClient({
  url: process.env.TURSO_DB_URL!,
  authToken: process.env.TURSO_DB_AUTH_TOKEN!,
});

export const orm = drizzle(database);

That's a lot of code... every single time. With the experimental database feature enabled here's what is left of the code.

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

The database layer internalizes the createClient() and exports useDatabase() for us.

// Current setup
export const database = createClient({
  url: process.env.TURSO_DB_URL!,
  authToken: process.env.TURSO_DB_AUTH_TOKEN!,
});
// becomes 
export const database = useDatabase()

The module comes with its own built in Drizzle ORM configuration. You can import it with:

import { drizzle } from "db0/integrations/drizzle";

The experimental layer bootstraps a majority of the initial database setup and configuration.

// Current setup
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
export const database = createClient({
  url: process.env.TURSO_DB_URL!,
  authToken: process.env.TURSO_DB_AUTH_TOKEN!,
});

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

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


/* or just dont?
// ./server/utils/db.ts - not found
// In reality you dont need this util function at all, it's automagically injected...
// You could just useDatabase(), everywhere....
// 👻
*/

Take a look through my Nightly Github Repo for more insight.

4. Next up Nitro Tasks: Database Migration Example

  1. Create a basic migration function inside ./server/utils/db.ts like the one provided here:
import { drizzle } from "db0/integrations/drizzle/index";
export const orm = drizzle(useDatabase())

export function migrateDatabase() {
  const db = useDatabase();
  const orm = drizzle(useDatabase());
  // simple and direct useDatabase() query
  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
  )`;
  };
  1. **Now lets run the migration with new experimental Nitro Tasks feature, basically schedulable cron jobs. Here's how you set it up declare and export a migration function inside ./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
  )`;
}
  1. Create a new folder called ./server/tasks/db and inside the new folder create a file called migrate.tswith the following content:
export default defineNitroPlugin(async () => {
    if (process.env.devDatabase) {
        console.group('> Task', 'db:migrate')
        const task = await runTask('db:migrate')
        console.group(task.result)
    }
})
  1. Create a new nitro server plugin called init.ts with the following content to fire off the nitro task.
export default defineTask({
    async run() {
        if (process.env.devDatabase) migrateDatabase()
        return { result: 'ok' }
    }
})

If you now check your terminal log, you'll see that Nitro automagically imports your init.ts plugin and outputs the following:

ℹ Vite client warmed up in 932ms                                                         
ℹ Vite server warmed up in 1047ms                                                        
✔ Nuxt Nitro server built in 781 ms                                                  
> Task db:migrate
  ok

If you see the above, the task has successfully migrated your database and created all the tables if they didn't exist yet. You can change or add more plugins or API handlers that trigger a Nitro Task. Read more about Nitro Tasks here.

Example Nitro Task Docs

export default defineTask({
  meta: {
    name: "db:migrate",
    description: "Run database migrations",
  },
  run({ payload, context }) {
    console.log("Running DB migration task...");
    return "Success";
  },
});

5. Hold up, What about my drizzle.config.ts and typed SQL schema?

If you're working with drizzle-orm and drizzle-kit and a schema.ts file like I have been, I have more good news, you don't need to change a thing. The experimental layers just saves you a ton of configuration time, each time you need it.

I recommend sticking with the ./server/database/schema.ts and the drizzle.config.ts route:

import type { Config } from "drizzle-kit";
export default {
  schema: "server/database/schema.ts",
  driver: "turso",
  dbCredentials: {
    url: process.env.TURSO_DB_URL as string,
    authToken: process.env.TURSO_DB_AUTH_TOKEN as string,
  },
} satisfies Config;

// or if your running devDatabase, don't forget the .env variable: devDatabase!

import { join } from 'pathe';
import type { Config } from "drizzle-kit";
export default {
  out: 'server/database/migrations', // optional for migration folder
  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;

You can then push your schema by running the following command in a second terminal:

// Push your schema
pnpm drizzle-kit push:sqlite --config=./drizzle.config.ts

// Launch Drizzle Studio to view your tables
pnpm drizzle-kit studio

Everything in your routes and api folder remains the same too. If you export the orm helper like I did, it also gets automagically imported where ever you use it. The only thing that remains, is cleaner code.

You can still use Drizzle Studio inside and outside of the Dev Tools. The DX keeps multiplying 🤯.

6. Nuxt / Nitro API Example

/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,
  });
  }
})

The experimental layer adds so much more fun on top the inherent fun that is working with Nitro inside and outside of Nuxt. The unjs team is dropping bangers!

You can A/B compare both branches yourself.

That's the end of the article, I hope you picked up some tricks. Stay tuned for more awesome Nuxt articles!

7. References