Idris Brhane

Idris Brhane

1606472580

A Type-safe Postgres Query Builder Pur Sang for Typescript

Mammoth: A type-safe Postgres query builder pur sang for TypeScript

📖 Work-in-progress documentation site is available at https://mammoth.tools/.

npm i @ff00ff/mammoth

Mammoth is a type-safe query builder. It only supports Postgres which we consider a feature. It’s syntax is as close to SQL as possible so you already know how to use it. It’s autocomplete features are great. It helps you avoid mistakes so you can develop applications faster.

const rows = await db
  .select(db.foo.id, db.bar.name)
  .from(db.foo)
  .leftJoin(db.bar)
  .on(db.foo.barId.eq(db.bar.id))
  .where(db.foo.id.eq(`1`));

The above query produces the following SQL:

SELECT
  foo.id,
  bar.name
FROM foo
LEFT JOIN bar ON (foo.bar_id = bar.id)
WHERE
  foo.id = $1

More importantly, the resulting type of rows is { id: string; name: string | undefined }[]. Notice how the name is automatically nullable because of the left join.

Query examples

Basic update

const updateCount = await db.update(db.foo).set({ name: `Test` }).where(db.foo.value.gt(0));
UPDATE foo
SET
  name = $1
WHERE
  value > $2

Basic insert

const rows = await db
  .insertInto(db.foo)
  .values({
    name: `Test`,
    value: 123,
  })
  .returning(`id`);
INSERT INTO foo (
  name,
  value
) VALUES (
  $1,
  $2
)
RETURNING
  id

Insert into select

const affectedCount = await db
  .insertInto(db.foo, ['name'])
  .select(db.bar.name)
  .from(db.bar)
  .where(db.bar.name.isNotNull());
INSERT INTO foo (name)
SELECT
  bar.name
FROM bar
WHERE
  bar.name IS NOT NULL

Select with count(*)

db.select(count()).from(db.foo);
SELECT COUNT(*) FROM foo

Select with aggregate expression

db.select(arrayAgg(db.foo.name.orderBy(db.foo.name.desc()))).from(db.foo);
SELECT array_agg(foo.name ORDER BY foo.name DESC) "arrayAgg" FROM foo

With (CTE) query

db.with(
  `regionalSales`,
  () =>
    db
      .select(db.orderLog.region, sum(db.orderLog.amount).as(`totalSales`))
      .from(db.orderLog)
      .groupBy(db.orderLog.region),
  `topRegions`,
  ({ regionalSales }) =>
    db
      .select(regionalSales.region)
      .from(regionalSales)
      .where(
        regionalSales.totalSales.gt(
          db.select(sum(regionalSales.totalSales).divide(10)).from(regionalSales),
        ),
      ),
  ({ topRegions }) =>
    db
      .select(
        db.orderLog.region,
        db.orderLog.product,
        sum(db.orderLog.quantity).as(`productUnits`),
        sum(db.orderLog.amount).as(`productSales`),
      )
      .from(db.orderLog)
      .where(db.orderLog.region.in(db.select(topRegions.region).from(topRegions)))
      .groupBy(db.orderLog.region, db.orderLog.product),
);
WITH "regionalSales" AS (SELECT order_log.region, SUM (order_log.amount) "totalSales" FROM order_log GROUP BY order_log.region), "topRegions" AS (SELECT "regionalSales".region FROM "regionalSales" WHERE "regionalSales"."totalSales" > (SELECT SUM ("regionalSales"."totalSales") / $1 FROM "regionalSales")) SELECT order_log.region, order_log.product, SUM (order_log.quantity) "productUnits", SUM (order_log.amount) "productSales" FROM order_log WHERE order_log.region IN (SELECT "topRegions".region FROM "topRegions") GROUP BY order_log.region, order_log.product

Quick start

Mammoth is a query builder pur sang so it doesn’t include a database driver. You need to create a db and pass a callback to execute the query.

