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:

import type { Infer } from "d1-orm";

type User = Infer<typeof users>;

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 the tableName 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 that emails must be unique - as well as any combination of username and tag.

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 the DataTypes export from the library, for example DataTypes.INTEGER, DataTypes.STRING, etc.
  • notNull - Optional. Whether the column can be null or not. Defaults to false.
  • defaultValue - Optional. The default value of the column. Defaults to null.

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

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.

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.

const user = await users.First({ where: { id: 1 } });

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:

InsertInsert or Replace

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:

InsertInsert or Replace

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.

await users.Delete({ where: { name: "John Doe" } });

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.

await users.Update({ where: { name: "Jane Doe" }, data: { name: "John Doe" }});

This is equivalent to UPDATE users SET name = "Jane Doe" WHERE name = "John Doe".

Upserting Data

See Upserting Data.

---

Last update: September 9, 2023
Created: September 9, 2023