DEV Community: justin gage

CRUD with Firestore using the Node.js SDK

justin gage — Mon, 21 Sep 2020 14:07:13 +0000

Cloud Firestore is great for building internal apps because it handles all the complicated ins and outs of managing a database for you. In this tutorial, we’ll show you how to set up a basic CRUD app with the Cloud Firestore using the Node.js SDK:

Set up the Cloud Firestore Node.js SDK
Create data
Read data
Update data
Delete data

Setting up the Node.js SDK for Cloud Firestore

To get started, you’ll first need install the Node.js client library and initialize your instance of the database. You can install the library via npm with:

npm install firebase-admin --save

Once the package is installed, you need to initialize the database. You can do this with Firebase Cloud Functions, the Google Cloud Platform, or through your own server. For this tutorial, we’re going to quickly show you how to initialize the database on your own server using a service account, which is an account identity used to make API requests to an application (learn more about service accounts here).

First, you’ll need to go to the Service Accounts menu in Google Cloud Platform account (it’s under IAM & Admin). From there, generate a new private key, save it as a JSON file, and add it to your server.

Then, in your index.js file, include the firebase-admin library and import your service account private key from your server. Use the firebase-admin library to initialize your application, passing an object with credential as the key and firestore.credential.cert() (with your service account as an argument) as the value.

const fs = require('firebase-admin');

const serviceAccount = require('./path/to/key.json');

fs.initializeApp({
 credential: fs.credential.cert(serviceAccount)
});

Finally, call the firestore() amethod to create your database.

const db = fs.firestore();

Basic CRUD with the Firestore Node.js SDK

This wouldn’t be a CRUD tutorial without some sample data, so let’s get that out of the way. Imagine we’re building internal tools for an online retailer — let’s say a tool to show customer support reps some user data:

{
 "first": "Liam",
 "last": "Ragozzine",
 "address": "133 5th St., San Francisco, CA",
 "birthday": "05/13/1990",
 "age": "30"
},
{
 "first": "Vanessa",
 "last": "Peluso",
 "address": "49 Main St., Tampa, FL",
 "birthday": "11/30/1977",
 "age": "47"
}

This dataset is, of course, overly simplified - for more complex setups, there are a couple of options for structuring your data in Firestore:

Documents
Multiple collections
Subcollections within documents

Each one has pros and cons that play into ease of use, scalability, and complexity. You can read more about structuring data in Cloud Firestore right here.

Got it? Great! Now for CRUD.

Creating data with `set()`

To fill our database, we’re going to use the set() method. First, we’re going to specify that we want to fill the users collection with the collection() method. To do this, simply pass the name of the collection into the method as a string:

const usersDb = db.collection('users');

Now, we need to specify the first document so we can add our new user, Liam Ragozzine. To do this, we’ll use the doc() method. To use this method, pass the id of the document into doc() as a string. For this example, the id is ‘lragozzine’.

const liam = usersDb.doc('lragozzine');

After specifying the document we want to add, we can set the data for the document by passing the data as an object into set().

await liam.set({
 first: 'Liam',
 last: 'Ragozzine',
 address: '133 5th St., San Francisco, CA',
 birthday: '05/13/1990',
 age: '30'
});

Awesome! Now, if we wanted to add our second user, it would look like this:

await usersDb.doc('vpeluso').set({
 first: 'Vanessa',
 last: 'Peluso',
 address: '49 Main St., Tampa, FL',
 birthday: '11/30/1977',
 age: '47'
});

One important thing to note: the set() method will overwrite a document if it already exists. We’ve been using .doc(ID) to create new documents via set(), but if a document with the passed ID already exists, the data you pass in .set() will override whatever currently exists.

Now that we’ve got data in our database, we can move on to reading it.

Reading data with `get()`

To read your data from Firestore, use the get() method. To read from a collection, specify a collection() before calling get(). Or, if you need to read from a document, specify a doc() before calling get().

// get collection
const users = await db.collection('users').get();

// get document
const liam = await db.collection('users').doc('liam').get();

If the document doesn’t exist, the result will be empty. So in the above example, liam would have no data. You can check this by calling exists on the doc. If the document exists, it will return true and you can call data() to read the data of the document. Otherwise, it will return false.

if (!liam.exists) {
 console.log('No document');
} else {
 console.log(liam.data());
}

To filter (“query”) your results, you can use where(), which takes three arguments:

A field from the collection to test the value of
Relational operator (like <, >, or == for example)
The value for the first argument to be evaluated against

For example, if we wanted all documents for users who are younger than 40, we’d use the following:

const under30 = await
 db.collection('users').where('age', '<=', 40).get();

What’s really cool about Cloud Firestore is that you can get real-time updates on your data using the onSnapshot() method. This lets you listen for changes to any of the documents in your database. After initializing the method and passing a callback function to handle the data, you’ll receive an update every time there’s a change to the content.

const liam = db.collection('users').doc('liam');

const observer = liam.onSnapshot(snapshot => {
 console.log(`changes: ${snapshot}`);
}, err => {
 console.log(`Error: ${err}`);
});

Now, your app is current with the latest trends. 😎

Updating data with `set()` and `update()`

In Firestore there are two ways to update data: set() or update(). In short, using set() will generally overwrite the entire document you’re working with, while update() is best for updating particular fields while leaving others untouched.

Starting with set(), if we wanted to make a note in Liam’s document that he is married, we would call set() with the object { married: true }. Like we mentioned above, it’s important to note that if you use set() on a document that already exists, you’ll overwrite it unless you specify merge: true like this:

const liam = await
 db.collection('users').doc('lragozzine').set({
   married: true
 }, { merge: true });

Hint: If you’re not sure whether a document already exists, add merge just in case, so you don’t overwrite your data.

To use the update() method to update a field in a document, pass an object with the field you wish to update as an argument to update().

const liam = await
 db.collection('users').doc('lragozzine').update({
   married: true
 });

To update data in a nested object, you’ll also want to use update(). If we wanted to add an object that contained key-value pairs about a customer’s favorite things (like favorite color, product line, or bad ’90s TV show), we would pass an object where the key is a path, like this:

const liam = await
 db.collection('users').doc('lragozzine').update({
   'favorites.item': 'Ties'
 });

Now that we know how to merge and update our documents, let’s move on to deleting.

Deleting data with `delete()`

This shouldn’t be a surprise: you delete data from Firestore using the delete() method. It’ll look something like this:

await db.collection('users').doc('lragozzine').delete();

This will delete Liam’s entire document from the users database.

Note: If you delete a document, it will not delete the document’s subcollections. So if Liam had a subcollection in his document that contained documents with order data, called orders, then even after deleting the lragozzine document, we would still be able to access the subcollection orders, like this:

const liamOrders = await db.collection('users').doc('lragozzine')
 .collection('orders').doc('123').get();

To delete a field, use FieldValue.delete() in an update() command. First, you must import the FieldValue object, and then call delete() on it, like this:

const FieldValue = fs.firestore.FieldValue;

const r = await
 db.collection('users').doc('lragozzine').update({
   married: FieldValue.delete();
 });

Deleting entire collections gets a little more complicated because you have to make requests to retrieve all documents in a collection and delete them one by one. For more information on deleting collections, check out Firebase’s guide.

Congratulations! You’re now a Cloud Firestore CRUD expert.

How to Build a React Form Component

justin gage — Tue, 15 Sep 2020 17:16:53 +0000

Whether it's a login page or an internal tool, your React app is going to need a form, and handling events and dataflow via raw HTML inputs isn't any fun. This guide will walk you through how to use the react-hook-form library and take you step-by-step through a project where we create a form for an internal tool and extend it with some useful features.

By the end of this article, you’ll know how to:

Create a simple form using react-hook-form
Style your form
Validate your form
Add errors to your form

Getting started / basics

If you’re just here to snag some code, we got you.

For this tutorial, we're working with a table that lists and orders our data, and has a nifty datepicker for sifting through the orders.

Now, while we know most folks place orders online, we have to recognize that sometimes customers like to order over the phone. This means that we need to give our reps the ability to add new orders to the table.

Our React form component needs to be able to:

Accept a customer’s name, address, the date the order was made, and an order number
Validate the data that the customer support rep enters
Display errors to the rep

Here is what the final product will look and feel like:

First things first, react-hook-form is a library built to handle the data in forms and do all the complicated work with validation, error handling, and submitting. There are no physical components in the library. The form component that we will build will just be made with standard jsx tags.

To start off, we’re going to build a simple form with no styling - it’s going to be a bunch of textarea inputs for the reps to fill out the customer’s name, address, the date of the order, and the order number, and, finally, a plain “submit” button. Keep in mind that react-hook-form uses React Hooks. Hooks are a fairly new feature to React, so if you aren’t familiar, we recommend checking out React’s Hooks at a Glance docs before starting this tutorial.

After you import the useForm() hook, there are basic steps to run through:

Use the useForm() hook to get register and handleSubmit().

You need to pass register into the ref prop when you create your form so the values the user adds—and your validation rules—can be submitted. Later on in this tutorial, we will use register to handle validation. handleSubmit() for onSubmit connects your actual form into react-hook-form (which provides register in the first place).

const { register, handleSubmit } = useForm();

Create a function to handle your data, so your data actually winds up in your database

Your backend is your own, but we’re going to pretend that we have a saveData() function in another file that handles saving our data to a database. It’s just console.log(data) for the purposes of this tutorial.

Render your form

We’re creating a React form component, so we will use form-related jsx tags to build it, like <form>, <h1>, <label>, and <input>

Let’s start with a <form> container. Be sure to pass your saveData() function into react-hook-form’s handleSubmit() that you got from the useForm() hook and then into the onSubmit() in the <form> tag. If that sounded really confusing, peep the code below:

<form onSubmit={handleSubmit(data => saveData(data))}>
 ...
</form>

Next, let’s add a header with <h1> so our reps know what this form is for:

<form ...>
 <h1>New Order</h1>
</form>

We’re going to create four <label> and <input> pairs for name, address, date, and order number. For each <input>, be sure to pass register from the useForm() hook into the ref prop and give it a name in the name prop.

<label>Name</label>
<input name="name" ref={register} />
<label>Address</label>
<input name="address" ref={register} />
<label>Date</label>
<input name="date" ref={register} />
<label>Order Number</label>
<input name="order" ref={register} />

Finally, we’ll add a submit button by using an <input> with a “submit” type:

<input type="submit" />

Putting it all together, we will have the following:

import React from "react";
import { useForm } from "react-hook-form";

import saveData from "./some_other_file";

export default function Form() {
 const { register, handleSubmit } = useForm();

 return (
   <form onSubmit={handleSubmit(data => saveData(data))}>
     <h1>New Order</h1>
     <label>Name</label>
     <input name="name" ref={register} />
     <label>Address</label>
     <input name="address" ref={register} />
     <label>Date</label>
     <input name="date" ref={register} />
     <label>Order Number</label>
     <input name="order" ref={register} />
     <input type="submit" />
   </form>
 );
}

Which will look like this:

Cool, now we have a (kinda) working form.

Styling with CSS

You can easily style your form with CSS modules, styled-components, or your favorite kind of styling. For our tutorial, we’re going to use styled-components.

First, we install and import style-components into our project. Then, we create a styled component (based on a <div>) and plop all of our pretty CSS into that. Finally, we wrap our form in the <Styles> tag to apply the styles. Easy!

import React from "react";
import { useForm } from "react-hook-form";
import styled from "styled-components";

import saveData from "./some_other_file";

const Styles = styled.div`
 background: lavender;
 padding: 20px;

 h1 {
   border-bottom: 1px solid white;
   color: #3d3d3d;
   font-family: sans-serif;
   font-size: 20px;
   font-weight: 600;
   line-height: 24px;
   padding: 10px;
   text-align: center;
 }

 form {
   background: white;
   border: 1px solid #dedede;
   display: flex;
   flex-direction: column;
   justify-content: space-around;
   margin: 0 auto;
   max-width: 500px;
   padding: 30px 50px;
 }

 input {
   border: 1px solid #d9d9d9;
   border-radius: 4px;
   box-sizing: border-box;
   padding: 10px;
   width: 100%;
 }

 label {
   color: #3d3d3d;
   display: block;
   font-family: sans-serif;
   font-size: 14px;
   font-weight: 500;
   margin-bottom: 5px;
 }

 .error {
   color: red;
   font-family: sans-serif;
   font-size: 12px;
   height: 30px;
 }

 .submitButton {
   background-color: #6976d9;
   color: white;
   font-family: sans-serif;
   font-size: 14px;
   margin: 20px 0px;
`;

function Form() {
 const { register, handleSubmit } = useForm();

 return (
   <form onSubmit={handleSubmit(data => saveData(data))}>
     <label>Name</label>
     <input name="name" ref={register} />
     <label>Address</label>
     <input name="address" ref={register} />
     <label>Date</label>
     <input name="date" ref={register} />
     <label>Order Number</label>
     <input name="order" ref={register} />
     <input type="submit" className="submitButton" />
   </form>
 );
}

