SQL Database

Getting Started

Enable the database in your NuxtHub project by adding the database property to the hub object in your nuxt.config.ts file.

export default defineNuxtConfig({
  hub: {
    database: true
  }
})

Nuxt DevToolsNuxtHub Admin

hubDatabase()

Server composable that returns a D1 database client.

const db = hubDatabase()

prepare()

Generates a prepared statement to be used later.

const stmt = db.prepare('SELECT * FROM users WHERE name = "Evan You"')

bind()

Binds parameters to a prepared statement.

const stmt = db.prepare('SELECT * FROM users WHERE name = ?1')

stmt.bind('Evan You')

const stmt = db
  .prepare('SELECT * FROM users WHERE name = ?2 AND age = ?1')
  .bind(3, 'Leo Chopin')

all()

Returns all rows as an array of objects, with each result row represented as an object on the results property (see Return Object).

const { results } = db.prepare('SELECT name, year FROM frameworks LIMIT 2').all()

console.log(results)
/*
[
  {
     name: "Laravel",
     year: 2011,
  },
   {
     name: "Nuxt",
     year: 2016,
  }
 ]
*/

The method return an object that contains the results (if applicable), the success status and a meta object:

{
  results: array | null, // [] if empty, or null if it does not apply
  success: boolean, // true if the operation was successful, false otherwise
  meta: {
    duration: number, // duration of the operation in milliseconds
    rows_read: number, // the number of rows read (scanned) by this query
    rows_written: number // the number of rows written by this query
  }
}

first()

Returns the first row of the results. This does not return metadata like the other methods. Instead, it returns the object directly.

const framework = db.prepare('SELECT * FROM frameworks WHERE year = ?1').bind(2016).first()

console.log(framework)
/*
{
  name: "Nuxt",
  year: 2016,
}
*/

Get a specific column from the first row by passing the column name as a parameter:

const total = db.prepare('SELECT COUNT(*) AS total FROM frameworks').first('total')
console.log(total) // 23

raw()

Returns results as an array of arrays, with each row represented by an array. The return type is an array of arrays, and does not include query metadata.

const rows = db.prepare('SELECT name, year FROM frameworks LIMIT 2').raw()
console.log(rows);

/*
[
  [ "Laravel", 2011 ],
  [ "Nuxt", 2016 ],
]
*/

Column names are not included in the result set by default. To include column names as the first row of the result array, use .raw({ columnNames: true }).

const rows = db.prepare('SELECT name, year FROM frameworks LIMIT 2').raw({ columnNames: true })
console.log(rows);

/*
[
  [ "name", "year" ],
  [ "Laravel", 2011 ],
  [ "Nuxt", 2016 ],
]
*/

run()

Runs the query (or queries), but returns no results. Instead, run() returns the metrics only. Useful for write operations like UPDATE, DELETE or INSERT.

const result = db
  .prepare('INSERT INTO frameworks (name, year) VALUES ("?1", ?2)')
  .bind('Nitro', 2022)
  .run()

console.log(result)
/*
{
  success: true
  meta: {
    duration: 62,
  }
}
*/

batch()

Sends multiple SQL statements inside a single call to the database. This can have a huge performance impact as it reduces latency from network round trips to the database. Each statement in the list will execute and commit, sequentially, non-concurrently and return the results in the same order.

const [info1, info2] = await db.batch([
  db.prepare('UPDATE frameworks SET version = ?1 WHERE name = ?2').bind(3, 'Nuxt'),
  db.prepare('UPDATE authors SET age = ?1 WHERE username = ?2').bind(32, 'atinux'),
])

info1 and info2 will contain the results of the first and second queries, similar to the results of the .all() method (see Return Object).

console.log(info1)
/*
{
  results: [],
  success: true,
  meta: {
    duration: 62,
    rows_read: 0,
    rows_written: 1
  }
}
*/

The object returned is the same as the .all() method.

exec()

Executes one or more queries directly without prepared statements or parameters binding. The input can be one or multiple queries separated by \n.

If an error occurs, an exception is thrown with the query and error messages, execution stops and further statements are not executed.

const result = await hubDatabase().exec(`CREATE TABLE IF NOT EXISTS frameworks (id INTEGER PRIMARY KEY, name TEXT NOT NULL, year INTEGER NOT NULL DEFAULT 0)`)
console.log(result)
/*
{
  count: 1,
  duration: 23
}
*/

Database Migrations

Database migrations provide version control for your database schema. They track changes and ensure consistent schema evolution across all environments through incremental updates.

Automatic Application

SQL migrations in server/database/migrations/*.sql are automatically applied when you:

• Start the development server (npx nuxt dev or npx nuxt dev --remote)
• Preview builds locally (npx nuxthub preview)
• Deploy via npx nuxthub deploy or Cloudflare Pages CI

Creating Migrations

Generate a new migration file using:

npx nuxthub database migrations create <name>

Migration files are created in server/database/migrations/.

> npx nuxthub database migrations create create-todos
✔ Created ./server/database/migrations/0001_create-todos.sql

After creation, add your SQL queries to modify the database schema.

Checking Migration Status

View pending and applied migrations across environments:

# Local environment status
npx nuxthub database migrations list

# Preview environment status
npx nuxthub database migrations list --preview

# Production environment status
npx nuxthub database migrations list --production

> npx nuxthub database migrations list --production
ℹ Connected to project atidone.
ℹ Using https://todos.nuxt.dev to retrieve migrations.
✔ Found 1 migration on atidone...
✅ ./server/database/migrations/0001_create-todos.sql 10/25/2024, 2:43:32 PM
🕒 ./server/database/migrations/0002_create-users.sql Pending

Marking Migrations as Applied

For databases with existing migrations, prevent NuxtHub from rerunning them by marking them as applied:

# Mark applied in local environment
npx nuxthub database migrations mark-all-applied

# Mark applied in preview environment
npx nuxthub database migrations mark-all-applied --preview

# Mark applied in production environment
npx nuxthub database migrations mark-all-applied --production

Migrating from Drizzle ORM

Since NuxtHub doesn't recognize previously applied Drizzle ORM migrations (stored in __drizzle_migrations), it will attempt to rerun all migrations in server/database/migrations/*.sql. To prevent this:

1. Mark existing migrations as applied in each environment:
   Terminal

   # Local environment
   npx nuxthub database migrations mark-all-applied
   
   # Preview environment
   npx nuxthub database migrations mark-all-applied --preview
   
   # Production environment
   npx nuxthub database migrations mark-all-applied --production
   

2. Remove server/plugins/database.ts as it's no longer needed.

That's it! You can keep using npx drizzle-kit generate to generate migrations when updating your Drizzle ORM schema.