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 = "[email protected]"
, _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, "[email protected]")
  , (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, "[email protected]")
  , (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, "[email protected]")
  , (country, pgIn, ["IN", "US"])
  , (state, pgIn, ["UP", "MH"])
  , (userId, pgEq, PK 10)
  )


-- return all matching rows
filter1 tableForUser
  (
    (email, pgEq, "[email protected]")
  )

filter2 tableForUser
  (
    (email, pgEq, "[email protected]")
  , (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)