Database

VitNode use Drizzle ORM (opens in a new tab) to manage database. Drizzle ORM is a TypeScript-first ORM for Node.js and PostgreSQL. It's designed to be simple to use, flexible, and powerful.

Initial setup

After created plugin you can find plugins/{your_plugin}/admin/database folder.

Schema

Create File

Inside database folder go to schema folder (If is your first schema, create folder). Create new TypeScript file with any name. Here is example file tree after created posts file:

- - - - posts.ts

Create Schema

Inside posts.ts file create schema for your tables. Follow Drizzle ORM (opens in a new tab) documentation for more information how create schema.

Here is example schema for posts table:

import {
  integer,
  pgTable,
  serial,
  timestamp,
  varchar
} from "drizzle-orm/pg-core";
 
import { blog_categories } from "./categories";
 
import { core_languages } from "@/plugins/core/admin/database/schema/languages";
import { core_users } from "@/plugins/core/admin/database/schema/users";
 
export const blog_articles = pgTable("blog_articles", {
  id: serial("id").primaryKey(),
  author_id: integer("author_id").references(() => core_users.id, {
    onDelete: "cascade"
  }),
  category_id: integer("category_id").references(() => blog_categories.id, {
    onDelete: "cascade"
  }),
  created: timestamp("created").notNull().defaultNow(),
  update: timestamp("update").notNull().defaultNow(),
  ip_address: varchar("ip_address", { length: 45 })
});
 
export const blog_articles_content = pgTable("blog_articles_content", {
  id: serial("id").primaryKey(),
  article_id: integer("article_id").references(() => blog_articles.id, {
    onDelete: "cascade"
  }),
  language_code: varchar("language_code")
    .notNull()
    .references(() => core_languages.code, {
      onDelete: "cascade"
    }),
  value: varchar("value").notNull()
});

Good to know

Inside schema folder you can create as many files as you want (folders not supported),
You can create as many schemas as you want inside one file,
VitNode using PostgreSQL,
The best naming convention for tables is snake_case with {your_plugin}_ prefix (example: blog_articles)

Indexes

Indexes are important part of schema. Thanks to indexes your database will be faster.

For example, if you have blog_articles table and you want to search by author_id, you can create index for this column.

import {
  index,
  integer,
  pgTable,
  serial,
  timestamp,
  varchar
} from "drizzle-orm/pg-core";
 
import { blog_categories } from "./categories";
 
import { core_languages } from "@/plugins/core/admin/database/schema/languages";
import { core_users } from "@/plugins/core/admin/database/schema/users";
 
export const blog_articles = pgTable(
  "blog_articles",
  {
    id: serial("id").primaryKey(),
    author_id: integer("author_id").references(() => core_users.id, {
      onDelete: "cascade"
    }),
    category_id: integer("category_id").references(() => blog_categories.id, {
      onDelete: "cascade"
    }),
    created: timestamp("created").notNull().defaultNow(),
    update: timestamp("update").notNull().defaultNow(),
    ip_address: varchar("ip_address", { length: 45 })
  },
  table => ({
    author_id_idx: index("blog_articles_author_id_idx").on(table.author_id),
    category_id_idx: index("blog_articles_category_id_idx").on(
      table.category_id
    )
  })
);
 
export const blog_articles_content = pgTable(
  "blog_articles_content",
  {
    id: serial("id").primaryKey(),
    article_id: integer("article_id").references(() => blog_articles.id, {
      onDelete: "cascade"
    }),
    language_code: varchar("language_code")
      .notNull()
      .references(() => core_languages.code, {
        onDelete: "cascade"
      }),
    value: varchar("value").notNull()
  },
  table => ({
    article_id_idx: index("blog_articles_content_article_id_idx").on(
      table.article_id
    ),
    language_code_idx: index("blog_articles_content_language_code_idx").on(
      table.language_code
    )
  })
);

Relations

Relations is importatnt part of schema. Thanks to relations your database will be more readable and easier to use by you.

For example, if you have blog_articles table and blog_articles_content table, you can create relation between them. If some blog_articles will be removed, all blog_articles_content related to this blog_articles will be removed too.

import { relations } from "drizzle-orm";
import {
  index,
  integer,
  pgTable,
  serial,
  timestamp,
  varchar
} from "drizzle-orm/pg-core";
 
import { blog_categories } from "./categories";
 
import { core_languages } from "@/plugins/core/admin/database/schema/languages";
import { core_users } from "@/plugins/core/admin/database/schema/users";
 
export const blog_articles = pgTable(
  "blog_articles",
  {
    id: serial("id").primaryKey(),
    author_id: integer("author_id").references(() => core_users.id, {
      onDelete: "cascade"
    }),
    category_id: integer("category_id").references(() => blog_categories.id, {
      onDelete: "cascade"
    }),
    created: timestamp("created").notNull().defaultNow(),
    update: timestamp("update").notNull().defaultNow(),
    ip_address: varchar("ip_address", { length: 45 })
  },
  table => ({
    author_id_idx: index("blog_articles_author_id_idx").on(table.author_id),
    category_id_idx: index("blog_articles_category_id_idx").on(
      table.category_id
    )
  })
);
 
export const blog_articles_relations = relations(
  blog_articles,
  ({ many, one }) => ({
    author: one(core_users, {
      fields: [blog_articles.author_id],
      references: [core_users.id]
    }),
    content: many(blog_articles_content),
    category: one(blog_categories, {
      fields: [blog_articles.category_id],
      references: [blog_categories.id]
    })
  })
);
 
export const blog_articles_content = pgTable(
  "blog_articles_content",
  {
    id: serial("id").primaryKey(),
    article_id: integer("article_id").references(() => blog_articles.id, {
      onDelete: "cascade"
    }),
    language_code: varchar("language_code")
      .notNull()
      .references(() => core_languages.code, {
        onDelete: "cascade"
      }),
    value: varchar("value").notNull()
  },
  table => ({
    article_id_idx: index("blog_articles_content_article_id_idx").on(
      table.article_id
    ),
    language_code_idx: index("blog_articles_content_language_code_idx").on(
      table.language_code
    )
  })
);
 
export const blog_articles_content_relations = relations(
  blog_articles_content,
  ({ one }) => ({
    article: one(blog_articles, {
      fields: [blog_articles_content.article_id],
      references: [blog_articles.id]
    })
  })
);

Your relations should be with _relations surfix.

Import schema to root file

After created schema, you need to import it to index.ts file. Inside index.ts file you can import as many schemas as you want.

Here is example index.ts file:

import * as articles from "./schema/articles";
import * as categories from "./schema/categories";
 
export default {
  ...articles,
  ...categories
};

Please remember to export all schemas to index.ts file. If you forget to export some schema, it will not be removed from database when you remove plugin.

Generate tables

Inside backend folder run:

pnpm db

Migration

You don't need to worry about migration. VitNode will handle it for you.

When you change your schema, VitNode will automatically create schema for migration when create new version or rebuild existing version of your plugin.

Helpers

VitNode provide a few helpers functions to help you build your query. We recommend using this helpers functions to build your query.

Pagination Ordering

Overview Pagination