Postgres with Drizzle · Chapter 2 of 40
The schema forge
By the end of this chapter you can ask Postgres what a table really looks like: its columns and their types, its constraints, its defaults, and whether it exists at all. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Foundations
- 5 tasks
- PostgreSQL
- Free chapter
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.
Why you need this
Rohan is writing the data dictionary for the analytics team: every column of the users table, its type, whether it may be empty, which constraints protect it and which columns fill themselves in. Anya wants something smaller: when the app starts, it should refuse to serve anyone if the users table is missing, because that means a migration did not run.
You could open src/schema.ts and copy it out by hand, but that file says what the code expects, not what the database has. A migration that never ran, or a column someone added in psql, is invisible there. Postgres keeps an exact description of every table, and you can query it with the pool.query you used in chapter 1.
The lesson
Columns: information_schema.columns
This view has one row per column of every table: its table_name, column_name, ordinal_position (its place in the table), data_type, is_nullable ('YES' or 'NO', as text) and column_default, plus more detail such as udt_name, the short type name, and character_maximum_length, the limit of a varchar:
const result = await pool.query(
`SELECT column_name, udt_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'tags'
ORDER BY ordinal_position`,
);
console.log(result.rows);
[
{
column_name: 'id',
udt_name: 'int4',
character_maximum_length: null
},
{
column_name: 'name',
udt_name: 'varchar',
character_maximum_length: 100
},
{
column_name: 'posts_count',
udt_name: 'int4',
character_maximum_length: null
}
]
The keys of each row are exactly the names you select, so pick the view's own column names: rename one with AS and the check cannot find it. A misspelt table name is no error, just an empty array.
Constraints: information_schema.table_constraints
One row per constraint, with its constraint_name and constraint_type: PRIMARY KEY, UNIQUE, FOREIGN KEY or CHECK. Postgres also lists every NOT NULL column here as a CHECK with a machine-made name, so this example leaves those out:
const result = await pool.query(
`SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'comments' AND constraint_type <> 'CHECK'
ORDER BY constraint_name`,
);
[
{
constraint_name: 'comments_author_id_fkey',
constraint_type: 'FOREIGN KEY'
},
{ constraint_name: 'comments_pkey', constraint_type: 'PRIMARY KEY' },
{
constraint_name: 'comments_post_id_fkey',
constraint_type: 'FOREIGN KEY'
}
]
Yes or no: EXISTS
EXISTS (subquery) is true when the subquery finds at least one row. Does the posts table have a likes_count column? And a shares_count?
for (const name of ['likes_count', 'shares_count']) {
const result = await pool.query(
`SELECT EXISTS (
SELECT FROM information_schema.columns
WHERE table_name = 'posts' AND column_name = $1
)`,
[name],
);
console.log(name, result.rows, typeof result.rows[0].exists);
}
likes_count [ { exists: true } ] boolean
shares_count [ { exists: false } ] boolean
($1 is a parameter: the second argument of pool.query fills it in.)
The column is called exists, and its value is a real JavaScript boolean. Return the row instead and the check says expected { exists: true } to be true.
Only the rows with a value
column_default holds the default as the text of its expression (now(), '0', nextval(...)), or NULL when there is none. Here are the posts columns that may not be empty:
`SELECT column_name, column_default
FROM information_schema.columns
WHERE table_name = 'posts' AND is_nullable = 'NO'
ORDER BY ordinal_position`
[
{
column_name: 'id',
column_default: "nextval('posts_id_seq'::regclass)"
},
{ column_name: 'author_id', column_default: null },
{ column_name: 'content', column_default: null },
{ column_name: 'type', column_default: "'text'::post_type" },
{ column_name: 'likes_count', column_default: '0' },
{ column_name: 'created_at', column_default: 'now()' },
{ column_name: 'updated_at', column_default: 'now()' }
]
To drop the NULLs, column_default <> NULL looks right and returns []: nothing is equal or unequal to NULL. SQL asks with IS NOT NULL.
Counting: a string, not a number
`SELECT count(*) FROM information_schema.columns
WHERE table_name = 'users'` // [ { count: '16' } ]
`SELECT count(*)::int AS columns FROM information_schema.columns
WHERE table_name = 'users'` // [ { columns: 16 } ]
count is a bigint, which can be larger than JavaScript numbers can hold, so the driver hands it over as text. ::int converts it in SQL.
Your turn
Your five tasks are at the top, all in exercise.ts, one function each. expected 0 to be greater than 0 means your query found no rows: check the table name. expected 'string' to be 'number' is the count arriving as text. For the table count, only real tables count: views such as geometry_columns sit in the same list with another table_type.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
getTableColumns: every column of users
Write getTableColumns(). It reads information_schema.columns for the users table.
Return an array with one object per column of users, each with the keys column_name, data_type and is_nullable, named exactly so. The id column must be among them. Order by ordinal_position (the order the table defines its columns); the check itself accepts any order.
- Task 2
getTableConstraints: the constraints on users
Write getTableConstraints(). It reads information_schema.table_constraints for the users table.
Return an array with one object per constraint, each with the keys constraint_name and constraint_type. Keep every type: the primary key must be among them, along with the unique and check constraints. Order by constraint_name; the check accepts any order.
- Task 3
verifyUsersTableExists: is the users table there?
Write verifyUsersTableExists(). It asks information_schema.tables whether a table named users exists.
Return the JavaScript boolean true when it exists (false when it does not): not a row object, not a number, not a string.
- Task 4
getColumnDefaults: the columns that fill themselves
Write getColumnDefaults(). It reads information_schema.columns for the users table and keeps only the columns that have a default value (their column_default is not NULL).
Return an array of objects with the keys column_name and column_default. created_at, whose default is now(), must be among them. Order by ordinal_position; the check accepts any order.
- Task 5
countTotalTables: how many tables?
Write countTotalTables(). It counts the tables in the public schema of information_schema.tables, counting only real tables (table_type is BASE TABLE), not views.
Return the count as a JavaScript number, not a string. There are at least ten.
What you will learn
- information_schema: a set of views that describe every table, column and constraint, queried like ordinary tables.
- EXISTS: a yes or no answer that arrives in JavaScript as true or false.
- IS NOT NULL: keeping only the rows where a column has a value.
- **count(*) comes back as a string**, and how to make it a number.
More in Foundations
Build The schema forge yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.