import { defineDb } from '@ff00ff/mammoth';
import { foo, bar } from './tables';

const db = defineDb({ foo, bar }, async (query, parameters) => {
  const result = await pool.query(query, parameters);

  return {
    affectedCount: result.rowCount,
    rows: result.rows,
  };
});

In the defineDb call you pass all your tables so they can be access through the db instance. You have to define all the tables to make sure Mammoth understands the type information. This should be close to the CREATE TABLE syntax.

const foo = defineTable({
  id: uuid().primaryKey().default(`gen_random_uuid()`),
  createDate: timestampWithTimeZone().notNull().default(`now()`),
  name: text().notNull(),
  value: integer(),
});

You should keep your column names camelCase in the defineTable call as they are automatically transformed to train_case throughout Mammoth.

Compatibility

Below is a list of clauses per query and a short description on what we Mammoth supports.

SELECT

  • [ WITH [ RECURSIVE ] with_query [, …] ] — Partial support. Recursive not supported yet.
  • SELECT [ ALL | DISTINCT [ ON ( expression [, …] ) ] ] — Mostly supported. Distinct not yet.
  • [ * | expression [ [ AS ] output_name ] [, …] ] — mostly supported. Selecting certain expressions like update queries, insert and delete queries are not supported yet. Select queries are though.
  • [ FROM from_item [, …] ] — partially supported. Only 1 table is currently supported in the from.
  • [ WHERE condition ] — mostly supported. The condition concept is pretty broad but it should contain a lot of cases.
  • [ GROUP BY grouping_element [, …] ] — supported.
  • [ HAVING condition [, …] ] — supported.
  • [ WINDOW window_name AS ( window_definition ) [, …] ] — not supported.
  • [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] — not supported yet
  • [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, …] ] — supported, but expressions are pretty broad and there might be cases not covered yet.
  • [ LIMIT { count | ALL } ] — supported.
  • [ OFFSET start [ ROW | ROWS ] ] — supported.
  • [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] — supported
  • [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, …] ] [ NOWAIT | SKIP LOCKED ] […] ] — supported

UPDATE

  • [ WITH [ RECURSIVE ] with_query [, …] ] — Partial support. Recursive not supported yet.
  • UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] — supported
  • SET { column_name = { expression | DEFAULT } | — supported, but expression concept is very broad and might be incomplete
  • ( column_name [, …] ) = [ ROW ] ( { expression | DEFAULT } [, …] ) | — supported, but expression concept is very broad and might be incomplete in some cases
  • ( column_name [, …] ) = ( sub-SELECT ) — not supported
  • } [, …]
  • [ FROM from_item [, …] ] — partially supported. Only 1 table as from item is supported
  • [ WHERE condition | WHERE CURRENT OF cursor_name ] — supported, but the condition concept is very broad and is incomplete in some cases.
  • [ RETURNING * | output_expression [ [ AS ] output_name ] [, …] ] — supported, but up to 10 expressions

DELETE

  • [ WITH [ RECURSIVE ] with_query [, …] ] — Partial support. Recursive not supported yet.
  • DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] — supported
  • [ USING from_item [, …] ] — supported
  • [ WHERE condition | WHERE CURRENT OF cursor_name ] — supported, but the condition concept is very broad and might be incomplete
  • [ RETURNING * | output_expression [ [ AS ] output_name ] [, … ] ] — supported, but up to 10 expressions

INSERT

  • [ WITH [ RECURSIVE ] with_query [, …] ] — Partial support. Recursive not supported yet.
  • INSERT INTO table_name [ AS alias ] [ ( column_name [, …] ) ] — supported
  • [ OVERRIDING { SYSTEM | USER } VALUE ] — not supported
  • { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, …] ) [, …] | query } - supported, but expression is a broad concept and may not be complete
  • [ ON CONFLICT [ conflict_target ] conflict_action ] — supported
  • [ RETURNING * | output_expression [ [ AS ] output_name ] [, …] ] — supported, but limited to 10 expressions

