Instead of putting all your tables in a single file, it's best practice to organize them into separate files within a server/database/schema/ directory.

1. Create the Schema Directory

Create a directory at server/database/schema/.

2. Create Your First Schema (Users)

Create a file server/database/schema/users.ts:

// server/database/schema/users.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  password: text('password').notNull(),
  avatar: text('avatar').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
})

3. Adding New Schemas (e.g., Posts)

To add a new table, simply create a new file. For example, server/database/schema/posts.ts:

// server/database/schema/posts.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'
import { users } from './users'

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content').notNull(),
  authorId: integer('author_id').references(() => users.id),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
})

4. Export Schemas in Index

You must export all your schema files in server/database/schema/index.ts so Drizzle can find them.

// server/database/schema/index.ts
export * from './users'
export * from './posts'

5. Generate Migrations

After defining your schemas, run the generation script to create the database migrations:

pnpm db:generate

yarn db:generate

npm run db:generate

bun db:generate

Your API endpoints (e.g., /api/users, /api/posts) will be automatically available.

6. Reserved Field: status

Nuxt Auto CRUD includes built-in logic for the status field to simplify content visibility.

• Automatic Filtering: If your table contains a column named status, the module will automatically filter list results. By default, only records with status = 'active' are returned to non-admin users.
• Recommendation: If you use the field name status, ensure that 'active' and 'inactive' are among its options.
• Use Alternatives: For other state-tracking purposes (e.g., payment status, order steps), use a different field name like state, payment_status, or stage to avoid triggering this automatic filtering behavior.