//-----------------------------------------------------
// lynsei database cheat sheet
//-----------------------------------------------------
// export postgres to json schema for use as rethink tables
var PostgresSchema = require('pg-json-schema-export');
var connection =
user: 'postgres',
password: '123',
host: 'localhost',
port: 5432,
database: 'thedb'
};
PostgresSchema.toJSON(connection, 'public')
.then(function (schemas) {
// handle json object
})
.catch(function (error) {
// handle error
});
// take the schemas and dump them into rethinkdb:
/*
justonepixel.com/thinky/documentation/relations/
npm install thinky
www.js-data.io/v2.9/docs/examples
npm install --save js-data
*/
//-----------------------------------------------------
// connect with options
//-----------------------------------------------------
var thinky = require('thinky')({
"user": "test",
"password": "",
"port": "",
"db": "",
"host": ""
});
var type = thinky.type;
// Create a model - the table is automatically created
var Post = thinky.createModel("Post", {
id: type.string(),
title: type.string(),
content: type.string(),
idAuthor: type.string()
});
// or with json schema support
const thinkagain = require('thinkagain')(/* rethinkdbdash options */);
// Create a model - the table is automatically created
let Post = thinkagain.createModel('Post', {
type: 'object',
properties: {
id: { type: 'string' },
title: { type: 'string' },
content: { type: 'string' },
idAuthor: { type: 'string' }
},
required: [ 'title' ]
});
//-----------------------------------------------------
// create a post and author with post joining to author
//-----------------------------------------------------
// Create a new post
var post = new Post({
title: "Hello World!",
content: "This is an example."
});
// Create a new author
var author = new Author({
name: "Michel"
});
// Join the documents
post.author = author;
//-----------------------------------------------------
// define a model
//-----------------------------------------------------
var Author = thinky.createModel("Author", {
id: type.string(),
name: type.string()
});
var Post = thinky.createModel("Post", {
id: type.string(),
title: type.string(),
content: type.string(),
authorId: type.string()
});
Author.hasMany(Post, "posts", "id", "authorId");
Post.belongsTo(Author, "author", "authorId", "id");
//-----------------------------------------------------
// thinky connection options
//-----------------------------------------------------
Options for the drivers:
max: the maximum number of connections in the pool, default 1000
buffer: the minimum number of connections available in the pool, default 50
timeoutError: number of milliseconds before reconnecting in case of an error,
default 1000
timeoutGb: number of milliseconds before removing a connection that has not been used,
default 60*60*1000
host: host of the RethinkDB server, default "localhost"
port: client port of the RethinkDB server, default 28015
db: the default database, default "test"
user/password: the user/password to access the database.
Options for the schemas
validate: can be "onsave" or "oncreate". The default value is "onsave"
timeFormat: can be "native" or "raw". The default value is "native"
r: An instance of rethinkdbdash
Global options
createDatabase: A boolean to indicate if thinky should create the database, default true.
//-----------------------------------------------------
// jsdata connections
//-----------------------------------------------------
// npm install --save js-data
// commonjs
const JSData = require('js-data');
const {Mapper, version} = JSData;
console.log(version.full);
const UserService = new Mapper({
name: 'user'
});
// amd
define(['js-data', (JSData) => {
const {Mapper, version} = JSData;
console.log(version.full);
const UserService = new Mapper({
name: 'user'
});
}])
//-----------------------------------------------------
// data objects using auto-mapper
//-----------------------------------------------------
// the reason I'm putting this in here is that most ORM
// systems do not use rethinkdb, but I was going to use
// it with either Thinky ORM or js-data mappers. however
// I want to use a well supported ORM that has no chance
// of deprecation, like Sequelize or Primsa. They auto
// generate their DAO objects, and I could easily map
// these objects to json data using auto-mapper.
// This would allow me to move data in and out of the
// rethinkdb adapter using objects which could also be
// easily translated to the ORM. I want to use rethinkdb
// for serverless layers because Postgres doesn't translate
// well to serverless apps and I'd like to avoid using postgres
// as the bearer of heavy-traffic or heavy read/writes and
// instead just leverage rethink for such operations and
// create an architecture where rethink is constantly
// buffering information to collection agents that are simply
// apis reading from serverless rethinkdb Node.js.
// In my serverless model, all NodeJS/TS containers that are
// operating as "worker nodes" are simply rethinkdb containers
// operating deno applications at the edge cache.
// in this model I don't believe there is a need to run
// javascript service workers unless you have many heavy async
// operations, and in that case you could cache those locally
// using indexdb ops:
// https://github.com/google/lovefield/blob/master/demos/todo/README.md
// then use a workbox service worker to push data to the rethinkdb Node.js
// but that may just entirely be overkill. Rethinkdb is really responsive
// and incredibly fast at reading/writing data as a cache layer.
//
https://www.npmjs.com/package/auto-mapping
//-----------------------------------------------------
// mikrorm connections
//-----------------------------------------------------
// mikrorm install
// yarn add @mikro-orm/core @mikro-orm/postgresql @mikro-orm/reflection. # for postgresql
// or
// npm i -s @mikro-orm/core @mikro-orm/postgresql @mikro-orm/reflection. # for postgresql
// npx mikro-orm generate-entities --dump # Dumps all generated entities
// npx mikro-orm generate-entities --save --path=./my-entities # Saves entities into given directory
// ts-morph based discovery?
// for nestjs
// yarn add @mikro-orm/core @mikro-orm/nestjs @mikro-orm/postgresql # for postgresql
// https://mikro-orm.io/docs/usage-with-nestjs
// attempt to use adminjs:
// https://demo.adminjs.co/admin
npm install @adminjs/express
npm install express express-formidable
//tsconfig.json / jsconfig.json:
"experimentalDecorators": true,
"emitDecoratorMetadata": true,
"esModuleInterop": true,
//bootstrap:
const orm = await MikroORM.init({
entities: [Author, Book, BookTag],
dbName: 'my-db-name',
type: 'mongo', // one of `mongo` | `mysql` | `mariadb` | `postgresql` | `sqlite`
clientUrl: '...', // defaults to 'mongodb://localhost:27017' for mongodb driver
});
console.log(orm.em); // access EntityManager via `em` property
//-----------------------------------------------------
// typeorm connections (deprecated)
//-----------------------------------------------------
//-----------------------------------------------------
// Sequelize
//-----------------------------------------------------
npm install sequelize-auto
$ sequelize-auto -l -o "./models" -d sequelize_auto_test -h localhost -u my_username -p 5432 -x my_password -e postgres
* -l mode for typescript
Produces a file/files such as ./models/User.js which looks like:
import Sequelize, { DataTypes, Model, Optional } from 'sequelize';
import type { Customer, CustomerId } from './customer';
import type { OrderItem, OrderItemId } from './order_item';
export interface OrderAttributes {
id: number;
orderDate: Date;
orderNumber?: string;
customerId: number;
totalAmount?: number;
status: 'PROCESSING' | 'SHIPPED' | 'UNKNOWN';
}
export type OrderPk = "id";
export type OrderId = Order[OrderPk];
export type OrderCreationAttributes = Optional<OrderAttributes, OrderPk>;
export class Order extends Model<OrderAttributes, OrderCreationAttributes> implements OrderAttributes {
id!: number;
orderDate!: Date;
orderNumber?: string;
customerId!: number;
totalAmount?: number;
status!: 'PROCESSING' | 'SHIPPED' | 'UNKNOWN';
// Order belongsTo Customer via customerId
customer!: Customer;
getCustomer!: Sequelize.BelongsToGetAssociationMixin<Customer>;
setCustomer!: Sequelize.BelongsToSetAssociationMixin<Customer, CustomerId>;
createCustomer!: Sequelize.BelongsToCreateAssociationMixin<Customer>;
// Order hasMany OrderItem via orderId
orderItems!: OrderItem[];
getOrderItems!: Sequelize.HasManyGetAssociationsMixin<OrderItem>;
setOrderItems!: Sequelize.HasManySetAssociationsMixin<OrderItem, OrderItemId>;
addOrderItem!: Sequelize.HasManyAddAssociationMixin<OrderItem, OrderItemId>;
addOrderItems!: Sequelize.HasManyAddAssociationsMixin<OrderItem, OrderItemId>;
createOrderItem!: Sequelize.HasManyCreateAssociationMixin<OrderItem>;
removeOrderItem!: Sequelize.HasManyRemoveAssociationMixin<OrderItem, OrderItemId>;
removeOrderItems!: Sequelize.HasManyRemoveAssociationsMixin<OrderItem, OrderItemId>;
hasOrderItem!: Sequelize.HasManyHasAssociationMixin<OrderItem, OrderItemId>;
hasOrderItems!: Sequelize.HasManyHasAssociationsMixin<OrderItem, OrderItemId>;
countOrderItems!: Sequelize.HasManyCountAssociationsMixin;
static initModel(sequelize: Sequelize.Sequelize): typeof Order {
Order.init({
id: {
autoIncrement: true,
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true
},
orderDate: {
type: DataTypes.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
field: 'OrderDate'
},
orderNumber: {
type: DataTypes.STRING(10),
allowNull: true,
field: 'OrderNumber'
},
customerId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: 'customer',
key: 'Id'
},
field: 'CustomerId'
},
totalAmount: {
type: DataTypes.DECIMAL(12,2),
allowNull: true,
defaultValue: 0.00,
field: 'TotalAmount'
},
status: {
type: DataTypes.ENUM('PROCESSING','SHIPPED','UNKNOWN'),
allowNull: false,
defaultValue: "UNKNOWN",
field: 'Status'
}
}, {
sequelize,
tableName: 'order',
timestamps: false,
});
return Order;
}
}
Example init-models.ts:
import { Sequelize } from "sequelize";
import { Customer, CustomerAttributes, CustomerCreationAttributes } from "./customer";
import { Order, OrderAttributes, OrderCreationAttributes } from "./order";
import { OrderItem, OrderItemAttributes, OrderItemCreationAttributes } from "./order_item";
import { Product, ProductAttributes, ProductCreationAttributes } from "./product";
import { Supplier, SupplierAttributes, SupplierCreationAttributes } from "./supplier";
export {
Customer, CustomerAttributes, CustomerCreationAttributes,
Order, OrderAttributes, OrderCreationAttributes,
OrderItem, OrderItemAttributes, OrderItemCreationAttributes,
Product, ProductAttributes, ProductCreationAttributes,
Supplier, SupplierAttributes, SupplierCreationAttributes,
};
export function initModels(sequelize: Sequelize) {
Customer.initModel(sequelize);
Order.initModel(sequelize);
OrderItem.initModel(sequelize);
Product.initModel(sequelize);
Supplier.initModel(sequelize);
Order.belongsTo(Customer, { as: "customer", foreignKey: "customerId"});
Customer.hasMany(Order, { as: "orders", foreignKey: "customerId"});
OrderItem.belongsTo(Order, { as: "order", foreignKey: "orderId"});
Order.hasMany(OrderItem, { as: "orderItems", foreignKey: "orderId"});
OrderItem.belongsTo(Product, { as: "product", foreignKey: "productId"});
Product.hasMany(OrderItem, { as: "orderItems", foreignKey: "productId"});
Product.belongsTo(Supplier, { as: "supplier", foreignKey: "supplierId"});
Supplier.hasMany(Product, { as: "products", foreignKey: "supplierId"});
return {
Customer: Customer,
OrderItem: OrderItem,
Order: Order,
Product: Product,
Supplier: Supplier,
};
import Sequelize, { DataTypes, Model, Optional } from 'sequelize';
import type { Customer, CustomerId } from './customer';
import type { OrderItem, OrderItemId } from './order_item';
export interface OrderAttributes {
id: number;
orderDate: Date;
orderNumber?: string;
customerId: number;
totalAmount?: number;
status: 'PROCESSING' | 'SHIPPED' | 'UNKNOWN';
}
export type OrderPk = "id";
export type OrderId = Order[OrderPk];
export type OrderCreationAttributes = Optional<OrderAttributes, OrderPk>;
export class Order extends Model<OrderAttributes, OrderCreationAttributes> implements OrderAttributes {
id!: number;
orderDate!: Date;
orderNumber?: string;
customerId!: number;
totalAmount?: number;
status!: 'PROCESSING' | 'SHIPPED' | 'UNKNOWN';
// Order belongsTo Customer via customerId
customer!: Customer;
getCustomer!: Sequelize.BelongsToGetAssociationMixin<Customer>;
setCustomer!: Sequelize.BelongsToSetAssociationMixin<Customer, CustomerId>;
createCustomer!: Sequelize.BelongsToCreateAssociationMixin<Customer>;
// Order hasMany OrderItem via orderId
orderItems!: OrderItem[];
getOrderItems!: Sequelize.HasManyGetAssociationsMixin<OrderItem>;
setOrderItems!: Sequelize.HasManySetAssociationsMixin<OrderItem, OrderItemId>;
addOrderItem!: Sequelize.HasManyAddAssociationMixin<OrderItem, OrderItemId>;
addOrderItems!: Sequelize.HasManyAddAssociationsMixin<OrderItem, OrderItemId>;
createOrderItem!: Sequelize.HasManyCreateAssociationMixin<OrderItem>;
removeOrderItem!: Sequelize.HasManyRemoveAssociationMixin<OrderItem, OrderItemId>;
removeOrderItems!: Sequelize.HasManyRemoveAssociationsMixin<OrderItem, OrderItemId>;
hasOrderItem!: Sequelize.HasManyHasAssociationMixin<OrderItem, OrderItemId>;
hasOrderItems!: Sequelize.HasManyHasAssociationsMixin<OrderItem, OrderItemId>;
countOrderItems!: Sequelize.HasManyCountAssociationsMixin;
static initModel(sequelize: Sequelize.Sequelize): typeof Order {
Order.init({
id: {
autoIncrement: true,
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true
},
orderDate: {
type: DataTypes.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
field: 'OrderDate'
},
orderNumber: {
type: DataTypes.STRING(10),
allowNull: true,
field: 'OrderNumber'
},
customerId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: 'customer',
key: 'Id'
},
field: 'CustomerId'
},
totalAmount: {
type: DataTypes.DECIMAL(12,2),
allowNull: true,
defaultValue: 0.00,
field: 'TotalAmount'
},
status: {
type: DataTypes.ENUM('PROCESSING','SHIPPED','UNKNOWN'),
allowNull: false,
defaultValue: "UNKNOWN",
field: 'Status'
}
}, {
sequelize,
tableName: 'order',
timestamps: false,
});
return Order;
}
}
Example init-models.ts:
import { Sequelize } from "sequelize";
import { Customer, CustomerAttributes, CustomerCreationAttributes } from "./customer";
import { Order, OrderAttributes, OrderCreationAttributes } from "./order";
import { OrderItem, OrderItemAttributes, OrderItemCreationAttributes } from "./order_item";
import { Product, ProductAttributes, ProductCreationAttributes } from "./product";
import { Supplier, SupplierAttributes, SupplierCreationAttributes } from "./supplier";
export {
Customer, CustomerAttributes, CustomerCreationAttributes,
Order, OrderAttributes, OrderCreationAttributes,
OrderItem, OrderItemAttributes, OrderItemCreationAttributes,
Product, ProductAttributes, ProductCreationAttributes,
Supplier, SupplierAttributes, SupplierCreationAttributes,
};
export function initModels(sequelize: Sequelize) {
Customer.initModel(sequelize);
Order.initModel(sequelize);
OrderItem.initModel(sequelize);
Product.initModel(sequelize);
Supplier.initModel(sequelize);
Order.belongsTo(Customer, { as: "customer", foreignKey: "customerId"});
Customer.hasMany(Order, { as: "orders", foreignKey: "customerId"});
OrderItem.belongsTo(Order, { as: "order", foreignKey: "orderId"});
Order.hasMany(OrderItem, { as: "orderItems", foreignKey: "orderId"});
OrderItem.belongsTo(Product, { as: "product", foreignKey: "productId"});
Product.hasMany(OrderItem, { as: "orderItems", foreignKey: "productId"});
Product.belongsTo(Supplier, { as: "supplier", foreignKey: "supplierId"});
Supplier.hasMany(Product, { as: "products", foreignKey: "supplierId"});
return {
Customer: Customer,
OrderItem: OrderItem,
Order: Order,
Product: Product,
Supplier: Supplier,
};
// sequelize aws lambda considerations:
They have spent a ton of time on this:
https://sequelize.org/v7/manual/aws-lambda.html
// connections to the database on function invokation should be closed after the function exits!
You can see that they had trouble with this:
If you just want to learn how to properly configure sequelize connection pooling for AWS Lambda, all you need to know is that
sequelize connection pooling does not get along well with AWS Lambda's Node.js runtime and it ends up causing more problems than it solves. Therefore,
the most appropriate configuration is to use pooling within the same invocation and avoid pooling across invocations (i.e. close all connections at the end):
Created
February 10, 2022 11:08
-
-
Save lynsei/2cf44ebe319557702891c315b153bab8 to your computer and use it in GitHub Desktop.
[Public ORM Notes] #recent #notes on a bunch of #orm systems I've #evaluated for using with #rethinkdb and a system I'm constructing for #crypto
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment