Constrain Numeric or Integer inputs
I've reached the point where I'm creating a Database Schema using Drizzle ORM with a Postgres SQL Database on a NodeJS backend.
I read the Drizzle Documentation, but I still don't get the full details of really setting up connection to my Database.
Then I watched all the possible Drizzle Videos on Youtube.
Unfortunately, only the video from Kyle ( https://youtu.be/7-NZ0MlPpJA ) -- got me started on Drizzle because it is the only video that was intended to teach the beginner, and did not assume that we already know something somewhere.
THE PROBLEM
1.) Phone or Mobile numbers are 11 Digits.
How do I constrain the Phone Field in my Postgres Schema to receive only 11 Digits either of the Numeric or Integer Data Type in Postgres?
2.) Which Data Type do I use to store Images, for my users?
3.) If I am asking for Social links-- where my users are expected to enter more than one Social link, is it Json Data Type or Array data Type, or which Data type should I use for this?
Regards.
Web Dev Simplified
YouTube
Learn Drizzle In 60 Minutes
Drizzle is a relatively new database ORM that aims to solve many of the problems with existing ORMs by being more flexible and performant. They have definitely accomplished their goals, but learning Drizzle can be quite overwhelming. In this crash course I will teach you everything you need to know about Drizzle to use it in your very own projec...
2 Replies
Hi I am really new to drizzle but what you ask has nothing to do with the drizzle but with the database storage in general so I'll try to give you some ideas 🙂
1. you shouldn't be thinking about the phone numbers as number since you most likely don't want to perform any mathematical operations with them. The best storage for the phone numbers in my opinion is just simple text - which you can very easily constraint to 11 characters.
Then you can add some phone number validation on top of it to be sure that user inputs only numbers (or even better
+ character followed by numbers).
I suggest to use phone package (https://www.npmjs.com/package/phone) to validate E.164 format - this format works with most of the standardized SMS gateways etc.
2. generally you don't want to store the images within the database itself but rather store it on some sort of disk and then within the database only store the path to that file.
In general you should use some object storage for storing the images like S3 from AWS (or basically any other cloud provider - all of them has some object storage). You can use some network disk, again AWS alternative is EFS. The last resort is to store images on the server disk (HDD or SSD) but this won't scale very well so it is a good idea to store images in some object storage (and maybe serve them through CDN) from start.
If this sounds like overengineering and you just have very simple project you can store images in the database as BLOB which is suitable for binary data - https://www.postgresql.org/docs/current/datatype-binary.html - this might get out of hand when you will try to scale your application since every image request will go against your database and your database will grow exponentially once you have enough users. I don't recommend this.npm
phone
With a given country and phone number, validate and format the phone number to E.164 standard. Latest version: 3.1.49, last published: a month ago. Start using phone in your project by running
npm i phone. There are 242 other projects in the npm registry using phone.PostgreSQL Documentation
8.4. Binary Data Types
8.4. Binary Data Types # 8.4.1. bytea Hex Format 8.4.2. bytea Escape Format The bytea data type allows storage of binary …
3. JSON data type is OK for this in general but since you are already using the relational database I would suggest to create separate table for SocialMediaLink and do a one to many relation between your user table and this table. See an example of one to many relation here https://hasura.io/learn/database/postgresql/core-concepts/6-postgresql-relationships/
PostgreSQL Relationships | One to One, One to Many, Many to Many | ...
There are 3 types of table relationships in a relational database like PostgreSQL. The relationships can be enforced by defining the right foreign key constraints on the columns.