Database

OpenGB provides a powerful and easy-to-use database layer that allows you to define your data models, perform queries, and manage migrations. Let’s dive into the key concepts and features of OpenGB’s database.

Global Database and Query Objects

OpenGB exposes two global objects for interacting with the database: Database and Query. These objects are automatically generated based on your schema definitions.

Database: Represents your database tables and columns. It provides a type-safe way to reference your database entities in queries and migrations.
Query: Provides a fluent and expressive API for building database queries. It allows you to perform operations like filtering, sorting, aggregation, and more.

Writing Schemas

Schemas are the foundation of your database in OpenGB. They define the structure and relationships of your data. Writing clear and well-defined schemas is crucial for several reasons:

Performance Schemas help optimize query performance by providing information about data types, indexes, and constraints.
Migrations OpenGB uses schemas to generate and manage database migrations, ensuring your database stays in sync with your application.
Security Schemas enforce data integrity and consistency, preventing invalid or malicious data from being inserted into your database.
Data Consistency Schemas ensure that your data adheres to a predefined structure, making it easier to reason about and maintain your application.

Tables

Here’s an example schema definition:

import { Query, schema } from "./schema.gen.ts";

export const users = schema.table("users", {
  id: Query.uuid("id").primaryKey().defaultRandom(),
  name: Query.text("name").notNull(),
  email: Query.text("email").notNull().unique(),
  createdAt: Query.timestamp("created_at").defaultNow().notNull(),
});

In this example, we define a users table with columns for id, name, email, and createdAt. We specify the data types, constraints, and default values for each column using the Query object.

Table Relationships

In addition to defining individual tables, OpenGB allows you to establish relationships between tables using foreign keys. Relationships help you connect related data across multiple tables, enabling you to model complex data structures and maintain data integrity.

To define a relationship, you can use the references method when defining a column in your schema. The references method takes the referenced table and column as arguments.

Here’s an example of defining a relationship between a posts table and a users table:

export const users = schema.table("users", {
  id: Query.uuid("id").primaryKey().defaultRandom(),
  name: Query.text("name").notNull(),
  email: Query.text("email").notNull().unique(),
  createdAt: Query.timestamp("created_at").defaultNow().notNull(),
});

export const posts = schema.table("posts", {
  id: Query.uuid("id").primaryKey().defaultRandom(),
  title: Query.text("title").notNull(),
  content: Query.text("content").notNull(),
  userId: Query.uuid("user_id")
    .references(users, users.id),
  createdAt: Query.timestamp("created_at").defaultNow().notNull(),
});

In this example, the posts table has a userId column that references the id column of the users table. This establishes a relationship between the two tables, indicating that each post is associated with a specific user.

By defining relationships using foreign keys, OpenGB ensures data integrity and consistency. It prevents orphaned records and helps maintain the referential integrity of your database.

When querying related data, you can use OpenGB’s query builder to join tables and retrieve associated records. OpenGB provides intuitive methods to perform common operations like eager loading, filtering based on related data, and more.

Defining relationships in your schemas is a fundamental aspect of designing a well-structured database. It allows you to model real-world entities and their associations, making it easier to work with and query your data effectively.

Queries

OpenGB provides a powerful query builder that allows you to interact with your database using a fluent and type-safe API.

Inserting Data

To insert data into a table, you can use the insert method:

const newUser = await ctx.db.insert(Database.users)
  .values({
    name: "John Doe",
    email: "[email protected]",
  })
  .returning();

Updating Data

To update existing data in a table, you can use the update method:

await ctx.db.update(Database.users)
  .set({ name: "Updated Name" })
  .where(Query.eq(Database.users.id, userId))
  .execute();

Querying Data

OpenGB provides a rich set of methods for querying data from your database.

select: Specify the columns to retrieve from the table.
where: Add filtering conditions to your query.
orderBy: Sort the result set based on specified columns.
limit and offset: Paginate the result set.
Aggregation functions: Perform calculations like count, avg, sum, etc.

Here’s an example query that retrieves users with a specific email:

