Posted on Oct 6 In today's article I will give you some information related to the creation and application of migrations, as well as the definition of Table schemas and how to interact with the database itself using Drizzle ORM.In the past I had used Drizzle multiple times but had never written a specific article for it that could serve as a guide for new users. Addressing different topics, with examples and links to documentation.It is expected that you have a basic knowledge of Node.js and that you have used an ORM or Query Builder in the past. As well as basic knowledge about relational databases.First we need to install the necessary dependencies:As you may have noticed in the previous command, in today's article we are going to use the SQLite dialect, so that as many people as possible can try it out without having a process running.Additionally, we need to install the following dependencies for the development environment:With the dependencies installed, we can move on to drizzle configuration so that the paths to the database schema can be defined and in which path the migrations should be generated.The default file name is drizzle.config.ts, it is worth mentioning that the file name may be different, however when running drizzle-kit the --config= flag must be specified with the file path configuration.Speaking of the configuration file, the database schema paths, the migration path, which driver should be used and the SQLite database path were defined. Last but not least, the verbose and strict properties are ideal if you want to have a prompt with more information when migrations are applied.With this we can now move on to the next point which is the definition of the schema of the database tables. Drizzle contains a collection of primitives that are specific to each dialect. Taking the following table as an example:In the code above we have a table called users that contains two columns. We have the id column whose data type is integer, which is an auto-incrementable primary key. Just like the username column, which has data type text, it must be unique and not null.This time we will create a second table, which must contain a one-to-many relationship. Let's take into account the following table:In the code snippet above we have a table called tasks that has the following five columns:Now let's take into account the following use case:"We will often query taking into account the start and end columns, just as these columns must be unique taking into account the user_id."Taking into account the case indicated in the previous paragraph, the ideal would be to define that the start and end columns should be indexed, as well as a constraint should be created between these columns and the user_id to ensure that they are unique. Like this:Not forgetting to mention that in the definition of the foreign key we have specified that when the user is deleted, all rows related to the user must be removed.With both tables defined, we need to specify the relationships between them. I had just mentioned that the relationship would be one-to-many, we can define this as follows:In the code snippet above we specify the relationships between the tables and to which columns of each the keys should be mapped.The users table can contain several tasks but a task must only be associated with one user. And in this way, the contraint that was created in the tasks table between the start, end and user_id columns is also formalized.With the database tables defined and the relationships between them specified, we can now create the first migration, to do so simply execute the following command:The above command takes into account the drizzle.config.ts file that we had created at the beginning of the article and it is in this command that the --config= flag must be specified if another name is given to the file.The expected behavior is that a folder called /migrations is created with the newly created migration.If it was successful, we can now apply this same migration that was created by running the following command:The expected behavior is that the migration that will be applied will be shown in the terminal and a prompt asking whether we want to apply these same changes. For this same reason, the verbose and strict properties were added to the Drizzle configuration file.With the migrations pulled into the database, we can move on to the next step, which involves creating the database client. Which may look similar to the following:Taking into account the code snippet above, one thing worth highlighting is the import of chemas from the database that can be used to have intellisense in the text editor. You will soon feel the benefits of this last point.Using the .insert() method we can define which table we want to add a new row to, and using the .values() method we can define the data to be inserted. This data can be an Object if we want to add just one row or an Array if we want to add multiple rows.In the example above, what is returned from Promise is just some metadata, such as changed rows, etc. If you want the datums of the inserted rows to be returned, you can use the .returning() method.If we follow the schema of the users table, an error is expected to occur with the insertion of the user called Foo, this is because it was added in a previous example to this one. To do this, we can use the .onConflictDoNothing() method if we do not want an error to be thrown when a conflict occurs, this is because it had been specified that the username must be unique.If we want to update a specific user, we can use the .update() method where we specify which table the update should be made on. Just as we should take advantage of the .set() method to define which columns should be changed and which row using the .where() method. This way:On the other hand, if we want to delete a row we can take advantage of the .delete() method where we must specify which table this operation should be performed on.One thing that must be taken into account is that if the .where() method is not used, all rows in the table are removed.To obtain all the rows of a table, it can be done in the following ways:In the code snippet above, we can use method chaining in order to specify which columns can be selected (in the example above, all of them) and which table this should be done to. While the second approach offers a very similar experience to other ORM's.Using the query examples from now on, if we want to obtain a row taking into account a column and specific datum we can do it as follows:Another interesting point is that using this API we can also query datums of relationships from other tables, such as obtaining the user and their tasks. This way:If we want something more granular, we can select which columns from the users table and the tasks table should be returned in the query, as follows:In the previous example, from the users table we selected the username column while from the tasks table we selected the id and name columns.With this I conclude the article, the objective was to give an overview of some things that I did not cover in other articles in which I used Drizzle and felt it was necessary. At least to try to help you in the first twenty or thirty minutes of use.I hope you found this article helpful, whether you're using the information in an existing project or just giving it a try for fun.Please let me know if you notice any mistakes in the article by leaving a comment.Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well Confirm For further actions, you may consider blocking this person and/or reporting abuse Sobin George Thomas - Oct 4 Eric - Sep 27 M Adeel - Sep 22 revoltez - Sep 26 Once suspended, franciscomendes10866 will not be able to comment or publish posts until their suspension is removed. Once unsuspended, franciscomendes10866 will be able to comment and publish posts again. Once unpublished, all posts by franciscomendes10866 will become hidden and only accessible to themselves. If franciscomendes10866 is not suspended, they can still re-publish their posts from their dashboard. Note: Once unpublished, this post will become invisible to the public and only accessible to Francisco Mendes. They can still re-publish the post if they are not suspended. Thanks for keeping DEV Community safe. Here is what you can do to flag franciscomendes10866: franciscomendes10866 consistently posts content that violates DEV Community's code of conduct because it is harassing, offensive or spammy. Unflagging franciscomendes10866 will restore default visibility to their posts. DEV Community — A constructive and inclusive social network for software developers. With you every step of your journey. Built on Forem — the open source software that powers DEV and other inclusive communities.Made with love and Ruby on Rails. DEV Community © 2016 - 2023. We're a place where coders share, stay up-to-date and grow their careers.