Versioning

Now that we’ve hit 1.0 Mammoth will stick to semantic versioning, meaning, breaking changes will only be included in major updates.

Contribute

Once you clone the repo, do a npm install + npm run build. Now you should be able to run npm test seeing everything turn green. Feel free to pick up one of the open issues — in particular you can pick up one labeled with “good first issue”. Be sure to claim the issue before you start so we avoid two or more people working on the same thing.

Mammoth logo created by Eucalyp from the Noun Project.

Download Details:

Author: Ff00ff

Demo: https://mammoth.tools/

Source Code: https://github.com/Ff00ff/mammoth

#typescript #deno #nodejs #javascript

What is GEEK

Buddha Community

A Type-safe Postgres Query Builder Pur Sang for Typescript
Idris Brhane

Idris Brhane

1606472580

A Type-safe Postgres Query Builder Pur Sang for Typescript

Mammoth: A type-safe Postgres query builder pur sang for TypeScript

📖 Work-in-progress documentation site is available at https://mammoth.tools/.

npm i @ff00ff/mammoth

Mammoth is a type-safe query builder. It only supports Postgres which we consider a feature. It’s syntax is as close to SQL as possible so you already know how to use it. It’s autocomplete features are great. It helps you avoid mistakes so you can develop applications faster.

const rows = await db
  .select(db.foo.id, db.bar.name)
  .from(db.foo)
  .leftJoin(db.bar)
  .on(db.foo.barId.eq(db.bar.id))
  .where(db.foo.id.eq(`1`));

The above query produces the following SQL:

SELECT
  foo.id,
  bar.name
FROM foo
LEFT JOIN bar ON (foo.bar_id = bar.id)
WHERE
  foo.id = $1

More importantly, the resulting type of rows is { id: string; name: string | undefined }[]. Notice how the name is automatically nullable because of the left join.

Query examples

Basic update

const updateCount = await db.update(db.foo).set({ name: `Test` }).where(db.foo.value.gt(0));
UPDATE foo
SET
  name = $1
WHERE
  value > $2

Basic insert

const rows = await db
  .insertInto(db.foo)
  .values({
    name: `Test`,
    value: 123,
  })
  .returning(`id`);
INSERT INTO foo (
  name,
  value
) VALUES (
  $1,
  $2
)
RETURNING
  id

Insert into select

const affectedCount = await db
  .insertInto(db.foo, ['name'])
  .select(db.bar.name)
  .from(db.bar)
  .where(db.bar.name.isNotNull());
INSERT INTO foo (name)
SELECT
  bar.name
FROM bar
WHERE
  bar.name IS NOT NULL

Select with count(*)

db.select(count()).from(db.foo);
SELECT COUNT(*) FROM foo

Select with aggregate expression

db.select(arrayAgg(db.foo.name.orderBy(db.foo.name.desc()))).from(db.foo);
SELECT array_agg(foo.name ORDER BY foo.name DESC) "arrayAgg" FROM foo

With (CTE) query

