Security News
tea.xyz Spam Plagues npm and RubyGems Package Registries
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
Readme
npm i oreo pg@6 mysql
import oreo from 'oreo'
const db = oreo({
driver: 'pg',
hosts: ['localhost'],
name: 'my_db',
user: 'root',
pass: '',
ssl: true
}
db.onReady(async () => {
// Assuming you have a table "artists"
// Get an artist by primary key
const artist = await db.artists.get(id)
console.log(artist)
})
† see the example database schema below
import oreo from 'oreo'
// initialize oreo: auto-detects the schema and determines writable/read-only hosts
const db = oreo({
driver: 'pg',
hosts: ['localhost:5432'],
name: 'my_db',
user: 'username',
pass: 'password',
ssl: true,
debug: console.log, // optional
memoize: 150, // optional duration in ms to memoize rows
cache: redisClient, // optional
Promise: Promise, // optional, default: global.Promise
models: {}, // optional
schema: {} // optional skips auto-detect schema
})
async function examples() {
// Insert a new book, its author and some reviews (in a single transaction)
let book = await db.books.insert({
title: 'Fear and Loathing in Las Vegas',
author: {
name: 'Hunter S.Thompson'
},
reviews: [ // shorthand for 'book:reviews'
{ stars: 5, body: 'Psychadelic!'},
{ stars: 4, body: 'Bizarre, unpredictable yet strangely alluring.'}
]
})
console.log(book) // { id: 1, title: Fear and Loathing in Las Vegas, author_id: 1 }
// Get the book's author (1-to-1 linked row)
await book.hydrate('author')
console.log(book.author) // { id: 1, name: Hunter S. Thompson }
// Get the book's reviews (1-to-many linked rows)
await book.hydrate('reviews')
console.log(book.reviews) // array
// Update a book
await book.update({
title: 'The Rum Diary'
})
console.log(book) // { id: 1, title: The Rum Diary, author_id: 1 }
// Delete a book
await book.delete()
console.log(book) // {}
// Get an author by primary key
let author = await db.authors.get(1)
console.log(author) // { id: 1, name: Hunter S. Thompson }
// Get multiple authors by primary key
let authors = await db.authors.mget([1])
console.log(authors) // [ { id: 1, name: Hunter S. Thompson } ]
// Find authors
authors = await db.authors.find({
where: {
name: 'Hunter S. Thompson'
},
order: 'name asc',
limit: 10,
offset: 0
}
})
console.log(authors) // [ { id: 1, name: Hunter S. Thompson } ]
// Find one author
author = await db.authors.findOne({
where: [
"name like 'Hunter %'"
]
})
console.log(author) // { id: 1, name: Hunter S. Thompson }
}
db.onReady(examples)
Example database schema:
create table authors (
id serial,
name varchar,
constraint author_pkey primary key(id)
);
create table books (
id serial,
title varchar,
author_id integer,
constraint book_pkey primary key(id),
constraint author foreign key (author_id) references authors(id)
);
create table reviews (
id serial,
book_id integer,
stars integer,
body varchar,
constraint review_pkey primary key(id),
constraint book foreign key (book_id) references book(id)
);
Pro Tip: Create a trigger to auto-populate author.books[]
.
Hacker Tip: Replicate to Redis so your cache is never stale.
Instantiates the db
object and configures the database connection string(s).
pg
or mysql
false
) set to true
to enable SSL connectionfalse
) set to console.log
to see info about running queriesfalse
) duration in milliseconds to cache rows in process memory. Setting this to 150
is generally a no-brainer to prevent redundant queries.false
) object with get(key)
and/or set(key, val)
methods (i.e. redis) to cache full rows (indefinitely). Cached rows are recached after save()
/insert()
/update()
/delete()
. The Table functions fetch rows from the cache (and only fetch from sql the rows that are not cached).global.Promise
) You may plug in your own Promise library that is compatible with native promises, i.e. Promise: require('bluebird')
. Then a promise will be returned if a callback is not specified.models.my_table = class MyTable {}
JSON.parse(JSON.stringify(db))
const oreo = require('oreo')
const db = oreo({
driver: 'pg',
hosts: ['localhost:5432'],
name: 'database',
user: 'username',
pass: 'password',
//ssl: false,
//debug: false, //console.log
//memoize: 0,
//cache: null,
//Promise: global.Promise
//models: {},
//schema: {}
}, function (err) {
db.execute('select now() as now')
.then(rows => {
console.log('now:', rows[0].now)
})
})
Executes an arbitrary SQL query.
opts
is specified) parameterized query datadb.execute([
'select now()', // arrays can be used for es5 multi-line convenience
'as now'
])
.then(rows => {
console.log(rows[0]) // 2014-06-24 21:03:08.652861-04
})
Parameterized query (SQL injection safe):
db.execute(`
select id
from authors
where name = :name
`, {
name: 'Jack Kerouac',
})
.then(rows => {
console.log(rows[0].id) // 1
})
.catch(err => {
})
Same as execute
but executes the query on a writable (primary) host.
Queues a function to be called when oreo's schema detection is complete (i.e. when oreo is initialized).
const db = oreo(config, (err) => {
console.log('Ready!')
})
.onReady(() => {
console.log('onReady #1')
})
db.onReady(() => {
console.log('onReady #2')
})
/*
Output:
onReady #1
onReady #2
Ready!
*/
Closes the db connection(s).
Counts the number of rows matching the specified criteria.
:key
patterns in the querydb.authors.count({
where: {
name: 'Jack'
}
})
.then(count => {
console.log(count) // 1
})
Finds multiple rows.
last_name ASC, age DESC
hydrate
):key
patterns in the querydb.authors.find({
where: [ "name like 'Jack%'" ],
order: 'name asc',
offset: 5,
limit: 5,
hydrate: ['books']
})
.then(authors => {
console.log(authors)
// [ { id: 1, name: Jack Kerouac, books: [ { id: 1, title: On the Road, author_id: 1 } ] } ]
})
The where
option has several valid formats:
{String}
where: "field = :f1 and field2 > :f2",
params: {
f1: 'abc',
f2: 1
}
{Array}
where: [
"field = :f1",
"field2 > :f2"
],
params: {
f1: 'abc',
f2: 1
}
{Object}
where: {
field: 'abc',
field2: { $gt: 1 } // query operators are coming soon
}
Finds exactly one row.
find
db.authors.findOne({
where: [ "name like 'Jack%'" ],
order: 'name asc',
offset: 5
})
.then(author => {
console.log(author.id) // 1
})
Gets a row by primary key.
const primaryKey = 1 // const primaryKey = { id: 1 } // this also works
db.authors.get(primaryKey)
.then(author => {
console.log(author) // { id: 1, name: Jack Kerouak }
})
Multi-column (composite) primary key:
const primaryKey = {
company: 'Cogswell Cogs',
part_no: 'A-12345'
}
db.parts.get(primaryKey)
.then(part => {
console.log(part) // { company: Cogswell Cogs, part_no: A-12345, price: 9.99, in_stock: true }
})
Inserts a new row.
db.books.insert({
title: 'On the Road',
author_id: 1
})
.then(book => {
console.log(book) // { id: 1, title: On the Road, author_id: 1 }
})
Insert multiple rows into related tables in a single transaction:
db.books.insert({
title: 'On the Road',
author: { // "author" is the foreign key name (1-to-1)
name: 'Jack Kerouac'
},
reviews: [ // shorthand for 'book:reviews' <foreignKeyName>:<tableName> (1-to-many)
{ stars: 5, body: 'Psychadelic!'},
{ stars: 4, body: 'Bizarre, unpredictable yet strangely alluring.'}
]
})
.then(book => {
console.log(book) // { id: 1, title: On the Road, author_id: 1 }
})
Gets many rows by primary key in the specified order. A null
value will be returned for each primary key that does not exist.
const bookIds = [1]
db.books.mget(bookIds)
.then(books => {
console.log(books) // [ { id: 1, title: On the Road, author_id: 1 } ]
})
Inserts or updates depending on whether the primary key exists in the db.
const formPOST = {
id: 1,
title: 'New Title'
}
db.books.save(formPOST)
.then(book => {
console.log(book) // { id: 1, title: New Title, author_id: 1 }
})
Deletes an existing row from the database.
book.delete()
.then(() => {
console.log(book) // {}
})
Hydrates the row(s) linked with the specified foreign key(s) and/or foreign table(s).
db.books.get(1)
.then(book => {
console.log(book) // { id: 1, title: On the Road, author_id: 1 }
// hydrate a 1-to-1 linked row
book.hydrate('author')
.then(() => {
console.log(book.author) // { id: 1, name: Jack Kerouac }
})
// hydrate 1-to-many linked rows
book.hydrate('reviews')
.then(() => {
console.log(book.reviews) // [ { stars: 5, body: 'Psychadelic!' }, { stars: 4, body: 'Bizarre...' } ]
})
})
When hydrating a 1-to-1 row, the propertyName is the name of the foreign key constraint.
For example, a book has one author, so we have a table books
with a column author_id
which has a foreign key constraint named author
which links to author.id
.
// 1-to-1
book.hydrate('author')
.then(() => {
console.log(book.author) // { id: 1, name: Jack Kerouac }
})
When hydrating 1-to-many rows, it is recommended to specify the fully qualified hydratable propertyName formatted as foreignKeyName:tableName
. However, for convenience, if the foreign table has only one foreign key that references this table, you may omit foreignKeyName:
and simply use tableName
shorthand notation.
For example, a book has many reviews, so we have a table reviews
with a column book_id
which has a foreign key constraint named book
which links to book.id
.
// 1-to-many (fully qualified notation)
book.hydrate('book:reviews')
.then(() => {
console.log(book['book:reviews'])
// [ { stars: 5, body: 'Psychadelic!' }, { stars: 4, body: 'Bizarre...' } ]
})
// 1-to-many (shorthand notation)
book.hydrate('reviews')
.then(() => {
console.log(book.reviews)
// [ { stars: 5, body: 'Psychadelic!' }, { stars: 4, body: 'Bizarre...' } ]
})
Hydrate multiple properties in parallel:
book.hydrate(['author', 'reviews'])
.then(() => {
console.log(book)
// {
// id: 1,
// title: On the Road,
// author_id: 1,
// author: { id: 1, name: Jack Kerouac },
// reviews: [ { stars: 5, body: 'Psychadelic!' }, { stars: 4, body: 'Bizarre...' } ]
// }
})
Saves the modified property values to the database (and saves linked rows recursively).
db.books.get(1)
.then(book => {
console.log(book) // { id: 1, title: On the Road, author_id: 1 }
book.author_id = 2
book.save()
.then(book => {
console.log(book) // { id: 1, title: On the Road, author_id: 2 }
})
})
Modifies multiple property values but does NOT save to the db.
db.books.get(1)
.then(book => {
console.log(book) // { id: 1, title: On the Road, author_id: 1 }
book.set({
title: 'New Title',
author_id: 2
})
book.save()
.then(book => {
console.log(book) // { id: 1, title: New Title, author_id: 2 }
})
})
Updates an existing row. A convenience method for set()
then save()
.
book.update({
title: 'New Title'
})
.then(book => {
console.log(book) // { id: 1, title: New Title, author_id: 1 }
})
JSON
data type are not supported (use JSONB
instead!)FAQs
SQL CRUD utility with schema detection
The npm package oreo receives a total of 14 weekly downloads. As such, oreo popularity was classified as not popular.
We found that oreo demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
Security News
As cyber threats become more autonomous, AI-powered defenses are crucial for businesses to stay ahead of attackers who can exploit software vulnerabilities at scale.
Security News
UnitedHealth Group disclosed that the ransomware attack on Change Healthcare compromised protected health information for millions in the U.S., with estimated costs to the company expected to reach $1 billion.