const users = await ctx.db.query.users.findMany({
  where: Query.eq(Database.users.email, "[email protected]"),
  orderBy: Query.desc(Database.users.createdAt),
  limit: 10,
});

Querying Relationships

OpenGB’s query builder provides powerful methods to query and retrieve related data based on the relationships defined in your schemas. You can easily fetch associated records, filter based on related data, and perform eager loading to optimize your queries.

Let’s consider the previous example of the users and posts tables with a one-to-many relationship. To query posts along with their associated user data, you can use the join method:

const postsWithUsers = await ctx.db.query.posts.findMany({
  join: {
    user: true,
  },
  where: Query.eq(Database.posts.title, "My First Post"),
});

In this example, we use the join option to specify that we want to include the associated user data for each post. The query will return an array of posts, and each post object will have a user property containing the related user data.

You can also filter posts based on the related user data using the where option:

const postsByUser = await ctx.db.query.posts.findMany({
  join: {
    user: true,
  },
  where: Query.eq(Database.users.email, "[email protected]"),
});

This query retrieves all posts where the associated user’s email is ”[email protected]”. The where condition is applied to the users table, allowing you to filter posts based on the related user data.

OpenGB’s query builder also supports eager loading, which allows you to retrieve related data in a single query to avoid the N+1 problem. By specifying the relationships to eager load using the with option, OpenGB will efficiently fetch the associated data in a single query.

const usersWithPosts = await ctx.db.query.users.findMany({
  with: {
    posts: true,
  },
});

In this example, the query retrieves all users along with their associated posts. The with option is used to specify that we want to eager load the posts relationship. Each user object in the result will have a posts property containing an array of their associated posts.

OpenGB’s query builder provides a clean and intuitive way to query and retrieve related data based on the relationships defined in your schemas. It allows you to easily fetch associated records, filter based on related data, and optimize your queries using eager loading.

Migrations

OpenGB handles database migrations differently depending on the environment and module type.

Development Environment

In the development environment, OpenGB uses a feature called “DB Push” to automatically synchronize your local database with your schema changes. When you make changes to your schemas, OpenGB will detect the modifications and automatically apply them to your database.

However, it’s important to note that DB Push can potentially lead to data loss if you make breaking changes to your schemas. Therefore, it’s recommended to use DB Push only during development and not in production environments.

Production Environment and External Modules

For production environments and external modules, OpenGB uses a migration-based approach. Migrations are generated automatically when you run opengb build or opengb deploy for your local modules.

Migrations ensure that your database schema evolves in a controlled and predictable manner. They allow you to make incremental changes to your database structure while preserving existing data. OpenGB keeps track of the applied migrations and ensures that only the necessary migrations are executed when deploying your application.

Using migrations in production and for external modules is crucial to maintain data integrity and avoid conflicts or inconsistencies between different versions of your application.

Advanced

Raw SQL Queries

While OpenGB’s query builder provides a convenient and type-safe way to interact with your database, there may be cases where you need to execute raw SQL queries. OpenGB allows you to do this using the execute method and provides some additional benefits.

When writing raw SQL queries in OpenGB, you can reference table names directly from the generated Database object. This ensures that your queries are always in sync with your schema definitions. For example:

const result = await ctx.db.execute(
  Query.sql`SELECT * FROM ${Database.users} WHERE email = ${"[email protected]"}`
);

In this example, we reference the users table directly from the Database object, ensuring that the table name is always accurate.

OpenGB also automatically handles safe injection of data into your raw SQL queries. By using the Query.sql tagged template literal and passing values as arguments, OpenGB will properly escape and sanitize the data to prevent SQL injection vulnerabilities. This is similar to how Drizzle, another database library, handles raw SQL queries.

Here’s an example from the send_request.ts script:

const { rows: existingFriendRows } = await tx.execute(
  Query.sql`
    SELECT 1
    FROM ${Database.friends}
    WHERE ${Database.friends.userIdA} = ${userIdA} OR ${Database.friends.userIdB} = ${userIdB}
    FOR UPDATE
  `
);

