Basic CRUD Operations with models

Model code-generator

Once you’ve generated your models using the migration tool you’ll notice a lot of files getting auto-generated in the <projectRoot>/autogen & <projectRoot>/src/Models directories:

1. For every table that your DB contains you’ll have an auto-generated DB interface called AutoGenerated.Models.<SingularizedTableNameInCamelCase>.
2. For every table that has a primary key called id (which is a recommended convention), you’ll have an auto-generated module called AutoGenerated.PrimaryKeys.<SingularizedTableNameInCamelCase>Id
3. For every unique column-name, across all your tables, you’ll have an auto-generated lens-class called AutoGenerated.Classes.Has<FieldNameInCamelCase>
4. For every model that is newly generated, you’ll have a file called Models.<SingularizedTableNameInCamelCase> and a file called Models.<SingularizedTableNameInCamelCase>.Types

For example, if you have the following two tables in your DB schema…

users	contacts
id	id
created_at	created_at
updated_at	updated_at
email	email
password	first_name
first_name	last_name
last_name	street_address
	state
	country
	zip
	user_id references users(id)

…you’ll end up with the following files:

Filename	Purpose	Overwitten?
autogen/AutoGenerated/Models/User.hs	Auto-generated DB interface	Yes
autogen/AutoGenerated/Models/Contact.hs	Auto-generated DB interface	Yes
autogen/AutoGenerated/PrimaryKeys/UserId.hs	newtype for PK	Yes
autogen/AutoGenerated/PrimaryKeys/ContactId.hs	newtype for PK	Yes
autogen/AutoGenerated/Classes/Id.hs	Lens class	Yes
autogen/AutoGenerated/Classes/Id.hs	Lens class	Yes
autogen/AutoGenerated/Classes/CreatedAt.hs	Lens class	Yes
autogen/AutoGenerated/Classes/UpdatedAt.hs	Lens class	Yes
autogen/AutoGenerated/Classes/Email.hs	Lens class	Yes
autogen/AutoGenerated/Classes/Password.hs	Lens class	Yes
autogen/AutoGenerated/Classes/FirstName.hs	Lens class	Yes
autogen/AutoGenerated/Classes/LastName.hs	Lens class	Yes
autogen/AutoGenerated/Classes/StreetAddress.hs	Lens class	Yes
autogen/AutoGenerated/Classes/State.hs	Lens class	Yes
autogen/AutoGenerated/Classes/Country.hs	Lens class	Yes
autogen/AutoGenerated/Classes/Zip.hs	Lens class	Yes
autogen/AutoGenerated/Classes/UserId.hs	Lens class	Yes
src/Models/User.hs	Domain-level model	No
src/Models/User/Types.hs	supporting types for Models.User	No
src/Models/Contact.hs	Domain-level model	No
src/Models/Contact/Types.hs	supporting types for Models.Contact	No

Points to note

1. All files in the <projectRoot>/autogen directory are marked as read-only and will be over-written if the underlying DB schema changes. You should not touch these files. Simply commit them into your version control.
2. All files in <projectRoot>/src/Models will be generated only once by the code-generation tool. Once generated, they will never be touched by the tool. You should put all your domain logic, custom types, enumeration types, etc. in these files.

(C)reate operations on models

Try the following in your REPL:

createModel UserPoly
{
  _userId = Nothing
, _userCreatedAt = Nothing
, _userUpdatedAt = Nothing
, _userEmail = "saurabh@vacationlabs.com"
, _userPassword = "blahblah"
, _userFirstName = "Saurabh"
, _userLastName = "Nanda"
}

(R)ead operations on models

Try the following in your REPL:

-- finding by a primary key
findByPk (PK 1 :: UserId)

-- find a single row by matching over two columns. Will throw an error if
-- this results in multiple rows being returned.

findSingle2 tableForUser
  (
    (email, pgEq, "saurabh@vacationlabs.com")
  , (password, pgEq, "blahblah")
  )

-- find a single row by matching over three columns. Will throw an error if
-- this results in multiple rows being returned.
findSingle3 tableForUser
  (
    (email, pgEq, "saurabh@vacationlabs.com")
  , (firstName, pgEq, "Saurabh")
  , (lastName, pgEq, "Nanda")
  )

-- find the first row by matching over four columns. Will not throw an error
-- if this results in multiple rows being returned. Will silently return the
-- first row.
findFirst4 tableForUser
  (
    (email, pgEq, "saurabh@vacationlabs.com")
  , (country, pgIn, ["IN", "US"])
  , (state, pgIn, ["UP", "MH"])
  , (userId, pgEq, PK 10)
  )


-- return all matching rows
filter1 tableForUser
  (
    (email, pgEq, "saurabh@vacationlabs.com")
  )

filter2 tableForUser
  (
    (email, pgEq, "saurabh@vacationlabs.com")
  , (country, pgIn, ["IN", "US"])
  )

-- and so on, up to filter6. If you need more than 6 columns, you should
-- probably use the underlying Opaleye querying infrastructure.

(U)pdate operations on models

Try the following in your REPL:

u <- findByPk (PK 1 :: UserId)
saveModel (u & firstName .~ "new name")

-- OR

updateModel
  (PK 1 :: UserId) -- which row to update
  (\u -> (u & firstName .~ (pgStrictText "new name"))) -- updater function

(D)elete operations on models

Try the following in your REPL:

u <- findByPk (PK 1 :: UserId)
deleteModel u

-- OR

deleteModelByPk (PK 1 :: UserId)