db.with(
  `regionalSales`,
  () =>
    db
      .select(db.orderLog.region, sum(db.orderLog.amount).as(`totalSales`))
      .from(db.orderLog)
      .groupBy(db.orderLog.region),
  `topRegions`,
  ({ regionalSales }) =>
    db
      .select(regionalSales.region)
      .from(regionalSales)
      .where(
        regionalSales.totalSales.gt(
          db.select(sum(regionalSales.totalSales).divide(10)).from(regionalSales),
        ),
      ),
  ({ topRegions }) =>
    db
      .select(
        db.orderLog.region,
        db.orderLog.product,
        sum(db.orderLog.quantity).as(`productUnits`),
        sum(db.orderLog.amount).as(`productSales`),
      )
      .from(db.orderLog)
      .where(db.orderLog.region.in(db.select(topRegions.region).from(topRegions)))
      .groupBy(db.orderLog.region, db.orderLog.product),
);
WITH "regionalSales" AS (SELECT order_log.region, SUM (order_log.amount) "totalSales" FROM order_log GROUP BY order_log.region), "topRegions" AS (SELECT "regionalSales".region FROM "regionalSales" WHERE "regionalSales"."totalSales" > (SELECT SUM ("regionalSales"."totalSales") / $1 FROM "regionalSales")) SELECT order_log.region, order_log.product, SUM (order_log.quantity) "productUnits", SUM (order_log.amount) "productSales" FROM order_log WHERE order_log.region IN (SELECT "topRegions".region FROM "topRegions") GROUP BY order_log.region, order_log.product

Quick start

Mammoth is a query builder pur sang so it doesn’t include a database driver. You need to create a db and pass a callback to execute the query.

import { defineDb } from '@ff00ff/mammoth';
import { foo, bar } from './tables';

const db = defineDb({ foo, bar }, async (query, parameters) => {
  const result = await pool.query(query, parameters);

  return {
    affectedCount: result.rowCount,
    rows: result.rows,
  };
});

In the defineDb call you pass all your tables so they can be access through the db instance. You have to define all the tables to make sure Mammoth understands the type information. This should be close to the CREATE TABLE syntax.

const foo = defineTable({
  id: uuid().primaryKey().default(`gen_random_uuid()`),
  createDate: timestampWithTimeZone().notNull().default(`now()`),
  name: text().notNull(),
  value: integer(),
});

You should keep your column names camelCase in the defineTable call as they are automatically transformed to train_case throughout Mammoth.

Compatibility

Below is a list of clauses per query and a short description on what we Mammoth supports.

SELECT

  • [ WITH [ RECURSIVE ] with_query [, …] ] — Partial support. Recursive not supported yet.
  • SELECT [ ALL | DISTINCT [ ON ( expression [, …] ) ] ] — Mostly supported. Distinct not yet.
  • [ * | expression [ [ AS ] output_name ] [, …] ] — mostly supported. Selecting certain expressions like update queries, insert and delete queries are not supported yet. Select queries are though.
  • [ FROM from_item [, …] ] — partially supported. Only 1 table is currently supported in the from.
  • [ WHERE condition ] — mostly supported. The condition concept is pretty broad but it should contain a lot of cases.
  • [ GROUP BY grouping_element [, …] ] — supported.
  • [ HAVING condition [, …] ] — supported.
  • [ WINDOW window_name AS ( window_definition ) [, …] ] — not supported.
  • [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] — not supported yet
  • [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, …] ] — supported, but expressions are pretty broad and there might be cases not covered yet.
  • [ LIMIT { count | ALL } ] — supported.
  • [ OFFSET start [ ROW | ROWS ] ] — supported.
  • [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] — supported
  • [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, …] ] [ NOWAIT | SKIP LOCKED ] […] ] — supported

UPDATE

  • [ WITH [ RECURSIVE ] with_query [, …] ] — Partial support. Recursive not supported yet.
  • UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] — supported
  • SET { column_name = { expression | DEFAULT } | — supported, but expression concept is very broad and might be incomplete
  • ( column_name [, …] ) = [ ROW ] ( { expression | DEFAULT } [, …] ) | — supported, but expression concept is very broad and might be incomplete in some cases
  • ( column_name [, …] ) = ( sub-SELECT ) — not supported
  • } [, …]
  • [ FROM from_item [, …] ] — partially supported. Only 1 table as from item is supported
  • [ WHERE condition | WHERE CURRENT OF cursor_name ] — supported, but the condition concept is very broad and is incomplete in some cases.
  • [ RETURNING * | output_expression [ [ AS ] output_name ] [, …] ] — supported, but up to 10 expressions

