Sqlite supports a boolean datatype which gets automatically coerced into a 0 or 1.

The new datatype was added, along with type coercion to support all the query types.

The change to support this ends up being fairly trivial, but I would love feedback and any thoughts on the implementation or suggestions on alteration.

I think this implementation sets it up to allow other types of coercion with even less updates.

Wanted to see if you have any ideas on how to structure a multi-column unique key?

The following model:

        username: {
            type: DataTypes.STRING,
            unique: true,
            notNull: true,
        },
        session: {
            type: DataTypes.STRING,
            unique: true,
            notNull: true,
        },

Producing a table like:

CREATE TABLE users (
usename text not null unique,
session text not null unique
);

produces a table which does not allow the same username to be inserted into multiple records.

In reality what is needed is:

        username: {
            type: DataTypes.STRING,
            notNull: true,
        },
        session: {
            type: DataTypes.STRING,
            notNull: true,
        },
        uniqueKeys: { [username, session], ... },

which when generated would produce

CREATE TABLE users (
usename text not null,
session text not null,
    UNIQUE (username, session) // This means the combination must be unique.
);

The benefit of this type of approach means foreign key definition support would be able to piggy back. Foreign keys have a few more options, but the basics are the most important part...

https://github.com/Interactions-as-a-Service/d1-orm/commit/b214e74a5c7c56fb593008b87bf819affb52fb47

Does not provide support for a boolean default value.

Is your feature request related to a problem? Please describe. I was trying to create a schema but the boolean type was missing

Describe the solution you'd like Adding the boolean type

Describe alternatives you've considered Using ints

Additional context I could make a pull request to add this if you want me to.

In README.md, the Model options propety name primaryKeys is incorrect, which is now primaryKey. I fixed it.

Resource: https://github.com/Interactions-as-a-Service/d1-orm/blob/e418a4f7455652853df951d1da6d8ca447d24391/src/model.ts#L22

Describe the bug When specifying a string defaultValue for a given model, the value is "encoded" with double quotes in the create statement. While this is technically ok with SQLite, it is actually not best practice (https://www.sqlite.org/quirks.html). While I understand that this is not a high-priority issue, it is causing downstream issues for me when use this for local development, so I thought I would submit an issue in case it is hitting anyone else. I'm happy to submit a PR for this (tiny) fix, if that would be accepted.

The following section of a model:

...
name: {
  type: DataTypes.STRING,
  notNull: true,
  defaultValue: "John Doe",
},
...

runs into this on line 138 of current main:

defaultStr = `"${column.defaultValue}"`;

Expected behavior

That line should probably be

defaultStr = `'${column.defaultValue}'`;

Closes #53

This change restructures the way that primary keys, auto increment keys and unique keys are provided. This allows for multiple unique keys to be provided for a model. These fields have been moved from the ModelColumn type to the first parameter in the Model constructor.

This also fixes non-string default values, such as numbers & booleans, by suppling them as is

Docs

These are now located at https://docs.interactions.rest/d1-orm/

Models no longer require you to specify a definition when you create them. Instead, you can just pass in an object and the model will infer the types for you. See the following example:

import { Model, DataTypes } from "d1-orm";
+ import type { Infer } from "d1-orm";

- const users = new Model<User>(
+ const users = new Model(
	{
		tableName: "users",
		D1Orm: MyD1OrmInstance,
	},
	{
		name: {
                     type: DataTypes.TEXT,
                },
		age: {
                    type: DataTypes.INTEGER,
                }
	}
);

+ type User = Infer<typeof users>;
- type User = {
-   name: string,
-   age: number
- }

What?

The NPM package link used a relative path which was opening a non existent url, on this PR we change this to an absolute URL to avoid this issue.

This PR was opened by the Changesets release GitHub action. When you're ready to do a release, you can merge this and the packages will be published to npm automatically. If you're not ready to do a release yet, that's fine, whenever you add more changesets to main, this PR will be updated.

Releases

[email protected]

Minor Changes

• #21 b2559ef Thanks @Skye-31! - [breaking] feat: Switch to use a QueryBuilder instead of duplicate code in the Model class

  This will be much more expandable in future to support things like advanced where querying, using operators other than AND, joins, etc.

Patch Changes

• #26 bc18cce Thanks @Skye-31! - Chore: Add a build test

  Also ensure that the lib is built before publishing.

• #25 1750a55 Thanks @Skye-31! - Chore: readable guides for interacting with the library Closes #22

Todo: Tests:

• [x] Select
• [x] Update
• [x] Insert
• [x] Delete

Implementations:

• [x] Select
• [x] Update
• [x] Insert
• [x] Delete

Refactoring Model to use this:

• [x] First
• [x] All
• [x] InsertOne
• [x] InsertMany
• [x] Update
• [x] Upsert?

The default primary key does work but is not suitable for all situations, there are use cases that need an email or an api key to be used as the conflicting column.

Adding on to #53 ...

For foreign keys there are a number of options a foreign key may have, but from a definition standpoint adding the foreign key to the column definition make the most sense as each column may only reference a single foreign column.

What I propose is the following:

{
  username: {
    type: DataTypes.STRING,
    unique: "some-arbitrary-key",
  },
  session: {
    type: DataTypes.STRING,
    foreignKey: {
      table: "session-table", // example like Model.tableName
      column: "session-table-field-name", // example like Model.columnName
      onDelete?: {
          cascade?: boolean,
          restrict?: boolean,
          setNull?: boolean,
          setDefault?: boolean,
      },
      onUpdate?: {
          cascade?: boolean,
          restrict?: boolean,
          setNull?: boolean,
          setDefault?: boolean,
      },
    },
  },
}

I think this approach is fairly straight-forward and easy to follow.

Thoughts?