export default function App() {
 return (
   <Styles>
     <Form />
   </Styles>
 );
}

That’s a lot of styling code, but look where it gets us!

Using a React component library

If you hate battling CSS, using a React component library might be a good option. It can add a lot of functionality, like animations, that are time-consuming to implement. If you’re not familiar with the plethora of React component libraries, you can check out our recent post that covers our favorites. For this example, we’re going to use Material UI.

The easiest way to incorporate a React component library is to use one that exposes the ref field as a prop. Then, all you have to do is substitute it for the <input> field and then pass register to that ref.

import { Button, TextField } from "@material-ui/core";

...

function Form() {
 const { register, handleSubmit } = useForm();

 return (
   <>
     <h1>New Order</h1>
     <form onSubmit={handleSubmit(data => saveData(data))}>
       <label>Name</label>
       <TextField name="name" inputRef={register} />
       ...
       // Let's use Material UI's Button too
       <Button variant="contained" color="primary">Submit</Button>
     </form>
   </>
 );
}

Now, we get the sleekness and functionality of Material-UI.

Validate your React form component

The last thing we want is for our customer support reps to add faulty data into our database. If we have any other apps using the same data, like reports running on the number of orders made in a certain time span, then adding in a date that isn’t formatted correctly could ruin the whole thing.

For our use case, we are going to add validation in the form of:

Making all fields required
Adding an address validator
Validating date
Validating order number

Making all fields required

All you have to do to make a field required is pass an object into the register() prop in input that says {required: true}.

<input name="name" ref={register({ required: true })} />

This will flag the errors prop for the “name” field, which can then be used to add an error message (see next section).

Adding an address validator

To make our life easy, we are going to add a validator to check whether the address the user enters exists and is properly formatted.We’ll use a mock function from our example and show you how to integrate it into the React form component.

First, we define our validator function. For our purposes, we are just checking a specific string. This is where you would hook into your validator library.

function addressValidator(address) {
 if (address === "123 1st St., New York, NY") {
   return true;
 }
 return false;
}

Next, we add validation to the register for address input. Make sure to pass the “value” that the user enters. If your validator function returns true, then it is validated and no error will appear.

<input name="address" ref={register({
 required: true,
 validate: value => addressValidator(value),
})} />

If you want to go further with your address validation than just adding a mock function (which you probably do because this is useless in production), we recommend checking out this awesome tutorial from HERE on validating location data.

Validating date

To make sure users only enter valid dates into our date input field, we're going to add type="date" to our date input field in the React form component in order to force the user to fill out the field in our specified format.

In some browsers (like Chrome), this will add a DatePicker to the input box. In all browsers, it will provide a clear format for the date the rep should enter and will not let them use a different format. We can even add a max date to stop the customer support rep from accidentally adding a future order date (as much as we’d all love to just skip 2020).

For this section, we’re going to use the moment library since it makes formatting dates much easier than JavaScript’s native date.

import moment from 'moment';

...
<input
 name="date"
 type="date"
 max={moment().format("YYYY-MM-DD")}
 ref={register({ required: true })}
/>

The cool thing about validating the date in the input as opposed to the register is that we won’t have to waste time and energy building out error messages since the input will stop our user from entering an erroneous value.

Looking good!

Validating order number

For our order number field, we need to add validation that ensures the input is a valid order number in our system. react-hook-form has a really easy way to apply regex validation by passing a “pattern” into the register.

Let’s say that our order numbers are always 14 integers long (though this regex could easily be updated to fit whatever your order numbers look like).

<input
 name="order"
 ref={register({
   required: true,
   minLength: 14,
   maxLength: 14,
   pattern: /\d{14}/,
 })}
/>

Great work! Now an error will bubble up when the order number does not meet our specified pattern. For more details, you can read more in the register section of the react-hook-form documentation.

Communicate errors in your React form component

Adding error handling to your form is easy with react-hook-form. Let’s start with communicating that certain fields are required. All we have to do is get errors from the useForm() hook and then add a conditional to render them under the input if they are needed.

function Form() {
 const { register, errors, handleSubmit } = useForm();

 return (
   <form onSubmit={handleSubmit(data => saveData(data))}>
     <h1>New Order</h1>
     <label>Name</label>
     <input name="name" ref={register({ required: true })} />
     {errors.name && "Required"}
     <label>Address</label>
     <input
       name="address"
       ref={register({
         required: true,
         validate: value => addressValidator(value)
       })}
     />
     {errors.address && "Required"}
     <label>Date</label>
     <input
       name="date"
       type="date"
       max={moment().format("YYYY-MM-DD")}
       ref={register({ required: true })}
     />
     {errors.date && "Required"}
     <label>Order Number</label>
     <input
       name="order"
       ref={register({
         required: true,
         pattern: /\d{14}/,
       })}
     />
     {errors.order && "Required"}
     <input type="submit" />
   </form>
 );
}

Notice how we refer to the error for a specific input field by using errors.name and errors.date. And here is what our error looks like:

One last issue - since these errors are conditionals, they’ll increase the size of our form. To get around this, we will make a simple error component that renders the height of the error, even if there is no text. We’ll also color the text red, so it’s easier to see.

import React from "react";
import { useForm } from "react-hook-form";
import styled from "styled-components";

import saveData from "./some_other_file";

const Styles = styled.div`
 background: lavender;
 ...
 .error {
   color: red;
   font-family: sans-serif;
   font-size: 12px;
   height: 30px;
 }
`;

// Render " " if no errors, or error message if errors
export function Error({ errors }) {
 return <div className={"error"}>{errors ? errors.message : " "}</div>;
}

export function Form() {
 const { register, handleSubmit } = useForm();

 return (
   <form onSubmit={handleSubmit(data => saveData(data))}>
     <h1>New Order</h1>
     <label>Name</label>
     <input name="name" ref={register({ required: true })} />
    <Error errors={errors.name} />
     <label>Address</label>
     <input
       name="address"
       ref={register({
         required: true,
         validate: value => addressValidator(value)
       })}
     />
    <Error errors={errors.address} />
     <label>Date</label>
     <input
       name="date"
       type="date"
       max={moment().format("YYYY-MM-DD")}
       ref={register({ required: true })}
     />
     <Error errors={errors.date} />
     <label>Order Number</label>
     <input
       name="order"
       ref={register({
         required: true,
         pattern: /\d{14}/,
       })}
     />
     <Error errors={errors.order} />
     <input type="submit" className="submitButton" />
   </form>
 );
}
...

But wait! There’s no error message text to render. To fix this, let’s start with the Required validation. We do this by adding the error message for that particular type of error.

<input name="name" ref={register({ required: 'Required' })} />

Go through your code and change required: true to required: 'Required' in every place that you see it. Now this functions a lot more like a form we would expect to see in the real world:

But hold on! We validated a lot more than just required fields. Let’s get a little more granular with these errors, so our customer support reps know how to fix the problem.

Adding an address error

To add an address error to your validate section, simply add an || so that if your validation function returns “false,” it will display your message instead.

<input
 name="address"
 ref={register({
   required: 'Required',
   validate: value => addressValidator(value) || 'Invalid address',
 })}
/>

Here is what your error will look like:

Adding an order number error

In our system, our order numbers are always 14 digits long and made up of positive integers between 0-9. To verify this order number pattern, we are going to use minLength and maxLength to verify length and pattern to verify the pattern.

First, change “minLength”, “maxLength”, and “pattern” into objects with a value key, where the regex pattern or number you defined is the value, and a message key, where the value is your error message.

<input
 name="order"
 ref={register({
   required: 'Required',
   minLength: {
     value: 14,
     message: 'Order number too short',
   },
   maxLength: {
     value: 14,
     message: 'Order number too long',
   },
   pattern: {
     value: /\d{14}/,
     message: "Invalid order number",
   },
 })}
/>

Here is what your error will look like:

And that’s it for errors! Check out react-hook-form’s API docs for more information.

Your React form component with `react-hook-form`

Here is our final React form component:

For more code samples that cover the vast range of features that react-hook-form has to offer, check out React Hook Form’s website. And for a full version of this code that you can test out and play around with, check out our code sandbox.

TL;DR: Syntax roundup

We know that this tutorial covered a ton of features for forms in react-hook-form, so just to make sure you didn’t miss anything, here is a roundup of the features we covered:

Create a simple React form component

import React from "react";
import { useForm } from "react-hook-form";

import saveData from "./some-other-file";

export default function Form() {
 const { register, handleSubmit } = useForm();

 return (
   <form onSubmit={handleSubmit(data => saveData(data))}>
     <label>Field</label>
     <input name="field" ref={register} />
     <input type="submit" />
   </form>
 );
}

Style your React form component

import React from "react";
import { useForm } from "react-hook-form";
import styled from "styled-components";

import saveData from "./some_other_file";

const Styles = styled.div`
background: lavender;
 padding: 20px;

 h1 {
   border-bottom: 1px solid white;
   color: #3d3d3d;
   font-family: sans-serif;
   font-size: 20px;
   font-weight: 600;
   line-height: 24px;
   padding: 10px;
   text-align: center;
 }

 form {
   background: white;
   border: 1px solid #dedede;
   display: flex;
   flex-direction: column;
   justify-content: space-around;
   margin: 0 auto;
   max-width: 500px;
   padding: 30px 50px;
 }

 input {
   border: 1px solid #d9d9d9;
   border-radius: 4px;
   box-sizing: border-box;
   padding: 10px;
   width: 100%;
 }

 label {
   color: #3d3d3d;
   display: block;
   font-family: sans-serif;
   font-size: 14px;
   font-weight: 500;
   margin-bottom: 5px;
 }

 .submitButton {
   background-color: #6976d9;
   color: white;
   font-family: sans-serif;
   font-size: 14px;
   margin: 20px 0px;
 }
`;

export function Form() {
 const { register, handleSubmit } = useForm();

 return (
   <form onSubmit={handleSubmit(data => saveData(data))}>
     <label>Field</label>
     <input name="field" ref={register} />
     <input type="submit" className="submitButton" />
   </form>
 );
}

export default function App() {
 return (
   <Styles>
     <Form />
   </Styles>
 );
}

Validate your React form component

<form onSubmit={handleSubmit(data => saveData(data))}>
 <label>Name</label>
 <input name="name" ref={register({ required: true })} />
 <label>Address</label>
 <input
   name="address"
   ref={register({
     required: true,
     validate: value => addressValidator(value)
   })}
 />
 <label>Date</label>
 <input
   name="date"
   type="date"
   max={moment().format("YYYY-MM-DD")}
   ref={register({ required: true })}
 />
 <label>Order Number</label>
 <input
   name="order"
   ref={register({
     required: true,
     pattern: /\d{14}/,
   })}
 />
 <input type="submit" />
</form>

Add errors to your React form component

export default function Form() {
 const { register, errors, handleSubmit } = useForm();

 return (
   <form onSubmit={handleSubmit(data => saveData(data))}>
     <label>Field</label>
     <input name="field" ref={register({ required: true })} />
     {errors.name && "Name is required"}
   </form>
 );
}

Full form

import React from "react";
import { useForm } from "react-hook-form";
import styled from "styled-components";
import moment from 'moment';

import saveData from "./some_other_file";

const Styles = styled.div`
 background: lavender;
 padding: 20px;

 h1 {
   border-bottom: 1px solid white;
   color: #3d3d3d;
   font-family: sans-serif;
   font-size: 20px;
   font-weight: 600;
   line-height: 24px;
   padding: 10px;
   text-align: center;
 }

 form {
   background: white;
   border: 1px solid #dedede;
   display: flex;
   flex-direction: column;
   justify-content: space-around;
   margin: 0 auto;
   max-width: 500px;
   padding: 30px 50px;
 }

 input {
   border: 1px solid #d9d9d9;
   border-radius: 4px;
   box-sizing: border-box;
   padding: 10px;
   width: 100%;
 }

 label {
   color: #3d3d3d;
   display: block;
   font-family: sans-serif;
   font-size: 14px;
   font-weight: 500;
   margin-bottom: 5px;
 }

 .error {
   color: red;
   font-family: sans-serif;
   font-size: 12px;
   height: 30px;
 }

 .submitButton {
   background-color: #6976d9;
   color: white;
   font-family: sans-serif;
   font-size: 14px;
   margin: 20px 0px;
 }
`;

export function addressValidator(address) {
 if (address === "123 1st St., New York, NY") {
   return true;
 }
 return false;
}

export function Error({ errors }) {
 return <div className={"error"}>{errors ? errors.message : " "}</div>;
}

