D1 Database Reference

This reference guide is for Cloudflare's new D1 Database. As D1 is just SQLite, much of this reference is just helpful hints we found for SQLite. We will update this reference guide over time.

Accessing Remote Database Locally

When running in local development, if you want to access the remote the database, you need to change npm to: wrangler dev --remote true I'm not quite sure at this point, how you interact with the local database.

Primary Key: AUTOINCREMENT or Not?

What's strange is that for many Cloudflare Tutorials, you will see the primary key in the database, created like this:

 id integer PRIMARY KEY AUTOINCREMENT

or sometimes like this:

 id integer PRIMARY KEY 

Sometimes, the AUTOINCREMENT is left out. What is the correct way to create the primary key? Use AUTOINCREMENT or not?

In SQLite's Documentation for AUTOINCREMENT, it is specifically noted that AUTOINCREMENT "should be avoided if not strictly needed." Basically, to keep it simple, SQLite already has something called ROWID which manages something akin to AUTOINCREMENT.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

The trouble with the ROWID is that:

If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

Based on the above, my thinking is that you should never use AUTOINCREMENT for the primary key, whenever it doesn't matter to you to possiby reuse ROWIDs. However, if you are using the "id" as a value for a foreign key, then you should probably use AUTOINCREMENT, because if by accident a row was deleted from a table that used an "id", which serves as a foreign key, and the previously deleted ROWID was used, you can easily have corrupted data, depending on how you set up your foreign key.

So to sum up, use AUTOINCREMENT for any table where the primary key serves as a foreign key in another table. Don't use it for tables where there id is just there to keep track of the row. It's just not necessary. I guess there might be other cases where making sure an ID is never reused is important, and if you come across that situation, then obviously use AUTOINCREMENT to be safe.

Boolean

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). Sample Schema could be:

do_not_ship INTEGER NOT NULL DEFAULT 0 CHECK(do_not_ship IN (0,1))

DATE

SQLite doesn't have a Date field, so you need to store the date as a string or integer. For Date and Time functions, see Date and Time Functions. I think it is better to use an integer, so based on documents online I came up with something like this:

created_at INTEGER DEFAULT (cast(strftime('%s', 'now') as INTEGER)),
updated_at INTEGER DEFAULT (cast(strftime('%s', 'now') as INTEGER))

The strftime() routine returns the date formatted according to the format string specified as the first argument. %s is seconds since 1970-01-01.

On the frontend, you can handle the updated_at by doing this:

const now = new Date().getTime(); // The getTime() method of Date instances returns the number of milliseconds for this date since the epoch, w
data.createdOn = now / 1000; //see why below.
data.updatedOn = now / 1000;

Why divide by 1,000? The problem is just that getTime is in milliseconds and the SQLite is in seconds, so you need to divide the JS date by 1,000.