The dropdb program executes the DROP DATABASE statement behind the scenes. PostgreSQL also provides a utility program named dropdbthat allows you to remove a database. In this case, you need to disconnect from the database and connect to another database e.g., postgres to execute the DROP DATABASE statement. In addition, you cannot execute the DROP DATABASE statement if the database still has active connections. Only superusers and the database owner can execute the DROP DATABASE statement. This action cannot be undone so you have to use it with caution. The DROP DATABASE statement deletes catalog entries and data directory permanently. Use IF EXISTS to prevent an error from removing a non-existent database.Specify the name of the database that you want to delete after the DROP DATABASE clause.DROP DATABASE database_name Code language: SQL (Structured Query Language) ( sql ) reltablespace LEFT JOIN pg_description As d ON ( d. relnamespace LEFT JOIN pg_tablespace t ON t. description FROM pg_class As c INNER JOIN pg_attribute As a ON c. relname # get all the comments for the contacts table SELECT a. description FROM pg_class As c LEFT JOIN pg_namespace n ON n. relkind = 'v' THEN 'view' ELSE 'table' END As type, pg_get_userbyid ( c. # get the all the table comments in your DB SELECT c. To generate documentation which could be pushed to a wiki, html, confluence, or elsewhere you can iterate through a tables columns and fetch the comments. You can add descriptions to call out deprecated fields, gotchas, planned refactorings, or add historical context that may be helpful to the next developer trying to understand what the field means.Ĭlass AddContactComments < ActiveRecord :: Migration def change msg = 'Contacts table holds individual details about our contacts, it is associated with leads and customers' change_table_comment ( :contacts, msg ) change_column_comment ( :contacts, :first_name, 'the contacts first name' ) change_column_comment ( :contacts, :last_name, 'the contacts last name' ) change_column_comment ( :contacts, :house_latitude, 'the house_latitude the contact lives at' ) change_column_comment ( :contacts, :house_longitude, 'the house_longitude the contact lives at' ) change_column_comment ( :contacts, :house_location_accuracy, 'the accuracy range we captured the GPS with' ) change_column_comment ( :contacts, :deleted_at, "the date the contact was 'hidden' from our DB" ) # call out gotchas # tasks have assignee_id while contacts still use agent_id in the DB, this is a recommended refactoring msg = 'the agent_id field is for who the contact is currently assigned to, various places in the code and API it is referenced by assignee_id' change_column_comment ( :leads, :agent_id, msg ) end end Ruby DB Docs Access MigrationĪ migration adding comments to a previously existing table. Postico OSX Postgres client showing comments as you explore DB structure Code Samplesīelow are some code samples to help you get started with a workflow around database documentation. Either from Rails, CI, or any other tool in your workflow (see examples below). It is easy to generate and push to documentation repositories (markdown, html, confluence). The documentation is embedded in most DB explorer tools (SQL workbench, Postico, etc).Documentation can be reviewed as part of PRs by a data team, analysts, or other folks who might be the target documentation audienceīy having the documentation embedded in the database directly, other values can be unlocked.Search in code editor tools (and github).See DB comments change over time because they are part of Git.When we embed our database documentation in our standard code change process we easily get many advantages. We want to do this in source code that is trackable for the same reason we run database migrations in Rails opposed to just having a DBA make schema changes outside of our application code change process. I also think we want to add human context to our information in same tools we build our database. I believe documentation close to code and embedded in the code change process has a better chance of staying up to date and relevant. Why would we want to use Rails to build our database documentation? For complete guide to the PostgreSQL database engine, refer to. The feature was also well covered in Rails 5 supports adding comments in migrations by Prajakta Tambeĭocumentation Embedded with Change Process.The best documentation about the feature is contained in the original Pull Request support comments in database.This feature makes it easy to keep your database documentation up to date as you can add descriptions at the same time as you add or update tables and columns. This post will cover how to use the Active Record feature to add comments to your tables, columns, and indexes. Documentation Folders CC image from Pixabay Active Record Database Documentation
0 Comments
Leave a Reply. |