CRUD Operations SWEN 344 WEB ENGINEERING Create Read

CRUD Operations SWEN 344 WEB ENGINEERING

Create, Read, Update, [Delete|Deactivate] ● These are the most common operations on any persistent system no matter the implementation ● NOT comprehensive - just baseline operations. DB ops not included: ○ ○ Schema creation More complex querying “Upsert” Many more. . . ○ ○ ○ Create Read → Update Delete Deactivate → ● In SQL, these map to: → SELECT → → UPDATE INSERT statements, i. e. add a new row/record … WHERE id=? LIMIT 1, i. e. get the single record UPDATE … WHERE id=? DELETE … WHERE id=? SET active=FALSE … WHERE id=? id title runtime_minutes 1 The Shawshank Redemption 144 2 Pulp Fiction 154

Create ● INSERT INTO movies(title, runtime_minutes) VALUES (‘The Departed’, ● See Intro Slides 151)

Read ● Obtaining one or more records, based on a set of criteria ○ ○ If you want a single record, usually from a primary key ○ Or from a combination of other keys intended to be unique in combination Otherwise, you will get a *set* of records matching the criteria ● Read operations do NOT modify the data ● ● SELECT * FROM movies WHERE id=1 LIMIT 1 SELECT * FROM movies WHERE title=’The Shawshank Redemption’ AND year=1994 LIMIT 1 ● SELECT * FROM movies WHERE year=1994

Update ● Change an existing record ○ ○ Usually from a primary key (to ensure you change the correct record) Or from a combination of other keys intended to be unique in combination ● UPDATE movies SET year=1994 WHERE id=1 ● UPDATE movies SET year=DEFAULT WHERE id=1 ● UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003 -07 -03' RETURNING temp_lo, temp_hi, prcp;

Delete or Deactivate ● Key decision: do we really want to lose this data? ○ ○ ○ ● ● Maybe the customer will come back? Privacy and the “Right to be forgotten” What about relationships? We’ll get to that. DELETE FROM … WHERE. . . ○ DO NOT FORGET THE WHERE CLAUSE ○ Returns the number of records deleted MANY BOTHANS HAVE DIED FORGETTING THE WHERE CLAUSE UPDATE … SET col=FALSE … WHERE… ○ On future queries, you will need to account for having deactivated records DELETE FROM movies WHERE id=2 UPDATE movies SET active=FALSE WHERE id=1

“Upsert” (Update/ Insert) ● “Insert this row, if a record with that id exists, update instead” ○ ○ Treats the table more like a dictionary Very common, helps reduce client logic, and very performant ● In Postgres, use INSERT INTO … ON CONFLICT ○ ○ ○ Tell the conflict to look for the uniqueness of the primary key But you can do much more if you use more complex constraints out of scope for this class e. g. (concatenate 2 nd email if already exists) INSERT INTO customers (name, email) VALUES(‘Facebook’, ’more_family@gmail. com') ON CONFLICT (name) DO UPDATE SET email = ’more_family@gmail. com’ || '; ' || customers. email;

Design Considerations on CRUD ● Input validation & trusting data ○ How do we ensure this data is well-formed? ■ Validate the data BEFORE going to DB layer, OR ■ Validate as PART of your DB layer ■ Validate with DB constraints (e. g. Postgres’ CHECK) ○ Is this the only API talking to this database, or are there other sources? ● How do we get the primary key in the first place? ○ ○ ○ Some other query that is like a “list all” INSERT returns generated primary keys currval('movies_id_seq') is the most recent generated ID (only during the active session) ○ Potential approach ○ Use autogen keys (in postgres, that is ‘SERIAL PRIMARY KEY’ (datatype INT)