eveningkid/denodb

MySQL, SQLite, MariaDB, PostgreSQL and MongoDB ORM for Deno

DenoDB

⛔️ This project is not actively maintained: expect issues, and delays in reviews

• 🗣 Supports PostgreSQL, MySQL, MariaDB, SQLite and MongoDB
• 🔥 Simple, typed API
• 🦕 Deno-ready
• Read the documentation

import { DataTypes, Database, Model, PostgresConnector } from 'https://deno.land/x/denodb/mod.ts';

const connection = new PostgresConnector({
  host: '...',
  username: 'user',
  password: 'password',
  database: 'airlines',
});

const db = new Database(connection);

class Flight extends Model {
  static table = 'flights';
  static timestamps = true;

  static fields = {
    id: { primaryKey: true, autoIncrement: true },
    departure: DataTypes.STRING,
    destination: DataTypes.STRING,
    flightDuration: DataTypes.FLOAT,
  };

  static defaults = {
    flightDuration: 2.5,
  };
}

db.link([Flight]);

await db.sync({ drop: true });

await Flight.create({
  departure: 'Paris',
  destination: 'Tokyo',
});

// or

const flight = new Flight();
flight.departure = 'London';
flight.destination = 'San Francisco';
await flight.save();

await Flight.select('destination').all();
// [ { destination: "Tokyo" }, { destination: "San Francisco" } ]

await Flight.where('destination', 'Tokyo').delete();

const sfFlight = await Flight.select('destination').find(2);
// { destination: "San Francisco" }

await Flight.count();
// 1

await Flight.select('id', 'destination').orderBy('id').get();
// [ { id: "2", destination: "San Francisco" } ]

await sfFlight.delete();

await db.close();

First steps

Setting up your database with DenoDB is a four-step process:

• Create a database, using Database (learn more about clients):

  const connection = new PostgresConnector({
    host: '...',
    username: 'user',
    password: 'password',
    database: 'airlines',
  });
  
  const db = new Database(connection);

• Create models, extending Model. table and fields are both required static attributes:

  class User extends Model {
    static table = 'users';
  
    static timestamps = true;
  
    static fields = {
      id: {
        primaryKey: true,
        autoIncrement: true,
      },
      name: DataTypes.STRING,
      email: {
        type: DataTypes.STRING,
        unique: true,
        allowNull: false,
        length: 50,
      },
    };
  }

• Link your models, to add them to your database instance:

  db.link([User]);

• Optional: Create tables in your database, by using sync(...):

  await db.sync();

• Query your models!

  await User.create({ name: 'Amelia' });
  await User.all();
  await User.deleteById('1');

Migrate from previous versions

• v1.0.21: Migrate to connectors

License

MIT License — eveningkid

denodb's Issues

is MS SQL support planned in the future?

Just that, are you going to support MS SQL in the near future?

Is there a recommended path to handling database migrations?

I probably missed it in the docs, but I was curious about how the library would handle an added or removed field on a model. Does db.sync always make sure the fields match in the db? I can imagine this being great for development lifecycle early in a project but would become dangerous in a longer living application and a more delicate migration path would be desired.

Return value of Model class .delete and .update methods

Not sure if I'm using in intended way ...

Have defined class LookupCodes extending class Model.
Have REST Api function to perform delete for existing LookupCode by id
NOTE : Focus on delete() for example, but applies to update() also.

Method #1
First step check if record exists by id for validation / messaging purposes

const lookupCode = await LookupCodes.select().find(paramId);

Interestingly LookupCode is not instantiated (unless denodb did this) but returns workable object if match found.
If find fails to locate record then lookupCode is undefined and can be used for error reporting

The returned object "lookupCode" can be used to perform update.

const result = await lookupCode.update();

This works, but I'm not sure I can interogate "result" to determine if update was truly successfull. It returns an object value.

So ..

class.find() useful for returning object and then use for further action
class.delete() useful as action is performed, but outcome unclear

Method #2
When I compare this approach with using where() + delete()

LookupCodes.where("id", id).delete()

I get a result (and not object) which indicates rowsAffected which can be used for error checking / messaging.

I probably suspect that Method #2 is the expected approach. But the class approach was handy when returning an object that could then be used.

Can class use of delete() or update() provide feedback in alternate way to indicate rowsAffected?

Hope this makes sense and apologies if use of terms not quite correct. Let me know if you need more info and whether I'm using a flawed approach that happened to do some good things.

Add field descriptor to set column name in schema to something else

I think it would be nice to have a field descriptor called fieldName or something similiar that lets you set the name of a field to something else in the table schema. This would be especially useful in cases where you have something like userName for example which is camelCase and is typically convention for field names in JS/TS but not usually convention for field names in SQL (from my understanding, snake_case is SQL convention). I understand that one of the pillars of an ORM is to abstract away SQL queries, but it would be nice to have some sort of way to keep things consistent across both your code and SQL schemas.

Camel Case Column

Hi, I'm working with existing database with camel case column. Apparently, this library tends to convert the field name into snake case. How can I use custom naming for certain column? Something like:

export class City extends Model {
  public static fields = {
    id: {
      primaryKey: true,
      autoIncrement: true,
    },
    state: DataTypes.INTEGER,
    name: DataTypes.STRING,
    zipCodeRange: {
      field: "customZipCodeRange", // force query to use this column name instead of `zip_code_range`
      type: DataTypes.STRING,
    },
    lat: DataTypes.FLOAT,
    lng: DataTypes.FLOAT,
  };
}

