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
andoffset
: 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.