export function Form() {
 const { register, errors, handleSubmit } = useForm();

 return (
   <form onSubmit={handleSubmit(data => saveData(data))}>
     <h1>New Order</h1>
     <label>Name</label>
     <input name="name" ref={register({ required: 'Required' })} />
     <Error errors={errors.name} />
     <label>Address</label>
     <input
       name="address"
       ref={register({
         required: 'Required',
         validate: value => addressValidator(value) || 'Invalid address',
       })}
     />
     <Error errors={errors.address} />
     <label>Date</label>
     <input
       name="date"
       type="date"
       max={moment().format("YYYY-MM-DD")}
       ref={register({ required: 'Required' })}
     />
     <Error errors={errors.date} />
     <label>Order Number</label>
     <input
       name="order"
       ref={register({
         required: 'Required',
         minLength: {
           value: 14,
           message: 'Order number too short',
         },
         maxLength: {
           value: 14,
           message: 'Order number too long',
         },
         pattern: {
           value: /\d{14}/,
           message: "Invalid order number",
         },
     })} />
     <Error errors={errors.order} />
     <input type="submit" className="submitButton" />
   </form>
 );
}

export default function App() {
 return (
   <Styles>
     <Form />
   </Styles>
 );
}

Other React form libraries

react-hook-form has nearly 13K stars on GitHub, but it's worth taking a second to explain why we decided to go with react-hook-form instead of other popular React form libraries, like formik and react-final-form. It’s worth recognizing that these form libraries are pretty awesome in their own ways:

formik has top-notch documentation and extremely thorough tutorials.
react-final-form is great for those used to working with redux-final-form.

Ultimately, we chose react-hook-form because it has a tiny bundle size, no dependencies, and is relatively new (many sources, like LogRocket and ITNEXT, are claiming it is the best library for building forms in React) compared to the rest. If you’re interested in learning about some other ways to build React forms, check this out.

CRUD with the Firebase RealtimeDB's REST API

justin gage — Tue, 08 Sep 2020 18:16:53 +0000

If you got started using Firebase before last year, chances are you’re working with the (now legacy) RealtimeDB and not Firestore. This guide will walk you through how to do basic CRUD on your giant JSON object (“database”) - creating, reading, updating, and deleting objects. We’ll also go over setting up db security, as well as some notes on data structure best practices.

For our example, we’re working with the database’s REST API, but the principles apply to the Node / iOS / Android / any other client library.

Realtime Database REST API basics

Any URL within the Firebase RealtimeDB can be used as a REST endpoint by adding .json to the end. One important thing to note is that all requests must be sent over HTTPS, not HTTP. You can use any request client that you want for this tutorial, but we are going to use cURL because it’s easy to use and works right from your terminal.

Let’s pretend that we’re creating an internal app for an online retailer. The users of the internal app will be customer support reps who will need access to a table full of customer data. Using the Firebase RealtimeDB REST API, we’ll give our customer support reps the ability to create, read, update, and delete data from the Firebase RealtimeDB.

Note: We're using test mode, so we don't need to authenticate our requests. To read more about authentication check out our section below. on data security.

For this tutorial, we’ll use the following data:

{
 "eantoni": {
   "name": "Erika Antoni",
   "state": "California",
   "emails": {
     "primary": "eantoni@example.com",
     "secondary": "erika@work.com"
   }
 }
}

Each object in the “users” database will have a unique key (made from the user’s first initial and last name), name, state, and emails, which are a nested object containing a primary and secondary email.

One thing to note: the RealtimeDB has a “test” mode that you can configure when you create it, which means that no authentication is required. It’s useful for testing, but remember not to put any sensitive data there.

To get started, we’ll first need to grab the URL of the database so we know where to send our requests. When you log in to Firebase, you should see something like this:

The base of the URL you are going to use is that one at the top:
https://{db-name}.firebaseio.com. Instead of {db-name}, you’ll use the auto-generated db name.

Creating data in the RealtimeDB

We may have a database created, but right now, there’s nothing in it. Let’s change that. We’re going to create a record for an example user—Erika Antoni—in our database. To do this, we will use PUT instead of POST since we want to define the unique key for the user.

Note that the URL we are going to use has users.json at the end of it. This endpoint doesn’t really exist - we’re actually creating a top level “users” object (via the endpoint itself) then and populating it with our payload, all in one request.

curl -X PUT -d '{
 "eantoni": {
   "name": "Erika Antoni",
   "state": "California",
   "emails": {
     "primary": "eantoni@example.com",
     "secondary": "erika@work.com"
   }
 }' 'https://{db-name}.firebaseio.com/users.json'

Now, when we return to our database in the Firebase console, we should see our data as a JSON tree:

Reading data from the RealtimeDB

To read data from our Firebase RealtimeDB, we are going to use GET with the added URI parameter “print” at the end because we don’t like looking at long, confusing lines of JSON.

curl 'https://{db-name}.firebaseio.com/users/eantoni.json?print=pretty'

And here is our output:

{
 "address" : "55 S. 5th St., Denver, CO",
 "emails" : {
   "primary" : "enantoni@example.com",
   "work" : "erika@work.com"
 },
 "name" : "Erika Antoni",
 "state" : "California"
}

Notice how we specified the URL to read from the eantoni node. If we wanted to read the whole users database, we would simply have it go to users.json instead of users/enantoni.json. These endpoint ergonomics can be really useful if you structure your objects correctly (refer to the Firebase docs about structuring your data). We’ll cover more about structuring later on.

You probably don’t want to return your entire database at once, so let’s try filtering our request. Maybe we want to order the data alphabetically by the user’s state. Here is our sample data:

{
 "eantoni": {
   "name": "Erika Antoni",
   "state": "California",
   "emails": {
     "primary": "eantoni@example.com",
     "secondary": "erika@work.com"
   }
 },
 "mmiller": {
   "name": "Michelle Miller",
   "state": "Arizona",
   "emails": {
     "primary": "mmiller@example.com",
     "secondary": "michelle@work.com"
   }
 }
}

All we need to do is add an orderBy URI parameter to the end of our request and specify that “state” is the child key we want the ordering to be based on.

curl 'https://{db-name}.firebaseio.com/users.json?orderBy="state"&print=pretty'

This is the response we’ll get:

{
 "mmiller": {
   "name": "Michelle Miller",
   "state": "Arizona",
   "emails": {
     "primary": "mmiller@example.com",
     "secondary": "michelle@work.com"
   }
 },
 "eantoni": {
   "name": "Erika Antoni",
   "state": "California",
   "emails": {
     "primary": "eantoni@example.com",
     "secondary": "erika@work.com"
   }
 }
}

You can check out the rest of the URI parameters for GET requests in the Firebase docs. You can also limit the results of your query (by first or last results).

Updating data in Firebase RealtimeDB

Say we want to add a field to our user, like an address. To do this, we’ll use PATCH and clearly specify the path when writing the data for the object. We can do this by changing the endpoint:

curl -X PATCH -d '{
 "address": "123 1st St., San Francisco, CA"}' \
'https://{db-name}.firebaseio.com/users/eantoni.json'

. . . or by specifying the path for the data in the request payload:

curl -X PATCH -d '{
 "eantoni/address": "123 1st St., San Francisco, CA"}' \
 'https://{db-name}.firebaseio.com/users.json'

Now we’ve added Erika’s address without disturbing the rest of her data:

Keep in mind that neglecting to append the new path to the URL or data will result in overwriting the entire node.

Similarly, if we want to update an existing field (instead of adding a new one), we just form the request using PATCH and the specific path. Say we found out that Erika moved to Colorado. We can actually update both the “address” and “state” fields for Erika at the same time like this:

curl -X PATCH -d '{
 "address": "55 S. 5th St., Denver, CO",
 "state": "Colorado"
}' \
 'https://{db-name}.firebaseio.com/users/eantoni.json'

And here is our updated record:

Now Erika lives in Colorado. Sweet. ⛰️

Deleting data from the Firebase RealtimeDB

If you want to give your users the ability to delete a record, use DELETE.

curl -X DELETE 'https://{db-name}.firebaseio.com/users/eantoni.json'

This will delete Erika’s entire record. If we only wanted to delete a field in her record, we could do so like this:

curl -X DELETE 'https://{db-name}.firebaseio.com/users/eantoni/emails/work.json'

Again, this is where the endpoint-based record location comes in the clutch - referencing and deleting nested fields just requires a deeper endpoint. And just like that, all of our data for Erika is gone, and we’re right back where we started.

Data security for the Firebase RealtimeDB

Like you’d be able to do with something like Postgres, Firebase allows users to set Rules so your DB isn’t open to random traffic. You can configure Rules via the GUI on Firebase’s website, or you can use the Rules REST API.

Rules are structured similar to a JSON object and give you the ability to specify read and write permissions at path-level, using .read and .write as keys in an object definition. For instance, if we wanted our database to be global read-only, we would do this:

{
 "rules": {
   "users": {
     ".read": true,
     ".write": false
   }
 }
}

“Users” is our root database ref, so the rules in its object get applied to the whole database.

.read and .write don’t have to just be boolean values. We can also use variables and write conditionals. Let’s say that we only want our user, Erika Antoni, to be able to access her data object. We would do so like this:

{
 "rules": {
   "users": {
     "$uid": {
       ".read": "$uid === auth.uid",
       ".write": "$uid === auth.uid"
     }
   }
 }
}

$uid and auth.uid are built-in variables that come from Firebase Authentication (which you can configure to work with your app).

First, you’ll need to generate an access token to be able to write Rules via REST (follow this guide for using Google’s OAuth2). Then, use PUT with the Rules object as your payload and be sure to include your newly generated access token.

curl -X PUT -d '{ "rules": { ".read": true, ".write": false } }'
 'https://{db-name}.firebaseio.com/.settings/rules.json?
   access_token={access-token}'

It’s important to remember that any Rules you add via REST API will overwrite any existing ones that have been configured. To avoid overwriting Rules, you can first GET configured Rules like this:

curl 'https://{db-name}.firebaseio.com/.settings/rules.json?
 access_token={access-token}'

You can always use the GUI if you’re afraid of overwriting everything.

Once you’ve set up your Rules, you simply need to add an auth URI parameter to each request, like this:

curl -X GET 'https://{db-name}.firebaseio.com/users/eantoni.json?auth={cred}'

In this case, cred is either an auth token or a Firebase app secret. You can read more here.

If you don’t want to use Firebase’s Authentication, you can set up your own custom authentication. First, you will need to generate a Google OAuth2 access token using an account that has permissions to your Realtime Database project. Use OAuth2 to generate a new private key, then use a client library of your choosing to generate the token.

If we were to use the Node.js client library, it would look like this:

var {google} = require(“googleapis”);
var key = require(“path/to/your/service/account/key.json”);

