Inserting rows

Warning

SQL for table creation

We'll stick with the same tenants table as the previous chapter:

--
-- Tenants
--

create type tenant_status as enum('active', 'inactive', 'new');
create table tenants(
       id serial primary key
       ,created_at timestamp with time zone not null default current_timestamp
       ,updated_at timestamp with time zone not null default current_timestamp
       ,name text not null
       ,first_name text not null
       ,last_name text not null
       ,email text not null
       ,phone text not null
       ,status tenant_status not null default 'inactive'
       ,owner_id integer
       ,backoffice_domain text not null
       constraint ensure_not_null_owner_id check (status!='active' or owner_id is not null)
);
create unique index idx_index_owner_id on tenants(owner_id);
create index idx_status on tenants(status);
create index idx_tenants_created_at on tenants(created_at);
create index idx_tenants_updated_at on tenants(updated_at);
create unique index idx_unique_tenants_backoffice_domain on tenants(lower(backoffice_domain));

Inserting rows

TODO

• Quick example of inserting a new row into the tenants table using runInsertMany
• Explanation of the code and how it corresponds to the type-signature of runInsertMany

Getting the ID of a newly inserted row

TODO

• Quick example of inserting a new row into the tenants table and getting back the ID
• Explanation of the type-signature of runInsertManyReturning API call
• Showing the actual SQL queries being executed in the background

Three functions missing from the Opaleye API

TODO: Recommended functions for the following two common operations:

• Inserting a row using Haskell types as input (as against the PG type as input)
• Inserting a single row and getting back the newly inserted ID
• Inserting a single row and getting back the newly inserted row

Dealing with errors

TODO:

• What happens when an insert fails at the DB level, eg. a CHECK CONSTRAINT prevents insertion?
• Take the example of idx_unique_tenants_backoffice_domain

Using a different record-type for INSERTs

TODO

• Example of defining and using a NewTenant type for row creation
• Commentary on why this could be useful
• Link-off to a later section which discusses these design decisions in detail - "Designing a domain API using Opaleye"