DELETE

  • [ WITH [ RECURSIVE ] with_query [, …] ] — Partial support. Recursive not supported yet.
  • DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] — supported
  • [ USING from_item [, …] ] — supported
  • [ WHERE condition | WHERE CURRENT OF cursor_name ] — supported, but the condition concept is very broad and might be incomplete
  • [ RETURNING * | output_expression [ [ AS ] output_name ] [, … ] ] — supported, but up to 10 expressions

INSERT

  • [ WITH [ RECURSIVE ] with_query [, …] ] — Partial support. Recursive not supported yet.
  • INSERT INTO table_name [ AS alias ] [ ( column_name [, …] ) ] — supported
  • [ OVERRIDING { SYSTEM | USER } VALUE ] — not supported
  • { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, …] ) [, …] | query } - supported, but expression is a broad concept and may not be complete
  • [ ON CONFLICT [ conflict_target ] conflict_action ] — supported
  • [ RETURNING * | output_expression [ [ AS ] output_name ] [, …] ] — supported, but limited to 10 expressions

Versioning

Now that we’ve hit 1.0 Mammoth will stick to semantic versioning, meaning, breaking changes will only be included in major updates.

Contribute

Once you clone the repo, do a npm install + npm run build. Now you should be able to run npm test seeing everything turn green. Feel free to pick up one of the open issues — in particular you can pick up one labeled with “good first issue”. Be sure to claim the issue before you start so we avoid two or more people working on the same thing.

Mammoth logo created by Eucalyp from the Noun Project.

Download Details:

Author: Ff00ff

Demo: https://mammoth.tools/

Source Code: https://github.com/Ff00ff/mammoth

#typescript #deno #nodejs #javascript

The Definitive Guide to TypeScript & Possibly The Best TypeScript Book

TypeScript Deep Dive

I've been looking at the issues that turn up commonly when people start using TypeScript. This is based on the lessons from Stack Overflow / DefinitelyTyped and general engagement with the TypeScript community. You can follow for updates and don't forget to ★ on GitHub 🌹

Reviews

  • Thanks for the wonderful book. Learned a lot from it. (link)
  • Its probably the Best TypeScript book out there. Good Job (link)
  • Love how precise and clear the examples and explanations are! (link)
  • For the low, low price of free, you get pages of pure awesomeness. Chock full of source code examples and clear, concise explanations, TypeScript Deep Dive will help you learn TypeScript development. (link)
  • Just a big thank you! Best TypeScript 2 detailed explanation! (link)
  • This gitbook got my project going pronto. Fluent easy read 5 stars. (link)
  • I recommend the online #typescript book by @basarat you'll love it.(link)
  • I've always found this by @basarat really helpful. (link)
  • We must highlight TypeScript Deep Dive, an open source book.(link)
  • Great online resource for learning. (link)
  • Thank you for putting this book together, and for all your hard work within the TypeScript community. (link)
  • TypeScript Deep Dive is one of the best technical texts I've read in a while. (link)
  • Thanks @basarat for the TypeScript Deep Dive Book. Help me a lot with my first TypeScript project. (link)
  • Thanks to @basarat for this great #typescript learning resource. (link)
  • Guyz excellent book on Typescript(@typescriptlang) by @basarat (link)
  • Leaning on the legendary @basarat's "TypeScript Deep Dive" book heavily at the moment (link)
  • numTimesPointedPeopleToBasaratsTypeScriptBook++; (link)
  • A book not only for typescript, a good one for deeper JavaScript knowledge as well. link
  • In my new job, we're using @typescriptlang, which I am new to. This is insanely helpful huge thanks, @basarat! link
  • Thank you for writing TypeScript Deep Dive. I have learned so much. link
  • Loving @basarat's @typescriptlang online book basarat.gitbooks.io/typescript/# loaded with great recipes! link
  • Microsoft doc is great already, but if want to "dig deeper" into TypeScript I find this book of great value link
  • Thanks, this is a great book 🤓🤓 link
  • Deep dive to typescript is awesome in so many levels. i find it very insightful. Thanks link
  • @basarat's intro to @typescriptlang is still one of the best going (if not THE best) link
  •  
  • This is sweet! So many #typescript goodies! link

