A persistent store that keeps data across restarts, organized into tables, rows, and columns.

What’s the difference between PostgreSQL, SQLite, and MongoDB?

PostgreSQL is a robust relational database; SQLite is a lightweight file-based database; MongoDB stores flexible JSON-like documents.

Why use an ORM like Prisma?

An ORM translates code calls into SQL under the hood, making database interactions easier while still leveraging SQL when needed.

databases

npx machina-cli add skill DojoCodingLabs/code-sensei/databases --openclaw

Files (1)

SKILL.md

2.8 KB

Databases — CodeSensei Teaching Module

What is a Database?

Analogy: A super-powered spreadsheet that your app can read and write to automatically. Each "table" is like a sheet, each "row" is an entry, each "column" is a field.
Key insight: Without a database, your app forgets everything when it restarts. Databases make data PERMANENT.

SQL (Structured Query Language)

Analogy: SQL is the language you use to talk to the database. Like asking a librarian: "Find me all books by this author, sorted by year."
Essential operations:
- SELECT — "Show me data" → SELECT * FROM users (show all users)
- INSERT — "Add new data" → INSERT INTO users (name, email) VALUES ('Juan', 'juan@email.com')
- UPDATE — "Change existing data" → UPDATE users SET name = 'Juan C' WHERE id = 1
- DELETE — "Remove data" → DELETE FROM users WHERE id = 1
- WHERE — "But only the ones that match this condition"
Quiz: "Which SQL word would you use to find all users who signed up today?"

Types of Databases

PostgreSQL — the reliable all-rounder. Most vibecoded apps use this. Think of it as Excel but 1000x more powerful.
SQLite — a lightweight database stored in a single file. Good for small projects. Like a notepad vs a full filing system.
MongoDB — stores data as flexible documents (JSON-like) instead of rigid tables. Like storing folders of papers vs a spreadsheet.

ORMs (Object-Relational Mappers)

Analogy: An ORM is a translator between your code and the database. Instead of writing SQL directly, you write code in your programming language and the ORM converts it to SQL.
Prisma (most common in vibecoded projects):
- prisma.user.findMany() → becomes SELECT * FROM users
- prisma.user.create({ data: { name: "Juan" } }) → becomes INSERT INTO users...
Key insight: ORMs make databases easier to work with, but the SQL is still happening underneath.

Schemas & Migrations

Schema — the blueprint of your database. Defines what tables exist and what columns each has.
Migration — a change to the blueprint. Like remodeling a room — you write instructions for the change, then apply them.
Analogy: The schema is your building's floor plan. A migration is the renovation permit that says "add a new room here."

Relationships

One-to-Many: One user has many posts. Like one author writing many books.
Many-to-Many: Users can have many tags, and tags can belong to many users. Like students and classes.
Foreign Key: The link that connects tables. Like a reference number that says "this post belongs to user #5."

Source

git clone https://github.com/DojoCodingLabs/code-sensei/blob/main/skills/databases/SKILL.mdView on GitHub

Overview

Databases store persistent app data in tables with rows and columns. This module covers core SQL operations, common database types (PostgreSQL, SQLite, MongoDB), ORMs like Prisma, and the role of schemas and migrations. Understanding these concepts helps you build reliable, scalable data-driven applications.

How This Skill Works

Data lives in a database and is accessed via SQL or an ORM. SQL commands like SELECT, INSERT, UPDATE, and DELETE perform operations directly, while ORMs translate language-native calls into SQL under the hood. Schemas define structure and migrations evolve that structure over time.

When to Use It

Building user accounts and related data (users, posts) where data must persist across restarts.
Prototyping or small projects with SQLite for simple, file-based storage.
Using PostgreSQL for reliable, all-round relational data support in vibecoded apps.
Working with document-style data in MongoDB when schema flexibility is advantageous.
Managing schema changes over time with migrations in a team project.

Quick Start

Step 1: Pick a DB (PostgreSQL, SQLite, or MongoDB) and sketch your schema (tables/columns/relationships).
Step 2: Implement basic queries (SELECT, INSERT, UPDATE, DELETE) or use Prisma to perform common operations.
Step 3: Create and apply a migration to evolve the schema as your app grows.

Best Practices

Define a clear schema and versioned migrations before adding data.
Choose the right database for the use case: PostgreSQL for reliability, SQLite for small projects, MongoDB for flexible documents.
Index critical queries, enforce foreign keys, and normalize data where appropriate.
Understand the SQL behind ORMs; don’t rely solely on ORM abstractions for complex queries.
Model relationships explicitly (one-to-many, many-to-many) and use migrations to evolve them safely.

Example Use Cases

SELECT * FROM users to fetch all users.
INSERT INTO users (name, email) VALUES ('Juan', 'juan@email.com')
UPDATE users SET name = 'Juan C' WHERE id = 1
prisma.user.findMany() — ORM call that translates to SELECT * FROM users
One-to-many relationship: one user has many posts via a foreign key in posts

Frequently Asked Questions

Add this skill to your agents