Creating Database Entities

An entity represents a database table within your application.

File Location

Place your entity schema files in the server/database/schema/ directory.

Defining an Entity

Wrap your Drizzle ORM table definition in defineEntity to register it with baseline. Since baseline is configured for MySQL, you must use the mysql-core package from Drizzle ORM.

Check out the available column types

import { defineEntity, primaryId, timestamps } from "@codingkiwi/baseline/database/utils.js";
import { bigint, boolean, mysqlTable, varchar } from "drizzle-orm/mysql-core";

export const todos = defineEntity({
    table: mysqlTable("todos", {
        id: primaryId(),
        title: varchar({ length: 255 }).notNull(),
        is_done: boolean().default(false),
        ...timestamps,
    })
});

Column Helpers

Parameter	Description
primaryId()	A helper to define an auto-incrementing primary key (bigint).
...timestamps	A utility that automatically adds created_at and updated_at columns.

Naming Conventions

The entity name (both the exported constant and the database table name) must be snake case and plural. The name is validated by defineEntity; use skipNameCheck: true in the entity options to bypass this validation.

Good

• todos
• custom_projects

Bad

• todo (singular)
• customProjects (camelCase)

Syncing Your Schema

After defining your entities, sync your schema to your database using db:push.

You can use the baseline db:push command to prototype changes quickly.

Once you are happy with the changes, make sure to generate a migration using db:commit.

--> See Database for more information about managing the schema.

Relating Entities

Connect entities using foreign keys in the table definition and relationship helpers in the relations property.

Check out the drizzle relation docs

import { users } from "./users.js";
import { defineEntity, primaryId, timestamps } from "@codingkiwi/baseline/database/utils.js";
import { bigint, varchar, mysqlTable } from "drizzle-orm/mysql-core";

export const todos = defineEntity({
    table: mysqlTable("todos", {
        id: primaryId(),
        title: varchar({ length: 255 }).notNull(),
        user_id: bigint({ mode: "number", unsigned: true })
            .notNull()
            .references(() => users.id),
        ...timestamps,
    }),
    relations(r) {
        return {
            user: r.one.users({
                from: r.todos.user_id,
                to: r.users.id,
            }),
        };
    },
});

• Foreign Key: The .references() method establishes the link at the database level.
• Relations Object: The relations function allows you to navigate these links within the framework. Relations can be used to filter entity lists using the where clause. See Filter Entities for details.

Adding File Columns

Use the file() utility to define columns for file uploads, which enables built-in multipart middleware support. See File Uploads for more details.

Hiding Sensitive Data

The API schema is generated automatically based on your entity definitions.

You can view the full documentation in the API Reference.

Prevent specific columns from being exposed in the automatically generated API by using the hidden property.

export const users = defineEntity({
    table: mysqlTable("users", {
        id: primaryId(),
        email: varchar({ length: 255 }).notNull(),
        password: varchar({ length: 255 }).notNull(),
        ...timestamps,
    }),
    hidden: ["password"],
});

Row-Level Security

Access Filter

You can enforce RLS by providing a filter function that returns a Drizzle Relation Query (RQB) object. See Filtering.

The callback function receives the current auth user, other where filters and the entity definition and can return an object or a boolean.

Returning an object

The object returned specifies the drizzle where filter that is applied.

export const tokens = defineEntity({
    ...
    filters: {
        view({ user, where, entity }) {
            return {
                user_id: user.id,
            };
        },
    },
});

To apply no filter at all, return an empty object {}.

Returning a boolean

To deny access to an entity, return false.

export const tokens = defineEntity({
    ...
    filters: {
        view({ user, request }) {
            //does not work for live filters
            if(request && request.validated.query.secret_param) return {};

            if(user.is_admin) return {};

            if(user.is_banned) return false;

            return {
                user_id: user.id,
            };
        },
    },
});

How does it work under the hood?

The filter is applied on SQL level when fetching entities from the database via the filter endpoint (QUERY) or when accessing an entity by id (GET, PUT, DELETE)

The filter is applied as an AND in the query where (<rls filter>) AND (<other where filters from request>) LIMIT ...

WARNING

The view filter also applies for live filters (which are pushed proactively from the server to the client) where no request object is present.

Action Filter

The Access filter affects all endpoints except POST (creating an entity).

Access filters must always return a boolean. Drizzle filters do not work.

To restrict creating an entity specify a create function:

export const tokens = defineEntity({
    ...
    filters: {
        create({ user }) {
            return user.is_admin;
        },
    },
});

Apart from view and create, you can also specify action filters for delete and update

export const tokens = defineEntity({
    ...
    filters: {
        delete({ user, entity }) {
            return entity.protected === false || user.is_admin;
        },
        update({ user, entity }) {
            return entity.protected === false || user.is_admin;
        }
    },
});