baseline
tutorial

Database ​

Baseline utilizes Drizzle ORM with MySQL as its core data layer.

Managing Entity Schemas ​

Prototype Schema Changes Locally ​

When actively designing your data model, use the "push" workflow to reflect changes in your database immediately without managing migration files for every minor tweak:

  1. Modify your schema: Update entity definitions within your schema files.
  2. Sync the database: Run the following CLI command to apply changes directly to your local MySQL instance:
    bash
    baseline db:push

Local Iteration

The "push" workflow is ideal for early-stage prototyping where schema changes are frequent and data loss in your local environment is acceptable.

Commit Changes to Version Control ​

Once your schema is stable and ready to be shared or deployed, you must convert those changes into a versioned migration file.

Run the following CLI command with a descriptive name to create a permanent record of your changes:

bash
baseline db:commit --name=add_user_preferences_table

INFO

Always commit migration files to version control (e.g., Git). These files provide a structured, reproducible history of schema changes across all environments.

Handle Migrations on Populated Tables ​

Modifying tables that already contain data requires caution. For example: Adding a non-nullable column to a populated table will trigger a database error because the database cannot assign a value to existing rows.

See Handling Existing Data for strategies to manage these transitions.

Why Use Migrations? ​

While db:push is ideal for prototyping, migrations provide an "insurance policy" for your data by ensuring:

  • Environment Consistency: Keeps the database schema identical across local, staging, and production environments.
  • Collaborative Safety: Facilitates seamless updates when working across multiple branches or with multiple developers.
  • Transparency: Provides an auditable history of exactly how the database structure has evolved.
  • Control: Enables manual review and fine-tuning of SQL statements before they are executed, preventing accidental data loss.

Backend Database Usage ​

You can interact with the database from the backend (in custom routes for example) via baseline.database:

js
//get a single user by primary key
baseline.database.find("users", 123);

//delete a single user by primary key
baseline.database.delete("users", 123);

//create a single user
const user = baseline.database.insert("users", {
    name: "example"
});

//update a single user
baseline.database.update("users", {
    id: 123
    name: "changed"
});

You can also use the underlying drizzle instance:

https://orm.drizzle.team/docs/rqb#select-filters

js
//drizzle relational queries 
const user = await baseline.database.db.query.users.findFirst({
    where: { name: "example" },
});

https://orm.drizzle.team/docs/select

js
//drizzle SQL 
const usersTable = baseline.database.entities.getTable("users");
const res = await baseline.database.db.select().from(usersTable).where(eq(usersTable.name, "example")).limit(1);