Models
Models are a wrapper around the Query Builder that allows you to manage the queries and data in a more object-oriented way.
Creating a Model¶
To start with, we'll 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(
{
D1Orm: orm,
tableName: "users",
primaryKeys: "id",
autoIncrement: "id",
uniqueKeys: [["email"]],
},
{
id: {
type: DataTypes.INTEGER,
notNull: true,
},
name: {
type: DataTypes.STRING,
notNull: true,
defaultValue: "John Doe",
},
email: {
type: DataTypes.STRING,
},
}
);
If you're using TypeScript
You can automatically determine the type of your table using the following:
Now, let's break down what's happening here.
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 thetableName
of the model.
D1Orm Field
You don't need to specify the D1Orm
field in the model options immediately. You can do it later, using the SetOrm
method. This is useful if you want to create a model, but not initialise it immediately. It must be initialised before you can run any queries, however.
- Additionally, it will have a
primaryKey
value, which can be a string, or an array of strings - for when more than one primary key is needed. - When using the
uniqueKeys
property, you provide an array of arrays of strings - for example:[["email"], ["username", "tag"]]
. This would mean thatemail
s must be unique - as well as any combination ofusername
andtag
.
Using AutoIncrement or WithRowid?
AutoIncrement can only be used on primary key, integer columns. Additionally, there can only be one primary key when using auto increment.
WithRowid should be used when you want to use the rowid
column, like if you want to use it as your primary key. By default, the rowid
column will be omitted unless you set WithRowid to true, or add an AutoIncrement column. Note that if both are set, it will throw an error.
-
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. There are three properties in a
ModelColumn
.type
- Required. The type of the column. It's recommended that you use theDataTypes
export from the library, for exampleDataTypes.INTEGER
,DataTypes.STRING
, etc.notNull
- Optional. Whether the column can be null or not. Defaults tofalse
.defaultValue
- Optional. The default value of the column. Defaults tonull
.
What next?¶
The next step is to create the table in the database. This is done by calling the Model.CreateTable()
method. There are two strategies for this:
- default: This will attempt to create the table, and error if it already exists.
- force: This will drop and recreate the table. Warning: This will delete all data in the table!
To use it, it's as simple as
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.
Selecting Data¶
There are two ways of selecting data from the database. The first is to use the Model.First()
method which will return one result, and the second is to use the Model.All()
method, which will return an array of results.
First()¶
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.
Note
The type of user
is automatically inferred to be User | null
when using TypeScript.
This will return the first user with an ID of 1, equivalent to SELECT * FROM users WHERE id = 1 LIMIT 1
.
All()¶
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
.
Inserting Data¶
There are two methods used to insert data into the database. The first is Model.InsertOne()
, which will insert a single row, and the second is Model.InsertMany()
, which will insert multiple rows. Both accept an optional boolean parameter instructing the Query Builder to generate INSERT or REPLACE
instead of just INSERT
. This mechanism differs from Upsert by its requirement of only replacing records based on the primary key.
InsertOne()¶
This method takes between one and two parameters, the first being the data to insert. This should be an object with a key of the column name, and a value of the value to insert. It also supports an optional boolean parameter to generate to generate an INSERT or REPLACE
query instead of just INSERT
. For example:
await users.InsertOne({
name: "John Doe",
email: "[email protected]",
});
await users.InsertOne({
name: "John Doe",
email: "[email protected]",
}, true);
This will insert a new user with a name of "John Doe" and an email of "[email protected]". This is equivalent to INSERT INTO users (name, email) VALUES ("John Doe", "[email protected]")
.
InsertMany()¶
This method takes between one and two parameters, the first of 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. It also supports an optional boolean parameter to generate an INSERT or REPLACE
query instead of just INSERT
. For example:
await users.InsertMany([
{
name: "John Doe",
email: "[email protected]",
},
{
name: "Jane Doe",
email: "[email protected]",
},
]);
await users.InsertMany([
{
name: "John Doe",
email: "[email protected]",
},
{
name: "Jane Doe",
email: "[email protected]",
},
], true);
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.
Deleting Data¶
To remove data from your table, you simply call the Model.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.
This is equivalent to DELETE FROM users WHERE name = "John Doe"
.
Updating Data¶
To update data in your table, you simply call the Model.Update()
method. This takes an object with a where
property, which is used to filter the rows to update. See Query Building for more information on how to use the where
property. The other property, data
, is an object with the data to update, with keys for the column names, and values for the data to update.
This is equivalent to UPDATE users SET name = "Jane Doe" WHERE name = "John Doe"
.
Upserting Data¶
See Upserting Data.
Created: September 9, 2023