Thanks!

Import 'https://deno.land/x/sqlite/src/db.js' failed: 404 Not Found

Configuration for SQLite

What is the configuration for SQLite 3, I tried this one:

const db = new Database('sqlite3', { database: 'db.sqlite' });

But i got the next error:

error: TS2345 [ERROR]: Argument of type '{ database: string; }' is not assignable to parameter of type 'PostgresOptions | SQLite3Options | MySQLOptions'. Type '{ database: string; }' is missing the following properties from type 'MySQLOptions': host, username, password const db = new Database('sqlite3', {

udpate failed with escapeFn problem

When run test

import { Database, DataTypes, Model } from 'https://deno.land/x/denodb/mod.ts';
import { hash } from "https://deno.land/x/bcrypt/mod.ts";
import { assertEquals } from "../testdeps.ts";
import { mysqlOptions} from "../config/db.ts";

class User extends Model {
  static table = 'user';
  static timestamps = true;

  static fields = {
    id: {
      primaryKey: true,
      autoIncrement: true,
    },
    name: DataTypes.STRING,
    password: DataTypes.STRING,
    email: DataTypes.STRING,
  };
}
const db = new Database('mysql', mysqlOptions);
db.link([User ]);
await db.sync({ drop: false });

Deno.test("model: create", async () => {
  const password = await hash('20090909');
  const data = await User.create({
    name: 'test',
    password,
    email: '[email protected]'
  })
  assertEquals(data.affectedRows, 1)
});

Deno.test("model: update", async () => {
  const record = await User.where('id', 1).update({name: 'deno'})
  assertEquals(record.name, 'deno')
});

db.close()

Record was created, but when update, got error like this

model: update
ReferenceError: Buffer is not defined
    at escapeFn (https://deno.land/x/dex/lib/query/string.js:44:16)
    at Client_MySQL.finalEscape [as _escapeBinding] (https://deno.land/x/dex/lib/query/string.js:18:12)
    at https://deno.land/x/dex/lib/client.js:151:19
    at String.replace (<anonymous>)
    at Client_MySQL._formatQuery (https://deno.land/x/dex/lib/client.js:143:16)
    at https://deno.land/x/dex/lib/interface.js:13:28
    at Array.map (<anonymous>)
    at Builder.Target.toQuery (https://deno.land/x/dex/lib/interface.js:12:8)
    at Builder.toString (https://deno.land/x/dex/lib/query/builder.js:68:17)
    at SQLTranslator.translateToQuery (https://deno.land/x/denodb/lib/translators/sql-translator.ts:209:25)

Uncaught PostgresError: relation "column" already exists

Using postgres.

When you have two models with the same field name and a constraint on the column you run into a duplicate constraint name issue.

For example

export class ModelA extends Model {
  static table = "modela";
  static timestamps = true;

  static fields = {
    name: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false
    }
  };
}

export class ModelB extends Model {
  static table = "modelb";
  static timestamps = true;

  static fields = {
    name: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false
    }
  };
}

db.link([ModelA, ModelB]);

await db.sync({ drop: true });

You would see the exception
error: Uncaught PostgresError: relation "name" already exists
return new PostgresError(errorFields);

I think the constraint name should be prefixed with the table name just like pk's are.

Error when trying to export a database and import it into another file

I am trying to create a database in one file, export it, and then import it into another file for use in that file. When I try to export and import it, I get the following error:

error: Uncaught ReferenceError: Cannot access 'database' before initialization
database.link([MyModel]);
^
    at file:///test.js:5:1
    at <anonymous> (<anonymous>)

Here is the file where I am exporting the database:

import { Database } from 'https://deno.land/x/denodb/mod.ts';

export const database = new Database("sqlite3", {filepath: "test.db"});

And here is the file where I am importing it:

import { database } from "./import_file.js";
import { MyModel } from "./model_file.js";

database.link([MyModel]);

Awesome library by the way! Glad to see dex being included in this library, very well done!

UNIQUE constraint not working in sqlite3 connector

I create a user table using this model:

class User extends Model {
  static table = "users";
  static timestamps = true;

  static fields = {
    id: {
      primaryKey: true,
      autoIncrement: true,
    },
    name: DATA_TYPES.STRING,
    email: {
        type: DATA_TYPES.STRING,
        unique: true,
        allowNull: false,
        length: 50
      },
  };
}

The structure looks like this when opened in a DB viewer: (I use TablePlus on macOS)

CREATE TABLE `users` (`id` integer not null primary key autoincrement, `name` varchar(255), `email` varchar(50) not null, `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP);

There doesn't seem to be a UNIQUE constraint for the email column, so I'm able to insert multiple records with same email.
I am able to report this issue for the sqlite3 connector, not sure if it exists in the other two yet.

MySQL one-to-one Uncaught error can't create table (errno 150 foreign key constraint is incorrectly formed)

(Sorry for my bad English)
I've tried to define a one to one relationship and although it works if I use sqlite it doesn't work if I use MariaDB, this code throws an Uncaught error at db.sync()

class Service extends Model {
    static table = "service";
    static timestamps = true;

    static fields = {
        id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true,
            unique: true,
            allowNull: false
        }
    }
 }

 class Plan extends Model {
    static table = 'plan';
    static timestamps = true;

    static fields = {
        id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true,
            unique: true,
            allowNull: false
        },
        serviceId: Relationships.belongsTo(Service),
    }

    static service() {
        return this.hasOne(Service);
    }

    public id!: number;
    public serviceId!: number;
 }
 db.link([Service, Plan]);
 await db.sync({ drop: true });

Linking models to database via an array

Hi Arnaud.

I am trying to use linking models to database via an array:

class Manufacturers extends Model {
    static table = 'manufacturers';
    static timestamps = true;
    static fields = {
        id: {
            primaryKey: true,
            autoIncrement: true,
        },
        manufacturer: DATA_TYPES.STRING,
    }
}

class Parts extends Model {
    static table = 'parts';
    static timestamps = true;
    static fields = {
        id: {
            primaryKey: true,
            autoIncrement: true,
        },
        part: DATA_TYPES.STRING,
    }
}

db.link([Manufacturers, Parts])
await db.sync({ drop: true });

And I get an error message:

error: Uncaught RangeError: Invalid typed array length: -4
    const msgBody = new Uint8Array(msgLength);
                    ^
    at new Uint8Array (<anonymous>)
    at Connection.readMessage (https://deno.land/x/postgres/connection.ts:104:21)
    at async Connection.startup (https://deno.land/x/postgres/connection.ts:154:11)
    at async Client.connect (https://deno.land/x/postgres/client.ts:14:5)
    at async PostgresConnector._makeConnection (https://deno.land/x/denodb/lib/connectors/postgres-connector.ts:38:5)
    at async PostgresConnector.query (https://deno.land/x/denodb/lib/connectors/postgres-connector.ts:43:5)
    at async ModelInitializer.init (https://deno.land/x/denodb/lib/model-initializer.ts:26:7)
    at async Function._createInDatabase (file:///home/3f-lab/_reusable/denodb/lib/model.ts:75:5)
    at async Promise.all (index 1)

But when I do

db.link([Manufacturers])
db.link([Parts])
await db.sync({ drop: true });

the code works OK.

Could you help to correct this?

Deploing at fly.io - ' has no exported member 'open'.

Hey guys,

i'm trying deploy my app on fly.io and

[builder] error: TS2305 [ERROR]: Modu le '"../sqlite/mod"' has no exported member 'open'. [builder] open as openSQLiteFile, [builder] ~~~~ [builder] at https://deno.land/x/denodb/deps.ts [0m:12:3 [builder] [builder] TS2305 [ERROR]: Module '"../sqlite/mod"' has no exported member 'save'. [builder] save as saveSQLiteFile, [builder] ~~~~ [builder] at https://deno.land/x/denodb/deps.ts [0m:13:3 [builder] [builder] Found 2 errors. [builder] ERROR: failed to build: exit status 1 Error failed with status code: 7

it's strange because I deployed my app 05/22 and everything works fine. Today starts this error.

as I see, the dependency deno-sqlite has updated today. could be it the reason once the sqlite/mod has no exported 'save' ?

Connection Mongodb

deno run --allow-net --allow-read --allow-write --unstable app.ts

only show :
INFO load deno plugin "deno_mongo" from local "/home/nestor/http/DB/.deno_plugins/deno_mongo_2970fbc7cebff869aa12ecd5b8a1e7e4.so"
Segment violation (generated core)

regards

The system cannot find the path specified. (os error 3)

deno version 1.0.5
windows 10
mysql 10.4.11

not able to connect mongodb

const db = new Database('mongo', {
uri: "mongodb+srv://username:[email protected]/test?retryWrites=true&w=majority",
database: 'test',
});

And url encoded connection--

const db = new Database('mongo', {
uri: "mongodb%2Bsrv%3A%2F%2Fusername%3Apassword%40cluster0-ms8o9.azure.mongodb.net%2Ftest%3FretryWrites%3Dtrue%26w%3Dmajority",
database: 'test',
});

both way i am getting below error

deno run --allow-net --allow-write --allow-read --allow-plugin --unstable main.ts

Compile file:///F:/deno/main.ts
error: The system cannot find the path specified. (os error 3)

Models sometimes won't be created after sync and close for SQLite3

Reproduction Steps

1. Create two models, don't think it matters what's in them, as long as there's a primary key and at least one other column
2. After that, call await db.link([Model1, Model2]), then await db.sync(), then await db.close()
3. If both tables were created successfully, delete one of them then rerun

Expected Behaviour

Both tables should be created successfully.

Actual Behaviour

Sometimes both will be created successfully, but sometimes not all of them will be created.

Let me know if there's a misunderstanding of how these methods work or if there's any issues reproducing it. Thanks.

thread 'main' panicked at 'Op already registered: mongo_command'

note: run with RUST_BACKTRACE=1 environment variable to display a backtrace
fatal runtime error: failed to initiate panic, error 5

running error

Decimal and UUID Data type

I notice these data types are available:
export const DATA_TYPES: Fields = {
INTEGER: "integer",
FLOAT: "float",

BOOLEAN: "boolean",

STRING: "string",
TEXT: "text",

DATETIME: "date",
TIMESTAMP: "timestamp",
};

will Decimal and UUID data type be added?
As well for enum.

Offset option not found

I am trying to make a pagination, but looks like there's no option for offsetting the entries?

Contraint

How to implement cascade deletion?

Bump Deno SQLite version to 2.0.0

Please consider upgrading the SQLite version, see https://github.com/dyedgreen/deno-sqlite/releases

The new version now automatically commits changes to databases to the file-system, and generally is more in-line with how a natively compiled SQLite3 package would function 😃

How to create an unique key on Model fields

I want to create a database column and design it using DenoDB Model, in the doc example we can use primaryKey: true to mark a field (i.e. id) as a primary key, and my question is how to make a duplicate key?

Database restart - how to handle

Hi!
If im running a OAK server for a long time, and the connection to the DB breaks, how should I handle reconnecting? Should I init a new Database object, and close the old one? Is there a way to check the state of the database object?

This is the error, which I get.

BrokenPipe: Broken pipe (os error 32)   
    at unwrapResponse ($deno$/ops/dispatch_minimal.ts:63:11)
    at Object.sendAsyncMinimal ($deno$/ops/dispatch_minimal.ts:106:10)
    at async Object.write ($deno$/ops/io.ts:65:18)
    at async BufWriter.flush (https://deno.land/[email protected]/io/bufio.ts:475:25)
    at async Connection._simpleQuery (https://deno.land/x/postgres/connection.ts:280:5)
    at async Connection.query (https://deno.land/x/postgres/connection.ts:546:16)
    at async Client.query (https://deno.land/x/postgres/client.ts:25:12)
    at async PostgresConnector.query (https://deno.land/x/denodb/lib/connectors/postgres-connector.ts:46:22)
    at async Database.query (https://deno.land/x/denodb/lib/database.ts:154:21)

The system cannot find the path specified. (os error 3)

Modeling of foreign keys

Hi! I'm intrigued about your module! Just wondering: how should I go about adding foreign keys to a model?

Typing autocomplete for Model Records

Can we get typing/autocomplete for being able to update values on the model directly? The fields don't show up when running intellisense on an instance of the model.

import { DataTypes, Database, Model } from 'https://deno.land/x/denodb/mod.ts';

const db = new Database('postgres', {
  host: '...',
  username: 'user',
  password: 'password',
  database: 'airlines',
});

class Flight extends Model {
  static table = 'flights';
  static timestamps = true;

  static fields = {
    id: { primaryKey: true, autoIncrement: true },
    departure: DataTypes.STRING,
    destination: DataTypes.STRING,
    flightDuration: DataTypes.FLOAT,
  };

  static defaults = {
    flightDuration: 2.5,
  };
}

const flight = new Flight();

///////////////
// the following does not show intellisense   vvvvvvvvvv
////////////////
flight.departure = 'Dublin';
flight.destination = 'Paris';

Adding release versions

Can you add release versions for various releases of denodb? It will allow users to link to various releases of the library instead of always being on the master branch (such as linking to "https://deno.land/x/[email protected]/mod.ts" when wanting to use version 1.0.0)

Also, I'm glad to see MongoDB is supported now!

error: No such file or directory (os error 2)

Hi guys,

System

Linux Elementary OS 5.1.5
deno 1.0.5
v8 8.4.300
typescript 3.9.2

Issue

import { Database } from "https://deno.land/x/denodb/mod.ts"

export const db = new Database("postgres", {
  username: "bot",
  password: "password",
  database: "bot",
  host: "localhost",
  port: 5435,
})
console.log(db)

When I run deno run -A --unstable thiscode.ts it outputs "No such file or directory (os error 2)"

Are there model events?

I am not sure whether this is already documented but I could not find it in the doc. I would like to know if there are model events to listen for like creating, created, updating, updated etc. It will be really helpful to do tasks on such events like cleaning up when deleting etc. Also it will be nice place to set uuid, if needed (I don't know if uuid is supported out-of-the-box). Pointing it out based on my experience with eloquent.

Thanks.

Permissions and default download of deno_mongo.dll

Added to project for use against mysql database

export const db = new Database('mysql', {
database: config.DB,
host: config.DB_URI,
username: config.DB_USER,
password: config.DB_PASSWORD,
port: 3306, // optional
});

deno run failed with error 'Deno.openPlugin' is an unstable API

deno run requires the following permissions
--unstable
--allow-write=.deno_plugins

Is this correct? (readme doesn't mention)

Once running app started downloading deno_mongo.dll, this was surpise as mysql was specified as the database.

Is this a bug? Downloading mongo plugin when not required.
Does denodb download other plugins dynamically? i.e. mysql

Haven't progressed further as deno requires now --allow-plugin.

Ideally would want to use denodb without providing additional parameters - other than allow-net

Uncaught TypeError when adding table with non-autoincrementing primary key

I am trying to add a model with a primary key that's set manually rather than autoincrementing.
My schema looks something like this:

export default class NetInfoModel extends Model {
  static table = "net_info";

  static fields = {
    id: {
      primaryKey: true,
      autoIncrement: false,
    },
    name: DATA_TYPES.STRING,
  };
}

When I try to add this model using db.link I get the following error:

error: Uncaught TypeError: table[type] is not a function
      instruction = table[type](...fieldNameArgs);
                               ^
    at addFieldToSchema (https://deno.land/x/denodb/lib/helpers/fields.ts:30:32)
    at TableBuilder._fn (https://deno.land/x/denodb/lib/translators/sql-translator.ts:107:15)
    at TableBuilder.toSQL (https://deno.land/x/dex/lib/schema/tablebuilder.js:45:12)
    at SchemaCompiler_SQLite3.createTableIfNotExists (https://deno.land/x/dex/lib/schema/compiler.js:89:25)
    at SchemaCompiler_SQLite3.toSQL (https://deno.land/x/dex/lib/schema/compiler.js:72:26)
    at SchemaBuilder.toSQL (https://deno.land/x/dex/lib/schema/builder.js:81:43)
    at SchemaBuilder.Target.toQuery (https://deno.land/x/dex/lib/interface.js:9:21)
    at SchemaBuilder.toString (https://deno.land/x/dex/lib/schema/builder.js:77:15)
    at SQLTranslator.translateToQuery (https://deno.land/x/denodb/lib/translators/sql-translator.ts:170:25)
    at SQLite3Connector.query (https://deno.land/x/denodb/lib/connectors/sqlite3-connector.ts:33:36)

Here's the line where it throws the exception:
https://github.com/eveningkid/denodb/blob/master/lib/helpers/fields.ts#L30
I am not really sure what's going on in this line. I am going to have another look in a bit, but so far I am not really sure if this is expected behaviour or not and wanted to confirm.

Mysql one to one Uncaught Error: Cannot add foreign key constraint

run https://eveningkid.github.io/denodb-docs/docs/guides/one-to-one examle
throw exception :

INFO connecting 127.0.0.1:3306
INFO connected to 127.0.0.1
error: Uncaught Error: Cannot add foreign key constraint
throw new Error(error.message);
^
at Connection.nextPacket (https://deno.land/x/mysql/src/connection.ts:95:17)
at async Connection.execute (https://deno.land/x/mysql/src/connection.ts:162:19)
at async https://deno.land/x/mysql/src/client.ts:101:14
at async Client.useConnection (https://deno.land/x/mysql/src/client.ts:111:22)
at async Client.execute (https://deno.land/x/mysql/src/client.ts:100:12)
at async MySQLConnector.query (https://deno.land/x/denodb/lib/connectors/mysql-connector.ts:57:22)
at async Database.query (https://deno.land/x/denodb/lib/database.ts:154:21)
at async Function.createTable (https://deno.land/x/denodb/lib/model.ts:120:5)
at async Database.sync (https://deno.land/x/denodb/lib/database.ts:124:7)
at async file:///E:/deno-practice/practice-15-ORM-one2one/main.ts:55:3

sync tries to always create a table

Hi there.

following the examples to work on a small side-project I have.

if I use

export class SessionModel extends Model {
  static table = "sessions";
  static timestamps = true;
  static fields = {
    id: {
      primaryKey: true,
      autoIncrement: true,
    },
    startTimestamp: DATA_TYPES.INTEGER,
    endTimestamp: DATA_TYPES.INTEGER,
    createdAt: DATA_TYPES.INTEGER,
    price: DATA_TYPES.STRING,
    category: DATA_TYPES.STRING,
    subCategory: DATA_TYPES.STRING,
    state: DATA_TYPES.STRING,
    withUser: DATA_TYPES.STRING,
    notes: DATA_TYPES.STRING,
  };
}

db.link([SessionModel]);

await db.sync({ drop: false /* true */ });

, the first time it runs, it creates the tables and everything is fine.
Every time after that, it fails trying to create a table

( logs from my 2 containers, one with the postgres db, and one with the deno api )

bp-db_1   | 2020-05-24 19:40:10.668 UTC [306] ERROR:  relation "sessions" already exists
bp-db_1   | 2020-05-24 19:40:10.668 UTC [306] STATEMENT:  create table "sessions" ("id" serial primary key, "startTimestamp" integer, "endTimestamp" integer, "createdAt" integer, "price" varchar(255), "category" varchar(255), "subCategory" varchar(255), "state" varchar(255), "withUser" varchar(255), "notes" varchar(255), "created_at" timestamptz not null default CURRENT_TIMESTAMP, "updated_at" timestamptz not null default CURRENT_TIMESTAMP)
bp-api_1  | error: Uncaught PostgresError: relation "sessions" already exists
bp-api_1  |   return new PostgresError(errorFields);
bp-api_1  |          ^
bp-api_1  |     at parseError (https://deno.land/x/postgres/error.ts:105:10)
bp-api_1  |     at Connection._processError (https://deno.land/x/postgres/connection.ts:430:19)
bp-api_1  |     at Connection._simpleQuery (https://deno.land/x/postgres/connection.ts:297:20)
bp-api_1  |     at async Connection.query (https://deno.land/x/postgres/connection.ts:539:16)
bp-api_1  |     at async Client.query (https://deno.land/x/postgres/client.ts:24:12)
bp-api_1  |     at async PostgresConnector.query (https://deno.land/x/denodb/lib/connectors/postgres-connector.ts:45:21)
bp-api_1  |     at async Function._createInDatabase (https://deno.land/x/denodb/lib/model.ts:75:5)
bp-api_1  |     at async Promise.all (index 0)
bp-api_1  |     at async file:///app/db.ts:32:1

If I switch drop to true, it will wipe out everything (after the first time)

I'm sure I'm missing something. How can I make it create the table only when it doesn't exist??

Thank you.

model.drop() not resetting isCreatedInDatabase flag

Application using db.sync to recreate model in the database.

await db.sync({ drop: true });

Operation works successfully the first time, but running a 2nd time causes failure ""This model has already been initialized." as it believes model still exists in the database even though its deleted.

Discovered property _isCreatedInDatabase is not set to false when drop is called.

Not enough experience/confidence to undertake pull request, but maybe change to drop() method in model.ts akin to suggestion below..

 /** Drop a model in the database. */
  static async drop() {
    const dropQuery = this._options.queryBuilder.queryForSchema(this).table(
      this.table,
    ).dropIfExists().toDescription();
    
    return this._options.database.query(dropQuery).then(() => {this._isCreatedInDatabase = false});
  }

Edge case with field name case conversion

If I have a field named flightID for example, it's returned from DB as flightId:

import { DataTypes, Database, Model } from "https://deno.land/x/denodb/mod.ts";

const db = new Database("sqlite3", {
  filepath: "temp.db",
});

class Flight extends Model {
  static table = "flights";
  static timestamps = true;

  static fields = {
    flightID: {
      primaryKey: true,
      autoIncrement: true,
    },
    departure: DataTypes.STRING,
  };
}

db.link([Flight]);

await db.sync({ drop: true });

await Flight.create([
  {
    departure: "Paris",
  },
  {
    departure: "London",
  },
]);

console.log(await Flight.all());

Output

[
  {
      flightId: 1,
      departure: "Paris",
      createdAt: "2020-06-06 15:03:54",
      updatedAt: "2020-06-06 15:03:54"
    },
  {
      flightId: 2,
      departure: "London",
      createdAt: "2020-06-06 15:03:54",
      updatedAt: "2020-06-06 15:03:54"
    }
]

Database data changes not updated unless db is closed

I was trying to create a simple REST API using Oak just to get used to the Deno workflow. I followed the readme and succesfully created and linked my models and I managed to write to an sqlite database. Then when I decided to implement some controllers for creating records in the database I realized that as soon as the execution stopped my records where gone. I digged through your library (I'm kind of a noob so I took the chance to learn something) and I realized that the data isn't written to disk unless db.close() is called. I was wandering if I was doing something wrong or if there are some methods that I'm missing since I can't find a proper place to call that method building an API.

Issue with passing Models[] into .link() method.

So I have a situation here where I have a function for dynamically importing models into an array to then be linked to the database instance. I'm getting this error:

  Type 'Model' is missing the following properties from type 'typeof Model': prototype, table, timestamps, fields, and 30 more.
database.link(await getModels("./models"));

So my function for dynamically importing is:

import { Model } from "https://deno.land/x/denodb/mod.ts";
import { walk } from "https://deno.land/std/fs/walk.ts";

const getModels = async (path: string): Promise<Model[]> => {
  const models: Model[] = [];

  for await (const file of walk(path)) {
    if (file.isFile) {
      const module = await import(`./${file.path}`);
      models.push(module.default);
    }
  }

  return Promise.resolve(models);
};

and the call to link is:

database.link(await getModels("./models"));

I did try:

const models: Model[] = await getModels("./models");
database.link(models);

but that had the same error.

I suspect it's to do with the link method requiring models to be of (typeof Model)[] which is function. But I can't seem to get it to work.

SQLite 'Unique Index' Uncaught Error

import { Database, DataTypes, Model } from "https://deno.land/x/denodb/mod.ts";

const db = new Database("sqlite3", {
  filepath: "./db.sqlite",
});

class User extends Model {
  static table = "users";

  static timestamps = true;

  static fields = {
    id: {
      primaryKey: true,
      autoIncrement: true,
    },
    name: DataTypes.STRING,
    email: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false,
      length: 50,
    },
  };
}

db.link([User]);
await db.sync();

await User.create([
  {
    name: "Eric",
    email: "[email protected]",
  },
]);

const person = await User.all();
console.log(person);

The fist time this code runs it will generate a 'db.sqlite' file in the root of the directory. And the console.log(person) will print to the console the User.all() query. If I stop the code, change the email string, and re-run it; I get an Uncaught SqliteError.

The errors seems to happen on the sync() method. If I delete the database and remove the 'unique' key word, no error.

error: No such file or directory (os error 2)

Error:

error: No such file or directory (os error 2)

on the connection:

  const db = new Database("mongo", {
    uri: "mongodb://localhost:27017",
    database: "test",
  });

autoincrement

Hola, no se si estoy en lo cierto pero , se supone que al poner autoincremento un campo id deberia de generarse un contador en dicho campo sin tener que escribirlo manualente pero en la base de datos mondodb eso no es asi simplemente pone una cadena tipo _id de mongo pero no un campo incremental.

Must it have that behavior or is it a bug?

static table = "test";

    static fields = {     
        _id_autoincrement: {
            primaryKey: true,
            type: DATA_TYPES.INTEGER, 
            autoIncrement: true
        }, 
        field1: {
            type: DATA_TYPES.STRING,
            length: 2,   //  <-- does not throw an exception
        }
    };

Saludos

Uncaught ReferenceError: Cannot access 'Owner' before initialization Relationships.belongsTo(Owner),

How to deal with circular dependencies? I have these two files which are in a relationship. So I am getting this error Uncaught ReferenceError: Cannot access 'Owner' before initialization Relationships.belongsTo(Owner),.

// owner.ts
import {Business} from './business.ts';

export class Owner extends Model {
  // ...
  // Fetch businesses bound to this owner
  static businesses() {
    return this.hasMany(Business);
  }
}
// business.ts
import {Owner} from './owner.ts';

export class Business extends Model {
  // ...
 static fields = {
  //...
   onwnerId: Relationships.belongsTo(Owner),
  };
  // Fetch owners binded to this business
  static owner() {
    return this.hasOne(Owner);
  }
}

// db.ts
import {Owner} from './owner.ts';
import {Business} from './business.ts';


const db = new Database(...);

db.link([Business, Owner]);

Error: Don't know how to parse column type: 1042

Error: Don't know how to parse column type: 1042

I found the type for 1042 to be bpchar https://deno.land/x/postgres/oid.ts
Yet I am not sure what is the cause.

I have the model:

 static fields = {
    id: {
      primaryKey: true,
      type: DATA_TYPES.UUID,
    },
    username: {
      type: DATA_TYPES.STRING,
      unique: true,
      allowNull: false,
      length: 25,
      //validate regex no empty spaces
    },
    email: {
      type: DATA_TYPES.STRING,
      unique: true,
      allowNull: true,
      length: 35,
      //validate regex
    },
    phone_number: {
      type: DATA_TYPES.DECIMAL,
      precision: 12,
      scale: 0,
      allowNull: true,
      unique: true,
    },
    fullname: {
      type: DATA_TYPES.STRING,
      length: 40,
    },
    password: {
      type: DATA_TYPES.STRING,
      length: 60,
    },
    email_confirmed: DATA_TYPES.BOOLEAN,
    phone_number_confirmed: DATA_TYPES.BOOLEAN,
    email_hash: {
      type: DATA_TYPES.UUID,
    },
    otp_secret: {
      type: DATA_TYPES.STRING,
      length: 52,
    },
    created_at: DATA_TYPES.TIMESTAMP,
    updated_at: DATA_TYPES.TIMESTAMP,
  };

my table is:

create table tbl_user
(
    id                     uuid        not null
        constraint tbl_user_pkey
            primary key,
    username               varchar(25) not null
        constraint tbl_user_username_key
            unique,
    email                  varchar(35)                 default NULL::character varying
        constraint tbl_user_email_key
            unique,
    phone_number           numeric(12)                 default NULL::numeric
        constraint tbl_user_phone_number_key
            unique,
    fullname               varchar(40),
    password               char(60),
    email_confirmed        boolean                     default false,
    phone_number_confirmed boolean                     default false,
    email_hash             uuid,
    otp_secret             char(52),
    created_at             timestamp(0) with time zone default CURRENT_TIMESTAMP,
    updated_at             timestamp(0) with time zone default CURRENT_TIMESTAMP
);

Creating records with null fields

I'm trying to insert a new record with some null fields.

I have this model:

class User extends Model {
  static table = "users";

  static timestamps = true;

  static fields = {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
    },
    first_name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    last_name: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    username: {
      type: DataTypes.STRING,
      allowNull: true,
    },
  };
}

And when trying to insert with this snippet:

let o = {
  id: from.id,
  first_name: from.first_name,
  last_name: from.last_name ? from.last_name : null,
  username: from.username ? from.username : null
};

await User.create(o);

I get this error:

error: TS2345 [ERROR]: Argument of type '{ id: number; first_name: string; last_name: string | null; username: string | null; }' is not assignable to parameter of type 'Values | Values[]'.
  Type '{ id: number; first_name: string; last_name: string | null; username: string | null; }' is not assignable to type 'Values'.
    Property 'last_name' is incompatible with index signature.
      Type 'string | null' is not assignable to type 'FieldValue'.
        Type 'null' is not assignable to type 'FieldValue'.
      await User.create(o);

How can I insert a new record with null fields? Thank you!

I'm kinda new to TypeScript so apologies in advance if this is not an issue with your lib.

Query with where returns all records when where condition is undefined

So I was testing the ORM and I have to say that I really appreciate the simplicity of it and it is really appreciated.
My issue is more of a question than a bug that I am trying to file.

I have this table in my database called languages and I want to query a certain language by iso code.
When I hardcode the value I want, in this case, fr, this is what I get:

 await Language.select("isoCode").where("isoCode", "fr").first();

{
   schema: [Function: Language],
   select: [ "iso_code" ],
   wheres: [ { field: "iso_code", operator: "=", value: "fr" } ],
   limit: 1,
   table: "languages",
   type: "select"
 }

and that generates this SQL query:

select 1;
select "iso_code" from "languages" where "iso_code" = 'fr' limit 1

However, when I try to pass the iso code dynamically and in my case it, was undefined

await Language.select("isoCode").where("isoCode", isoCode).first(); // note isoCode is undefined

As we can see below it ignores the where filter and just does the select part of the query. Because here I have the limit set to one with .first() function call otherwise, it returns all the records.

{
   schema: [Function: Language],
   select: [ "iso_code" ],
   limit: 1,
   table: "languages",
   type: "select"
 }

 select 1;
 select "iso_code" from "languages" limit 1;

So is this intended of it's a bug?

Edit:
I am using Postgres

Custom Validators

It would be great to have custom validation.
Like mongoose: https://mongoosejs.com/docs/validation.html#custom-validators

Order by multiple columns not working as expected

I'd like to be able to sort my select results by multiple columns. After attempting to use orderBy model method and taking a quick glance in the code it seems like it's only somewhat possible by stacking together orderBy calls. After doing this, I get inconsistent results:

Using denodb model

import { DataTypes, Database, Model } from "https://deno.land/x/denodb/mod.ts";

const db = new Database("sqlite3", {
  filepath: "temp.sqlite",
});

class Flight extends Model {
  static table = "flights";
  static timestamps = true;

  static fields = {
    id: {
      primaryKey: true,
      autoIncrement: true,
    },
    departure: DataTypes.STRING,
    destination: DataTypes.STRING,
    flightDuration: DataTypes.FLOAT,
  };

  static defaults = {
    flightDuration: 2.5,
  };
}

db.link([Flight]);

await db.sync({ drop: true });

await Flight.create([
  {
    departure: "Paris",
    destination: "Tokyo",
  },
  {
    departure: "London",
    destination: "San Francisco",
  },
  {
    departure: "Toronto",
    destination: "Montreal",
  },
  {
    departure: "London",
    destination: "New York",
  },
]);

console.log(await Flight.select("*").orderBy("departure", "asc").orderBy("destination", "asc").get());

Output:

[
  {
      id: 3,
      departure: "Toronto",
      destination: "Montreal",
      flightDuration: 2.5,
      created_at: "2020-06-05 20:01:58",
      updated_at: "2020-06-05 20:01:58"
    },
  {
      id: 4,
      departure: "London",
      destination: "New York",
      flightDuration: 2.5,
      created_at: "2020-06-05 20:01:58",
      updated_at: "2020-06-05 20:01:58"
    },
  {
      id: 2,
      departure: "London",
      destination: "San Francisco",
      flightDuration: 2.5,
      created_at: "2020-06-05 20:01:58",
      updated_at: "2020-06-05 20:01:58"
    },
  {
      id: 1,
      departure: "Paris",
      destination: "Tokyo",
      flightDuration: 2.5,
      created_at: "2020-06-05 20:01:58",
      updated_at: "2020-06-05 20:01:58"
 ]

Using SQL query

select * from flights
order by departure asc, destination asc;

Output

id	departure	destination	flightDuration	created_at	updated_at
4	London	New York	2.5	2020-06-05 20:01:58	2020-06-05 20:01:58
2	London	San Francisco	2.5	2020-06-05 20:01:58	2020-06-05 20:01:58
1	Paris	Tokyo	2.5	2020-06-05 20:01:58	2020-06-05 20:01:58
3	Toronto	Montreal	2.5	2020-06-05 20:01:58	2020-06-05 20:01:58

I have not tested with Postgres and MySQL, and I quickly glanced at dex's code and it doesn't seem like they make use of multiple column ordering either.

In my web app, I can work around this by sorting by one column for now. But it would be nice to have this sort of capability in the future if it's possible. Thanks.

can not connect mysql

InvalidData: data did not match any variant of untagged enum ArgsEnum
at unwrapResponse ($deno$/ops/dispatch_json.ts:43:11)
at Object.sendAsync ($deno$/ops/dispatch_json.ts:98:10)
at async Object.connect ($deno$/net.ts:182:11)
at async Connection._connect (https://deno.land/x/mysql/src/connection.ts:45:17)
at async Connection.connect (https://deno.land/x/mysql/src/connection.ts:82:5)
at async Client.createConnection (https://deno.land/x/mysql/src/client.ts:45:5)
at async DeferredStack.pop (https://deno.land/x/mysql/src/deferred.ts:33:20)
at async Client.useConnection (https://deno.land/x/mysql/src/client.ts:109:24)
at async Client.execute (https://deno.land/x/mysql/src/client.ts:100:12)
at async MySQLConnector.query (https://deno.land/x/denodb/lib/connectors/mysql-connector.ts:57:22)

deno 1.1.0
v8 8.4.300
typescript 3.9.2

Getting a error on one to many relationships, with percona server Mysql 5.7

Hey :),

i just getting a error with this example https://eveningkid.github.io/denodb-docs/docs/guides/one-to-many with the percona server mysql 5.7

error: Uncaught Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alter table `businesses` add constraint `businesses_ownerid_foreign` foreign key' at line 2

throw new Error(error.message);

at Connection.nextPacket (https://deno.land/x/mysql/src/connection.ts:95:17)
    at async Connection.execute (https://deno.land/x/mysql/src/connection.ts:162:19)
    at async https://deno.land/x/mysql/src/client.ts:101:14
    at async Client.useConnection (https://deno.land/x/mysql/src/client.ts:111:22)
    at async Client.execute (https://deno.land/x/mysql/src/client.ts:100:12)
    at async Function._createInDatabase (https://deno.land/x/denodb/lib/model.ts:80:5)
    at async Database.sync (https://deno.land/x/denodb/lib/database.ts:94:7)

Is mysql 5.7 not supported, or is it a bug?