In this example, the friends table and its columns (userIdA and userIdB) are referenced directly from the Database object. The userIdA and userIdB values are safely injected into the query using the Query.sql tagged template literal.

While raw SQL queries give you full control over the database operations, it’s generally recommended to use the query builder whenever possible to take advantage of its type safety and abstractions. However, when you do need to write raw SQL queries, OpenGB provides a convenient and safe way to do so.

Here’s a new section on transactions in OpenGB:

Transactions

Transactions are an essential concept in database management that ensure data consistency and integrity. OpenGB provides built-in support for transactions through the transaction method available on the database object.

Transactions allow you to group multiple database operations into a single atomic unit of work. This means that either all the operations within a transaction are successfully committed to the database, or none of them are applied if an error occurs. This helps maintain data consistency and prevents partial updates or inconsistencies.

OpenGB’s transaction support is powered by the underlying database engine (e.g., PostgreSQL) and follows the ACID properties:

Atomicity: All operations within a transaction are treated as a single unit. If any operation fails, the entire transaction is rolled back.
Consistency: The database remains in a consistent state before and after the transaction. Constraints and integrity rules are enforced.
Isolation: Transactions are isolated from each other, preventing interference and ensuring that concurrent transactions behave as if they were executed sequentially.
Durability: Once a transaction is committed, its changes are permanently stored in the database and can survive system failures.

To use transactions in OpenGB, you can wrap your database operations within the transaction method. Here’s an example:

const result = await ctx.db.transaction(async tx => {
  // Perform database operations using the transaction object (tx)
  const user = await tx.query.users.findFirst({
    where: Query.eq(Database.users.email, "[email protected]"),
  });

  if (user) {
    await tx.update(Database.users)
      .set({ name: "Updated Name" })
      .where(Query.eq(Database.users.id, user.id))
      .execute();
  }

  // Return the result of the transaction
  return user;
});

In this example, we use the transaction method to wrap our database operations. The transaction method takes a callback function that receives a transaction object (tx) as a parameter. We can use this transaction object to perform our database queries and mutations within the transaction.

If any error occurs within the transaction or if an exception is thrown, OpenGB will automatically roll back the transaction, ensuring that no partial changes are applied to the database. If all operations succeed, the transaction will be committed, and the changes will be persisted.

Here is an example of adjusting a user’s balance (adjust_balance.ts):

const updatedBalance = await ctx.db.transaction(async tx => {
  const wallet = await tx.query.userWallets.findFirst({
    where: Query.eq(Database.userWallets.userId, req.userId),
  });

  if (wallet) {
    const updatedBalance = wallet.balance + req.amount;
    if (updatedBalance < 0) {
      throw new RuntimeError("not_enough_funds");
    }

    await tx.update(Database.userWallets)
      .set({ balance: updatedBalance })
      .where(Query.eq(Database.userWallets.userId, req.userId))
      .execute();

    return updatedBalance;
  } else {
    // ... handle wallet creation ...
  }
});

In these examples, transactions are used to ensure data consistency and integrity across multiple database operations. By wrapping the operations within a transaction, we can guarantee that either all the changes are applied successfully or none of them are persisted if an error occurs.

Getting Started

Developing Modules

Miscellaneous

Global Database and Query Objects

Writing Schemas

Tables

Table Relationships

Queries

Inserting Data

Updating Data

Querying Data

Querying Relationships

Migrations

Development Environment

Production Environment and External Modules

Advanced

Raw SQL Queries

Transactions

Getting Started

Developing Modules

Miscellaneous

​Global Database and Query Objects

​Writing Schemas

​Tables

​Table Relationships

​Queries

​Inserting Data

​Updating Data

​Querying Data

​Querying Relationships

​Migrations

​Development Environment

​Production Environment and External Modules

​Advanced

​Raw SQL Queries

​Transactions

Global Database and Query Objects

Writing Schemas

Tables

Table Relationships

Queries

Inserting Data

Updating Data

Querying Data

Querying Relationships

Migrations

Development Environment

Production Environment and External Modules

Advanced

Raw SQL Queries

Transactions