Models are a wrapper around the Query Builder that allows you to manage the queries and data in a more object-oriented way.
To start with, we'll make a type for our model. This is optional, but it's recommended to make a type for your model so you can have improved type safety.
type User = {
id: number;
name: string;
email: string | undefined;
};
Now we need to define the Model structure.
import { D1Orm, DataTypes, Model } from "d1-orm";
// We must initialise an ORM to use the Model class. This is done by passing in a D1Database instance (in this case it's bound to the `env.DB` environment variable).
const orm = new D1Orm(env.DB);
// Now, to create the model:
const users = new Model<User>(
{
D1Orm: orm,
tableName: "users",
},
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
notNull: true,
},
name: {
type: DataTypes.STRING,
notNull: true,
defaultValue: "John Doe",
},
email: {
type: DataTypes.STRING,
unique: true,
},
}
);
Both arguments are required. The first argument is the model options, and the second argument is the model schema.
The model options should contain the D1Orm instance we created earlier, and the tableName of the model.
The model schema should contain the columns of the model. The key of the object should be the name of the column, and the value should be the column options. See ModelColumn for API reference.
The next step is to create the table in the database. This is done by calling the CreateTable method on the model. There are two strategies for this:
To use it, it's as simple as
await users.CreateTable({ strategy: "default" /* or "force", see above */ });
This will either return successfully or throw an error that your table already exists.
It's not recommended to use the force strategy in production, but it's useful for development. You shouldn't call this method on each worker request: it's very expensive and may take some time. Instead, it should be called when you deploy your Worker.
Alternatively, you can create your tables manually via the wrangler CLI and avoid using this method altogether.
That's it! You've now created a model. You can now use the model to query the database.
There are two ways of selecting data from the database. The first is to use the First method which will return one result, and the second is to use the All method, which will return an array of results.
Let's start with the First method. This method will return the first result that matches the query. It takes a single argument, which is an object containing a Where clause. See Query Building for more information on how to use the Where clause. This should be an object with a key of the column name, and a value of the value to match.
const user = await users.First({ where: { id: 1 } });
This will return the first user with an ID of 1, equivalent to SELECT * FROM users WHERE id = 1 LIMIT 1.
Now for the All method. This has one parameter, an object with where, limit, offset and orderBy properties. These are all optional, and are used to filter the results. See Query Building for more information on how to use these properties.
const users = await users.All({
where: { name: "John Doe" },
limit: 10,
offset: 0,
orderBy: ["id"],
});
This will return the first 10 users with a name of "John Doe", ordered by ID, equivalent to SELECT * FROM users WHERE name = "John Doe" ORDER BY "id" LIMIT 10 OFFSET 0.
There are two methods used to insert data into the database. The first is InsertOne, which will insert a single row, and the second is InsertMany, which will insert multiple rows.
This method takes just one parameter, which is the data to insert. This should be an object with a key of the column name, and a value of the value to insert. For example:
await users.InsertOne({
name: "John Doe",
email: "john-doe@gmail.com",
});
This will insert a new user with a name of "John Doe" and an email of "john-doe@gmail.com". This is equivalent to INSERT INTO users (name, email) VALUES ("John Doe", "john-doe@gmail.com").
This method takes just one parameter, which is an array of data to insert. This should be an array of objects with a key of the column name, and a value of the value to insert. For example:
await users.InsertMany([
{
name: "John Doe",
email: "john-doe@gmail.com",
},
{
name: "Jane Doe",
email: "jane-doe@gmail.com",
},
]);
This will insert two new users into our table. This uses D1's Batched statements, where an array of statements is sent to the database in a single request.
To remove data from your table, you simply call the Delete() method. This has one parameter, an object, with a where property. This is used to filter the rows to delete. See Query Building for more information on how to use the where property.
await users.Delete({ where: { name: "John Doe" } });
This is equivalent to DELETE FROM users WHERE name = "John Doe".
To update data in your table, you simply call the Update() method. This has two parameters, the first is an object with a where property. This is used to filter the rows to update. See Query Building for more information on how to use the where property. The second parameter is an object with the data to update. This should be an object with a key of the column name, and a value of the value to update.
await users.Update({ where: { name: "Jane Doe" } }, { name: "John Doe" });
This is equivalent to UPDATE users SET name = "Jane Doe" WHERE name = "John Doe".
See Upserting Data.
Generated using TypeDoc