var jwt = new google.auth.JWT(
  serviceAccount.firebase_database,
  null,
  key.private_key,
  [“https://www.googleapis.com/auth/firebase.database”], //scope
);

// generate access token
jwt.authorize(function(err, tokens) {
  if (err || tokens.access_token === null) {
    console.log(“error”);
  } else {
    var access_token = tokens.access_token;
  }
});

Once your access token is generated, you can use it to authenticate with the REST API, just like using the Firebase Authentication, like this:

curl -X GET 'https://{db-name}.firebaseio.com/users/eantoni.json?auth={cred}'

You can read more about setting up authentication for REST requests in the RealtimeDB docs.

Structuring data for the Firebase RealtimeDB

Firebase’s RealtimeDB stores data as a JSON tree (which makes simple data management easy, but will make you sweat if you need to organize something more complex later on). The objects we’ve been working with in this post fit that bill:

{
 "users": {
   "eantoni": {
     "name": "Erika Antoni",
     "state": "California",
     "emails": {
       "primary": "eantoni@example.com",
       "secondary": "erika@work.com",
     },
   },
   ...
 }
}

When designing a database that uses a JSON tree structure (as opposed to the document and collection structure in Cloud Firestore), there are a few key things to keep in mind:

Try not to nest data. When you fetch data, all child nodes are returned with the one you fetched. This means that not only are you being inefficient by grabbing extra data, but you’re also unnecessarily exposing that data to the client.
Flatten your data structures. Keep only relevant data in each path and make the client do separate calls to get the data they need. This will keep code clear and understandable while helping you avoid writing lots of loops.
Create indexable data. If you have lots of data, iterating through all of it to gather the information you need on a regular basis isn’t feasible. Instead, create groups with indexes to indicate membership. For example, let’s say we have our “users” table from above. We often need to find a list of all customers who live in the same state. We could do this by iterating through every customer and checking their “state,” but instead, we can do the following:

{
 "users": {
   "eantoni": {
     "name": "Erika Antoni",
     "state": "California",
     ...
   },
   ...
 },
 "states": {
   "california": {
     "residents": {
       "eantoni": true,
       ...
     },
   },
   ...
 }
}

Now “states” can be easily indexed to find the members, which scales super well.

For more information on structuring your data, check out Firebase’s docs.

Getting started with react-datepicker

justin gage — Wed, 02 Sep 2020 21:15:03 +0000

Nobody likes working with dates (especially in Javascript) but we all have to. If your app requires a date picker component (think: select a date from a calendar), HackerOne has you covered with the react-datepicker library. This guide will walk through react-datepicker basics with a focus on building internal tools

By the end of this article, you’ll know how to:

Import and use react-datepicker in your React app to build a simple datepicker
Customize your datepicker with time functionality, disabling dates, and a clear button

`react-datepicker` basics and setup

react-datepicker is a React library that is used by more than 40K developers with almost 5K stars on GitHub. The simple version of the datepicker component is incredibly easy to use and comes with some great features, including:

Localization
Accessibility
Advanced customization
Range support

The entire library comes out to 437kB (the small size partially thanks to using date-fns instead of moment) and runs on the latest versions of Chrome, Firefox, and IE10+.

In this tutorial, we’ll rum with an example use case: building an internal tool for a customer support team. Let’s say that we’ve already built a table that displays order data using react-table, and now we just need to add two datepickers so customer support reps can view orders between a range of dates.

Our datepicker needs to have the ability to:

Open a calendar view when the datepicker is clicked
Display selected range
Display selectable times
Disable future dates
Clear the datepicker when the X button is clicked

And here's what the final product will look and feel like:

Getting started

If you're just here for the code, go ahead and jump to the TL;DR syntax summary.

To start off, let’s keep things simple. We’ll create one datepicker without added customization, style, or features (literally all we’re doing is picking a date).

We'll be using the useState() hook in our function. If you’re not familiar with React Hooks, we recommend checking out React’s Hooks at a Glance docs before starting this tutorial.

There are three simple steps for creating a datepicker:

Import the datepicker component from react-datepicker and react-datepicker.css for styling.
Set an initial date in the state (using the useState() Hook).
Render the datepicker, telling onChange to update the date in state using the setDate() function.

import React, { useState } from "react";

import DatePicker from "react-datepicker";
import "react-datepicker/dist/react-datepicker.css";

export default function TableDatePicker() {
 const [date, setDate] = useState(new Date());

 return (
   <DatePicker selected={date} onChange={date => setDate(date)} />
 );
}

And, voila, you have a datepicker that starts on today’s date and will open a calendar to select a new date when clicked!

Create a datepicker range

The first feature we are going to add is the ability to set a date range on our datepicker. We want our customer support reps to be able to narrow down orders that happened between a specific set of dates.

react-datepicker doesn’t have native support for ranges, but we can get around it by doubling the datepicker component: one for the start date and one for the end date. Now that we already have our first datepicker, we simply need to adjust it to specifically handle a start date:

export default function TableDatePicker() {
 const [startDate, setStartDate] = useState(new Date());

 return (
   <DatePicker
     selected={startDate}
     onChange={date => setStartDate(date)}
     selectsStart // tells this DatePicker that it is part of a range*
     startDate={startDate}
   />
 );
}

Then, we'll create a second datepicker that can handle the end date. Note that the endDate datepicker needs a minDate to be set. Since we’re picking a range, we can’t have the endDate be earlier than the startDate (time doesn’t work like that!).

export default function TableDatePicker() {
 const [startDate, setStartDate] = useState(new Date());
 const [endDate, setEndDate] = useState(new Date());

 return (
   <div> // don't forget to wrap your DatePickers
     <DatePicker
       selected={startDate}
       selectsStart
       startDate={startDate}
       endDate={endDate} // add the endDate to your startDate DatePicker now that it is defined
       onChange={date => setStartDate(date)}
     />
    <DatePicker
       selected={endDate}
       selectsEnd
       startDate={startDate}
       endDate={endDate}
       minDate={startDate}
       onChange={date => setEndDate(date)}
     />
   </div>
 );
}

And that’s it! The final version of the code all put together will look like this:

import React, { useState } from "react";

import DatePicker from "react-datepicker";
import "react-datepicker/dist/react-datepicker.css";

export default function TableDatePicker() {
 const [startDate, setStartDate] = useState(new Date());
 const [endDate, setEndDate] = useState(new Date());

 return (
   <div>
     <DatePicker
       selected={startDate}
       selectsStart
       startDate={startDate}
       endDate={endDate}
       onChange={date => setStartDate(date)}
     />
     <DatePicker
       selected={endDate}
       selectsEnd
       startDate={startDate}
       endDate={endDate}
       minDate={startDate}
       onChange={date => setEndDate(date)}
     />
   </div>
 );
}

Now we have two datepickers that the customer support reps can use to select their ranges. Plus, the react-datepicker library already handles highlighting the selected dates for us.

Selecting times

Let’s assume that our example company has thousands of rows of data, filled with row after row of customer order data. Even if reps only select a couple days, they’ll still get flooded with a ton of data. To make life easier for them, let’s add in time to the datepicker so that the range can get super granular.

react-datepicker comes with two options for adding time to the calendar view:

While the input version can be very useful since it lets the user type in any time they want, we’re going to go with the select version because we’re okay with only letting our customer support reps choose times in half-hour increments.

To add the time selector to our datepickers, we’ll first add showTimeSelect to our datepicker component to let it know we want to display the time selector, and then we’ll format the date that’s displayed in the datepicker window so that it shows time too.

<DatePicker
 showTimeSelect
 dateFormat="MMMM d, yyyy h:mmaa"
 selected={startDate}
 selectsStart
 startDate={startDate}
 endDate={endDate}
 onChange={date => setStartDate(date)}
/>
<DatePicker
 showTimeSelect
 dateFormat="MMMM d, yyyy h:mmaa"
 selected={startDate}
 selectsEnd
 startDate={startDate}
 endDate={endDate}
 minDate={startDate}
 onChange={date => setEndDate(date)}
/>

Hey, that took almost no time at all (sorry).

Extend your datepicker with more features

We've just barely scratched the surface of what react-datepicker can do. A few useful ones:

→ Add a placeholder prompt to the datepicker

Instead of having the datepicker start on today’s date, why don’t we prompt the user to enter a start and end date so that our range datepicker is a little more clear? To do this, we need to add a placeholderText field and change the initial startDate and endDate values to null.

export default function TableDatePicker() {
 const [startDate, setStartDate] = useState(null);
 const [endDate, setEndDate] = useState(null);

 return (
   <div>
     <DatePicker
       placeholderText="Select Start Date"
       showTimeSelect
       dateFormat="MMMM d, yyyy h:mmaa"
       selected={startDate}
       selectsStart
       startDate={startDate}
       endDate={endDate}
       onChange={date => setStartDate(date)}
     />
     <DatePicker
       placeholderText="Select End Date"
       showTimeSelect
       dateFormat="MMMM d, yyyy h:mmaa"
       selected={endDate}
       selectsEnd
       startDate={startDate}
       endDate={endDate}
       minDate={startDate}
       onChange={date => setEndDate(date)}
     />
   </div>
 )
}

→ Disable future dates

Since our reps are dealing with orders from customers, all of the data in the table they are viewing will be in the past. If they were to select a date in the future, there would be no data to view, and the rep would be looking at an empty table. Instead of that happening, lets disable all future dates in the calendar so that the rep can’t select them.

For this feature, we’re going to add the filterDate field and define an arrow function that returns a Boolean value depending on whether the date displayed is in the future or not.

<DatePicker
 filterDate={d => {
   return new Date() > d;
 }}
 placeholderText="Select Start Date"
 showTimeSelect
 dateFormat="MMMM d, yyyy h:mmaa"
 selected={startDate}
 selectsStart
 startDate={startDate}
 endDate={endDate}
 onChange={date => setStartDate(date)}
/>
<DatePicker
 filterDate={d => {
   return new Date() > d;
 }}
 placeholderText="Select End Date"
 showTimeSelect
 dateFormat="MMMM d, yyyy h:mmaa"
 selected={endDate}
 selectsEnd
 startDate={startDate}
 endDate={endDate}
 minDate={startDate}
 onChange={date => setEndDate(date)}
/>

Great! Now the rep will only be able to filter the table based on dates that actually have order data.

→ Add a clear button to the datepicker

If the customer support rep decides that they no longer want to filter by date, we want to make that experience as easy as possible for them. Instead, let’s give them a simple X they can press to clear the datepicker.

<DatePicker
 isClearable
 filterDate={d => {
   return new Date() > d;
 }}
 placeholderText="Select Start Date"
 showTimeSelect
 dateFormat="MMMM d, yyyy h:mmaa"
 selected={startDate}
 selectsStart
 startDate={startDate}
 endDate={endDate}
 onChange={date => setStartDate(date)}
/>
<DatePicker
 isClearable
 filterDate={d => {
   return new Date() > d;
 }} 
 placeholderText="Select End Date"
 showTimeSelect
 dateFormat="MMMM d, yyyy h:mmaa"
 selected={endDate}
 selectsEnd
 startDate={startDate}
 endDate={endDate}
 minDate={startDate}
 onChange={date => setEndDate(date)}
/>

This one is just a simple Boolean prop type that is passed in to datepicker. Here is what the default clear button looks like:

At this point we should probably let you know that we added a little extra styling to make the datepicker look this way. If you choose to display the time in your datepicker, the clear button sits right on top of it, like this:

In order to expand the width of the datepicker boxes, we have to override some of the styling that we imported in react-datepicker.css. To do this, we are going to use Styled Components, a popular React library used for low-level styling.

First, import styled-components, and then define a new Styles component that will wrap around your datepicker. Then, move all of your datepicker code into a new function. You want your default function to export just your datepicker code (all wrapped up) with the <Styles> component around it.

Take careful note of the react-datepicker classnames that must be overwritten:

react-datepicker-wrapper
react-datepicker__input-container
react-datepicker__input-container input

import React, { useState } from "react";
import DatePicker from "react-datepicker";
import "react-datepicker/dist/react-datepicker.css";
import styled from "styled-components";

const Styles = styled.div`
 .react-datepicker-wrapper,
 .react-datepicker__input-container,
 .react-datepicker__input-container input {
   width: 175px;
 }
`;

export function DatePickerRange() {
 const [startDate, setStartDate] = useState(null);
 const [endDate, setEndDate] = useState(null);

 return (
   <div>
     <DatePicker
       isClearable
       ...
     />
     <DatePicker
       isClearable
       ...
     />
   </div>
 );
}

export default function TableDatePicker() {
 return (
   <Styles>
     <DatePickerRange />
   </Styles>
 );
}

And now that we’re overwriting classnames from react-datepicker, we might as well change up the style of our clear button. To override the button styles, you just need to change .react-datepicker__close-icon::before and .react-datepicker__close-icon::after.

const Styles = styled.div`
 .react-datepicker-wrapper,
 .react-datepicker__input-container,
 .react-datepicker__input-container input {
   width: 175px;
 }

 .react-datepicker__close-icon::before,
 .react-datepicker__close-icon::after {
   background-color: red;
 }
`;

Here is our new, styled X button:

Extracting data from the datepicker

Extracting the selected date(s) from your datepicker will depend on how your code is set up. Maybe that’s obvious, but let me explain.

If I’m within the same component, getting the startDate and endDate are as simple as accessing the state.

<div>
 <div style={{ display: "flex" }}>
   <DatePicker ... />
   <DatePicker ... />
 </div>
 <div>Selected start date={startDate ? startDate.toString() : null}</div>
 <div>Selected end date={endDate ? endDate.toString() : null}</div>
</div>

Here you can see we are printing the selected date below the datepickers. Note that startDate and endDate are saved as Date objects so you must convert them to Strings with the toString() method before printing (or else your IDE will yell at you).

If you are working with multiple components, then you will likely need to lift the state out of the datepicker components. That work goes a bit beyond the scope of this tutorial, but you can read up on how to do it in the React docs.

Your datepicker component with `react-datepicker`

Here is our final datepicker:

Hopefully, this tutorial helped you understand how to create and customize a datepicker in React to suit your needs. We know we covered a lot of features here, so for good measure, here is the code for the datepicker we created, in its entirety:

import React, { useState } from "react";

import DatePicker from "react-datepicker";
import "react-datepicker/dist/react-datepicker.css";
import styled from "styled-components";

const Styles = styled.div`
 .react-datepicker-wrapper,
 .react-datepicker__input-container,
 .react-datepicker__input-container input {
   width: 175px;
 }

 .react-datepicker__close-icon::before,
 .react-datepicker__close-icon::after {
   background-color: grey;
 }
`;

export function DatePickerRange() {
 const [startDate, setStartDate] = useState(null);
 const [endDate, setEndDate] = useState(null);

 return (
   <div style={{ display: "flex" }}>
     <DatePicker
       isClearable
       filterDate={d => {
         return new Date() > d;
       }}
       placeholderText="Select Start Date"
       showTimeSelect
       dateFormat="MMMM d, yyyy h:mmaa"
       selected={startDate}
       selectsStart
       startDate={startDate}
       endDate={endDate}
       onChange={date => setStartDate(date)}
     />
     <DatePicker
       isClearable
       filterDate={d => {
         return new Date() > d;
       }}
       placeholderText="Select End Date"
       showTimeSelect
       dateFormat="MMMM d, yyyy h:mmaa"
       selected={endDate}
       selectsEnd
       startDate={startDate}
       endDate={endDate}
       minDate={startDate}
       onChange={date => setEndDate(date)}
     />
   </div>
 );
}

export default function TableDatePicker() {
 return (
   <Styles>
     <DatePickerRange />
   </Styles>
 );
}

For more code samples that cover the vast range of features that react-datepicker has to offer, check out React Datepicker’s website.

For a full version of this code that you can test out and play around with, check out our code sandbox.

TL;DR: Syntax roundup

Create a simple datepicker

import React, { useState } from "react";

import DatePicker from "react-datepicker";
import "react-datepicker/dist/react-datepicker.css";

export default function TableDatePicker() {
 const [date, setDate] = useState(new Date());

 return (
   <DatePicker selected={date} onChange={date => setDate(date)} />
 );
}

Create a datepicker range

export default function TableDatePicker() {
 const [startDate, setStartDate] = useState(new Date());
 const [endDate, setEndDate] = useState(new Date());

 return (
   <div>
     <DatePicker
       selected={startDate}
       selectsStart
       startDate={startDate}
       endDate={endDate}
       onChange={date => setStartDate(date)}
     />
     <DatePicker
       selected={endDate}
       selectsEnd
       startDate={startDate}
       endDate={endDate}
       minDate={startDate}
       onChange={date => setEndDate(date)}
     />
   </div>
 );
}

Selecting time with datepicker

<DatePicker
 showTimeSelect
 dateFormat="MMMM d, yyyy h:mmaa"
 selected={date}
 onChange={date => setDate(date)}
/>

Add a placeholder

export default function TableDatePicker() {
 const [date, setDate] = useState(null);

 return (
   <DatePicker
     placeholderText="Select Date"
     selected={date}
     onChange={date => setDate(date)}
   />
 );
}

Disable future dates

<DatePicker
 filterDate={d => {
   return new Date() > d;
 }}
 selected={date}
 onChange={date => setDate(date)}
/>

Add a clear button to the datepicker

<DatePicker
 isClearable
 selected={date}
 onChange={date => setDate(date)}
/>

Building a React Table Component with react-table

justin gage — Tue, 18 Aug 2020 19:06:02 +0000

If you’re building internal tools – admin panels, dashboards, CRMs, you name it – chances are you’re thinking about how to build a table component to display, edit, and manipulate data. And if you’re working in React, you (thankfully) don’t need to build it from scratch: the react-table library gives you Hooks to get tables up and running quickly.

By the end of this tutorial, you’ll know how to:

Build a simple table with React and modify data, columns, and headers
Give your table a custom UI by passing CSS into each component using styled-components or piggybacking off a React component library.
Extend your table with more features like sorting, filtering, and pagination.

Learning all of these things yourself can be complicated. Thankfully, the react-table library is killer (and we made you this guide to help).

Intro: react-table

React-table is an open-source library specifically for building (you guessed it) tables in React. The library has over 11.5k stars on GitHub and is used by tons of big-name tech companies, like Google, Apple, and Microsoft. Plus, we like it so much here at Retool that we sponsor it.

We like react-table because it’s easy to set up, customize, and extend. The library covers the basics of a useful table — sorting, filtering, and pagination — but also goes much deeper with advanced features like:

Grouping
Expanded State
Custom Plugin Hooks

It should be noted that react-table is a “headless” UI library. The library doesn’t actually render a user interface. While this may sound strange, it was designed this way to give you more control over the look and feel of the react-table component while keeping the package size small. Don’t worry, adding UI is easy, and we will cover that later on.

Since we’re all about internal tools, let’s imagine that we are building a table to display order information for customer service reps. Our table will need to display customer info (name and address) and order info (order number and date) for each customer’s purchase.

When you’ve finished working through this tutorial, you will have five versions of a table built with react-table: simple, styled, sortable, filterable, and paged. Below is the final, paged version we’re aiming for.

It could look better, but that’s what CSS is for!

Build a simple table with react-table

First, we’ll build a basic table to display data, no styling or extra features. Our customer support reps need an easy way to view order information for each customer. Our simple table will have two top-level headers: User Info and Order Info. Under User Info, we need two secondary headers to display each customer’s Name and Address. Under Order Info, we need two more secondary headers to display the Date that the order was made and the Order Number.

In this section, we’ll build a table with four columns split into two groups. We’ll break down how to define the shape of column objects and data, parse header groups, and fill out our rows and cells. At the end, expect to see something like this:

Note that we did add a tiny bit of extra styling to this section, so the table has lines.

Taking care of housekeeping basics first, you’ll need to install react-table by using a package manager (Yarn or npm) and import the library into your React app:

import { useTable } from 'react-table';

Then, once react-table is installed and imported, it’s time to define our data and columns by way of the useTable Hook. React-table leverages Hooks, which are a fairly new addition to React (as of version 16.8). If you’re not familiar with React Hooks, we recommend taking a look at React’s Hooks at a Glance documentation.

The most important Hook for our table is useTable. We’ll pass two arguments to useTable:

data = table data defined with the useMemo Hook (data must be memo-ized before it can be passed to useTable to cut down on calculation time by preventing unchanged data from being rerun)

const data = React.useMemo(() =>
 [
 {
 name: 'Kim Parrish',
 address: '4420 Valley Street, Garnerville, NY 10923',
 date: '07/11/2020',
 order: '87349585892118',
 },
 {
 name: 'Michele Castillo',
 address: '637 Kyle Street, Fullerton, NE 68638',
 date: '07/11/2020',
 order: '58418278790810',
 },
 {
 name: 'Eric Ferris',
 address: '906 Hart Country Lane, Toccoa, GA 30577',
 date: '07/10/2020',
 order: '81534454080477',
 },
 {
 name: 'Gloria Noble',
 address: '2403 Edgewood Avenue, Fresno, CA 93721',
 date: '07/09/2020',
 order: '20452221703743',
 },
 {
 name: 'Darren Daniels',
 address: '882 Hide A Way Road, Anaktuvuk Pass, AK 99721',
 date: '07/07/2020',
 order: '22906126785176',
 },
 {
 name: 'Ted McDonald',
 address: '796 Bryan Avenue, Minneapolis, MN 55406',
 date: '07/07/2020',
 order: '87574505851064',
 },
 ],
 []
)

columns = column definitions defined with the useMemo Hook (column defs must be memoized before they can be passed to useTable)

const columns = React.useMemo(
 () => [
 {
 Header: 'User Info',
 columns: [
 {
 Header: 'Name',
 accessor: 'name',
 },
 {
 Header: 'Address',
 accessor: 'address',
 },
 ],
 },
 {
 Header: 'Order Info',
 columns: [
 {
 Header: 'Date',
 accessor: 'date',
 },
 {
 Header: 'Order #',
 accessor: 'order',
 },
 ],
 },
 ],
 []
)

Take a second to look at the relationship between data and columns. The accessor in columns is the “key” in the data object. This is important to be able to access the right data for each column once we use useTable.

Once we have defined data and columns, it’s time to implement our useTable Hook. Pass data and columns into useTable, which will return properties that we can extract to build our table UI.

const {
 getTableProps,
 getTableBodyProps,
 headerGroups,
 rows,
 prepareRow,
} = useTable({ columns, data })

Now, we’ll use these extracted properties to build out our table via familiar JSX tags – <table>, <thead>, <tr>, <th>, and <tbody> – and then fill in our properties from useTable.

Table

First, we need <table> to wrap the rest of our code, and we need to pass the getTableProps() function in to resolve any table props.

<table {...getTableProps()}>
 ...
</table>

Headers

Things start to heat up a little bit when we start to build our headers! On a high level, all we are doing is creating our header rows using the column header names that we defined above. Before we jump into the code, let’s look at the rendered table to get a better idea:

Each circled section in the table above is a headerGroup, which is simply an object that contains an array of headers for that row. For this table, we will have two header rows: the header circled in red is the first headerGroup, and the header circled in blue is the second headerGroup.

To get the data we need to build these headers out of headerGroups, we will be using JavaScript’s map() method. If you’re unfamiliar with it, take a second to read the docs.

First, we have our <thead> tag, which is simple enough. Inside of that tag, we are going to use map() to parse each headerGroup, creating a new row using the <tr> and passing that headerGroup’s getHeaderGroupProps() method in.

{headerGroups.map(headerGroup => (
   <tr {...headerGroup.getHeaderGroupProps()}>
     ...
   </tr>
))}

Inside of the <tr>, we use map() again, but this time on the array of headers. Each header object has a Header property (which is the name you’ll give each header), a render() function, and another prop resolver function called getHeaderProps().

For each column, we use the <th> tag to create the column, being sure to pass that column’s prop resolver function getHeaderProps() and then use the render() function to access the Header.

<thead>
 {headerGroups.map(headerGroup => (
   <tr {...headerGroup.getHeaderGroupProps()}>
     {headerGroup.headers.map(column => (
       <th {...column.getHeaderProps()}>{column.render('Header')}</th>
     ))}
   </tr>
 ))}
</thead>

Table Body

Similar to how we did <table> and <thead>, we add <tbody> and pass the prop resolver function getTableBodyProps() in. Then, we use map() to iterate through rows, which is an array of Row objects. Each Row object has a cells field, which is just an array of Cell objects that contain the data for each cell in the row.

The orange circle shows a row, and the pink circle shows a cell.

For each row, we need to pass the row object to the prepareRow() function, which helps with rendering efficiently. Next, we return <tr> tags to render the row. In each <tr>, we again use map() to parse cells. For each cell, we create a <td> tag, pass in the prop resolver function getCellProps(), and then render the cell data using the render() method.

<tbody {...getTableBodyProps()}>
 {rows.map(row => {
   prepareRow(row)
   return (
     <tr {...row.getRowProps()}>
       {row.cells.map(cell => {
         return <td {...cell.getCellProps()}>{cell.render('Cell')}</td>
       })}
     </tr>
   )
 })}
</tbody>

Let’s put all of that together to render our table.

return (
 <table {...getTableProps()}>
   <thead>
     {headerGroups.map(headerGroup => (
       <tr {...headerGroup.getHeaderGroupProps()}>
         {headerGroup.headers.map(column => (
           <th {...column.getHeaderProps()}>{column.render('Header')}</th>
         ))}
       </tr>
     ))}
   </thead>
   <tbody {...getTableBodyProps()}>
     {rows.map(row => {
       prepareRow(row)
       return (
         <tr {...row.getRowProps()}>
           {row.cells.map(cell => {
             return <td {...cell.getCellProps()}>{cell.render('Cell')}</td>
           })}
         </tr>
       )
     })}
   </tbody>
 </table>
)

Using the above code, you’ll end up with a rendered table that looks like this:

Give your table a custom UI

Even if you’re building a tool that will only be used by an internal team, it’s still important for the UI to look good (or at least not terrible). Styling (at least the basics) is extra important with react-table too since no components are actually rendered as part of the library. Without any styling, you’ll end up with a table like this:

You can style your react-table component by either creating custom styles or through a React component library. The final product from this section will look like this:

Isn’t it beautiful?

Using the `style` Prop

Styling your table with react-table is as simple as passing CSS into the style prop of each component. Let’s look at the <th> tag for styling a header row:

<th
 {...column.getHeaderProps()}
 style={{
   borderBottom: 'solid 3px blue',
   background: 'green',
   color: 'white',
   fontWeight: 'bold',
 }}
>

You can also use CSS files and CSS modules if you’d like. Check out React’s CSS docs for more info.

Using `styled-components`

styled-components is a nifty React library that lets you style React components with CSS directly inside of JS code (as opposed to external CSS files). Lately, it’s become a really popular way to handle component styling in React, so you might want to use it for your table.

To use styled-components, install the library and import it into your project. Create a component Styles that uses styled from the styled-components library to create a div with the styles for your table in CSS. Move all of your code for creating the Table component into its own function. Then, in your App function (where your columns and data are defined), return <Styles> with your <Table> rendered inside. This will apply the styles from the styled-components on to your table.

import styled from 'styled-components'

/* Pssst this has the rest of the styling we've been using
to give our table borders and non-ugly spacing */

const Styles = styled.div`
 table {
   border-spacing: 0;
   border: 1px solid black;

   tr {
     :last-child {
       td {
         border-bottom: 0;
       }
     }
   }

   th,
   td {
     padding: 0.5rem;
     border-bottom: 1px solid black;
     border-right: 1px solid black;

     :last-child {
       border-right: 0;
     }
   }

   th {
     background: green;
     border-bottom: 3px solid blue;
     color: white;
     fontWeight: bold;
   }
 }
`

function Table({ columns, data }) {
 const {
   getTableProps,
   getTableBodyProps,
   headerGroups,
   rows,
   prepareRow,
 } = useTable({
   columns,
   data,
 })

 // Render the UI for your table
 return (
   <table {...getTableProps()} >
     ...
   </table>
 )
}

function App() {
 const columns = React.useMemo(...)

 const data = React.useMemo(...)

 return (
   <Styles>
     <Table columns={columns} data={data} />
   </Styles>
 )
}

export default App

Using a React Component Library

If you don’t want to style things yourself, using a React component library is the way to go. For this example, we’re going to use the material-ui library to create a nice table with react-table.

Going off of the Cell styling example above, we simply have to import TableCell from @material-ui/core/TableCell.

import TableCell from '@material-ui/core/TableCell'
...
<TableCell {...cell.getCellProps()}>
   {cell.render('Cell')}
</TableCell>

This will bring all of the styling of the TableCell component in material-ui. No extra work for you!

Click here for a full sandbox version of this code.

Extend your table with more features

No table worth rendering is going to have only two columns and three rows, like our example. Most likely, you're going to have a hefty portion of columns and row upon row of data. You'll need features to allow users to sift through all that data, like sorting, filtering, and pagination.

Sorting

We want to give our customer service reps the ability to easily find what they are looking for, and sorting is a great way to accomplish that! If the reps want to see the most recent orders placed, they can sort by date from the Date column. If they want to scan through the customers alphabetically, they can sort by name in the Name column.

Sorting is accomplished by using the useSortBy Hook from react-table. Be sure to add that to your import statements:

import { useTable, useSortBy } from 'react-table'

Next, you’ll need to pass useSortBy into the useTable Hook arguments:

const {
 getTableProps,
 headerGroups,
 rows,
 getRowProps,
 prepareRow,
} = useTable(
 {
   columns,
   data,
 },
 useSortBy,
)

If you want the sorting to be done by anything other than the default alphanumeric value, you’ll need to update your columns definition with a sortType field. Sorting options include:

alphanumeric = Best for sorting letters and numbers (default)
basic = Best for sorting numbers between 0 and 1
datetime = Best for sorting by date

For this example, we will be using the default, but if you needed to add that code, it would look like this:

const columns = React.useMemo(
 () => [
   {
     Header: 'Rating',
     accessor: 'rating',
     sortType: 'basic',
   },
 ],
 []
)

Now there are two more things to do. First, pass the getSortByToggleProps() function into your getHeaderProps() function. The getSortByToggleProps() function resolves props for toggling the sort direction when the user clicks on the header.

Second, add a span tag to display an arrow up, an arrow down, or nothing to the column header to indicate how that column is sorted. You can determine how the column is sorted by checking column properties isSorted and isSortedDesc.

<thead>
 {headerGroups.map(headerGroup => (
   <tr {...headerGroup.getHeaderGroupProps()}>
     {headerGroup.headers.map(column => (
       <th {...column.getHeaderProps(column.getSortByToggleProps())}>
         {column.render('Header')}
         <span>
           {column.isSorted ? (column.isSortedDesc ? ' 🔽' : ' 🔼') : ''}
         </span>
       </th>
     ))}
   </tr>
 ))}
</thead>

Check out this code sandbox for a more advanced version of sorting using react-table.

Filtering

For the sake of simplicity, this tutorial is going to focus on how to add a text filter on the columns of our simple table. This will give our customer support reps the ability to quickly find the information they are looking for. If a customer contacts them, the rep can easily search the Names column for that customer to find their orders or search the Order Numbers column to look up a specific order.

For further examples of all the different kinds of filters (including global, which is really useful), check out this the react-table docs.

Filtering is accomplished by using the useFilters() Hook from react-table. Be sure to add that to your import statements:

import { useTable, useFilters } from 'react-table'

Next, you’ll need to pass useFilters into the useTable Hook arguments:

const {
 getTableProps,
 headerGroups,
 rows,
 getRowProps,
 prepareRow,
} = useTable(
 {
   columns,
   data,
 },
 useFilters,
)

Now, we are going to add the UI for the column filter into our table. If there is a filter applied to this column, we render the UI for the filter by calling the column’s render() method on the Filter field. Otherwise, do nothing.

<th {...column.getHeaderProps()}>
 {column.render('Header')}
 <div>{column.canFilter ? column.render('Filter') : null}</div>
</th>

But wait! We haven’t defined the UI for the filter yet. We’ll need a function to do that – for our filter function, we first want to find out how many rows are left to be filtered, so we can display that number to the user as an input placeholder. Then, we will render an <input> for the user to type what they want to filter.

function TextFilter({
 column: { filterValue, preFilteredRows, setFilter },
}) {
 const count = preFilteredRows.length
 return (
   <input
     value={filterValue || ''}
     onChange={e => {
       setFilter(e.target.value || undefined)
     }}
     placeholder={`Search ${count} records...`}
   />
 )
}

Our TextFilter() function receives three values from column:

filterValue = the current value that this column is using to filter.
- This value is set from the table’s state filters object.
preFilteredRows = Array of rows passed to the column before any filtering was done.
setFilter = function that takes in a filterValue in order to update the filterValue of this column (in this case, it’s taking the value that the user types into the
<input>

).

Once we’ve defined our filter function, we’ll update the definition of our column object to have a Filter field. Add this code to your Table function:

const defaultColumn = React.useMemo(
 () => ({
   Filter: TextFilter,
 }),
 []
)

Finally, make sure to pass defaultColumn in with the columns and data arguments when you use useTable():

const {
 getTableProps,
 ...
} = useTable(
 {
   columns,
   data,
   defaultColumn,
 },
 useFilters,
)

Pagination

The data we created for the example in this tutorial is pretty small compared to what you would see in the real world. Only six rows? Please. In reality, our customer support reps would be dealing with hundreds (maybe even thousands) of rows of customer data. To avoid long render times and ginormous pages to scroll through, we are going to add pagination to our table. This will allow react-table to only deal with rendering some rows at a time and will take some strain off the customer support reps from having to look at overwhelming amounts of data.

Pagination is accomplished by using the usePagination() Hook from react-table. Be sure to add that to your import statements:

import { useTable, usePagination } from 'react-table'

Next, you’ll need to pass usePagination() into the useTable() Hook arguments, set the initial state (if you want to start the pageIndex at anything other than 0 or have the pageSize bigger or smaller than 10), and extract extra properties from what it returns.

const {
   getTableProps,
   headerGroups,
   getRowProps,
   prepareRow,
   page,
   pageOptions,
   state: { pageIndex, pageSize },
   previousPage,
   nextPage,
   canPreviousPage,
   canNextPage,
 } = useTable(
   {
     columns,
     data,
     initialState: { pageSize: 2 },
   },
   usePagination,
 )

Note that as well as iterating through rows in <tbody> as we did previously before pagination, we are going to iterate through page, which is similar to rows except it only has the number of rows that fit on the page. If you don’t do this, you can click those buttons as much as you want — the data won’t move. Trust me.

<tbody {...getTableBodyProps()}>
 {page.map(row => {
   prepareRow(row)
   ...

In this example, we have a button to go to the Previous Page, a button to go to the Next Page, and an input that lets the user type a page number to jump to.

return (
   <div>
     <table {...getTableProps()}>
       ...
     </table>
     <div>
       <button onClick={() => previousPage()} disabled={!canPreviousPage}>
         Previous Page
       </button>
       <button onClick={() => nextPage()} disabled={!canNextPage}>
         Next Page
       </button>
       <div>
         Page{' '}
         <em>
           {pageIndex + 1} of {pageOptions.length}
         </em>
       </div>
     </div>
   </div>
 )
}

Check out this code sandbox for a more advanced version of pagination using react-table.

Your Table Component with react-table

Hopefully, this tutorial helps you understand how to create, style, and extend a table in React using react-table. For more advanced tutorials, we recommend checking out the “Kitchen Sink” example from react-table’s docs. It has fully fleshed-out examples of most things that react-table has to offer, from pagination and sorting to filtering, grouping, expandable rows, and row selection.

What's an ACID compliant database?

justin gage — Thu, 09 Apr 2020 20:09:12 +0000

When you’re building and maintaining an application, the last thing you want to worry about is data integrity; Charging a customer the wrong amount or losing their data can be catastrophic. Thankfully, the databases you’re using — like MySQL and Postgres — take special measures to make sure that doesn’t happen. But what’s going on behind the hood? Most modern SQL DBs use transactional standards like ACID to ensure data integrity and keep your users from seeing wrong or stale data, and this post explores how they work.

All the things that can go wrong with your transactions

Data Scientists worry about long analytical queries and warehousing, but for developers, databases are all about transactions. A database transaction is a series of logically grouped database operations: insert a row here, update a record there, and more stuff like that. Your application code is constantly making transactions every time you sign up a new user or that user updates their account information.

The thing about transactions, though, is that they can go very wrong. Any number of things can happen when you’re trying to write to your database: you can lose connection to a remote instance, you can encounter value errors, or anything else under the sun. You’ve seen it, you’ve dealt with it, and it can mean disaster for your underlying data. Let’s take a look at a quick example that a company like Amazon might run into:

- User updates order quantity and clicks “order now” →
- Update order quantity in the pending orders table
- Add row to orders table
- Apply the purchase to user’s balance / charge credit card

If something goes wrong in the middle of this group of operations but the system continues executing them, the user will get charged the wrong amount. And if the charge doesn’t work, they’ll get their order for free. It turns out that these kinds of data errors have names, and there are a bunch of them. A few examples:

Dirty Reads

If a transaction is in the middle of updating some data and hasn’t committed yet, and another transaction is allowed to read that uncommitted data, that’s dirty, and could lead to your app showing incorrect data that got rolled back.

An example of a dirty read could be a transaction that invalidates login tokens when a user changes their password. If as the first transaction loads the token, a second one reads that token before the first invalidates it, you’d have yourself a dirty read.

In terms of actual SQL, here's what a dirty read might look like:

### Transaction 1 ###

SELECT user_login_token_id
FROM tokens

UPDATE tokens
SET token_status = "INVALID"
WHERE token_id = user_login_token_id

### Transaction 2 ###

SELECT user_login_token_id
FROM tokens

Non-Repeatable Reads

If you’ve got two consecutive reads in one transaction with a concurrent update in between, those reads are going to show different results even though they’re part of the same transaction.

An example might be two writers working on a blog. Our first user starts a transaction that reads a post’s title, writes to the post, and then reads that post’s title again. If a second user changes that post’s title in the middle of the first user’s transaction, the first user is going to see different values for the title across the two reads; or in other words, a non-repeatable read.

Here's what a non-repeatable read might look like in SQL:

### Transaction 1 ###

SELECT post_title
FROM posts

SELECT
    post_title,
    post_content
FROM posts

### Transaction 2 ###

UPDATE posts
SET post_title = "something_new"
WHERE post_title = post_title

Phantom Reads

If a transaction reads data and then a concurrent transaction inserts data that would have been read in the original transaction, that’s a phantom read.

Let’s use the same example as a non-repeatable read: if our second user adds content in between our first user’s two reads, the first read will be missing data that appears in the second read (this is actually really similar to a non-repeatable read, which is why the same example works).

In SQL, a phantom read might look like this:

### Transaction 1 ###

SELECT post_title
FROM posts

SELECT
    post_title,
    post_content
FROM posts

### Transaction 2 ###

INSERT INTO posts
VALUES "something_new", ...

These are the 3 transactional errors as defined by the SQL standard — the big three, you might say. A lot of these errors sound like one another and tend to overlap in practice, so don’t sweat the details. For more detailed information, check out Vlad Mihalcea’s blog series on the topic.

Now, the important question: how do we avoid these?

ACID, but the good kind

Popular relational databases like MySQL avoid these kinds of data integrity issues by following a few core principles that govern how transactions work. They conform to a transactional standard called ACID. ACID is an acronym for four different words, but it really breaks down into two core principles: completeness and concurrency. First, here’s what ACID stands for:

Atomicity: the “all or nothing” rule — the transaction either happens completely or doesn’t happen at all
Consistency: data is consistent before and after a transaction without any missing steps
Isolation: multiple transactions can happen concurrently without reading the wrong data
Durability: transactional success is robust to system failure

These overlap a lot, so just remember: the point of any ACID compliant DB is to make sure that

Transactions can fail without hurting data integrity
Multiple transactions can occur concurrently without reading and writing the wrong data

ACID is a set of properties, but it’s not a process: how do the SQL databases we use actually achieve ACID compliance? They use a system called locking to keep the database on hold while transactions happen. Locking works like you’d imagine it would: when a transaction begins, the database engine locks the data that it’s working with until the transaction is completed (and sometimes, beyond that). That way, concurrent transactions can’t work with the data that’s being changed by the first transaction.

Once a transaction begins and acquires a lock, it can either finish successfully and commit, or run into an error and abort. It’s sort of like writing in an Excel spreadsheet before saving your work — things are changed, but only softly, until you save them (commit) or revert them (abort). Back to our Amazon example: if our database runs into an error right after updating a user’s order quantity, the transaction will abort, and it’s as if that update never happened. And if the error happened during the update, the credit card will never get charged. Either everything happens or nothing happens, and that’s ACID.

The intricacies of commiting and locks are pretty hairy, but a good start is Methods and Tools’ excellent paper on the topic.

ACID concepts in NoSQL and distributed systems

ACID was a building block of the reliable relational DBs we’re familiar with today, but NoSQL has changed the game: many NoSQL DBs are built as distributed systems, and they can’t always ensure complete transactional consistency. There’s actually a theory that governs this — called CAP theorem — that in distributed systems, you can’t never have full consistency and full availability; you need to choose one. So what does ACID look like for something like MongoDB or Cassandra?

A new quasi-standard has emerged for NoSQL databases called BASE (a rare overlap between SQL and chemistry jokes), and it’s a weak or soft consistency model that relaxes some of the assumptions of ACID in order to achieve scalability:

Basic Availability: the database basically works most of the time, even though it’s not perfect
Soft-State: nodes of the database aren’t necessarily consistent with each other all the time
Eventual Consistency: data will be consistent across nodes eventually, like by read time

In a lot of ways, this is the exact opposite of ACID, and prioritizes availability over perfect consistency; but that’s kind of the point of NoSQL in the first place. As NoSQL becomes a more established part of app development (more than 25% of developers are using MongoDB already), expect more advancements on this front.

Today, we’re dealing with more data than we ever have, and we’re building database systems that can scale and handle that load. ACID might not be the exact transactional standard for the future, but its building blocks will certainly make their way in there.

Isolation levels and locking in relational databases

justin gage — Wed, 25 Mar 2020 17:04:06 +0000

“We need to build this for scale!” yells your manager, in a fury. You think back to a simpler time: when your application only had a few users. When your transactions executed sequentially, and you didn’t need to deal with things like Hot Locks and stalemates. Time to hire a DBA.

Running a database would be easy if your app only had one user, or if your users did one thing after another, never concurrently and never in contention. In reality though, you want to get more people using what you’ve built – your business needs to grow. Increased user volume tends to mean a lot of concurrent transactions, and all those requests bring us into the realm of database locking and isolation, a topic with little publicly available information (until you know what to search for!) and an almost mystic air to it. This post will hopefully make things simpler.

Why database locking and isolation exist

A modern web application might need to handle hundreds of thousands of transactions daily, and the biggest ones deal with orders of magnitude more than that. That kind of scale can create a bunch of problems with data integrity, starting with the big 3 as defined by the SQL standard:

Dirty Reads

If a transaction is in the middle of updating some data and hasn’t committed yet, and another transaction is allowed to read that uncommitted data, that’s called a dirty read, and could lead to your app showing incorrect data that got rolled back.

In terms of actual SQL, here's what a dirty read might look like:

### Transaction 1 ###

SELECT user_login_token_id
FROM tokens
WHERE ...

UPDATE tokens
SET token_status = "INVALID"
WHERE ...

### Transaction 2 ###

SELECT user_login_token_id
FROM tokens
WHERE ...

If the second SELECT statement was allowed to read the user_login_token_id before the first transaction invalidated it, the read would be dirty: it's reading stale data.

Non-Repeatable Reads

If you’ve got two consecutive reads in one transaction with a concurrent update in between, those reads are going to show different results even though they’re part of the same transaction.

Here's what a non-repeatable read might look like in SQL:

### Transaction 1 ###

SELECT post_title
FROM posts

SELECT
    post_title,
    post_content
FROM posts

### Transaction 2 ###

UPDATE posts
SET post_title = "something_new"
WHERE post_title = post_title

Phantom Reads

If a transaction reads data and then a concurrent transaction inserts data that would have been read in the original transaction, that’s a phantom read.

Let’s use the same example as a non-repeatable read: if our second user adds content in between our first user’s two reads, the first read will be missing data that appears in the second read (this is actually really similar to a non-repeatable read, which is why the same example works).

In SQL, a phantom read might look like this:

### Transaction 1 ###

SELECT post_title
FROM posts

SELECT
    post_title,
    post_content
FROM posts

### Transaction 2 ###

INSERT INTO posts
VALUES "something_new", ...

To avoid all of this nastiness, most SQL databases follow a set of principles called ACID that prioritizes transactional integrity. We wrote all about ACID and concurrency in databases here, but the TL;DR is that databases use locks that prevent data from being read or changed while a transaction is making use of it.

How database locking works

So you’ve got your database — let’s say it’s a Postgres database — up and running, and you start a transaction. How does locking actually happen? Let’s take a look at two concurrent transactions and see how locking impacts them:

Transaction 1 (T1) wants to read a user’s password and change it to a new one (SELECT and UPDATE)
Transaction 2 (T2) wants to read that user’s password (SELECT)

Think of the leftmost column as a time axis. When a transaction acquires a lock, other transactions basically won’t be able to interact with the locked data. Here’s how these concurrent transactions will play together:

Time	Transaction 1	Transaction 2
1	Selects row #1, and acquires a lock on row #1
2		Tries to select row #1, but blocked by T1’s lock
3	Updates row #1 and commits transaction
4		Selects row #1

Notice that Transaction 1 is made up of two queries — a SELECT and an UPDATE — and even though the lock is acquired on that first query, it _remains _until the end of the entire transaction when the transaction gets committed.

The most common lock is on the row level, like in our example; but locks are maintained in database memory, so having too many of them active at any point can be computationally prohibitive. This means if your DBMS (database management system) is running out of memory, it might escalate a lock to a higher level to free up space. For example, if one database table has 40 different locks active on 40 different rows, the DBMS might escalate the lock to the table level so it can continue blocking while using less memory.

Locks can exist across larger sections of your data, and even on an entire database. System level updates might acquire a database lock, which would block all transactions on any part of a database. That kind of lock is pretty uncommon, but table level locks are used pretty often (like our escalation example above), and page or file locks (this means different things across different database setups) are also not infrequent.

Levels of database isolation

We’ve been assuming that a lock means that no other transactions can use the locked data at all. But that’s not entirely true; in fact, if it was, your database would basically never work at even low levels of concurrency. Databases deal in different isolation levels to help avoid lock craziness, and there are four (4) major ones:

READ_UNCOMMITTED

This is the lowest level of isolation, and does almost nothing. It means that transactions can read data being worked with by other transactions, even if the changes aren’t committed yet. READ_UNCOMMITTED doesn’t avoid any of the three read problems that we outlined above. Let’s go back to our two transaction example and see what would happen:

Time	Transaction 1	Transaction 2
1	Selects row #1
2		Selects row #1 (even though it’s currently being used by T1)
3	Updates row #1 and commits transaction

Transaction 1 never acquires a lock when it starts, so Transaction 2 is able to slide in there and execute its SELECT query. In this example, things turned out fine: but if T2 did its SELECT query after the update from T1, but T1 got rolled back (aborted), we’d have a dirty read on our hands.

READ_COMMITTED

The next level of isolation is READ_COMMITTED, which adds a little locking into the equation to avoid dirty reads. In READ_COMMITTED, transactions can only read data once writes have been committed. Let’s use our two transactions, but change up the order a bit: T2 is going to read data after T1 has written to it, but then T1 gets rolled back (for some reason).

Time	Transaction 1	Transaction 2
1	Selects row #1
2	Updates row #1, acquires lock
3		Tries to select row #1, but blocked by T1’s lock
4	Rolls back transaction
5		Selects row #1

READ_COMMITTED helps avoid a dirty read here: if T2 was allowed to read row #1 at Time 3, that read would be invalid; T1 ended up getting rolled back, so the data that T2 read was actually wrong. Because of the lock acquired at Time 2 (thanks READ_COMMITTED!), everything works smoothly and T2 waits to execute its SELECT query.

REPEATABLE_READ

The most popular isolation level is REPEATABLE_READ, which (surprise!) helps avoid non-repeatable reads. This iso level locks data from the start of the entire transaction – including on SELECT queries – as opposed to the start of the query, which adds an additional layer of isolation.

If you recall from above, a non-repeatable read happens when one transaction has two SELECT queries, and another transaction is able to insert or update a row that makes those two SELECT queries show different results. REPEATABLE_READ isolation avoids that by letting the first transaction acquire a lock when it starts the first SELECT query:

Time	Transaction 1	Transaction 2
1	Selects row #1, acquires lock on row #1
2		Tries to update row #1, but is blocked by T1’s lock
3	Selects row #1, commits transaction
4		Updates row #1

If you’re really clever, you might notice that REPEATABLE_READ still doesn’t solve for phantom reads: Transaction 1 only acquired a lock on the row it was working with (Row #1), so Transaction 2 is still able to insert a row that might show up in T1’s second SELECT query. The only way to avoid that is with a higher lock level than a row, which is our next level of isolation.

SERIALIZABLE

This is the highest possible level of isolation, and is a bit difficult to define: in practice, SERIALIZABLE means that from the user’s perspective, transactions appear as if they’re executing sequentially, not concurrently. In other words, isolation is so intense that transactions will only execute concurrently if the end result is the same as them executing sequentially.

SERIALIZABLE also uses range locks to avoid phantom reads. Range locks are a mechanism that’s in between locking a row and locking a table: if you’re running a SELECT query with a WHERE clause, range locks will lock some of the rows that exist close to your selected rows (before and after).

Wikipedia has a pretty great summary chart for wrapping up how these isolation levels help avoid the SQL standard read problems:

You can think of isolation levels as lock settings: they determine when locks get acquired and released, and how intense that process is. Most modern SQL databases use REPEATABLE_READ as the default isolation level, but it’s a setting that you can change internally if you need (even on a transaction level).

Problems you’ll run into with database locks

If you’ve been following along, you’ve probably gathered that locks solve a lot of problems, but they also block data access; and that might not always work out well for your application’s user. There are three big ones to worry about:

Lock Contention

If a bunch of user sessions all require access to the same lock concurrently, they’re not going to be able to retrieve the data they need. A good example here is anything that gets incremented on activity, like a total monthly orders count in a metrics table. If every user order needs to increment that row, the constant locking is going to block your user requests. The cool kids also call this one a Hot Lock.

Long Term Blocking

Long term lock issues happen when you have some sort of transaction or job that requires a lock for a long period of time. If you’re running a batch job that needs locking access to a table or row for a while (think: 15 minutes), and that table or row is also heavily used by smaller transactions, they’re going to get stuck waiting while the job finishes.

Database Deadlocks

A deadlock happens when two transactions both need to wait for each other to commit...to commit. This might sound kind of funky, so let’s use our classic two transaction example and see what can go wrong:

Time	Transaction 1	Transaction 2
1	Selects row #1, acquires lock on row #1
2		Selects row #2, acquires lock on row #2
3	Tries to select row #2, blocked by T2’s lock
4		Tries to select row #1, blocked by T1

Your DBMS will usually resolve this stalemate by rolling back whichever transaction is easiest to roll back, but this is also a setting you can tweak when configuring your database. Methods and Tools wrote a great paper about this entire “locking issues” topic and how it impacts QA if you want to go deeper.

If you’re building your app on top of a SQL database, check out Retool! Retool saves you a bunch of time and hassle by letting you drag and drop components like tables, search boxes, and buttons, and connecting them to SQL or Javascript on the backend. Retool also protects you against bad writes (no accidental DROP TABLE;) through a GUI based write interface.

Formatting and dealing with dates in SQL

justin gage — Wed, 04 Mar 2020 21:29:41 +0000

It’s 3AM, and you’re sleeping soundly in your room. But slowly, your sweet dream turns into a nightmare: all of the queries you wrote earlier in the day are parsing dates wrong, your app is down, and your boss is angry. It turns out migrating from Redshift to Bigquery was not “as easy as 123” and your DBA switched all of your timestamps to unix time. Nice.

Nobody likes dates, especially programmers, but they’re a critical part of pretty much every application. In Javascript you’ve got Moment, but parsing dates in SQL is a bit more complex. This post will run through how you can effectively work with dates in SQL, resolve your issue quickly, and get back to bed.

SQL dialects and function prototypes

Part of why writing SQL is annoying is that there are hundreds of different flavors. Syntax is slightly different from MySQL to PostgreSQL (for example), and some dialects have functions that others don’t (e.g. PIVOT in Snowflake). When you’re working with dates, there are prototypes for types of functions: even though the exact syntax might differ between dialects, the idea is the same. We’ll tackle 5 broad categories:

Formatting
Time differences / deltas
Intervals
Time zones
Current times

For each function prototype, we’ll provide the right syntax and documentation for 5 of the more popular SQL dialects:

MySQL – the world’s most popular open source relational database (thanks, Oracle)
PostgreSQL – the world’s second most popular open source relational database, and a developer favorite for syntax
BigQuery – Google’s cloud based data warehouse that shares SQL syntax with other GCP databases (Standard SQL)
Redshift – Amazon’s cloud based data warehouse (or at least one of them)
Presto – a popular open source query engine built by Facebook and often used with HDFS / Hive

Something that often gets confusing is the difference between DATE and TIMESTAMP. A TIMESTAMP is just a DATE with an additional two levels of precision: fractional seconds and fractional seconds with time zones.

#DATE
2019-01-01 04.55.14 PM
#TIMESTAMP
2019-01-01 04.55.14.000148 PM

In general, we’ll use “date” in this tutorial but the distinction isn’t super important. Let’s go!

Formatting

Dates never seem to be in the format you want them to be in.

Computers interpret dates in all different formats, from unixtime to strings and timestamps, and they’re usually not friendly to each other. Here are the function prototypes:

FROM_UNIXTIME() – convert a unix time date into a normal date.
TO_UNIXTIME() – convert a regular date format into a unix time date.
TO_DATE() – convert a string to a date format. Sometimes you’ll need to specify what format the string is in through the function arguments.
FORMAT_DATE() – convert a date into a string. This function is usually used to format dates in specific ways as strings, so the arguments are the important part.

Unix time (which is also called epochs time) is kind of funky: it corresponds to the number of seconds that have elapsed since January 1st, 1970. A typical unix timestamp might look like this: 1284352323.

Here’s how these different conversion functions look across major SQL dialects:

Function	MySQL	PostgreSQL	BigQuery	Redshift	Presto
Convert unix time to date format	`FROM_UNIXTIME()`	`TO_TIMESTAMP()`	`DATE_FROM_UNIX_DATE()`	`TIMESTAMP 'epoch' + your_timestamp_column * INTERVAL '1 second'`	`FROM_UNIXTIME()`
Convert date format to unix time	`UNIX_TIMESTAMP()`	`EXTRACT(EPOCH FROM TIMESTAMP )`	`UNIX_DATE()`	`DATE_PART(EPOCH, )`	`TO_UNIXTIME()`
Convert string to date format	`STR_TO_DATE()`	`TO_DATE()`	`PARSE_DATE()`	`TO_DATE()`	`DATE_PARSE()`
Convert date to string format	`DATE_FORMAT()`	`TO_CHAR()`	`FORMAT_DATE()`	`TO_CHAR()`	`DATE_FORMAT()`

Here’s how we’d use these functions in Postgres, with expected inputs and outputs:

#Convert unix time to date format
SELECT TO_TIMESTAMP(‘1284352323’);
-- 2010-09-13T04:32:03.000Z
#Convert date format to unix time
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
-- 982355920
#Convert string to date format
SELECT TO_DATE('05 Dec 2000', 'DD Mon YYYY')
-- 2000-12-05
#Convert date to string format
SELECT TO_CHAR(DATE('2001-02-16 08:00:00-05'), 'DD Mon YYYY')
-- 16 Feb 2001

There’s a whole “language” of string formatting arguments that developers need to frantically search Google for every time they use them. Almost every single dialect follows the C strftime standard, except for Postgres. These are usually the same across TO_DATE() and FORMAT_DATE().

Date rounding

Date rounding lets you lower the specificity of your date; this is useful for aggregations and looking at trends over time. The prototype function here is DATE_TRUNC(), which truncates your date to a lower level of specificity, like month or year. Here’s how things look across dialects:

Function	MySQL	PostgreSQL	BigQuery	Redshift	Presto
Truncate date specificity	`DATE_FORMAT()`	`DATE_TRUNC()`	`DATE_TRUNC()`	`DATE_TRUNC()`	`DATE_TRUNC()`

For a more concrete example of when you’d use a date truncation function, imagine we have a table of order where each row represents an order, and each order has a date. If we want to look at order growth month over month, we’d use a truncation function in Postgres:

#Truncate date specificity
SELECT
    DATE_TRUNC(order_time, ‘month’),
    COUNT(*) as count_orders
FROM orders
GROUP BY 1
ORDER BY 1 DESC

The DATE_TRUNC() function grabs the month and year from the date so you can get a monthly view.

Date parts

If you have a timestamp, date part functions will pick out a particular part of that timestamp; this is useful if you want to display what day of the week a user logged in, what hour someone made an order, or aggregate event data by month to see which months of the year your website gets the most traffic. Our function prototype:

DATE_PART() – extract a specific part of a date, like the day of the week or year.

Here’s how it plays out across different types of SQL:

Function	MySQL	PostgreSQL	BigQuery	Redshift	Presto
Extract specific part of date	`EXTRACT()`	`DATE_PART()`	`EXTRACT()`	`DATE_PART()`	`EXTRACT()`

In Postgres, we can extract just the day of a user’s order using DATE_PART():

#Extract specific part of date
SELECT date_part('day', DATE('2001-02-16 20:38:40'))
-- 16

Each SQL dialect has different approaches for how to specify data parts to extract, so be sure to check the documentation linked in the table above.

Differences / Deltas

If you need to calculate the duration or difference between two dates, most SQL dialects have functions for that. The popular one here is DATE_DIFF():

DATE_DIFF() – get the difference between two dates in any specificity (days, years).

If you want to get the difference between two dates in days (i.e. how many days exist between date number one and date number two) you’d use something like DATE_DIFF(‘day’, ‘2019-01-01’, ‘2019-01-06’). Weirdly, in some languages, time deltas are their own data type (see, for example, the Pandas package in Python). Here’s how these functions line up:

Function	MySQL	PostgreSQL	BigQuery	Redshift	Presto
Get difference between two dates at some specificity	`DATEDIFF()`	Subtraction	`DATE_DIFF()`	`DATEDIFF()`	`DATE_DIFF()`

Here’s how we’d use this in Postgres:

#Get difference between two dates
SELECT DATE('2019-01-31') - DATE('2019-01-01')
-- 30

A closely related cousin to time deltas is interval functions.

Intervals

Intervals let you add and subtract time from dates in SQL. This kind of function is useful for calculating rolling widows, like filtering your data for every record in the past 7 days or the past year.

DATE_ADD() – add an amount of time to a date.
DATE_SUB() – subtract an amount of time from a date.
INTERVAL – use plus and minus signs to add time to a date.
PARSE_DURATION() – parse a formatted duration into a date. This is useful if you want to create a duration from scratch instead of subtracting two dates from each other.

The first two behave like normal functions, but in Postgres and Presto, using the INTERVAL operator is funky.

Function	MySQL	PostgreSQL	BigQuery	Redshift	Presto
Add time to a date	`DATE_ADD()`	`+ INTERVAL`	`DATE_ADD()`	`DATEADD()`	`DATE_ADD()`
Subtract time from a date	`DATE_SUB()`	`- INTERVAL`	`DATE_SUB()`	`DATEADD()` with negative values	`DATE_ADD()` with negative values
Extract date difference		`MAKE_INTERVAL()`			`PARSE_DURATION()`

The INTERVAL operator in SQL has unusual syntax: you usually need to use the keyword, follow it with a number in single quotes, and then a date interval. In Presto, for example, you can add one day to your date by using <some_date> + INTERVAL ‘1’ day. Notice how the string quotes are only around the quantity (1) and not the chosen interval (day).

If we wanted to see the number of orders users have made in the past 7 days:

#Count orders over the past 7 days
SELECT
    order_date,
    count(*)
FROM orders
WHERE order_date <= NOW() - INTERVAL ‘7’ day

And here’s how we’d use the MAKE_INTERVAL() function in Postgres:

#Extract date difference
SELECT MAKE_INTERVAL(days => 10)
-- { "days": 10 }

Time zones

Time zones are one of the biggest nightmares in date handling, and chances are you’re not sitting in UTC right now. Thankfully, most SQL dialects have a bunch of functions to handle TZ conversion. Our function prototypes:

AT_TIMEZONE() – add a timezone to a date. Useful if the date doesn’t have an existing timezone attached to it.
CONVERT_TZ() – convert between timezones. Useful if that date already has an existing timezone.

Timezones also rear their ugly head when dealing with string parsing and date formatting. Here’s how these functions line up across dialects:

Function	MySQL	PostgreSQL	BigQuery	Redshift	Presto
Add timezone to a date	`CONVERT_TZ()` or system settings	`AT TIME ZONE`	In strings	`AT TIME ZONE`	`AT TIME ZONE`
Convert date to timezone or convert between timezones	`CONVERT_TZ()`	`AT TIME ZONE`	`FORMAT_DATE()`	`CONVERT_TIMEZONE()`	`AT TIME ZONE`

Here’s how we’d add a timezone to a user’s click event or convert between timezones in Postgres:

#Add timezone to a date
SELECT DATE('2001-02-16 20:38:40') AT TIME ZONE 'America/Los_Angeles';
-- 2001-02-15T16:00:00.000Z
#Convert between timezones
SELECT DATE('2001-02-15T16:00:00.000Z') AT TIME ZONE 'America/Denver';
-- 2001-02-14T17:00:00.000Z

In a few of these dialects, you use the same function (AT TIMEZONE) to add a timezone to a date, as well as convert a date that already has a timezone to a different one. MySQL also has a dedicated function (UTC_DATE()) for converting dates to UTC time. For more about MySQL timezones, check out this wonderful cheatsheet.

Current times

The last big category of time related functionality is getting the current time. This kind of functionality is useful in similar cases as intervals, like building a rolling window. Because most of these functions return timestamps, they’re often used in conjunction with something like DATE_TRUNC to pick the right intervals.

NOW() – gets the current timestamp at query run time.

This is a pretty popular series of functions, so there are often a bunch of aliases. Here’s how things stack up across SQL flavors:

Function	MySQL	PostgreSQL	BigQuery	Redshift	Presto
Get the current timestamp	`NOW()`, `CURTIME()`	`CURRENT_DATE` or 10 others	`CURRENT_DATE()`	`CURRENT_DATE`	`NOW()`

General best practice is to keep all of your dates and timestamps in UTC time, and display them based on the client’s timezone. If we wanted to log the time that a user logs in using Postgres, here’s what our query might look like:

#Get the current timestamp
SELECT CURRENT_TIMESTAMP
#Display the timestamp to the user in PST
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles';

Keep in mind that some of these functions return a date that’s tied to the timestamp that your system is set to, which can vary pretty widely across database engines. Postgres returns UTC by default when using CURRENT_TIMESTAMP, so no worries here.

The truth is that as useful as some of these dialects are, scripting languages are often easier to parse dates in (Moment.js is a really popular one for Javascript). If you’d prefer to use more fluid programming languages like JS to work with your relational data that's already in a SQL database, give Retool a spin.

DEV Community: justin gage

CRUD with Firestore using the Node.js SDK

Setting up the Node.js SDK for Cloud Firestore

Basic CRUD with the Firestore Node.js SDK

Creating data with set()

Reading data with get()

Updating data with set() and update()

Deleting data with delete()

How to Build a React Form Component

Getting started / basics

Styling with CSS

Using a React component library

Validate your React form component

Making all fields required

Adding an address validator

Validating date

Validating order number

Communicate errors in your React form component

Adding an address error

Adding an order number error

Your React form component with react-hook-form

TL;DR: Syntax roundup

Create a simple React form component

Style your React form component

Validate your React form component

Add errors to your React form component

Full form

Other React form libraries

CRUD with the Firebase RealtimeDB's REST API

Realtime Database REST API basics

Creating data in the RealtimeDB

Reading data from the RealtimeDB

Updating data in Firebase RealtimeDB

Deleting data from the Firebase RealtimeDB

Data security for the Firebase RealtimeDB

Structuring data for the Firebase RealtimeDB

Getting started with react-datepicker

react-datepicker basics and setup

Getting started

Create a datepicker range

Selecting times

Extend your datepicker with more features

Extracting data from the datepicker

Your datepicker component with react-datepicker

TL;DR: Syntax roundup

Create a simple datepicker

Create a datepicker range

Selecting time with datepicker

Add a placeholder

Disable future dates

Add a clear button to the datepicker

Building a React Table Component with react-table

Intro: react-table

Build a simple table with react-table

Table

Headers

Table Body

Give your table a custom UI

Using the style Prop

Using styled-components

Using a React Component Library

Extend your table with more features

Sorting

Filtering

Pagination

Your Table Component with react-table

What's an ACID compliant database?

All the things that can go wrong with your transactions

Dirty Reads

Non-Repeatable Reads

Phantom Reads

ACID, but the good kind

ACID concepts in NoSQL and distributed systems

Isolation levels and locking in relational databases

Why database locking and isolation exist

How database locking works

Levels of database isolation

Problems you’ll run into with database locks

Formatting and dealing with dates in SQL

SQL dialects and function prototypes

Creating data with `set()`

Reading data with `get()`

Updating data with `set()` and `update()`

Deleting data with `delete()`

Your React form component with `react-hook-form`

`react-datepicker` basics and setup

Your datepicker component with `react-datepicker`

Using the `style` Prop

Using `styled-components`