Would you like to clone this notebook?

When you clone a notebook you are able to make changes without affecting the original notebook.

Cancel

Building Queries

node v10.24.1
version: 15.0.0
endpointsharetweet
const {Entity} = require("electrodb"); function print(...args) { for (let arg of args) { console.log(typeof arg !== "string" ? JSON.stringify(arg, null, 2) : arg); } } let schema = { model: { service: "MallStoreDirectory", entity: "MallStore", version: "1", }, attributes: { cityId: { type: "string", required: true, }, mallId: { type: "string", required: true, }, storeId: { type: "string", required: true, }, buildingId: { type: "string", required: true, }, unitId: { type: "string", required: true, }, category: { type: [ "spite store", "food/coffee", "food/meal", "clothing", "electronics", "department", "misc" ], required: true }, leaseEndDate: { type: "string", required: true }, rent: { type: "string", required: true, validate: /^(\d+\.\d{2})$/ }, discount: { type: "string", required: false, default: "0.00", validate: /^(\d+\.\d{2})$/ } }, indexes: { stores: { pk: { field: "pk", composite: ["cityId", "mallId"] }, sk: { field: "sk", composite: ["buildingId", "storeId"] } }, units: { index: "gis1pk-gsi1sk-index", pk: { field: "gis1pk", composite: ["mallId"] }, sk: { field: "gsi1sk", composite: ["buildingId", "unitId"] } }, leases: { index: "gis2pk-gsi2sk-index", pk: { field: "gis2pk", composite: ["storeId"] }, sk: { field: "gsi2sk", composite: ["leaseEndDate"] } } }, filters: { byCategory: ({category}, name) => category.eq(name), rentDiscount: (attributes, discount, max, min) => { return `${attributes.discount.lte(discount)} AND ${attributes.rent.between(max, min)}` } } }; const table = "store_locations_table" const StoreLocations = new Entity(schema, {table}); // Each Access Pattern is available on the Entity instance // StoreLocations.query.stores // StoreLocations.query.units // StoreLocations.query.leases
All queries require (*at minimum*) the Composite Attributes included in its Partition Key definition, and the Composite Attributes your caller has from the start of the Sort Key.
** Important: Composite Attributes must be supplied in the order they are composed when invoking the Access Pattern **
const cityId = "Atlanta1"; const mallId = "EastPointe"; const storeId = "LatteLarrys"; const unitId = "B24"; const buildingId = "F34"; /* Index Definition: stores: { pk: { field: "pk", composite: ["cityId", "mallId"] }, sk: { field: "sk", composite: ["buildingId", "storeId"] } } */ /** ATTENTION READER: Uncomment out any the following lines to see the results! Note that you would use `.go()` instead of `.params()` to make the query when calling DynamoDB! **/ // Good: Includes at least the PK // StoreLocations.query.stores({cityId, mallId}).params(); // Good: Includes at least the PK, and some of the SK // StoreLocations.query.stores({cityId, mallId, buildingId}).params(); // Good: Includes at least the PK, and all of the SK // StoreLocations.query.stores({cityId, mallId, buildingId, storeId}).params(); // Bad: No PK Composite Attributes specified, will throw // StoreLocations.query.stores().params(); // Bad: Not All PK Composite Attributes included (cityId), will throw // StoreLocations.query.stores({mallId}).params(); // Bad: Composite Attributes not included in order, will NOT throw but will ignore `storeId` // StoreLocations.query.stores({cityId, mallId, storeId}).params();
Sort Key Operations: Carefully considering your Composite Attribute order will allow ElectroDB to express hierarchical relationships and unlock more available Access Patterns for your application.
Unlike Partition Keys, Sort Keys can be partially provided. We can leverage this to multiply our available access patterns and use the Sort Key Operations: "begins", "between", "lt", "lte", "gt", and "gte". These queries are more performant and cost effective than filters. The costs associated with DynamoDB directly correlate to how effectively you leverage Sort Key Operations.
/** IMPORTANT NOTE: These examples end in .params() for the purpose of demonstration on runkit. To execute these queries you would use .go() instead of .params() if you want to query dynamodb directly. **/ let june = "2020-06"; let july = "2020-07"; let discount = "500.00"; let maxRent = "2000.00"; let minRent = "5000.00"; print( "Lease Agreements by StoreId", StoreLocations.query.leases({storeId}).params(), "Lease Agreement by StoreId for March 22nd 2020", StoreLocations.query.leases({storeId, leaseEndDate: "2020-03-22"}).params(), "Lease agreements by StoreId for 2020", StoreLocations.query.leases({storeId}).begins({leaseEndDate: "2020"}).params(), "Lease Agreements by StoreId after March 2020", StoreLocations.query.leases({storeId}).gt({leaseEndDate: "2020-03"}).params(), "Lease Agreements by StoreId after, and including, March 2020", StoreLocations.query.leases({storeId}).gte({leaseEndDate: "2020-03"}).params(), "Lease Agreements by StoreId before 2021", StoreLocations.query.leases({storeId}).lt({leaseEndDate: "2021-01"}).params(), "Lease Agreements by StoreId before Feburary 2021", StoreLocations.query.leases({storeId}).lte({leaseEndDate: "2021-02"}).params(), "Lease Agreements by StoreId between 2010 and 2020", StoreLocations.query .leases({storeId}) .between( {leaseEndDate: "2010"}, {leaseEndDate: "2020"}) .params(), "Lease Agreements by StoreId after, and including, 2010 in the city of Atlanta and category containing food", StoreLocations.query .leases({storeId}) .gte({leaseEndDate: "2010"}) .where((attr, op) => ` ${op.eq(attr.cityId, "Atlanta1")} AND ${op.contains(attr.category, "food")} `) .params(), "Rents by City and Store who's rent discounts match a certain rent/discount criteria", StoreLocations.query .units({mallId}) .begins({leaseEndDate: june}) .rentDiscount(discount, maxRent, minRent) .params(), "Stores by Mall matching a specific category", StoreLocations.query .units({mallId}) .byCategory("food/coffee") .params() );
One important consideration when using Sort Key Operations to make is when to use and not to use "begins". It is possible to supply partially supply Sort Key composite attributess. While they do have to be in order, you can provide only some of the properties to ElectroDB. By default, when you supply a partial Sort Key in the Access Pattern method, ElectroDB will create a `beginsWith` query. The difference between doing that and using .begins() is that ElectroDB will post-pend the next composite attribute's label onto the query. The difference is nuanced and makes better sense with an example, but the rule of thumb is that data passed to the Access Pattern method should represent values you know strictly equal the value you want.
/** Index Definition: units: { index: "gis1pk-gsi1sk-index", pk: { field: "gis1pk", composite: ["mallId"] }, sk: { field: "gsi1sk", composite: ["buildingId", "unitId"] } } An Access Pattern method is the method after query you use to query a particular accessType: StoreLocations.query.units({mallId, buildingId}); ---------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^ Data passed to the Access Pattern method is considered to be full, known, data. In the above example, we are saying we *know* the `mallId`, `buildingId` and `unitId`. If you only know the start of a piece of data, use .begins(): StoreLocations.query.units({mallId}).begins({buildingId}); ------------------------------------^^^^^^^^^^^^^^^^^^^^^ Data passed to the .begins() method is considered to be partial data. In the second example, we are saying we *know* the `mallId` and `buildingId`, but only know the beginning of `unitId`. **/ print( `We know 'mallId', 'buildingId' and 'unitId'`, StoreLocations.query.units({mallId, buildingId}).params(), `'We know the 'mallId' and 'buildingId', but only know the beginning of 'unitId'`, StoreLocations.query.units({mallId}).begins({buildingId}).params() ) /** For the above queries we see two different sort keys: 1. "$mallstore_1#buildingid_f34#unitid_" 2. "$mallstore_1#buildingid_f34" The first example shows how ElectroDB postpends the label of the next composite attribute (unitId) on the SortKey to ensure that buildings such as "f340" are not included in the query. This is useful to prevent common issues with overloaded sort keys like accidental overquerying. The second example allows you to make queries that do include buildings such as "f340" or "f3409" or "f340356346". For these reasons it is important to consider that Data passed to the Access Pattern method is considered to be full, known, data. **/
Get Record Provide all table index composite attributes in an object to the `get` method
print( StoreLocations.get({ storeId, mallId, buildingId, cityId}).params() )
Delete Record Provide all table index composite attributes in an object to the `delete` method to delete a record.
print( StoreLocations.delete({ storeId, mallId, buildingId, cityId}).params() )
Bulk Delete Records Provide all table index composite attributes in an array of objects to the `delete` method to batch delete records.
print( StoreLocations.delete([ { storeId: "LatteLarrys", mallId: "EastPointe", buildingId: "F34", cityId: "LosAngeles1" }, { storeId: "MochaJoes", mallId: "EastPointe", buildingId: "F35", cityId: "LosAngeles1" } ]).params() )
Put Record (with optional conditions) Provide all *required* Attributes as defined in the model to create a new record. ElectroDB will enforce any defined validations, defaults, casting, and field aliasing.
print( StoreLocations .put({ cityId: "Atlanta1", storeId: "LatteLarrys", mallId: "EastPointe", buildingId: "BuildingA1", unitId: "B47", category: "food/coffee", leaseEndDate: "2020-03-22", rent: "4500.00" }) .where((attr, op) => op.eq(attr.rent, "4500.00")) .params() )
Batch Put Record Provide all *required* Attributes as defined in the model, as an array, to create a new record. ElectroDB will enforce any defined validations, defaults, casting, and field aliasing.
print( StoreLocations.put([ { cityId: "LosAngeles1", storeId: "LatteLarrys", mallId: "EastPointe", buildingId: "F34", unitId: "a1", category: "food/coffee", leaseEndDate: "2022-03-22", rent: "4500.00" }, { cityId: "LosAngeles1", storeId: "MochaJoes", mallId: "EastPointe", buildingId: "F35", unitId: "a2", category: "food/coffee", leaseEndDate: "2021-01-22", rent: "1500.00" } ]).params() )
Create Record In DynamoDB, Put operations by default will overwrite a record if record being updated does not exist. In ElectroDB, the `patch` method will utilize the `attribute_not_exists()` parameter dynamically to ensure records are only "created" and not overwriten when inserting new records into the table.
print( StoreLocations .create({ cityId: "Atlanta1", storeId: "LatteLarrys", mallId: "EastPointe", buildingId: "BuildingA1", unitId: "B47", category: "food/coffee", leaseEndDate: "2020-03-22", rent: "4500.00" }) .where((attr, op) => op.eq(attr.rent, "4500.00")) .params() )
Update Record To update a record, pass all composite attributes to the update method and then pass `set` attributes that need to be updated. This example contains an optional conditional expression. *Note: If your update includes changes to an attribute that is also a composite attribute for a global secondary index, you must provide all composite attributes for that index.*
print( StoreLocations .update({cityId, mallId, storeId, buildingId}) .set({category: "food/meal"}) .where((attr, op) => op.eq(attr.category, "food/coffee")) .params() )
Patch Records In DynamoDB, Update operations by default will insert a record if record being updated does not exist. In ElectroDB, the `patch` method will utilize the `attribute_exists()` parameter dynamically to ensure records are only "patched" and not inserted when updating.
print( StoreLocations .patch({cityId, mallId, storeId, buildingId}) .set({category: "food/meal"}) .where((attr, op) => op.eq(attr.category, "food/coffee")) .params() )
Scan Records When scanning for rows, you can use filters the same as you would any query. For more detial on filters, see the [Where](#where) section. *Note: `Scan` functionality will be scoped to your Entity. This means your results will only include records that match the Entity defined in the model.*
print( StoreLocations.scan .where(({category}, {eq}) => ` ${eq(category, "food/coffee")} OR ${eq(category, "spite store")} `) .where(({leaseEndDate}, {between}) => ` ${between(leaseEndDate, "2020-03", "2020-04")} `) .params() )
Find Records DynamoDB offers three methods to find records: `get`, `query`, and `scan`. In ElectroDB, there is a fourth type: `find`. Unlike `get` and `query`, the `find` method does not require you to provide keys, but under the covers it will leverage the attributes provided to find the best index to query on. Provide the `find` method will all properties known to match a record and **_ElectroDB_** will generate the most performant query it can to locate the results. This can be helpful with highly dynamic querying needs. If an index cannot be satisfied with the attributes provided, `scan` will be used as a last resort.
print( StoreLocations.find({ mallId: "EastPointe", buildingId: "BuildingA1", leaseEndDate: "2020-03-22", rent: "1500.00" }).params() )
Loading…

no comments

    sign in to comment