Get Started

If you are here to read the book online get started.

Translations

Book is completely free so you can copy paste whatever you want without requiring permission. If you have a translation you want me to link here. Send a PR.

Other Options

You can also download one of the Epub, Mobi, or PDF formats from the actions tab by clicking on the latest build run. You will find the files in the artifacts section.

Special Thanks

All the amazing contributors 🌹

Share

Share URL: https://basarat.gitbook.io/typescript/

Author: Basarat
Source Code: https://github.com/basarat/typescript-book/ 
License: View license

#typescript #opensource 

Ahebwe  Oscar

Ahebwe Oscar

1620185280

How model queries work in Django

How model queries work in Django

Welcome to my blog, hey everyone in this article we are going to be working with queries in Django so for any web app that you build your going to want to write a query so you can retrieve information from your database so in this article I’ll be showing you all the different ways that you can write queries and it should cover about 90% of the cases that you’ll have when you’re writing your code the other 10% depend on your specific use case you may have to get more complicated but for the most part what I cover in this article should be able to help you so let’s start with the model that I have I’ve already created it.

**Read More : **How to make Chatbot in Python.

Read More : Django Admin Full Customization step by step

let’s just get into this diagram that I made so in here:

django queries aboutDescribe each parameter in Django querset

we’re making a simple query for the myModel table so we want to pull out all the information in the database so we have this variable which is gonna hold a return value and we have our myModel models so this is simply the myModel model name so whatever you named your model just make sure you specify that and we’re gonna access the objects attribute once we get that object’s attribute we can simply use the all method and this will return all the information in the database so we’re gonna start with all and then we will go into getting single items filtering that data and go to our command prompt.

Here and we’ll actually start making our queries from here to do this let’s just go ahead and run** Python manage.py shell** and I am in my project file so make sure you’re in there when you start and what this does is it gives us an interactive shell to actually start working with our data so this is a lot like the Python shell but because we did manage.py it allows us to do things a Django way and actually query our database now open up the command prompt and let’s go ahead and start making our first queries.

#django #django model queries #django orm #django queries #django query #model django query #model query #query with django

Christa  Stehr

Christa Stehr

1599315360

Nominal typing in Typescript

Nominal & structural typing

Type systems are typically categorized as either structural or nominal. Languages like Java and Scala have primarily nominal type systems, whereas a language like Typescript has a structural type system. Let’s take a brief look at both systems.

Nominal Typing

In a nominal typing system, type compatibility is checked using the name of the types. If they do not have the same name, then they are not compatible; end of story. **If **Typescript had a nominal typing system the type check for the last line would fail:

Image for post

Structural typing

Typescript uses structural typing to decide whether two types are compatible with one another or not. What do we mean by structural typing? Well, let’s consider the following code snippet:

Image for post

To determine whether the type of the constant color(RGBA) is compatible with the type of serializeColor’s parameter x(RGB) the type system must verify that each member of RGB has a corresponding compatible member in RGBA. In this case, RGB has a single member color for which RGBA has a corresponding member with the same type — [number, number, number] — and so it passes the type check. Notice how the type system ignores the additional members that exist on RGBA (alpha).

#typescript #type-safe #type-systems

Verdie  Murray

Verdie Murray

1636240140

What Is The Main Difference Of TYPES Vs INTERFACES in TypeScript

TypeScript has two ways of declaring structures of your objects in the form of  #types (type aliases) and #interfaces.

In this lesson we will look at the technical differences between these two, when you should use which, along with real world #TypeScript code analysis, and community thoughts

#typescript